Re: Hot Backup and Recovery - When to switch log files.

Paul Drake wrote:

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<40a27562$0$31367$afc38c87_at_news.optusnet.com.au>...
>

[snip]

>>>(tested but not possible as the office has burnt down and I do not have a
>>>redo backup) or I suppose I could cancel at this point (not tested).
>>>
>>>This is my hot backup script:
>>>
>>>for all tablespaces
>>>{
>>>    alter tablespace c1.ts begin backup;
>>>    copy data file to tape
>>>    alter tablespace c1.ts end backup;
>>>}
>>>
>>>alter system switch logfiles (??? Is this in the correct place ???)
>>
>>It's fine.
>>
>>
>>Regards
>>HJR

It really makes no difference, because it's a *backup* control file. It could have been backed up 400 days ago, and yet still be used to recover a database to the way it was yesterday.

That's why the phrase 'using backup controlfile' is there in the command, of course. Translated, it says "please pay no attention to me at all, except for the fact that SMON can use my pointers to head directly off to the data files and check their SCNs to work out what to do".

The only "requirement" for switching logs (there is no reason to actually do so at all, of course) is that the log switch should happen after the data files are backed up. The current redo logs may contain redo which will be needed to make coherent the data file backup just performed. By forcing a log switch, you ensure that redo gets put into the archived logs and your latest data file backup is therefore guaranteed usable. (The reason I say there is no need to force a log switch at all is that if you lost your current redo log under whatever circumstances you'd be doing an incomplete recovery, and you can always use the night-before-last's backup to perform that if last night's has been rendered non-useful. But people tend to get the idea that that sounds slightly scary, so if they want to throw in extra log switches, they might as well. 9i RMAN, for example, throws in an extra log switch automatically at the end of every backup or copy command, IIRC).

> As switching the logfile before creating the backup controlfile
> doesn't really do anything additional, I'd just switch it after and
> make sure that log is available during recovery.

I am getting increasingly depressed, and frankly frustrated, by the "it doesn't do any harm" school of database management. These people should just put up a neon sign saying "I don't know actually how Oracle works, but at least doing things this way Doesn't Do Any Harm". Maybe it does and maybe it doesn't, but instead of just floundering around in the dark hoping to do no harm, these people should try and actually understand how Oracle works in the first place, and therefore base their actions and advice on a foundation of known facts and comprehension.

If you *understand* what the 'using backup controlfile' command meant, you wouldn't, in other words, have said that. A backup controlfile is not what governs the application of redo in a recovery scenario, and therefore an additional redo log switch before, after or at any other time, has no possible bearing on the matter.

If you *understand* backup and recovery, you will know why the log switch is there in the first place (to make backup *data files* useful in a future recovery). Therefore, as I said to the original poster, his backup script is just fine.

I think I would even prefer people to have the wrong understanding of Oracle and make really bad suggestions to users than that we flounder around in a miasma of 'it doesn't do any harm' ignorance. At least bad advice can be tested and disproved and corrected.

How can anyone test, disprove or correct "Oh well, either way, it's up to you, it doesn't do any harm"??

We might as well all ditch Oracle and install SQL Server if we adopt that attitude.

>
> A test exercise is in order (of course).

That is a *lot* more like it, and a very good idea.

Regards
HJR
>
> Paul
Received on Thu May 13 2004 - 17:12:58 CDT