RMAN Recovery Methods
When performing a restore operation, it is best to open two telnet sessions, one for SQL commands, and one for RMAN commands. For the rest of this document, RMAN commands will be prefaced with the RMAN> prompt, and SQL commands will be prefaced with the SQL> prompt. A $ indicates that the command is executed from the Unix prompt.
Restoring and Recovering All Datafiles
In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with backup database plus archivelog;
Your first step is to make sure that the target database is shut down:
$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
ORACLE instance shut down.
Next, you need to start up your target database in mount mode. RMAN cannot restore datafiles unless the database is at least in mount mode, because RMAN needs to be able to access the control file to determine which backup sets are necessary to recover the database. If the control file isn't available, you have to recover it first. Issue the STARTUP MOUNT command shown in the following example to mount the database:
SQL> startup mount;
Oracle instance started.
Database mounted.
Since backup set files are created in an RMAN-specific format, you must use RMAN to restore the datafiles. To use RMAN, connect to the target database:
$ rman target / rcvcat rcvcat/rcvcat@oemprod
The remainder of this example shows how to restore all of the datafiles of the target database. When the restore command is executed, RMAN will automatically go to its last good backup set and restore the datafiles to the state they were in when that backup set was created.
When restoring database files, RMAN reads the datafile header and makes the determination as to whether the file needs to be restored. The recovery is done by allocating a channel for I/O and then issuing the RMAN restore database command.
With Oracle9i and above, you don't need to allocate a channel explicitly. Instead, you can use the default channel mode:
RMAN> restore database;
RMAN> recover database;
SQL> alter database open;
For Oracle8i, the ALLOCATE, RESTORE, and RECOVER commands need to be enclosed by the run{} command:
RMAN> run {
allocate channel d1 type disk;
restore database;
recover database;
}
alter database open;
Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.
Restoring Specific Tablespaces
In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with backup database plus archivelog;
Take the tablespace that needs recovery offline, restore the tablespace, recover the tablespace, and bring the tablespace online. If you cannot take the tablespace offline, then shutdown abort the database and restore in mount mode.
First try to take the tablespace offline;
$ sqlplus "/ as sysdba"
SQL> alter tablespace tab offline;
If this works, continue with the RMAN recovery:
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> restore tablespace tab;
RMAN> recover tablespace tab;
SQL> alter tablespace tab online;
If taking the tablespace offline fails, follow these steps:
$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> restore tablespace tab;
RMAN> recover tablespace tab;
SQL> alter database open;
Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.
Restoring Specific Datafiles
In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with backup database plus archivelog;
Take the datafile that needs recovery offline, restore the datafile, recover the datafile, and bring the datafile online. If you cannot take the datafile offline, then shutdown abort the database and restore in mount mode.
First try to take the datafile offline:
SQL> alter database datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf' offline;
If this works, continue with the RMAN recovery:
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> restore datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf'
RMAN> recover datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf'
SQL> alter database datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf' online;
If taking the datafile offline fails, follow these steps:
$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> restore datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf';
RMAN> recover datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf';
SQL> alter database open;
Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.
Restoring Control Files
In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;
In an ideal world you'll never use RMAN to restore a control file. But if something catastrophic happens, and you lose all control files, here are the steps for getting them back:
The following examples assume that you are using a catalog. First, here's the simplest Oracle9i syntax for restoring a control file:
$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod
Set the database id (DBID) with the following command. This is a 10-digit number that RMAN uses to uniquely identify this database in the recovery log. The number can be obtained from any previous RMAN backup log file.
RMAN> set dbid = xxxxxxxxxx;
RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;
If this fails with ...
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
... then you must perform a recover database:
SQL> shutdown abort;
SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;
Note, that all offline archivelogs are now useless, perform a full back as soon as possible.
Restoring Online Redologs
In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod
Set the database id (DBID) with the following command. This is a 10-digit number that RMAN uses to uniquely identify this database in the recovery log. The number can be obtained from any previous RMAN backup log file.
RMAN> set dbid = xxxxxxxxxx;
RMAN> restore controlfile;
SQL> alter database mount;
RMAN> restore database;
RMAM> recover database;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/28/2004 11:03:23
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 8448414
Since the online logs were lost, complete recovery is not possible. Open the database with resetlogs to continue.
RMAN> alter database open resetlogs;
-------------------------------------IMPORTANT-------------------------------------------
During this type of recovery, if you receive error messages like this:
RMAN> restore database;
Starting restore at 11-JUL-05
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/11/2005 14:25:22
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
…use the following directions to recover (recreate the controlfile):
- With the database mounted, execute ‘alter database backup controlfile to trace resetlogs;’
- Perform a shutdown abort on the database, but remain at the SQL> prompt.
- In another telnet session, go to the udump directory to retrieve the resulting trace file and copy it to another location to edit it.
- Edit the file and add the phrase “until cancel” to the recover database command at the end. The phrase should read “recover database until cancel using backup controlfile”. Remove the “alter database open” command after the recover command. Save the file with a .sql extension.
- Back at the SQL> prompt, execute the modified trace file. When prompted for an archived log, type in “cancel” and the reply should be “media recovery cancelled”.
- Issue “alter database open resetlogs”. The database should open after a few moments.
- Connect to the RMAN recovery catalog and issue the “reset database” command.
- Perform a full RMAN backup as soon as possible.
----------------------------------------------------------------------------------------
Time-Based, Change-Based, or SCN-based Incomplete Recovery
Incomplete recovery uses a backup to produce a non-current version of the database. In other words, you
do not apply all of the redo records generated after the most recent backup.
You usually perform incomplete recovery of the whole database in the following situations:
Media failure destroys some or all of the online redo logs. A user error causes data loss, for example, a user inadvertently drops a table. You cannot perform complete recovery because an archived redo log is missing. You lose your current control file and must use a backup control file to open the database. To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database; in other words, a database with a new stream of log sequence numbers starting with log sequence 1.
NOTE – Start every RMAN incomplete recovery with the following commands:
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprod
--For time-based recovery, use these commands:
RMAN> restore database until time "to_date('07/12/05 12:0:00','MM/DD/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('07/12/05 12:0:00','MM/DD/YY HH24:MI:SS')";
media recovery complete.
SQL> alter database open resetlogs;
--For SCN-based recovery, user these commands:
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
media recovery complete.
SQL> alter database open resetlogs;
--For change-based recovery, user these commands:
RMAN> restore database until sequence 9923; --Archived log sequence number
RMAN> recover database until sequence 9923; --Archived log sequence number
media recovery complete.
SQL> alter database open resetlogs;
Once the recovery has been completed, execute the following steps:
- Delete prior backups with this command (from the RMAN prompt):
RMAN> delete force backup;
This command removes all prior backups from the RMAN catalog as they can no longer be used once the database has been restarted with the resletlogs option. After completing this command, create a new RMAN backup to establish a new baseline.
Recovering Archived Logs only
In the event that you want to recover the database archived redo logs until a desired time, you can use the following commands:
$ rman target / rcvcat rcvcat/rcvcat@oemprod
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
or
RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')
UNTIL TIME 'SYSDATE';
