Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
|
|
View PDF |
This section contains the following topics:
Restore Control File from Backup After Loss of All Current Control Files
Create New Control File After Losing All Current and Backup Control Files
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.
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
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
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 |
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 |
Available |
Backup |
Restore a backup control file, perform complete recovery, and then open |
Unavailable |
Backup |
Restore a backup control file, perform incomplete recovery, and then open |
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;
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'
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 not required 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 |
Use the |
Performed your most recent execution of |
Edit the output of |
Backed up the control file with the |
Use the control file copy to obtain SQL output. Create a temporary database instance, mount the backup control file, and then run |
Do not have a control file backup in either |
Execute the |
Note:
If your character set is not the default US7ASCII, then you must specify the character set as an argument to theCREATE
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.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;