Re: backup without archivelog mode

"obakesan" <cjundieseastwd_at_powerup.com.au> wrote in message news:7emn8.11476$T4.89487_at_nnrp.gol.com...
> HiYa
>
> In article <a7jncp$jro$1_at_lust.ihug.co.nz>, some kind human wrote:
> >Export doesn't count as a backup. It's a *logical* backup, true enough.
> >But that's not the same thing as a physical backup involving the copying
of
>
> I'm a little vague here, on inconsistent. If there is no activity on the
> database, for hours at a time (after the loads, before the users start
reporting
> work) what will be missing from the database?
>
> I thought that inconsistent meant that there could be activity taking
place
> during the export, and so that would be missed. Is there some other
meaning?
>

I think I've seen another post from you where you sort of say 'ah! that's why', so apologies if I wasn't making myself clear, but now I hope you see the actual problem, which is that even if you are not performing transactions, Oracle is doing things to the datafiles all the time in the background (principally, checkpointing them, and writing to the header block of the datafiles. It's that activity which means you can't rely on a physical copy of the data file being internally consistent. You might get away with it. But you might not. And if a backup strategy is going to be any good, it needs to yield reliable results.

> I understand that its a logical backup, but in reality for this type of
> datawarehouse usage, which would be faster to restore?
>
> would there be a difference?
>

Definitely. But they're complementary strategies, not mutually exclusive. One of the problems with relying on exports is that in a data warehouse, it's going to be quite something to perform the export in the first place: the dump file is going to get seriously large. You'll maybe have to be into things like named pipes or be selective about what you export in order to not hit filesystem size limits.

Assuming you resolve that issue, though, exports are a reasonable way to resolve user stuff-ups on a particular table (an accidental truncate, for example). The physical backups are rather more useful ways of resolving hardware/media problems (corrupt blocks, deleted files, that sort of thing). So they're really addressing different situations, both have their strengths, both have their weaknesses (recovering with import means the table is out of date, for example, and you'll need to repeat loads you performed that affected it; on the other hand, applying redo to a restored datafile means having to recover, say, 100 tables rather than 1 -but all 100 will be bang up-to-date at the end of the process, so no loads need to be repeated).

> those are the gut points I am trying to get at here ... I asked because I
dont
> have the experience to know myself (you may recall me just learning about
> hotbackup last month)
>

Fair enough. It's just that it's hard to be definitive on these sorts of comparisons, because it depends so much on the volume of your data, and so on.

Re-reading your original post (which seems an awfully long time ago!), the issue I saw was that you were thinking of simply copying datafiles nightly, without the 'begin backup' and 'end backup' commands, and not taking archives. Hopefully it's now clear that you can't do it like that if the database is up and running.

Now there's a question of whether export/import would be a suitable alternative. My gut feel on that is no, not for a particular range of failure scenarios. Export can be ponderous, the dump files huge, and import can sometimes seem to have a mind of its own. Dump files have a habit of being treated like text files by certain DBAs of my acquaintence (risking loss of the dump file). And, as snapshots, even when import does its stuff without incident, you're left with an out-of-date table that needs transactions/loads repeated to bring it up-to-date. Exports/Imports beat having to do an "incomplete recovery" (with redo) any day, but that's a fairly specialised use.

As I say, they're complementary strategies, not mutually exclusive.

Regards
HJR
> thanks again for all your time folks
>
>
>
>
> See Ya
>
> --
> (when bandwidth gets better ;-)
>
> Chris Eastwood
> Photographer, Programmer, Motorcyclist and dingbat
>
> please remove u n d i e s for reply
Received on Sun Mar 24 2002 - 13:51:39 CST