Skip Headers

Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 1 (10.1)

Part Number B10734-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

8
Advanced RMAN Recovery Techniques

This chapter describes how to use Recovery Manager to perform restore and recovery operations. This chapter contains these topics:

Performing Database Point-In-Time Recovery

RMAN can perform recovery of the whole database to a specified past time, SCN, or log sequence number. This type of recovery is sometimes called incomplete recovery because it does not completely use all of the available redo. Incomplete recovery of the whole database is also called database point-in-time recovery (DBPITR).

If you have enabled the collectionof flashback logs, you may be able to use Oracle Flashback Database instead of performing DBPITR. Flashback Database is generally faster and simpler to use, when it is available, because it does not require restoring a past backup. Depending upon your situation, you may also find one of the other Oracle flashback features can meet your data recovery need. See "Oracle Flashback Technology: Overview" for more details about these alternatives before deciding whether to use DBPITR.

DBPITR requires restoring your database from an older backup, then performing media recovery until your specified target time, SCN or log sequence number. Note that because you need your archived redo log files to perform this process, you cannot perform database point-in-time recovery if you have been running your database in NOARCHIVELOG mode.

After database point-in-time recovery, you must open the database with the RESETLOGS option. Using the RESETLOGS option archives the current online redo logs, resets the log sequence to 1, and then gives the online redo logs a new time stamp and SCN. In this way, the database eliminates the possibility of corrupting datafiles by the application of obsolete archived redo logs.

You have to recover all datafiles: you cannot recover some datafiles before the RESETLOGS and others after the RESETLOGS.

The OPEN RESETLOGS operation will fail if a datafile is off-line, unless the datafile went offline normally or is read-only. You can bring files in read-only or offline normal tablespaces online after the RESETLOGS because they do not need any redo.

When performing DBPITR, consider using the SET UNTIL command to set the target time at the beginning of the process, rather than specifying the UNTIL clause on the RESTORE and RECOVER commands individually. SET UNTIL sets the desired time for any subsequent RESTORE, SWITCH, and RECOVER commands in the same RUN job.

Note that if you specify a SET UNTIL command after a RESTORE and before a RECOVER, you may not be able to recover the database to the point in time required because the restored files may already have time stamps more recent than the set time. Hence, it is recommended that you specify the SET UNTIL command before the RESTORE command.

Performing Point-in-Time Recovery with a Current Control File

The database must be closed to perform database point-in-time recovery. If you are recovering to a time, then you should set the time format environment variables before invoking RMAN. The following are sample Globalization Support settings:

NLS_LANG = american_america.us7ascii
NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS" 

To recover the database until a specified time, SCN, or log sequence number:

  1. After connecting to the target database and, optionally, the recovery catalog database, ensure that the database is mounted. If the database is open, shut it down and then mount it:
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    
    
  2. Determine the time, SCN, or log sequence that should end recovery. For example, if you discover that a user accidentally dropped a tablespace at 9:02 a.m., then you can recover to 9 a.m.--just before the drop occurred. You will lose all changes to the database made after that time.

    You can also examine the alert.log to find the SCN of an event and recover to a prior SCN. Alternatively, you can determine the log sequence number that contains the recovery termination SCN, and then recover through that log. For example, query V$LOG_HISTORY to view the logs that you have archived.

    RECID      STAMP      THREAD#    SEQUENCE#  FIRST_CHAN FIRST_TIM NEXT_CHANG
    ---------- ---------- ---------- ---------- ---------- --------- ----------
             1  344890611          1          1      20037 24-SEP-02      20043
             2  344890615          1          2      20043 24-SEP-02      20045
             3  344890618          1          3      20045 24-SEP-02      20046
    
    
  3. Perform the following operations within a RUN command:
    1. Set the end recovery time, SCN, or log sequence. If specifying a time, then use the date format specified in the NLS_LANG and NLS_DATE_FORMAT environment variables.
    2. If automatic channels are not configured, then manually allocate one or more channels.
    3. Restore and recover the database.

    The following example performs an incomplete recovery until November 15 at 9 a.m.

    RUN
    { 
      SET UNTIL TIME 'Nov 15 2002 09:00:00';
      # SET UNTIL SCN 1000;       # alternatively, specify SCN
      # SET UNTIL SEQUENCE 9923;  # alternatively, specify log sequence number
      RESTORE DATABASE;
      RECOVER DATABASE;
    }
    
    
  4. If recovery was successful, then open the database and reset the online logs:
    ALTER DATABASE OPEN RESETLOGS;
    

