Restoring Control Files

Restoring Control Files
This section contains the following topics:

Restore Lost Copy of a Multiplexed Control File
Restore Control File from Backup After Loss of All Current Control Files
Create New Control File After Losing All Current and Backup Control Files

Restore Lost Copy of a Multiplexed Control File
Use the following procedures to recover a database if a permanent media failure has damaged one or more control files of a database and at least one control file has not been damaged by the media failure.

Copying a Multiplexed Control File to a Default Location
If the disk and file system containing the lost control file are intact, then you can simply copy one of the intact control files to the location of the missing control file. In this case, you do not have to alter the CONTROL_FILES initialization parameter setting.


To replace a damaged control file by copying a multiplexed control file:

If the instance is still running, then shut it down:

SHUTDOWN ABORT

Correct the hardware problem that caused the media failure. If you cannot repair the hardware problem quickly, then proceed with database recovery by restoring damaged control files to an alternative storage device, as described in "Copying a Multiplexed Control File to a Nondefault Location".

Use an intact multiplexed copy of the database's current control file to copy over the damaged control files. For example, to replace bad_cf.f with good_cf.f, you might enter:

% cp /oracle/good_cf.f /oracle/dbs/bad_cf.f

Start a new instance and mount and open the database. For example, enter:

STARTUP

Copying a Multiplexed Control File to a Nondefault Location:

Assuming that the disk and file system containing the lost control file are not intact, then you cannot copy one of the good control files to the location of the missing control file. In this case, you must alter the CONTROL_FILES initialization parameter to indicate a new location for the missing control file.

To restore a control file to a nondefault location:

If the instance is still running, then shut it down:

SHUTDOWN ABORT

If you cannot correct the hardware problem that caused the media failure, then copy the intact control file to alternative locations. For example, to copy a good version of control01.dbf to a new disk location you might issue:

% cp $ORACLE_HOME/oradata/trgt/control01.dbf /new_disk/control01.dbf

Edit the parameter file of the database so that the CONTROL_FILES parameter reflects the current locations of all control files and excludes all control files that were not restored. Assume the initialization parameter file contains:

CONTROL_FILES='/oracle/oradata/trgt/control01.dbf','/bad_disk/control02.dbf'

Then, you can edit it as follows:

CONTROL_FILES='/oracle/oradata/trgt/control01.dbf','/new_disk/control02.dbf'

Start a new instance and mount and open the database. For example:

STARTUP

Restore Control File from Backup After Loss of All Current Control Files

Use the following procedures to restore a backup control file if a permanent media failure has damaged all control files of a database and you have a backup of the control file. When a control file is inaccessible, you can start the instance, but not mount the database. If you attempt to mount the database when the control file is unavailable, then you receive this error message:

ORA-00205: error in identifying control file, check alert log for more info 

You cannot mount and open the database until the control file is accessible again. If you restore a backup control file, then you must open RESETLOGS.
As indicated in the following table, the procedure for restoring the control file depends on whether the online redo logs are available.

Table 18-1 Scenarios When Control Files Are Lost

Status of Online Logs	Status of Datafiles	Restore Procedure
Available	Current	If the online logs contain redo necessary for recovery, then restore a backup control file and apply the logs during recovery. You must specify the filename of the online logs containing the changes in order to open the database. After recovery, open RESETLOGS.
Unavailable	Current	If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible, open RESETLOGS (when the online logs are accessible it is not necessary to OPEN RESETLOGS after recovery with a created control file).
Available	Backup	Restore a backup control file, perform complete recovery, and then open RESETLOGS.
Unavailable	Backup	Restore a backup control file, perform incomplete recovery, and then open RESETLOGS.

Restoring a Backup Control File to the Default Location

If possible, restore the control file to its original location. In this way, you avoid having to specify new control file locations in the initialization parameter file.
To restore a backup control file to its default location:

1. If the instance is still running, shut it down:
   

   SHUTDOWN ABORT
   
   

2. Correct the hardware problem that caused the media failure.
3. Restore the backup control file to all locations specified in the CONTROL_FILES parameter. For example, if ORACLE_HOME/oradata/trgt/control01.dbf and ORACLE_HOME/oradata/trgt/control02.dbf are the control file locations listed in the server parameter file, then use an operating system utility to restore the backup control file to these locations:
   

   % cp /backup/control01.dbf ORACLE_HOME/oradata/trgt/control01.dbf
   % cp /backup/control02.dbf ORACLE_HOME/oradata/trgt/control02.dbf
   
   

4. Start a new instance and mount the database. For example, enter:
   

   STARTUP MOUNT 
   
   

5. Begin recovery by executing the RECOVER command with the USING BACKUP CONTROLFILE clause. Specify UNTIL CANCEL if you are performing incomplete recovery. For example, enter:
   

   RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
   
   

