Re: How to apply archive logs on an cold backup

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 01 Sep 2004 20:57:43 +1000
Message-ID: <4135ab46$0$32424$afc38c87@news.optusnet.com.au>

Cris Carampa wrote:

> Tim Kearsley wrote:
>

>> 4) If you have a control file creation script (e.g. the output of a
>> BACKUP CONTROLFILE TO TRACE)

>
> Can't you use the binary control file that comes with your old, cold
> backup?
>
> Sorry if it's a trivial question.
>
> Kind regards,

It's not a trivial question at all, since the level of ignorance exhibited on this issue by other posters has, frankly, astonished me.

There are three ways of recovering control files. In descending order of desirability, and in ascending order of cost and complexity, they are:

1. Multiplex them first, and if one of the multiplexed set gets lost or corrupted, copy back one of the surviving ones. This does not generally get regarded as a recovery, simply because there is no 'recover database' command issued at all. The database is shut or in nomount state for the duration of the copy, but since the controlfile is rarely more than a few megs in size, the downtime is really quite trivial.
2. Re-create the missing control files by executing a 'create controlfile' command. This command is quite syntactically complex, and it's hard to get it right. Fortunately, Oracle provides the 'alter database backup controlfile to trace' command to generate a SQL script that gets all the syntax correct for you. Recovery of the control file therefore simply becomes a matter of editing the script to remove any non-SQL commands, and then executing it. The create controlfile command re-creates all the control files mentioned in the init.ora or the spfile.

The create controlfile command, and the script which the backup to trace command generates, do NOT require 'recover database using backup controlfile' syntax at recovery time, because you are not actually using a backup control file. You are asking the instance to re-create a brand new control file, and that only requires the 'recover database' syntax.

3. Restore a binary copy of the control file. The copy can be taken when the database is fully closed, or with the 'alter database backup controlfile to '/path/filename' command. Use of a binary image of the control file in a recovery scenario REQUIRES that you use the 'using backup controlfile' syntax of the recover database command. It is required, because the SCN in the header of the control file will be behind the last SCN in the online redo logs, or the headers of the data files. Normally, it is the SCN in the header of the control file which is used to determine to what SCN the logs and the data files should agree. So when it's the control file itself that is at fault, you *have* to flag that fact to Oracle, which is what the 'using backup controlfile' clause does. Essentially, it acts as a flag which says "Pay no attention to the control file's SCN, because I know it to be wrong, because I just restored a copy which I know to be out of date".

It is for this reason that the clause is NOT required in option 2. When the control file is being re-created from scratch, as in option 2, the instance will read the SCN from the headers of all the data files and the last part of the current redo log (the locations of which must be specified in the create controlfile syntax). The highest SCN found is deemed to be the latest point in time to which the database must ever have gotten, and that number is therefore written into the header of the freshly-created control file. Therefore, the new control file is NOT out of date compared with the data files and redo logs, and therefore the 'using backup controlfile' syntax IS TOTALLY UNNECESSARY.

Which will be why the tracefile script generated by Oracle itself does NOT include it.

Option 3 is also the least desirable option of the lot because it MUST and INEVITABLY WILL require the user to issue an 'alter database open resetlogs' command when recovery has finished. A resetlogs option means that until a new, complete backup has been taken, a single further media failure on the database cannot be recovered from. Therefore, commonsense and prudence require that option 3 be followed by an immediate shutdown of the database and the performance of a complete cold backup.

A resetlogs also renders all prior backups and archivelogs useless, except under very peculiar circumstances. So the cost of option 3 is immense, and only an idiot would post it as the solely-mentioned answer to someone asking how to recover a database (never mind how to recover from the loss of a controlfile).

"Using backup controlfile" is not a set of three words that you apply to a recover database command when the mood takes you, or because it is an alternate Thursday, or because your backups were taken cold, or because you like your coffee hot. They are used under a specific set of failure conditions. Not one of which the original poster in this thread mentioned applied to him.

And so, in answer to the specific question *you* asked: yes, of course you could restore your old, cold backed up control file. If all your controlfiles had been lost, and this was the only backup of them you had available. But you are now in scenario 3, and that is the worst of the scenarios to be in.

Regards
HJR Received on Wed Sep 01 2004 - 05:57:43 CDT