Point-in-Time Recovery to a Previous Incarnation

RMAN can seamlessly restore and recover backups from previous incarnations to the current incarnation. To perform point-in-time recovery to a target time prior to the most recent RESETLOGS, however, you must run the RESET DATABASE command to reset the database to the incarnation current at the desired target time.

Assume the following situation:

On October 25, you discover that you need crucial data that was dropped from the database at 8:00 a.m. on October 8, 2002. You decide to reset trgt to the prior incarnation, restore the October 2 backup, and recover to 7:55 a.m. on October 8.


Note:

It is not possible to restore one datafile of a previous incarnation while the current database is in a different incarnation--you must restore the whole database.


To recover the database by means of a backup from the old incarnation:

  1. Obtain the primary key of the prior incarnation with a LIST command:
    # obtain primary key of old incarnation
    LIST INCARNATION OF DATABASE trgt;
    
    List of Database Incarnations
    DB Key  Inc Key   DB Name   DB ID       STATUS     Reset SCN    Reset Time
    ------- -------   -------   ------      -------    ----------   ----------
    1       2         TRGT      1224038686  PARENT     1            02-OCT-02
    1       582       TRGT      1224038686  CURRENT    59727        10-OCT-02
    
    
  2. Make sure the database is started but not mounted:
    SHUTDOWN FORCE NOMOUNT
    
    
  3. Reset the incarnation to the primary key that you just obtained:
    # reset database to old incarnation
    RESET DATABASE TO INCARNATION 2;
    
    
  4. Recover the database, performing the following actions in the RUN command:
    • Set the end time for recovery to the time just before the loss of the data.
    • If automatic channels are not configured, then manually allocate one or more channels.
    • Restore the control file and mount it.
    • Restore and recover the database.

    For example, run the following commands:

    RUN
    {
      # set time to just before data was lost.
      SET UNTIL TIME 'Oct 8 2002 07:55:00'; 
      RESTORE CONTROLFILE; # FROM AUTOBACKUP not needed in catalog mode
      ALTER DATABASE MOUNT; # mount database after restoring control file
      RESTORE DATABASE;
      RECOVER DATABASE;
    }
    
    
  5. If recovery is successful, then reset the online redo logs:
    # this command automatically resets the database so that this incarnation is
    # the new incarnation
    ALTER DATABASE OPEN RESETLOGS;
    

Performing Recovery with a Backup Control File

If all copies of the current control file are lost or damaged, then you must restore and mount a backup control file before you can perform recovery. There are two cases to consider:

The following notes and restrictions apply regardless of whether you use a recovery catalog:

Performing Recovery with a Backup Control File and a Recovery Catalog

If you use a recovery catalog and have a backup control file available, then this procedure does not differ substantially from a standard restore and recovery. The procedure in this section assumes that you are restoring the control file to its default location. If you must restore the control file to a new location, then refer to Oracle Database Backup and Recovery Basics for instructions.

When you perform a restore operation using a backup control file and you use a recovery catalog, RMAN automatically adjusts the control file to reflect the structure of the restored backup.

The following procedure assumes that you do not have more than one target database registered in the catalog with the same name. If multiple target databases are registered with the same name, then you must specify the DBID with the SET DBID command so that RMAN knows which control file to restore. The DBID is the unique numerical identifier for a database.

See Also:

"Performing Recovery with a Backup Control File and No Recovery Catalog" to learn how to set the DBID

To recover the database with a backup control file and a recovery catalog:

  1. After connecting to the target database and recovery catalog database, start the instance without mounting the database:
    STARTUP NOMOUNT
    
    
  2. Restore the backup control file, then restore and recover the database. Do the following:
    1. Run the RESTORE CONTROLFILE command to restore the control file to all default locations specified in the CONTROL_FILES initialization parameter. To restore a control file from an older backup, you can run SET UNTIL or specify the UNTIL clause on the RESTORE CONTROLFILE command.
    2. Mount the restored control file.
    3. Optionally, run a SET UNTIL command for incomplete recovery. Note that you can also specify the UNTIL clause on the RESTORE and RECOVER commands.
    4. Restore and recover the database

    This example restores the control file to its default location, then restores and completely recovers the database:

    RESTORE CONTROLFILE;
    ALTER DATABASE MOUNT;
    RESTORE DATABASE;
    RECOVER DATABASE;
    
    
  3. If recovery was successful, then open the database and reset the online logs:
    ALTER DATABASE OPEN RESETLOGS;
    
    
  4. If the database uses locally-managed temporary tablespaces, then add new tempfiles to these tablespaces. For example:
    SQL "ALTER TABLESPACE temp 
         ADD TEMPFILE ''?/oradata/trgt/temp01.dbf'' REUSE";
    

