RMAN RECOVER

RMAN RECOVER, Steps required

Recovering a Tablespace in an Open Database

Assume that the disk containing the data files for tablespace users becomes unavailable because of a hardware error, but is repaired after a few minutes. This example takes tablespace users offline, uses automatic channels to restore the data files to their default location and recover them (deleting the logs that it restored from tape), then brings the tablespace back online.

ALTER TABLESPACE users OFFLINE IMMEDIATE;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users DELETE ARCHIVELOG MAXSIZE 2M;
ALTER TABLESPACE users ONLINE;

Recovering Data Files Restored to New Locations

This example uses the preconfigured disk channel and manually allocates one media management channel to use data file copies on disk and backups on tape, and restores a data file in tablespace USERS to a different location.

CopyRUN
{  
  ALLOCATE CHANNEL ch1 DEVICE TYPE sbt;  
  ALTER TABLESPACE users OFFLINE IMMEDIATE;  
  SET NEWNAME FOR DATAFILE '/disk1/oradata/prod/users01.dbf' 
    TO '/disk2/users01.dbf';
  RESTORE TABLESPACE users;
  SWITCH DATAFILE ALL;
  RECOVER TABLESPACE users;
  ALTER TABLESPACE users ONLINE;
}

Performing DBPITR with a Backup Control File and Recovery Catalog

Assume that all data files, all control files, and archived redo log 58 were lost due to a disk failure. Also assume that you do not have incremental backups. You must recover the database with available archived redo log files. You do not need to restore tablespace TOOLS because it has been read-only since before the most recent backup. After connecting RMAN to the target database and recovery catalog, issue the following commands:

STARTUP FORCE NOMOUNT;
RUN
{  
  SET UNTIL SEQUENCE 40 THREAD 1;  # Recover database until log sequence 40 
  RESTORE CONTROLFILE;
  ALTER DATABASE MOUNT;
  RESTORE DATABASE SKIP TABLESPACE tools;
  RECOVER DATABASE SKIP TABLESPACE tools;
}
ALTER DATABASE OPEN RESETLOGS;

RMAN automatically skips the restore and recovery of data file 8, which is the data file in the read-only tablespace. The following portion of sample output indicates the skip:

Copyusing channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=104 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
 
skipping datafile 8; already restored to file /disk1/oradata/prod/tools01.dbf
channel ORA_DISK_1: starting datafile backup set restore
.
.
.
Finished restore at 19-FEB-13 

Starting recover at 19-FEB-13
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
datafile 8 not processed because file is read-only

Incrementally Updating Backups

By incrementally updating backups, you can avoid the overhead of making full image copy backups of data files, while also minimizing time required for media recovery of your database. This example enables you to recover to any SCN within the previous week, but enables you to avoid having to apply more than one day of redo.

Assume you run the following script daily. On first execution, the script creates an image copy backup of the database on disk with the specified tag. On the second through the seventh executions, the script creates a level 1 differential backup of the database. On the eighth and all subsequent executions, RMAN applies the level 1 incremental to the data file copy made 7 days ago and then makes a new level 1 backup with the changes from the previous day.

RUN
{
  RECOVER COPY OF DATABASE 
    WITH TAG 'incr_update' 
    UNTIL TIME 'SYSDATE - 7';
  BACKUP
    INCREMENTAL LEVEL 1 
    FOR RECOVER OF COPY WITH TAG 'incr_update'
    DATABASE;
}

RMAN Recovery from Loss of a Control File on a Standby Database

Assume that the standby database dgprod3 control files are lost because of a media failure. The primary and standby database share SBT storage. A backup of the primary database control file exists on tape.

You start the RMAN client and connect to dgprod3 as TARGET and connect to the recovery catalog. The following RMAN commands restore a control file that is usable by the standby database, update the file names to existing files on disk, and recover the standby database:

RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RECOVER DATABASE;

You can then start redo apply on the standby database.

Recovering a NOARCHIVELOG Database

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

Assume that you run database prod in NOARCHIVELOG mode with a recovery catalog. You shut down the database consistently and make a level 0 backup of database prod 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.

On Saturday, a media failure destroys half of the data files and all the online redo logs. Because the online logs are lost, you must specify the NOREDO option in the RECOVER command. Otherwise, RMAN searches for the redo logs after applying the Friday incremental backup and issues an error message when it does not find them.

After connecting RMAN to prod and the catalog database, recover as follows:

STARTUP FORCE NOMOUNT;
RESTORE CONTROLFILE;      # restore control file from consistent backup
ALTER DATABASE MOUNT;
RESTORE DATABASE;         # restore data files 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 log files, there is no way to recover changes made after the incremental backup.

Recovering All Block Corruption in the Database

This example runs a backup validation to populate the V$DATABASE_BLOCK_CORRUPTION view, then recovers any corrupt blocks recorded in the view. Sample output is included for both commands.

RMAN> VALIDATE DATABASE;
 
Starting validate at 19-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
.
.
.
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    FAILED 0              4070         57600           555975
  File Name: /disk1/oradata/prod/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       1              41550
  Index      0              7677
  Other      0              4303
.
.
.
RMAN> RECOVER CORRUPTION LIST;
 
