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.
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:
- If the instance is still running, shut it down:
SHUTDOWN ABORT
- Correct the hardware problem that caused the media failure.
- 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
- Start a new instance and mount the database. For example, enter:
STARTUP MOUNT
- 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
- 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".
- 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.
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:
- Start the database in
NOMOUNT mode. For example, enter:
STARTUP NOMOUNT
- 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.
- Recover the database as normal (without specifying the
USING BACKUP CONTROLFILE clause):
RECOVER DATABASE
- Open the database after recovery completes (
RESETLOGS option not required):
ALTER DATABASE OPEN;
- 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;