Performing Recovery with a Backup Control File and No Recovery Catalog

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.


Note:

If you know the backup piece name (for example, from the media manager or because the piece is on disk), then you can specify the piece name using the RESTORE CONTROLFILE FROM 'filename' command. The server records the location of every autobackup in the alert log.


Because you are not connected to a recovery catalog, the control file must have a record of all needed backups. If any backups are not listed in the control file, then RMAN cannot restore them. You can add backup pieces and image copies to the control file repository with the CATALOG command.

Because the repository is not available when you restore the control file, run the SET DBID command to identify the target database. You should only run the SET DBID command in the following specialized circumstances:

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

  1. Start RMAN and connect to the target database. For example, run:
    CONNECT TARGET /
    
    
  2. Start the target instance without mounting the database. For example:
    STARTUP NOMOUNT;
    
    
  3. Set the database identifier for the target database with SET DBID. RMAN displays the DBID whenever you connect to the target. You can also obtain it by inspecting saved RMAN log files, querying the catalog, or looking at the filenames of control file autobackup. (refer to "Restoring When Multiple Databases in the Catalog Share the Same Name: Example"). For example, run:
    SET DBID 676549873;
    
    
  4. Restore the autobackup control file, then perform recovery. Do the following:
    1. Optionally, specify the most recent backup time stamp that RMAN can use when searching for a control file autobackup to restore.
    2. If a nondefault format was used to create the control file, then specify a nondefault format for the restore of the control file.
    3. If the channel that created the control file autobackup was device type sbt, then you must allocate one or more sbt channels. Because no repository is available, you cannot use preconfigured channels. If the autobackup was created on a disk channel, however, then you do not need to manually allocate a channel.
    4. Restore the autobackup of the control file, optionally setting the maximum number of days backward that RMAN can search (up to 366) and the initial sequence number that it should use in its search for the first day.
    5. If you know that your control file contained information about configured channels that will be useful to you in the rest of the restore process, you can exit the RMAN client at this point, to clear manually allocated channels from step "c". If you then restart the RMAN client and mount the database those configured channels become available for your use in the rest of the restore and recovery process.

      If you do not care about using configured channels from your control file, then you can simply mount the database at this point.

    6. If the online logs are inaccessible, then restore and recover the database as described in "Performing Database Point-In-Time Recovery". You must terminate recovery by setting the UNTIL clause to a time, log sequence, or SCN before the online redo logs. If the online logs are usable, then perform a complete recovery as described in Oracle Database Backup and Recovery Basics.

    In this example, the online redo logs have been lost. This example limits the restore of the control file autobackup, then performs recovery of the database to log sequence 13243, which is the most recent archived log:

    RUN 
    {
      # Optionally, set upper limit for eligible time stamps of control file 
      # backups
      # SET UNTIL TIME '09/10/2000 13:45:00';
      # Specify a nondefault autobackup format only if required
      # SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK 
      #   TO '?/oradata/%F.bck';
      ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...'; # allocate manually
      RESTORE CONTROLFILE FROM AUTOBACKUP
        MAXSEQ 100           # start at sequence 100 and count down
        MAXDAYS 180;         # start at UNTIL TIME and search back 6 months
      ALTER DATABASE MOUNT DATABASE;
    }
    # uses automatic channels configured in restored control file
    RESTORE DATABASE UNTIL SEQUENCE 13243;
    RECOVER DATABASE UNTIL SEQUENCE 13243; # recovers to latest archived log
    
    
  5. If recovery was successful, then open the database and reset the online logs:
    ALTER DATABASE OPEN RESETLOGS;
    

Restoring the Database to a New Host

Various scenarios are possible when restoring a database to a new host. For example, you may want to:

To create a duplicate database for testing while maintaining the original database, use the DUPLICATE command instead of the RESTORE command (refer to "Duplicating a Database with Recovery Manager"). RMAN automatically creates a unique database identifier for the duplicate database. This chapter covers the use of the RESTORE command only.