6. Apply the prompted archived logs. If you then receive another message saying that the required archived log is missing, it probably means that a necessary redo record is located in the online redo logs. This situation can occur when unarchived changes were located in the online logs when the instance crashed.
   For example, assume that you see the following:
   

   ORA-00279: change 55636 generated at 11/08/2002 16:59:47 needed for thread 1
   ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_111.arc
   ORA-00280: change 55636 for thread 1 is in sequence #111
   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
   
   

   You can specify the name of an online redo log and press Enter (you may have to try this a few times until you find the correct log):
   

   ORACLE_HOME/oradata/redo01.dbf
   Log applied.
   Media recovery complete.
   
   

   If the online logs are inaccessible, then you can cancel recovery without applying them. If all datafiles are current, and if redo in the online logs is required for recovery, then you cannot open the database without applying the online logs. If the online logs are inaccessible, then you must re-create the control file, using the procedure described in "Create New Control File After Losing All Current and Backup Control Files".
7. Open the database with the RESETLOGS option after finishing recovery:
   

   ALTER DATABASE OPEN RESETLOGS;
   

Restoring a Backup Control File to a Nondefault Location

If you cannot restore the control file to its original place because the media damage is too severe, then you must specify new control file locations in the server parameter file. A valid control file must be available in all locations specified by the CONTROL_FILES initialization parameter. If not, then the database prevents you from the mounting the database.
To restore a control file to a nondefault location:
Follow the steps in "Restoring a Backup Control File to the Default Location", except after step 2 add the following step:
Edit all locations specified in the CONTROL_FILES initialization parameter to reflect the new control file locations. For example, if the control file locations listed in the server parameter file are as follows, and both locations are inaccessible:

CONTROL_FILES='/oracle/oradata/trgt/control01.dbf',
              '/oracle/oradata/trgt/control01.dbf'

Then, you can edit the initialization parameter file as follows:

CONTROL_FILES='/good_disk/control01.dbf','/good_disk/control02.dbf'

Create New Control File After Losing All Current and Backup Control Files

If all control files have been lost in a permanent media failure, but all online redo log members remain intact, then you can recover the database after creating a new control file. The advantage of this tactic is that you are notrequired to open the database with the RESETLOGS option.
Depending on the existence and currency of a control file backup, you have the options listed in the following table for generating the text of the CREATE CONTROLFILE statement. Note that changes to the database are recorded in the alert_SID.log, so check this log when deciding which option to choose.

Table 18-2 Options for Creating the Control File

If you . . .	Then . . .
Executed ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS after you made the last structural change to the database, and if you have saved the SQL command trace output	Use the CREATE CONTROLFILE statement from the trace output as-is.
Performed your most recent execution of ALTER DATABASE BACKUP CONTROLFILE TO TRACEbefore you made a structural change to the database	Edit the output of ALTER DATABASE BACKUP CONTROLFILE TO TRACE to reflect the change. For example, if you recently added a datafile to the database, then add this datafile to the DATAFILE clause of the CREATE CONTROLFILE statement.
Backed up the control file with the ALTER DATABASE BACKUP CONTROLFILE TO filenamestatement (not the TO TRACE option)	Use the control file copy to obtain SQL output. Create a temporary database instance, mount the backup control file, and then run ALTERDATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. If the control file copy predated a recent structural change, then edit the trace to reflect the change.
Do not have a control file backup in either TO TRACE format or TO filename format	Execute the CREATE CONTROLFILE statement manually (refer to Oracle Database SQL Reference).

Note:

If your character set is not the default US7ASCII, then you must specify the character set as an argument to the CREATE CONTROLFILE statement. The database character set is written to the alert log at startup. The character set information is also recorded in the BACKUP CONTROLFILE TO TRACE output.

To create a new control file:

1. Start the database in NOMOUNT mode. For example, enter:
   

   STARTUP NOMOUNT
   
   

2. Create the control file with the CREATE CONTROLFILE statement, specifying the NORESETLOGS option (refer to Table 18-2 for options). The following example assumes that the character set is the default US7ASCII:
   

   CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
        MAXLOGFILES 32
        MAXLOGMEMBERS 2
        MAXDATAFILES 32
        MAXINSTANCES 16
        MAXLOGHISTORY 1600
   LOGFILE
        GROUP 1 (
          '/diska/prod/sales/db/log1t1.dbf',
          '/diskb/prod/sales/db/log1t2.dbf'
        )  SIZE 100K
        GROUP 2 (
          '/diska/prod/sales/db/log2t1.dbf',
          '/diskb/prod/sales/db/log2t2.dbf'
        )  SIZE 100K,
   DATAFILE
        '/diska/prod/sales/db/database1.dbf',
        '/diskb/prod/sales/db/filea.dbf';
   
   

   After creating the control file, the instance mounts the database.
3. Recover the database as normal (without specifying the USING BACKUP CONTROLFILE clause):
   

   RECOVER DATABASE
   
   

4. Open the database after recovery completes (RESETLOGS option not required):
   

   ALTER DATABASE OPEN;
   
   

5. Immediately back up the control file. The following SQL statement backs up a database's control file to /backup/control01.dbf:
   

   ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01.dbf' REUSE;