7 Advanced RMAN Recovery Techniques

Recovering a Database in NOARCHIVELOG Mode Using Incremental Backups

You can perform limited recovery of changes to a database running in NOARCHIVELOG mode by applying incremental backups. Note that the incremental backups must be consistent, like all backups of a database run in NOARCHIVELOG mode, so you cannot make backups of the database when it is open.

Assume the following scenario:

• You run database trgt in NOARCHIVELOG mode.

• You use a recovery catalog.

• You shut down the database consistently and make a level 0 backup of database trgt to tape on Sunday afternoon.

• You shut down the database consistently and make a level 1 differential incremental backup to tape at 3:00 a.m. on Wednesday and Friday.

• The database has a media failure on Saturday, destroying half of the datafiles as well as the online redo logs.

In this case, you must perform an incomplete media recovery until Friday, the date of the most recent incremental backup. RMAN uses the level 0 Sunday backup as well as the Wednesday and Friday level 1 backups.

Because the online redo logs are lost, you must specify the NOREDO option in the RECOVER command.

You must also specify NOREDO if the online logs are available but the redo cannot be applied to the incrementals.

If you do not specify NOREDO, then RMAN searches for redo logs after applying the Friday incremental backup, and issues an error message when it does not find them.

After connecting to trgt and the catalog database, recover the database with the following command:

STARTUP FORCE MOUNT;
RESTORE CONTROLFILE;  # restore control file from consistent backup
ALTER DATABASE MOUNT;
RESTORE DATABASE;  # restore datafiles from consistent backup
RECOVER DATABASE NOREDO;  # specify NOREDO because online redo logs are lost
ALTER DATABASE OPEN RESETLOGS;

The recovered database reflects only changes up through the time of the Friday incremental backup. Because there are no archived redo logs, there is no way to recover changes made after the incremental backup.

Preparing for Restore of a Database to a New Host

To prepare for the restore of the database to a new host, take the following steps:

• Record the DBID for your source database. If you do not know the DBID for your database, see Oracle Database Backup and Recovery Basics for details on ways to determine the DBID.

• Make the source database initialization parameter file accessible on the new host. Copy the file from the old host to a new host using an operating system utility.

• Make sure backups used for the restore are accessible on the restore host. For example, if the backups were made with a media manager, then make sure the tape device is connected to the new host.

Testing the Restore of a Database to a New Host: Scenario

This scenario assumes the following:

• Two networked machines, hosta and hostb, are running Linux

• A target database named trgta is on hosta and uses a recovery catalog catdb

• You want to test the restore and recovery of trgta on hostb, while keeping database trgta up and running on hosta

• The directory structure of hostb is different from hosta, so that trgta is located in /net/hosta/dev3/oracle/dbs, but you want to restore the database to /net/hostb/oracle/oradata/test

• Database trgta uses a server parameter file (not a client-side initialization parameter file)

• The ORACLE_SID for the trgta database is trgta and will not change for the restored database

• You have a record of the DBID for trgta

• A media manager is accessible by both machines

• You have recoverable backups on tape of all datafiles

• You have backups of the archived logs required to recover the datafiles

• You have control file and server parameter file autobackups on tape

Use the following steps to perform the restore process:

1. Make backups of the target database available to hostb. To test disaster recovery, you need to have a recoverable backup of the target database. When preparing your disaster recovery strategy, ensure that the backups of the datafiles, control files, and server parameter file are restorable on hostb. Hence, you must configure the media management software so that hostb is a media manager client and can read the backup sets created on hosta. Consult the media management vendor for support on this issue.

   Configure the ORACLE_SID on hostb. This scenario assumes that you want to authenticate yourself through the operating system, which is much faster than configuring Oracle Net and creating a password file. However, you must be connected to hostb either locally or through a SQLNet alias.

   While connected to hostb with administrator privileges, edit the /etc/group file so that you are included: in the DBA group:

   dba:*:614:<your_user_name>
   

   Set the ORACLE_SID environment variable on hostb to the same value used on hosta:

   % setenv ORACLE_SID trgta
   

   Start RMAN and connect to the target instance without connecting to the recovery catalog.

   % rman TARGET / NOCATALOG
   

   Start the instance without mounting it. To start the instance, you first need to set the DBID. (If you do not know the DBID for your database, see Oracle Database Backup and Recovery Basics for details on how to determine the DBID.)

   Run SET DBID to set the DBID, then run STARTUP NOMOUNT:

   SET DBID 1340752057;
   STARTUP NOMOUNT
   

   RMAN will fail to find the server parameter file, which has not yet been restored, but will start the instance with a "dummy" file. Sample output follows:

   startup failed: ORA-01078: failure in processing system parameters
   LRM-00109: could not open parameter file '/net/hostb/oracle/dbs/inittrgta.ora'
   
   trying to start the Oracle instance without parameter files ...
   Oracle instance started
   

   Restore and edit the server parameter file.

   Because you enabled the control file autobackup feature when making your backups, the server parameter file is included in the backup sets.

   Allocate a channel to the media manager, then restore the server parameter file (SPFILE) as a client-side parameter file (PFILE).

   RUN
   {
     ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
     RESTORE SPFILE TO PFILE '?/oradata/test/inittrgta.ora' FROM AUTOBACKUP;
     SHUTDOWN ABORT;
   }
   

   Next, edit the restored PFILE. Change any location-specific parameters, for example, those ending in _DEST and _PATH, to reflect the new directory structure. For example, edit the following parameters:

     - IFILE
     - *_DUMP_DEST
     - LOG_ARCHIVE_DEST*
     - CONTROL_FILES
   

   Then restart the instance, using the edited PFILE:

   STARTUP FORCE NOMOUNT PFILE='?/oradata/test/inittrgta.ora';
   

   Restore the control file from an autobackup and then mount the database. RMAN restores the control file to whatever location you specified in the CONTROL_FILES initialization parameter. For example:

   RUN 
   {
     ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
     RESTORE CONTROLFILE FROM AUTOBACKUP;
     ALTER DATABASE MOUNT;
   }
   

   Query the database filenames recorded in the control file on the new host (hostb). Because the control file is from the trgta database, the recorded filenames use the original hosta filenames. You can query V$ views to obtain this information. Start a new SQL*Plus session and connect to the newly created instance on hostb:

   % sqlplus '/ AS SYSDBA'
   

   Run the following query in SQL*Plus:

   SQL> COLUMN NAME FORMAT a60
   SQL> SPOOL LOG 'db_filenames.out'
   SQL> SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
        UNION
        SELECT GROUP#,MEMBER FROM V$LOGFILE;
   SQL> SPOOL OFF
   SQL EXIT
   

   Write the RMAN recovery script. The script must include the following steps:

   • For each datafile on the destination host that is restored to a different path than it had on the source host, use a SET NEWNAME command to specify the new path on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use SET NEWNAME for those files restored to the same path as on the source host.)

2. For each online redo log that is to be created at a different location than it had on the source host, use SQL ALTER DATABASE RENAME FILE commands to specify the pathname on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use ALTER DATABASE RENAME FILE for those files restored to the same path as on the source host.)

3. Perform a SET UNTIL to limit media recovery to the end of the archived redo logs.

4. Run SWITCH so that the control file recognizes the new path names as the official new names of the datafiles