To test the restore of a database to a new host or to move the database to a new host, run the RESTORE command. If you perform a test restore only, then you should do the following to prevent overwriting the target records in the recovery catalog:

Table 8-1 describes the impact on the RMAN repository when you are restoring or duplicating to a new host.

Table 8-1 Restoring and Duplicating to a New Host
Command Catalog? Effect on Repository

RESTORE

yes

If you run SWITCH commands after the restore, then RMAN considers the restored database on the new host as the new location of the original target database. If you do not run SWITCH commands, then RMAN views the restored datafiles as image copies that are candidates for future restore jobs.

RESTORE

no

If you run SWITCH commands after the restore, then RMAN considers the restored database as a new target database. This new database, however, has the same DBID as the original. As a result, you cannot later register this duplicate in the same recovery catalog as the original target database.

If you do not run SWITCH commands, then the restore operation has no effect on the repository.

DUPLICATE

yes

Generates a new DBID for the duplicate database, which you must manually register in the catalog. After registration, the repository has records of two distinct databases: the target and the duplicate.

DUPLICATE

no

Generates a new DBID for the duplicate database. The repository in the original target control file is unaffected.

Specifying Filenames When Restoring to a New Host

The basic procedure for restoring the database to a new host does not differ substantially from incomplete recovery on the original host. The principal issue is whether the path names of the database files on the new host are going to be the same as the path names of the files on the primary host.

Which restore procedure you should use depends on your situation. If the path names of the restored files will be the same as the original path names, see "Restoring Datafile Copies to a New Host: Example". If the path names are different, refer to "Performing Disaster Recovery".

Note the following when restoring to a new host:

Determining the SCN for Incomplete Recovery After Restore

Because the restored database will not have the online redo logs of the production database, perform incomplete recovery up to the lowest SCN of the most recently archived log in each thread and then open with the RESETLOGS option. Obtain the SCN for recovery termination by finding the lowest SCN among the most recent archived logs for each thread.

Start SQL*Plus and use the following query to determine the necessary SCN:

