Backup Control Files - Are they special?

I read an article today that talked about backup control files. Typically, DBAs don’t deal with backup control files on a daily basis so I thought I would take a look. This article asserts that Oracle requires a RESETLOGS operation after performing recovery using a backup control file because –

Oracle needs to update certain structures in the backup control file before opening the database

I certainly agree that Oracle will update the control file when opening the database and resetting the logs. But, let's try to understand what is updated and why. Before we talk about the criticality of the RESETLOGS post-recovery, using a backup control file, let’s try to define what a backup control file really is. Better yet, let’s first list what a backup control file is NOT:

· An OS image of the control file taken while the database is closed.
· An OS image of the control file taken while the database is open (I just shuddered).
· An OS image of the control file taken while the database is mounted.
· A trace backup of the database control file (not unless a can of tomato sauce, some dough and grated cheese is a pizza).

A backup control file is an image of the database control file. But this image possesses very distinctive qualities.

· It contains a file type flag (value=4) that tells Oracle it is a backup control file.
· It is consistent with respect to a single point in time – thus the file type flag for Oracle’s benefit.
· The Stop SCN markers for each data file record are set to 0xffff.ffffffff. To you and I this is hexadecimal for “not available.”

These qualities assure Oracle that the integrity of the backup control file has been preserved. If a database is open and an OS image is taken of the control file it will most likely be corrupt. In contrast, when a backup control file is created using the ALTER DATABASE BACKUP CONTROLFILE TO ‘’ command (or via RMAN) the acquisition of the control file enqueue ensures the file is static during the operation. The file type flag in the backup control file tells Oracle that it cannot (and should not) rely on its redo thread, checkpoint progress and log file records. By instructing Oracle to recover using a backup control file you are telling Oracle to avoid the redo thread records and log file records in the control file – and for good reason.

Well, what about those control file backups to trace – can you create a backup control file using the SQL dumped to trace? Yes. Simply instruct Oracle to RESETLOGS in the CREATE CONTROLFILE statement. The resultant control file will have the same file type flag setting as the backup control file created using RMAN or ALTER DATABASE BACKUP CONTROLFILE TO ‘’ command. You can get at this file type flag data using the oradebug command.

SQL> oradebug setmypid;
SQL> oradebug dump controlf 10;

Be careful during your recovery because you can very easily convert your unsuspecting current control file into a backup control file – destroying the transactional ancestry of the database via the requisite RESETLOGS operation. Consider the following:

sys@erice:SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@erice:SQL> startup mount;
ORACLE instance started.

Total System Global Area 110070328 bytes
Fixed Size 731704 bytes
Variable Size 88080384 bytes
Database Buffers 20971520 bytes
Redo Buffers 286720 bytes
Database mounted.
sys@erice:SQL> recover database using backup controlfile;
ORA-00279: change 4379115 generated at 02/01/2006 22:41:36 needed for thread 1
ORA-00289: suggestion : /opt/oracle/dba/erice/erice_arch_1.dbf
ORA-00280: change 4379115 for thread 1 is in sequence #1


Specify log: {=suggested filename AUTO CANCEL}
cancel
Media recovery cancelled.
sys@erice:SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


sys@erice:SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

By issuing recovery using a backup control file I have actually converted the current control file to a backup control file. This does not mean that I could not completely recover this database. All I had to do was issue RECOVER DATABASE USING BACKUP CONTROLFILE and supply the appropriate (preserved) online redo log(s).

Back to the original question – why does Oracle require a RESETLOGS operation post-recovery using a backup control file? Within each control file there are checkpoint progress records that provide Oracle with the on-disk RBA (redo byte address). This tells Oracle the offset into the current redo log file(s) that the LGWR has flushed the redo thread.

Let’s suppose Oracle permitted you to open the database without a RESETLOGS after recovery using a backup control file. Then Oracle must use what it knows to be true about its online redo thread: the log file records, redo stream records and checkpoint progress records. If the redo log files remained intact during your restore and recovery then they probably have previously generated redo. If you have 3 online redo log groups and your backup control file states that the on-disk RBA is half way into group 2 and the existing (non-reset) redo log for group 2 is full of previously generated redo, Oracle would start writing redo to its last know on-disk RBA. Hopefully you see where this is going. Potentially, the last half of redo written to group 2 would correspond to changes post-recovery, i.e. you would have a redo log that is corrupt. There could be a major gap in SCNs between the last entry pre-recovery and the first entry post-recovery within the same redo log file. For this reason, Oracle needs to update the redo thread, log file and checkpoint progress records in accord with the new incarnation of the database.

I am sure we could come up with dozens of scenarios where not resetting the logs would cause corruption. But, I have already given too many words to a post that was intended to be short.