5. Restore and recover the database

   For example, consider the following RMAN script to perform these steps, which is contained in text file reco_test.rman:

   RUN
   {
     # allocate a channel to the tape device
     ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
   
     # rename the datafiles and online redo logs
     SET NEWNAME FOR DATAFILE 1 TO '?/oradata/test/system01.dbf';
     SET NEWNAME FOR DATAFILE 2 TO '?/oradata/test/undotbs01.dbf';
     SET NEWNAME FOR DATAFILE 3 TO '?/oradata/test/cwmlite01.dbf';
     SET NEWNAME FOR DATAFILE 4 TO '?/oradata/test/drsys01.dbf';
     SET NEWNAME FOR DATAFILE 5 TO '?/oradata/test/example01.dbf';
     SET NEWNAME FOR DATAFILE 6 TO '?/oradata/test/indx01.dbf';
     SET NEWNAME FOR DATAFILE 7 TO '?/oradata/test/tools01.dbf';
     SET NEWNAME FOR DATAFILE 8 TO '?/oradata/test/users01.dbf';
     SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo01.log''
         TO ''?/oradata/test/redo01.log'' ";
     SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo02.log''
         TO ''?/oradata/test/redo02.log'' ";
   
     # Do a SET UNTIL to prevent recovery of the online logs
     SET UNTIL SCN 123456;
     # restore the database and switch the datafile names
     RESTORE DATABASE;
     SWITCH DATAFILE ALL;
   
     # recover the database
     RECOVER DATABASE;
   }
   EXIT
   

   Online logs and datafiles are relocated as specified,

   For example, connect and execute the script as shown here:

   % rman TARGET / NOCATALOG
   RMAN> @reco_test.rman
   

   RMAN will apply as many of the archived redo logs as it can and leave the database in a state in which is can be opened.

   Now perform an OPEN RESETLOGS at the restored database.

   Caution:

   When you re-open your database in the next step, do not connect to the recovery catalog. Otherwise, the new database incarnation created is registered automatically in the recovery catalog, and the filenames of the production database are replaced by the new filenames specified in the script.

   If this is a test restore, never connect RMAN to the test-restore database and the recovery catalog.

   From the RMAN prompt, open the database with the RESETLOGS option:

   RMAN> ALTER DATABASE OPEN RESETLOGS;
   

   If this was a test restore, and it was successful, then you can shut down the test database instance, and delete the test database with all of its files. Use the DROP DATABASE command to delete all files associated with the database automatically.

   Note:

   If you used an ASM disk group, then DROP DATABASE is the only way to safely remove the files of the test database. If you restored to non-ASM storage then you can also use operating system commands to remove the database.

   RMAN> SHUTDOWN ABORT
   RMAN> EXIT
   

   Remove all test files. You can do this with an operating system utility or in RMAN. For example, in Unix you could perform the procedure this way:

   % rm $ORACLE_HOME/oradata/test/*
   

   You can also use RMAN for a procedure that works on all platforms. For example:

   RMAN> STARTUP FORCE NOMOUNT PFILE='?/oradata/test/inittrgta.ora';
   RMAN> DROP DATABASE;
   

   Because you did not perform the restore and recovery when connected to the recovery catalog, the recovery catalog contains no records for any of the restored files or the procedures performed during the test. Likewise, the control file of the trgta database is completely unaffected by the test.

Performing Recovery with a Backup Control File and No Recovery Catalog: Scenario

This section assumes that you have RMAN backups of the control file, but do not use a recovery catalog.

Assuming that you enabled the control file autobackup feature for the target database, you can restore an autobackup of the control file. Because the autobackup uses a default format, RMAN can restore it even though it does not have a repository available that lists the available backups. You can restore the autobackup to the default or a new location. RMAN replicates the control file to all CONTROL_FILES locations automatically.

Because you are not connected to a recovery catalog, the RMAN repository contains only information about available backups at the time of the control file backup. If you know the location of other usable backup sets or image copies, add them to the control file RMAN repository with the CATALOG command.

Because the repository is not available when you restore the control file, you must use the SET DBID command to identify the target database. The DBID is used to determine the location of control file autobackups. Use SET DBID command only in the following special circumstances:

• You are not connected to a recovery catalog and want to restore the control file or server parameter file.

• You are connected to a recovery catalog and want to restore the control file, but the database name is not unique in the recovery catalog.

• The server parameter file is lost and you want to restore it.

To recover the database with an autobackup of the control file without a recovery catalog:

Block Media Recovery Using All Available Backups

In this scenario, you identify the blocks that require recovery and then use any available backup to perform the restore and recovery of these blocks.

To recover datablocks by using all available backups:

Block Media Recovery Using Specific Backups

In this scenario, you identify the blocks that require recovery, and then use only selected backups to perform the restore and recovery of these blocks.

To recover datablocks while limiting the type of backup:

Note that if you limit the restore of datablocks with the UNTIL clause, then RMAN must perform more recovery on the blocks, and the recovery phase must scan all logs for changes to the specified blocks.

Block Media Recovery of Blocks Listed in V$DATABASE_BLOCK_CORRUPTION

The V$DATABASE_BLOCK_CORRUPTION view indicates which blocks in a datafile were marked corrupt since the most recent BACKUP or BACKUP VALIDATE command was run. After a corrupt block is repaired, the row identifying this block is deleted from the view.

You can check for logical corruption in the database by running the BACKUP (with or without VALIDATE option) with the CHECK LOGICAL command. If RMAN finds corrupt blocks, then it populates V$DATABASE_BLOCK_CORRUPTION. The backup will stop if the number of corrupt blocks exceeds MAXCORRUPT. A historical record of block corruptions in RMAN backups is kept in V$BACKUP_CORRUPTION and V$COPY_CORRUPTION.

In this scenario, you identify the blocks that require recovery by querying V$DATABASE_BLOCK_CORRUPTION, and then instruct RMAN to recover all blocks listed in this view by means of the CORRUPTION LIST keyword.

To recover datablocks while limiting the type of backup:

See Oracle Database Backup and Recovery Reference for more details on block media recovery in RMAN.

Restoring Datafile Copies to a New Host: Example

To move the database to a new host by means of datafile copies, you must transfer the copies manually to the new machine. This example assumes that you are using a recovery catalog.

Restoring Control File When Databases in the Catalog Have the Same Name: Example

When using a recovery catalog and attempting to restore a lost control file, you encounter an error if there are other databases are registered in the recovery catalog with the same name as your target database.

To resolve this error, you must uniquely identify the database by DBID for the restore operation. This requires determining the correct DBID for your database, and then using the SET DBID command to identify the target database before the RESTORE CONTROLFILE command, as shown in the following example:

SET DBID = target_dbid;

RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;

RESTORE DATABASE; 
RECOVER DATABASE
  # optionally, delete logs restored for recovery and limit disk space used
  DELETE ARCHIVELOG MAXSIZE 2M; 

Recovering a Lost Datafile Without a Backup: Example

RMAN can handle lost datafiles without user intervention during restore and recovery. When a datafile is lost, the possible cases can be classified as follows:

• The control file knows about the datafile, that is, the user backed up the control file after datafile creation, but the datafile itself is not backed up. If the datafile record is in the control file, then RESTORE creates the datafile in the original location or in a user-specified location (for example, with SET NEWNAME). The RECOVER command can then apply the necessary logs to the datafile.

• The control file does not have the datafile record, that is, the user did not back up the control file after datafile creation. During recovery, the database will detect the missing datafile and report it to RMAN, which will create a new datafile and continue recovery by applying the remaining logs. If the datafile was created in a parent incarnation, it will be created during restore or recover as appropriate.

In this example, the following sequence of events occurs:

In this case, the current control file knows about the datafile. To restore and recover the datafile, start RMAN, connect to the target database, and then enter the following commands at the RMAN prompt:

# take the tablespace with the missing datafile offline
SQL "ALTER TABLESPACE history OFFLINE IMMEDIATE";
# restore the tablespace even though you have no backup
RESTORE TABLESPACE history;
# recover tablespace
RECOVER TABLESPACE history;
# bring the recovered tablespace back online
SQL "ALTER TABLESPACE history ONLINE";