Starting recover at 19-FEB-13
using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=104 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
searching flashback logs for block images until SCN 547548
finished flashback log search, restored 1 blocks
 
starting media recovery
media recovery complete, elapsed time: 00:00:03
 
Finished recover at 19-FEB-13

Recovering Tables Partitions from a Backup

This example recovers the partitions sales_2009 and sales_2010 from the table SALES to the time when the SCN of the target database was 34582. In the source database, the tables are owned by the schema SH. While importing these partitions into the target database, the partitions are created as tables named historic_sales_2009 and historic_sales_2010.

RECOVER TABLE SH.SALES:SALES_2009, SH.SALES:SALES_2010
     UNTIL SCN 34582
     AUXILIARY DESTINATION '/tmp/oracle/recover'
     REMAP TABLE 'SH'.'SALES':'SALES_2009':'HISTORIC_SALES_2009', 'SH'.'SALES':'SALES_2010':'HISTORIC_SALES_2010';

Recovering Tables to a Specified Log Sequence and Renaming the Tables

This example uses an auxiliary instance to recover the table EMP from the SCOTT schema to the time when the log sequence number of the database was 5466. After the EMP table is recovered, it is imported into the target database using the name MY_EMP.

RECOVER TABLE SCOTT.EMP
     UNTIL SEQUENCE 5466
     AUXILARY DESTINATION '/tmp/recover'
     REMAP TABLE 'SCOTT'.'EMP':'MY_EMP';

Recovering Tables to a Specified Time and Into a Different Tablespace

This example recovers tables EMP and DEPT to the point in time specified by the UNTIL TIME clause. The tables were originally part of the EXAMPLE_TBS tablespace. However, after the recovery operation, they are mapped to the tablespace MY_TBS in the target database.

RECOVER TABLE SCOTT.EMP, SCOTT.DEPT
      UNTIL TIME "TO_CHAR('12/23/2012 12:00:00','mm/dd/yyyy hh24:mi:ss')"
      AUXILIARY DESTINATION '/tmp/oracle/recover'
      REMAP TABLESPACE 'EXAMPLE_TBS':'MY_TBS';

Recovering Multiple Tables Into a Different Schema

This example recovers the tables HR.EMPLOYEES and SH.CHANNELS tables until the specified SCN. The recovered EMPLOYEES table is mapped to the EXAMPLES schema and the recovered CHANNELS table is mapped to the TEST schema. These schemas are already created in the target database. The CHANNELS table was stored in the SALES_TBS tablespace. After the table is recovered, it is mapped to the NEW_SALES_TBS tablespace. An auxiliary destination is used to store the temporary database files created as part of the table recovery process.

To recover this table, you must have a backup of the SYSTEMSYSAUX, undo, HR, and SH tablespaces. The database must be in ARCHIVELOG mode when the backup was created.

RECOVER TABLE hr.employees, sh.channels
UNTIL SCN 3456
REMAP TABLE hr.employees:examples.employees, sh.channels:test.channels
REMAP TABLESPACE 'SALES_TBS':'NEW_SALES_TBS'
AUXILIARY DESTINATION '/tmp/auxdest';

Recovering Table Partitions into a Different Schema

This example recovers the partitionsSALES_H1_1997 and SALES_H2_1997 in the SH schema to a previous point in time that is specified using an SCN. The partitions are renamed as historic_sales_h1_1997 and historic_sales_h2_1997 respectively. The recovered partitions must be imported into the schema new_sh, which exists in the target database.

COMPATIBLE must be set to 11.1.0 or higher because partitions are being recovered. A backup of the SYSTEMSYSAUXUNDO, and SH tablespaces at the specified recovery SCN exists. The database must be in ARCHIVELOG mode when the backup was created.

RECOVER TABLE sh.sales:sales_h1_1997, sh.sales:sales_h2_1997 
UNTIL SCN 810234878
REMAP TABLE sh.sales:sales_h1_1997:sh.historic_sales_h1_1997, sh.sales:sales_h2_1997:sh.historic_sales_h2_1997
AUXILIARY DESTINATION '/tmp/auxdest/';

Recovering a Cross-platform Backup of a PDB Into a Destination CDB

This example restores a cross-platform consistent incremental level 1 backup of the PDB pdb2 on a destination database. The destination CDB and the source CDB are on different platforms, but use the same endian format.

The USING clause specifies the name of the XML file that contains metadata required to plug the PDB into a destination CDB. The FOREIGN DATAFILECOPY clause lists all the data files that were created when this PDB’s data files were restored. Recovery needs to be performed for all these data files. The FILE_NAME_CONVERT clause specifies how file names on the source CDB must be renamed in the destination CDB

RECOVER 
FROM PLATFORM 'Linux x86 64-bit’
USING '/u02/backup_restore/metadata_pdb2.xml’
FILE_NAME_CONVERT = ('/u01/oradata','/u02/oradata/cdb')
FOREIGN DATAFILECOPY '/u02/oradata/pdb1.dbf’,’/u02/oradata/pdb1_tmp.dbf’
FROM BACKUPSET '/u02/backup_restore/bkup_level1_pdb1.bck’;

Also See:

RMAN User-Managed Recovery

Oracle RMAN