SQL> SELECT MIN(maxnc) FROM 
 (SELECT MAX(a.NEXT_CHANGE#) maxnc 
      FROM V$ARCHIVED_LOG a, V$THREAD t
      WHERE a.THREAD# = t.THREAD#
        AND a.ARCHIVED='YES' 
        AND t.ENABLED='DISABLED'
      GROUP BY a.THREAD#);

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

The DUPLICATE command is the preferred method of copying the target database. DUPLICATE creates a new DBID for the copied database, allowing it to be registered in the same recovery catalog as the original target database. However, you may wish to perform a test run of your disaster recovery scenarios that uses exactly the same steps that you would use in a genuine emergency. If so, then you should use the RESTORE and RECOVER commands rather than DUPLICATE.

This scenario assumes the following:

To test the restore of the database to a new host:

  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.
  2. Configure the ORACLE_SID on hostb. This case study 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 telnet.

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

    dba:*:614:<your_user_name>
    
    

    Run the setenv command on hostb to set the ORACLE_SID. In this example, you set the SID to the same value that you used on hosta:

    % setenv ORACLE_SID trgta
    
    

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

    % rman TARGET / NOCATALOG
    
    
  3. Start the instance without mounting it. To start the instance, you first need to set the DBID. The DBID is recorded in several places, including:
    • V$DATABASE in the target and RC_DATABASE in the catalog
    • The RMAN output (command-line and V$RMAN_STATUS)
    • The filename of the control file autobackups

    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
    
    
  4. 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. Hence, you can allocate a channel to the media manager and restore the server parameter file to a new location as a client-side initialization parameter file. Then you can edit the client-side file and restart the instance with the edited client-side file. For example:
    RUN
    {
      ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
      RESTORE SPFILE TO PFILE '?/oradata/test/inittrgta.ora' FROM AUTOBACKUP;
      SHUTDOWN ABORT;
    }
    
    

    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
    
    

    Restart the instance, specifying the client-side initialization parameter file that you restored:

    STARTUP FORCE NOMOUNT PFILE='?/oradata/test/inittrgta.ora';
    
    
  5. Restore the control file from an autobackup and then mount the database. Because you edited the init.ora in the preceding step, 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;
    }
    
    
  6. 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
    
    
  7. Restore and recover the database. At this point you are ready to write the RMAN recovery script. The script should do the following:
    • Run SET NEWNAME for each datafile so it is renamed to its new hostb path name
    • Run SQL commands to rename the online redo logs to their new hostb path names
    • Perform a SET UNTIL to limit media recovery to the end of the archived redo logs, as described in "Determining the SCN for Incomplete Recovery After Restore"
    • Run SWITCH so that the control file recognizes the new path names as the official new names of the datafiles
    • Restore and recover the database

    The following is an example of an 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
    
    

    Caution:

    It is imperative that you not be connected with the recovery catalog when you run this script, so that you do not incorporate extraneous repository data about backups into the recovery catalog.


    For example, connect and execute as follows:

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

  8. Open the database. From the RMAN prompt, open the database with the RESETLOGS options:
    RMAN> ALTER DATABASE OPEN RESETLOGS;
    
    
  9. Remove the test files from the operating system. If the test is successful, then shut down the instance and exit the RMAN session:
    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 ok 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 Disaster Recovery

If you are in a disaster recovery scenario, then presumably you have lost the target database, the recovery catalog database, all control files, all online redo logs, and all parameter files.

To perform a disaster recovery, the minimum required set of backups is backups of some datafiles, some archived redo logs generated after the time of the backup, and at least one autobackup of the control file.

See Also:

"Control File and Server Parameter File Autobackups"

The basic procedure for disaster recovery is found in "Performing Recovery with a Backup Control File", with an additional first step of restoring an autobackup of the server parameter file as described in Oracle Database Backup and Recovery Basics. After the instance is started, you can restore an autobackup of the control file, mount it, then restore and recover the datafiles. Because you are restoring to a new host, you should review the considerations described in "Restoring the Database to a New Host".

The following scenario restores and recovers the database to the most recently available archived log, which in this example is log 1124 in thread 1. It assumes that:

In this scenario, perform the following steps:

  1. If possible, restore all relevant network files such as tnsnames.ora and listener.ora by means of operating system utilities.
  2. Start RMAN and connect to the target database. If you do not have the Oracle Net files, then connect through operating system authentication.
  3. Specify the DBID for the target database with the SET DBID command, as described in "Performing Recovery with a Backup Control File and No Recovery Catalog".
  4. Run the STARTUP NOMOUNT command. RMAN attempts to start the instance with a dummy server parameter file.
  5. Allocate a channel to the media manager and then run the RESTORE SPFILE FROM AUTOBACKUP command.
  6. Run STARTUP FORCE NOMOUNT mode so that the instance is restarted with the restored server parameter file.
  7. Allocate a channel to the media manager and then restore a control file autobackup (refer to"Performing Recovery with a Backup Control File and No Recovery Catalog").
  8. Mount the restored control file.
  9. Catalog any backups not recorded in the repository with the CATALOG command (refer to"Removing Recovery Catalog Records with Status DELETED").
  10. Restore the datafiles to their original locations. If volume names have changed, then run SET NEWNAME commands before the restore and perform a switch after the restore to update the control file with the new locations for the datafiles (refer to"Performing Disaster Recovery").
  11. Recover the datafiles. RMAN stops recovery when it reaches the log sequence number specified.
  12. Open the database in RESETLOGS mode. Only complete this last step if you are certain that no other archived logs can be applied.
    # Start RMAN and connect to the target database
    % rman TARGET SYS/oracle@trgt
    
    # Set the DBID for the target database
    RMAN> SET DBID 676549873;
    RMAN> STARTUP FORCE NOMOUNT;  # rman starts instance with dummy parameter file
    RUN
    {
      ALLOCATE CHANNEL t1 DEVICE TYPE sbt;
      RESTORE SPFILE FROM AUTOBACKUP;
    }
    # Restart instance with restored server parameter file
    RMAN> STARTUP FORCE NOMOUNT;  
    
    RMAN> RUN
    {
      # Manually allocate a channel to the media manager
      ALLOCATE CHANNEL t1 DEVICE TYPE sbt;
      # Restore autobackup of the control file. This example assumes that you have 
      # accepted the default format for the autobackup name.
      RESTORE CONTROLFILE FROM AUTOBACKUP;
      #  The set until command is used in case the database
      #  structure has changed in the most recent backups, and you wish to
      #  recover to that point-in-time. In this way RMAN restores the database
      #  to the same structure that the database had at the specified time.
      ALTER DATABASE MOUNT;
      SET UNTIL SEQUENCE 1124 THREAD 1;
      RESTORE DATABASE;
      RECOVER DATABASE;
    }
    RMAN> ALTER DATABASE OPEN RESETLOGS; # Reset the online logs after recovery 
    completes
    
    

The following example of the RUN command shows the same scenario except with new filenames for the restored datafiles:

RMAN> RUN
{
  #  If you need to restore the files to new locations, tell Recovery Manager
  #  to do this using SET NEWNAME commands:
  SET NEWNAME FOR DATAFILE 1 TO '/dev/vgd_1_0/rlvt5_500M_1';
  SET NEWNAME FOR DATAFILE 2 TO '/dev/vgd_1_0/rlvt5_500M_2';
  SET NEWNAME FOR DATAFILE 3 TO '/dev/vgd_1_0/rlvt5_500M_3';
  ALLOCATE CHANNEL t1 DEVICE TYPE sbt;
  RESTORE CONTROLFILE FROM AUTOBACKUP;
  ALTER DATABASE MOUNT;
  SET UNTIL SEQUENCE 124 THREAD 1;
  RESTORE DATABASE;
  SWITCH DATAFILE ALL; # Update control file with new location of datafiles.
  RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

Performing Block Media Recovery with RMAN

The BLOCKRECOVER command can restore and recover individual datablocks within a datafile. This procedure is useful when a trace file or standard output reveals that a small number of blocks within a datafile are corrupt.

Block media recovery is not useful in cases where the extent of data loss or corruption is not known; in this case, use datafile recovery instead.

See Also:

Recovering Datablocks By 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:

  1. Obtain the datafile numbers and block numbers for the corrupted blocks. Typically, you obtain this output from the standard output, the alert.log, trace files, or a media management interface. For example, you may see the following in a trace file:
    ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
    ORA-01110: data file 8: '/oracle/oradata/trgt/users01.dbf'
    ORA-01578: ORACLE data block corrupted (file # 2, block # 19)
    ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'
    
    
  2. Assuming that you have preallocated automatic channels, run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks as in the following example:
    RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19;
    

Recovering Datablocks By Using Selected 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:

  1. Obtain the datafile numbers and block numbers for the corrupted blocks. Typically, you obtain this output from the standard output, the alert.log, trace files, or a media management interface. For example, you may see the following in a trace file:
    ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
    ORA-01110: data file 8: '/oracle/oradata/trgt/users01.dbf'
    ORA-01578: ORACLE data block corrupted (file # 2, block # 19)
    ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'
    
    
  2. Assuming that you have preallocated automatic channels, execute the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, you can specify what type of backup should be used to restore the blocks:
    # restore from backupset
    RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
    # restore from datafile image copy
    RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 
          FROM DATAFILECOPY;
    
    

    You can indicate the backup by specifying a tag:

    # restore from backupset with tag "mondayam"
    RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199 
          FROM TAG = mondayam;
    
    

    You can limit the backup candidates to those made before a certain point:

    # restore using backups made before one week ago
    RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 
      RESTORE UNTIL 'SYSDATE-7';
    # restore using backups made before SCN 100
    RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 
      RESTORE UNTIL SCN 100;
    # restore using backups made before log sequence 7024
    RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 
      RESTORE UNTIL SEQUENCE 7024;
    
    

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.

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

  1. Query V$DATABASE_BLOCK_CORRUPTION to determine whether corrupt blocks exist in the most recent backups of the datafiles:
    SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
    
    
  2. Assuming that you have preallocated automatic channels, recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION by running the BLOCKRECOVER CORRUPTION LIST command. For example, this command restores blocks from backups created more than 10 days ago:
    BLOCKRECOVER CORRUPTION LIST 
      RESTORE UNTIL TIME 'SYSDATE-10';
    
    

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

RMAN Restore and Recovery Examples

The following sections illustrate the use of RMAN restore and recovery techniques in advanced scenarios.

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.

  1. After connecting to the target database and recovery catalog, run a LIST command to see a listing of datafile copies and their associated primary keys, as in the following example:
    LIST COPY;
    
    
  2. Copy the datafile copies to the new host with an operating system utility. For example, in UNIX:
    % cp -r /tmp/*dbf /net/new_host/oracle/oradata/trgt
    
    
  3. Start RMAN and then uncatalog the datafile copies on the old host. For example, enter:
    CHANGE COPY OF DATAFILE 1,2,3,4,5,6,7,8 UNCATALOG;
    
    
  4. Catalog the datafile copies, using their new filenames or CATALOG START WITH (if you know all the files are in directories with a common prefix easily addressed with a CATALOG START WITH). For example, run:
    CATALOG START WITH '?/oradata/trgt/';
    
    

    Or this example specifies files individually:

    
    CATALOG DATAFILECOPY
      '?/oradata/trgt/system01.dbf', '?/oradata/trgt/undotbs01.dbf', 
      '?/oradata/trgt/cwmlite01.dbf', '?/oradata/trgt/drsys01.dbf',
      '?/oradata/trgt/example01.dbf', '?/oradata/trgt/indx01.dbf', 
      '?/oradata/trgt/tools01.dbf', '?/oradata/trgt/users01.dbf';
    
    
  5. Perform the restore and recovery operation described in "Performing Disaster Recovery".

Restoring When Multiple Databases in the Catalog Share the Same Name: Example

As explained in the description for SET DBID in Oracle Database Recovery Manager Reference, you must run the SET DBID command to restore the control file when the target database is not mounted and multiple databases registered in the recovery catalog share the same name. In this case, do the following steps in order:

  1. Start RMAN and connect to the target database.
  2. Run the STARTUP FORCE NOMOUNT command.
  3. Run the SET DBID command to distinguish this connected target database from other target databases that have the same name.
  4. Run the RESTORE CONTROLFILE command. After restoring the control file, you can mount the database to restore the rest of the database.

This procedure avoids the RMAN-20005 message when you attempt to restore the control file. This message occurs because more than one target database has the same name, so RMAN requires the unique DBID to distinguishes the databases from one another.

Obtaining the DBID of a Database That You Need to Restore

If you have saved the RMAN output log files, then refer to these logs to determine the database identifier. RMAN automatically provides the DBID whenever you connect to the database:

% rman TARGET /

Recovery Manager: Release 10.1.0.2.0 - Production

connected to target database: RMAN (DBID=1231209694)

The output from RMAN jobs is also stored persistently in V$RMAN_STATUS and RC_RMAN_STATUS. The DBID is also stored in the RC_DATABASE and RC_DATABASE_INCARNATION recovery catalog views.

Because the names of the databases that are registered in the recovery catalog are presumed nonunique in this scenario, you must use some other unique piece of information to determine the correct DBID. If you know the filename of a datafile or online redo log associated with the database you wish to restore, and this filename is unique across all databases registered in the recovery catalog, then substitute this fully specified filename for filename_of_log_or_df in the following queries. Determine the DBID by performing one of the following queries:

SELECT DISTINCT DB_ID  
FROM DB, DBINC, DFATT  
WHERE DB.DB_KEY = DBINC.DB_KEY  
  AND DBINC.DBINC_KEY = DFATT.DBINC_KEY  
  AND DFATT.FNAME = 'filename_of_log_or_df';

SELECT DISTINCT DB_ID  
FROM DB, DBINC, ORL  
WHERE DB.DB_KEY = DBINC.DB_KEY  
  AND DBINC.DBINC_KEY = ORL.DBINC_KEY  
  AND ORL.FNAME = 'filename_of_log_or_df';  

Restoring a Backup Control File By Using the DBID

To set the DBID, connect RMAN to the target database and run the following SET command, where target_dbid is the value you obtained from the previous step:

SET DBID = target_dbid;

To restore the control file to its default location and then mount it, run:

RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;

To restore and recover the database, run:

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

Recovering a Database in NOARCHIVELOG Mode: Example

You can recover a database running in NOARCHIVELOG mode with 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:

In this case, you must perform an incomplete media recovery until Friday, since that is 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. If the correct online logs for the restored backup had been available, then you could have run RECOVER DATABASE without specifying NOREDO. The changes in the online logs would have been applied.

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.

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:

In this example, the following sequence of events occurs:

  1. You make a whole database backup of your ARCHIVELOG mode database.
  2. You create a tablespace history containing a single datafile called /mydb/history01.dbf.
  3. You populate the newly created datafile with data.
  4. You archive all the active online redo logs.
  5. A user accidentally deletes the datafile history01.dbf from the operating system before you have a chance to back it up.

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 hisotry;
# bring the recovered tablespace back online
SQL "ALTER TABLESPACE history ONLINE";

Transporting a Tablespace to a Different Database on the Same Platform: Example

You can use the transportable tablespace feature to copy a tablespace from one database to another database. As described in Oracle Database Administrator's Guide, the basic method for transporting tablespaces to a database on the same platform does not make use of RMAN. Nevertheless, if you use RMAN to back up your target database, then you can also use RMAN to transport backups of a tablespace from one database into another, following the procedure described in this section.

Reasons for using this procedure instead of ordinary tablespace transport include:

In the following procedure, assume that:

To transport a tablespace into a different database:

  1. Create an auxiliary instance on hostb according to the instructions in the "Preparing the Auxiliary Instance for Duplication: Basic Steps".
  2. Connect RMAN to the auxiliary instance as if it were a new target instance. For example:
    rman TARGET SYS/oracle@auxdb CATALOG rman/rman@catdb
    
    
  3. Restore the control file to a temporary location, then mount the control file and exit the session. For example:
    RESTORE CONTROLFILE TO '/net/hostb/tmp/cf.f';
    STARTUP FORCE MOUNT;
    EXIT
    
    
  4. Reconnect RMAN to the same auxiliary instance in NOCATALOG mode. You connect in NOCATALOG so that you do not pollute the recovery catalog with unecessary metadata about the restored files. For example:
    rman TARGET SYS/oracle@auxdb NOCATALOG
    
    
  5. Restore and recover the auxiliary database. Perform the following steps:
    1. Specify the past point in time, SCN, or archived log sequence number to which you want to recover the tablespace. You cannot recover the tablespace to the current time. Use the specified UNTIL time to indicate which backup of the tablespace that you want to restore.
    2. If the restored control file does not included configured channels, then manually allocate a channel to the device containing the backups.
    3. Run SET NEWNAME to specify temporary filenames for the SYSTEM datafiles and the datafiles containing rollback or undo segments.
    4. Run SET NEWNAME to specify the filenames in the trgtb database that will be used by the datafiles in the transported tablespace.
    5. Restore and recover the tablespaces.

    For example, run the following commands:

    RUN
    {
      SET UNTIL ARCHIVELOG 1243 THREAD 1;  # set the end recovery log
      ALLOCATE CHANNEL c1 DEVICE TYPE sbt; # allocate channels if not configured
      # specify temporary name for SYSTEM datafile
      SET NEWNAME FOR DATAFILE 1 TO '/net/hostb/tmp/df1.dbf';
      # specify temporary names for datafiles with undo or rollback segments
      SET NEWNAME FOR DATAFILE 2 TO '/net/hostb/tmp/df2.dbf';
      # specify names for datafiles to be plugged into trgtb database
      SET NEWNAME FOR DATAFILE 8 TO 
        '/net/hostb/oracle/oradata/trgtb/users01.dbf';
      # restore and recover the datafiles
      RESTORE DATAFILE 1, 2, 8;
      SWITCH DATAFILE ALL;   # points control file to SET NEWNAME filenames
      RECOVER DATAFILE 1, 2, 8;
    }
    
    
  6. Take all auxiliary tablespaces offline except the tablespaces that you recovered in the preceding step. For example:
    SQL 'ALTER TABLESPACE cwmlite,drsys,example,indx,tools OFFLINE IMMEDIATE';
    
    
  7. Open the auxiliary database with the RESETLOGS option. For example:
    ALTER DATABASE OPEN RESETLOGS;
    
    
  8. Make the tablespace that you are transporting into trgtb read-only. For example:
    SQL 'ALTER TABLESPACE users READ ONLY';
    
    
  9. Export the metadata from the transported users tablespace as described in "Step 2: Generate a Transportable Tablespace Set" in Oracle Database Administrator's Guide. For example:
    exp TRANSPORT_TABLESPACE=y TABLESPACES=(users) 
            TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp
    
    
  10. Shut down the auxiliary instance, and then delete all auxiliary files except the datafiles in the transported tablespace. For example:
    % sqlplus SYS/oracle@auxdb
    SQL> SHUTDOWN ABORT
    SQL> EXIT
    % rm /net/hostb/tmp/*
    
    
  11. Import the metadata from the transported tablespace into the trgtb database as described in "Step 4: Plug In the Tablespace Set" in Oracle Database Administrator's Guide. For example:
    imp TRANSPORT_TABLESPACE=y FILE=expdat.dmp
      DATAFILES=('/net/hostb/oracle/oradata/trgtb/users01.dbf')
      TABLESPACES=(users) TTS_OWNERS=(usera)  
      FROMUSER=(usera) TOUSER=(userb)
    
    

    Note: You cannot make a backup of a transported tablespace until after it has been opened read/write.