29 Performing User-Managed Recovery: Advanced Scenarios

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 edit the CONTROL_FILES initialization parameter.

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

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

   SQL> SHUTDOWN ABORT
   

2. 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".

3. 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
   

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

   SQL> 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:

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

   SQL> SHUTDOWN ABORT
   

2. 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 /disk1/oradata/trgt/control01.dbf /new_disk/control01.dbf
   

3. 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 the following setting:

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

   You can edit the CONTROL_FILES initialization parameter as follows:

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

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

   SQL> STARTUP
   

Recovering with a Backup Control File in 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:

   SQL> 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 /disk1/oradata/trgt/control01.dbf and /disk2/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 /disk1/oradata/trgt/control01.dbf
   % cp /backup/control02.dbf /disk2/oradata/trgt/control02.dbf
   

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

   SQL> 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:

   SQL> 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, then 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 "Re-Creating a Control File".

7. Open the database with the RESETLOGS option after finishing recovery:

   SQL> ALTER DATABASE OPEN RESETLOGS;
   

Recovering with a Backup Control File in 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 "Recovering with a Backup Control File in 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. Assume that the control file locations listed in the server parameter file are as follows, and both disks are inaccessible:

  CONTROL_FILES='/disk1/oradata/trgt/control01.dbf',
                '/disk2/oradata/trgt/control02.dbf'
  

  You can edit the initialization parameter file and specify accessible locations, as shown in the following example:

  CONTROL_FILES='/disk3/cf/control01.dbf','/disk4/cf/control02.dbf'
  

Recovering Through an Added Datafile with a Backup Control File

If database recovery with a backup control file rolls forward through a CREATE TABLESPACE or an ALTER TABLESPACE ADD DATAFILE operation, then the database stops recovery when applying the redo record for the added files and lets you confirm the filenames.

For example, suppose the following sequence of events occurs:

1. You back up the database

2. You create a new tablespace containing the following datafiles: /disk1/oradata/trgt/test01.dbf and /disk1/oradata/trgt/test02.dbf.

3. You restore a backup control file and perform media recovery through the CREATE TABLESPACE operation.

You may see the following error when applying the CREATE TABLESPACE redo data:

ORA-00283: recovery session canceled due to errors 
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 11: '/disk1/oradata/trgt/test02.dbf'
ORA-01110: data file 10: '/disk1/oradata/trgt/test01.dbf'

To recover through an ADD DATAFILE operation:

1. View the files added by querying V$DATAFILE. For example:

   SELECT FILE#,NAME 
   FROM V$DATAFILE;
   
   FILE#           NAME
   --------------- ----------------------
   1               /disk1/oradata/trgt/system01.dbf
   .
   .
   .
   10               /disk1/oradata/trgt/UNNAMED00001
   11               /disk1/oradata/trgt/UNNAMED00002
   

2. If multiple unnamed files exist, then determine which unnamed file corresponds to which datafile by using one of these methods:

   • Open the alert_SID.log, which contains messages about the original file location for each unnamed file.

   • Derive the original file location of each unnamed file from the error message and V$DATAFILE: each unnamed file corresponds to the file in the error message with the same file number.

3. Issue the ALTER DATABASE RENAME FILE statement to rename the datafiles. For example, enter:

   ALTER DATABASE RENAME FILE '/db/UNNAMED00001' TO
                              '/disk1/oradata/trgt/test01.dbf';
   ALTER DATABASE RENAME FILE '/db/UNNAMED00002' TO
                              '/disk1/oradata/trgt/test02.dbf';
   

4. Continue recovery by issuing the previous recovery statement. For example:

   RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
   

Recovering Read-Only Tablespaces with a Backup Control File

If you have a read-only tablespace on read-only or slow media, then you may encounter errors or poor performance when recovering with the USING BACKUP CONTROLFILE option. This situation occurs when the backup control file indicates that a tablespace was read/write when the control file was backed up. In this case, media recovery may attempt to write to the files. For read-only media, the database issues an error saying that it cannot write to the files. For slow media, such as a hierarchical storage system backed up by tapes, performance may suffer.

To avoid these problems, use current control files rather than backups to recover the database. If you need to use a backup control file, then you can also avoid this problem if the read-only tablespace has not suffered a media failure. You have the following alternatives for recovering read-only and slow media when using a backup control file:

• Take datafiles from read-only tablespaces offline before doing recovery with a backup control file, and then bring the files online at the end of media recovery.

• Use the correct version of the control file for the recovery. If the tablespace will be read-only when recovery completes, then the control file backup must be from a time when the tablespace was read-only. Similarly, if the tablespace will be read/write at the end of recovery, then the control file must be from a time when the tablespace was read/write.

Recovering Through a RESETLOGS with a Created Control File

You can recover backups through an OPEN RESETLOGS operation so long as:

• You have a current, backup, or created control file that knows about the prior incarnations

• You have all available archived redo logs

If you need to re-create the control file, then the trace file generated by ALTER DATABASE BACKUP CONTROLFILE TO TRACE will contain the necessary commands to re-construct the complete incarnation history. The V$DATABASE_INCARNATION view displays the RESETLOGS history known to the control file, while the V$LOG_HISTORY view displays the archived log history.

It is possible for the incarnation history to be incomplete in the in re-created control file. For example, archived logs necessary for recovery may be missing. In this case, it is possible to create incarnation records explicitly with the ALTER DATABASE REGISTER LOGFILE statement.

