Re: backup without archivelog mode

"Sean M" <smckeownNO_at_BACKSIESearthlink.net> wrote in message news:3C9D0D7E.8E08F33E_at_BACKSIESearthlink.net...
> "Howard J. Rogers" wrote:
> >
> > True enough, though there isn't a nologging transaction in existence
which
> > cannot be recovered by some other means (index rebuilds, for example).
The
> > database simply ignores the nologging keyword for anything which
couldn't
> > possibly be recovered (such as regular DML). So even a nologging
> > transaction *is* recoverable.
>
> Now who's nitpicking (are not unrecoverable transactions, by definition,
> unrecoverable?)? But fine, I'll play along. If "by some other means"
> you're referring to repeating the original operation (direct SQL*Loader
> jobs, direct load inserts, etc.), then I agree, you can "recover" it.
> But that's true of *any* transaction, logging or not. Any transaction
> is "recoverable" under your definition by simply doing it over again.
> The trick is knowing/remebering what you did.
>

But the point is that for a regular piece of DML, you would indeed have to remember it to reperform it. The updated data doesn't exist anywhere else other than in the table. Hence redo is needed to reperform it. But Direct SQL Loads don't need to be remembered at all. The text file which was the source of the load still exists (we presume: at least, it's bad practice to delete it until a new backup of the relevant tablespace has been performed). All you'd need to do is re-perform the load.

So no, not all transactions are "recoverable" under my definition. Regular DML is only recoverable through redo, since the source of the transaction is only in your head. Transactions which respect the nologging keyword don't need redo, because the source data is available in tangible form elsewhere.

> > > > But: You can never, ever make a copy of any database file whilst
the
> > > > database is running, without that copy being internally
inconsistent.
>
> You're not trying to defend this statement, are you?
>

I don't have to defend anything. You seem to make a habit of leaping on posts to make slight and inconsequential points about them, to prove you're cleverness I suppose. Read my post in context, and it is quite clear what the point is: the qualification I included below was that you can't copy hot files without them being internally inconsistent. Seizing on a sentence and ripping it out of the context of the entire post gets you nowhere.

> > > > If you want to take hot backups (which your use of the 'alter
tablespace
> > > > Florence begin backup' command suggests you want to do) then you
will
> > > > absolutely, positively have to be in archivelog mode. There is no
> > exception
> > > > to the rule that hot files cannot simply be copied.
> > >
> > > Except for datafiles belonging to read-only tablespaces.
> > >
> >
> > Be definition, read-only tablespaces are not hot.
>
> By whose definition? The files are part of a running database. The
> files are available to users queries. Arguing semantics at this stage
> is bit late. Fine, though, we can uses Oracle's definition of a "hot
> backup" from the Backup and Recovery Guide: "You can perform a hot
> backup, that is, a backup made while the database is open and available
> for use." Read-only datafiles are available to the users in a running
> database. And yet, one can simply copy them.
>

You'll note that the quote you supply talks about the *database* being available for use. Not the datafile. You can't therefore say 'read only datafile X is available for use, therefore it's hot'. The state of the database tells us nothing, in and of itself, as to the temperature of the datafiles of which it is composed.

If you have the slightest understanding of why a database file cannot be copied whilst hot without it being internally inconsistent, which I am sure that you do, it is blindingly obvious that "hot" refers to files whose internal contents are changing, and not just because you are performing DML, but because CKPT updates the contents of the header block from time to time. If you or CKPT are not changing the contents of a file, it cannot possibly be inconsistent, can it?

That definition is not mere semantics, but arises from a simple understanding of what the essential problem with O/S copies of database files is.

Therefore read onlys are cold. As are offline files. Because not even CKPT is allowed to update them, and no user can either. It's got sod-all to do with whether the contents are accessible to users, and everything to do with whether the contents are changing.

> Offline-normal, I'll grant you, is a bit more of a grey area. The
> database is still running (i.e. "hot"), the backups are still good by
> simply copying the files, but the particular files are not "available
> for use."
>

As I said, it's got sod-all to do with whether the contents are accessible to users, and everything to do with whether the contents are changing. Therefore, they aren't a grey area at all. They are stone-cold cold.

> I wouldn't "nitpick" if you hadn't used such strongly absolute language
> "never, ever," "any database file," and "there is no exception." Maybe
> not the best words when you're trying to give "generic" advice.
>

There *is* no exception to the rule that you can never, ever, simply copy a hot database file without its contents being internally inconsistent. You want to nitpick that I missed the word "hot" out of one sentence, when I included it three sentences later. Go ahead, but you waste my time and yours "correcting" things that don't need correcting.

The original poster (remember him, and why we're here?) was asking whether the fact he didn't perform regular DML but merely performed bulk loads meant he could just copy the files. I told him no. I assume that you don't actually disagree with that advice?

Now, if you just want to keep making "smart" comments that purport to demonstrate the depth of your wit and wisdom every time I post, go right ahead, but I'd rather actually try and provide useful advice to people asking for help. Perhaps you could try and do likewise, correct advice that is plain wrong, and move on when the only thing at stake is word-play.

HJR
> Regards,
> Sean
Received on Sat Mar 23 2002 - 17:58:10 CST