In the following example, you register four logs that are necessary for recovery but are not recorded in the re-created control file, and then recover the database:

ALTER DATABASE REGISTER LOGFILE '/disk1/oradata/trgt/arch/arcr_1_1_42343523.arc';
ALTER DATABASE REGISTER LOGFILE '/disk1/oradata/trgt/arch/arcr_1_1_34546466.arc';
ALTER DATABASE REGISTER LOGFILE '/disk1/oradata/trgt/arch/arcr_1_1_23435466.arc';
ALTER DATABASE REGISTER LOGFILE '/disk1/oradata/trgt/arch/arcr_1_1_12343533.arc';
RECOVER AUTOMATIC DATABASE;

Recovery of Read-Only Files with a Re-Created Control File

If a current or backup control file is unavailable for recovery, then you can execute a CREATE CONTROLFILE statement. Read-only files should not be listed in the CREATE CONTROLFILE statement so that recovery can skip these files. No recovery is required for read-only datafiles unless you restored backups of these files from a time when the datafiles were read/write.

After you create a new control file and attempt to mount and open the database, the database performs a data dictionary check against the files listed in the control file. For each file that is not listed in the CREATE CONTROLFILE statement but is present in the data dictionary, an entry is created for them in the control file. These files are named as MISSINGnnnnn, where nnnnn is a five digit number starting with 0.

After the database is open, rename the read-only files to their correct filenames by executing the ALTER DATABASE RENAME FILE statement for all the files whose name is prefixed with MISSING.

To prepare for a scenario in which you might have to re-create the control file:

• Run the following statement when the database is mounted or open to obtain the CREATE CONTROLFILE syntax:

  ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
  

  The preceding SQL statement produces a trace file that you can edit and use as a script to re-create the control file. You can specify either the RESETLOGS or NORESETLOGS (default) keywords to generate CREATE CONTROLFILE ... RESETLOGS or CREATE CONTROLFILE ... NORESETLOGS versions of the script.

All the restrictions related to read-only files in CREATE CONTROLFILE statements also apply to offline normal tablespaces, except that you need to bring the tablespace online after the database is open. You should leave out tempfiles from the CREATE CONTROLFILE statement and add them after opening the database.

Recovering After Losing a Member of a Multiplexed Online Redo Log Group

If the online redo log of a database is multiplexed, and if at least one member of each online redo log group is not affected by the media failure, then the database continues functioning as normal, but error messages are written to the log writer trace file and the alert_SID.log of the database.

Solve the problem by taking one of the following actions:

• If the hardware problem is temporary, then correct it. The log writer process accesses the previously unavailable online redo log files as if the problem never existed.

• If the hardware problem is permanent, then drop the damaged member and add a new member by using the following procedure.

  Note:

  The newly added member provides no redundancy until the log group is reused.

1. Locate the filename of the damaged member in V$LOGFILE. The status is INVALID if the file is inaccessible:

   SELECT GROUP#, STATUS, MEMBER 
   FROM V$LOGFILE
   WHERE STATUS='INVALID';
   
   GROUP#    STATUS       MEMBER
   -------   -----------  ---------------------
   0002      INVALID      /disk1/oradata/trgt/redo02.log
   

2. Drop the damaged member. For example, to drop member redo02.log from group 2, issue:

   ALTER DATABASE DROP LOGFILE MEMBER '/disk1/oradata/trgt/redo02.log';
   

3. Add a new member to the group. For example, to add redo02.log to group 2, issue:

   ALTER DATABASE ADD LOGFILE MEMBER '/disk1/oradata/trgt/redo02b.log' 
     TO GROUP 2;
   

   If the file you want to add already exists, then it must be the same size as the other group members, and you must specify REUSE. For example:

   ALTER DATABASE ADD LOGFILE MEMBER '/disk1/oradata/trgt/redo02b.log'
     REUSE TO GROUP 2;
   

Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.

If the damaged online redo log group is current and active, then it is needed for crash recovery; otherwise, it is not.

Your first task is to determine whether the damaged group is active or inactive.

1. Locate the filename of the lost redo log in V$LOGFILE and then look for the group number corresponding to it. For example, enter:

   SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;
   
   GROUP#    STATUS       MEMBER
   -------   -----------  ---------------------
   0001                    /oracle/dbs/log1a.f
   0001                    /oracle/dbs/log1b.f
   0002      INVALID       /oracle/dbs/log2a.f
   0002      INVALID       /oracle/dbs/log2b.f
   0003                    /oracle/dbs/log3a.f
   0003                    /oracle/dbs/log3b.f
   

2. Determine which groups are active.

   For example, execute the following SQL query (sample output included):

   SELECT GROUP#, MEMBERS, STATUS, ARCHIVED 
   FROM V$LOG;
   
   GROUP#  MEMBERS           STATUS     ARCHIVED
   ------  -------           ---------  -----------
    0001   2                 INACTIVE   YES
    0002   2                 ACTIVE     NO
    0003   2                 CURRENT    NO
   

3. Perform one of the following actions:

   • If the affected group is inactive, then follow the procedure in Losing an Inactive Online Redo Log Group.

   • If the affected group is active (as in the preceding example), then follow the procedure in "Losing an Active Online Redo Log Group".