RMAN RESTORE

RMAN RESTORE Operations

Restoring a Tablespace

This example takes a tablespace offline, restores it, then performs media recovery.

ALTER TABLESPACE users OFFLINE IMMEDIATE; 
RESTORE TABLESPACE users; 
RECOVER TABLESPACE users;  
ALTER TABLESPACE users ONLINE;

Setting a New Name for a Restored Data File

Assume that /disk1, which contains data file 9, suffers a media failure. This example specifies a new name for the data file, restores it, updates the control file to use the new name, recovers it, and then brings it online:

RUN
{
  ALTER DATABASE DATAFILE 9 OFFLINE;
  SET NEWNAME FOR DATAFILE 9 TO '/disk2/users01.dbf';
  RESTORE DATAFILE 9;
  SWITCH DATAFILE ALL;
  RECOVER DATAFILE 9;
  ALTER DATABASE DATAFILE 9 ONLINE;
}

Restoring the Control File When Using a Recovery Catalog

Assume that you want to restore the control file backup with the tag monday_cf_backup. You start the RMAN client, connect to the target and recovery catalog databases, and run the following commands:

RUN
{ # SET DBID is not necessary when RMAN is connected to a recovery catalog
  STARTUP FORCE NOMOUNT;
  RESTORE CONTROLFILE FROM TAG 'monday_cf_backup';
  ALTER DATABASE MOUNT;
  RESTORE DATABASE;
  RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS; # required after recovery with backup control file

RMAN restores the control file to its default location and replicates it automatically to all CONTROL_FILES locations. RMAN mounts the control file and restores and recovers the database. RMAN automatically updates the control file to reflect the structure of the restored database based on the metadata in the recovery catalog.

Recovering the Database with a Control File Autobackup

Assume that the control file and some data files are lost and must be restored from tape. Because RMAN does not use a recovery catalog in this scenario, the SET DBID command is necessary to identify the control file to be restored. The example restores the control file from tape, mounts the database, and then restores and recovers the database.

CONNECT TARGET /
STARTUP FORCE NOMOUNT;
SET DBID 36508508;  # required when restoring control file in NOCATALOG mode
RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
  RESTORE CONTROLFILE FROM AUTOBACKUP;
  ALTER DATABASE MOUNT;
  RESTORE DATABASE;
  RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;

Restoring a Control File Autobackup to a Nondefault Location

In this scenario, the control file autobackup is located on disk in a nondefault location. RMAN starts searching for backups with a sequence number of 20, and searches backward for 5 months:

CONNECT TARGET /
STARTUP FORCE NOMOUNT
SET DBID 36508508;  # required when restoring control file in NOCATALOG mode
RUN
{
  SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK 
    TO '/disk1/prod_cf_auto_%F';
  RESTORE CONTROLFILE TO '/tmp/cf_auto.dbf' FROM AUTOBACKUP 
    MAXSEQ 20 MAXDAYS 150;
  ALTER DATABASE MOUNT;
  RESTORE DATABASE;
  RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;

Restoring Control File Autobackups Stored on Tape or Oracle Cloud

Database backups are created on disk according to a backup schedule. Control file autobackups are enabled, but a recovery catalog is not used. Subsequently, these backup sets are backed up to Oracle Cloud using the BACKUP BACKUPSET ALL command. This example restores a control file using the autobackup that was created on Oracle Cloud. RMAN scans both disk and Oracle Cloud backups and then retrieves the latest control file autobackup. You must configure one disk channel and one SBT channel, for Oracle Cloud.

RUN
{
SET DBID 1928835918;
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
ALLOCATE CHANNEL sbt1 DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/disk1/oss/libopc.so ENV=(OPC_PFILE=/disk1/oss/opc_sbt.ora)';
RESTORE CONTROLFILE FROM AUTOBACKUP;
}

Restoring a Server Parameter File Autobackup to the Current Location

The following series of commands restores the current server parameter file in NOCATALOG mode and then starts the instance with the restored server parameter file.

CONNECT TARGET /
SET DBID 1620189241; # set dbid to dbid of target database
STARTUP FORCE NOMOUNT; # start instance with dummy SPFILE
RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
  RESTORE SPFILE FROM AUTOBACKUP; # FROM AUTOBACKUP needed in NOCATALOG mode
  STARTUP FORCE; # startup with restored SPFILE
}

Previewing Backups

This example shows the results of a RESTORE ... PREVIEW command, which identifies the backup sets RMAN selects for use in restoring archived redo log files.

RMAN> RESTORE ARCHIVELOG ALL DEVICE TYPE sbt PREVIEW;
 
Starting restore at 01-MAR-13
released channel: ORA_SBT_TAPE_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=85 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
 
List of Backup Sets
===================
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
53      1.25M      SBT_TAPE    00:00:18     01-MAR-13
        BP Key: 53   Status: AVAILABLE  Compressed: NO  Tag: TAG20130301T150155
        Handle: 2aibhej3_1_1   Media: RMAN-DEFAULT-000001
 
  List of Archived Logs in backup set 53
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    8       526376     01-MAR-13 527059     01-MAR-13
  1    9       527059     01-MAR-13 527074     01-MAR-13
  1    10      527074     01-MAR-13 527091     01-MAR-13
  1    11      527091     01-MAR-13 527568     01-MAR-13
  1    12      527568     01-MAR-13 527598     01-MAR-13
validation succeeded for backup piece
Finished restore at 01-MAR-13

Recalling Offsite Backups from Offsite Storage

When used with a media manager that reports information about offsite storage of backups and supports recalling offsite backups, RESTORE ... PREVIEW RECALL requests that any media needed to restore archived redo log files from backup be recalled from offsite storage.

RMAN> RESTORE ARCHIVELOG ALL PREVIEW RECALL;

Starting restore at 10-JUN-13
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
 
 
List of Backup Sets
===================
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
31      12.75M     SBT_TAPE    00:00:02     10-JUN-13     
        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20130610T152755
        Handle: 15gmknbs   Media: /v1,15gmknbs
 
  List of Archived Logs in backup set 31
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1       221154     06-JUN-13 222548     06-JUN-13
  1    2       222548     06-JUN-13 222554     06-JUN-13
  1    3       222554     06-JUN-13 222591     06-JUN-13
  1    4       222591     06-JUN-13 246629     07-JUN-13
  1    5       246629     07-JUN-13 262451     10-JUN-13
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
32      256.00K    SBT_TAPE    00:00:01     10-JUN-13     
        BP Key: 34   Status: AVAILABLE  Compressed: NO  Tag: TAG20130610T153105
        Handle: 17gmknhp_1_1   Media: /v1,17gmknhp_1_1
 
  List of Archived Logs in backup set 32
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    6       262451     10-JUN-13 262547     10-JUN-13
  1    7       262547     10-JUN-13 262565     10-JUN-13
 
Initiated recall for the following list of offsite backup files
==========================================================
        Handle: 15gmknbs   Media: /v1,15gmknbs
Finished restore at 10-JUN-13

Example 3-33 Validating the Restore of a Backup

The following example illustrates using RESTORE... VALIDATE to confirm that backups required to restore the database are present on disk or tape, readable, and not corrupted:

RMAN> RESTORE DATABASE VALIDATE;
 
Starting restore at 01-MAR-13
using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=85 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
 
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /disk2/PROD/backupset/2013_03_01/o1_mf_nnndf_TAG20130301T161038_2ygtvzg0_.bkp
channel ORA_DISK_1: piece handle=/disk2/PROD/backupset/2013_03_01/o1_mf_nnndf_TAG20130301T161038_2ygtvzg0_.bkp tag=TAG20130301T161038
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:16
Finished restore at 01-MAR-13

Restoring Data Files on the Primary Database Using the Standby

This example restores the data file users.dbf that was lost on the primary database by restoring it, over the network, from the standby database:

RESTORE DATAFILE '/oradata/files/users.dbf'
          FROM SERVICE standby_tns
          SECTION SIZE 200M
          USING COMPRESSED BACKUPSET;

The service name of the remote database that contains the data file to be restored is standby_tns. The SECTION SIZE clause indicates that the data file is restored using multisection backup sets. The USING COMPRESSED BACKUPSET clause specifies that the backup sets are compressed using the default compression algorithm that is configured for RMAN.

Restoring a Database from a Cross-Platform Database Backup

This backup was created on a Microsoft Windows IA (32-bit) platform and is being restored on Linux x86 64-bit. The backup set containing the database is stored in /tmp/xplat_restores/full_db.bck. The restored data files are stored in /oradata/datafiles using unique file names that begin with df_

RESTORE 
   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
   ALL FOREIGN DATAFILES
   FORMAT '/oradata/datafiles/df_%U'
   FROM BACKUPSET '/tmp/xplat_restores/full_db.bck';

Restoring a Tablespace from a Cross-Platform Tablespace Backup

This example restores the tablespace example from the cross-platform backup created in Example 2-35. The backup set containing the tablespace to be restored is stored in /tmp/xplat_restores/example_readonly.bck. The restored data files use unique names that being with example_readonly_. The metadata required to plug this tablespace into the target database is stored in the backup set /tmp/xplat_restores/example_dmp.bck.

RESTORE
   FOREIGN TABLESPACE example
   FORMAT '/tmp/xplat_restores/example_readonly_%U_%n'
   FROM BACKUPSET '/tmp/xplat_restores/example_readonly.bck'   DUMP FILE
   DATAPUMP DESTINATION '/tmp/datapump'
   FROM BACKUPSET '/tmp/xplat_restores/example_dmp.bck';

Restoring a Tablespace Using a Cross-Platform Backup Consisting of Multiple Backup Sets

You must use a separate BACKUPSET clause for each backup set. The backup sets must be listed in the order in which they were created, starting with the first backup set.

RESTORE
   BACKUPSET '/tmp/xplat_restores/db_multiple_59nkcln6_1_1'
   BACKUPSET '/tmp/xplat_restores/db_multiple_5ankcln7_1_1'
   BACKUPSET '/tmp/xplat_restores/db_multiple_5bnkcln8_1_1'
   BACKUPSET '/tmp/xplat_restores/db_multiple_5cnkcln9_1_1'
   DUMP FILE
       FROM BACKUPSET '/tmp/xplat_restores/db_multiple.dmp';

Restoring a Tablespace Using a Cross-Platform Consistent Backup that Contains Multiple Backup Pieces

The export dump file containing the metadata of the tablespace is stored in /tmp/xplat_restores/example_mutli-piece_dmp.bck. The FROM BACKUPSET clause contains a comma-separated list of all the backup pieces. List the backup pieces in the same order in which they were created.

RESTORE
   FOREIGN TABLESPACE sales
   FORMAT '/tmp/xplat_restores/datafiles/example_mult_%u'
   FROM BACKUPSET 
       '/tmp/xplat_restores/example_multi-piece_0lnjnujs_1_1',
       '/tmp/xplat_restores/example_multi-piece_0lnjnujs_2_1',
       '/tmp/xplat_restores/example_multi-piece_0lnjnujs_3_1'
   DUMP FILE 
   FROM BACKUPSET '/tmp/xplat_restores/example_multi-piece_dmp.bck';

Restoring a Cross-Platform Inconsistent Tablespace Backup

The restored data files are stored using unique names that begin with inconsist_. Because the tablespace was not read-only when the backup was created, you cannot directly plug it into the target database. You must apply an incremental backup of the tablespace taken when the tablespace is read-only to the recovered foreign data files.

RESTORE
    FOREIGN TABLESPACE example
    FORMAT '/tmp/xplat_restores/datafiles/inconsist_%u'
    FROM BACKUPSET '/tmp/xplat_backups/example_inconsist.bck';

Restoring a PDB into a New CDB Using Cross-platform Backups of the PDB

This example restores a cross-platform backup of the pluggable database (PDB) pdb3 on the destination CDB. The destination CDB and the source CDB are on different platforms, but use the same endian format.

The destination CDB is open in read-write mode. The backup set contained the cross-platform backup of the source PDB is stored in /u02/backups/backup_full_pdb3.bck. The metadata required to plug the source PDB into the destination CDB is stored in /u02/backups/metadata_pdb3.xml. The FILE_NAME_CONVERT clause specifies how file names on the source CDB must be renamed in the destination CDB.

RESTORE FROM PLATFORM 'Linux x86 64-bit’
USING '/u02/backups/metadata_pdb3.xml’
FILE_NAME_CONVERT = ('/u01/oradata’,’/u02/oradata/cdb’)
FOREIGN PLUGGABLE DATABASE pdb3 FORMAT '/u02/oradata/cdb/pdb3_%U’
FROM BACKUPSET '/u02/backups/backup_full_pdb3.bck’;

Moving an On-premise Database to Oracle Cloud with Encryption

This example moves an on-premise database to Oracle Cloud by restoring database backups. The on-premise database does not use encryption and the database backups are also not encrypted. However, the database on Oracle Cloud must use encryption. Therefore, to maintain consistency and security, the backups of the unencrypted database must be restored on Oracle Cloud using encryption. You can achieve this by using the AS ENCRYPTED clause with the RESTORE command. The Oracle keystore must be open before the RESTORE...AS ENCRYPTED command is run.

The COMPATIBLE parameter for the on-premise database is set to 12.2 and the backups are created with COMPATIBLE set to 12.2. The following commands restore the unencrypted backups to create a database on Oracle Cloud with encryption:

SELECT ts#, encryptionalg, encryptedts, key_version, status FROM v$encrypted_tablespaces;
STARTUP FORCE MOUNT;
RESTORE DATABASE AS ENCRYPTED;
RECOVER DATABASE;
ALTER DATABASE OPEN;

Moving a Database from Oracle Cloud to an On-premise Model

This example moves a database from Oracle Cloud, which uses encryption, to an on-premise model. Because encryption is not mandatory for on-premise databases, you decide to restore the encrypted backups from Oracle Cloud without using encryption. Use the AS DECRYPTED clause of the RESTORE command to perform this restore operation.

The COMPATIBLE parameter for the on-premise database is set to 12.2. Backups were created with COMPATIBLE set to 12.2. The following commands restore the encrypted backups from Oracle Cloud to an on-premise database and without using encryption:

SELECT ts#, encryptionalg, encryptedts, key_version, status FROM v$encrypted_tablespaces;
STARTUP FORCE MOUNT;
RESTORE DATABASE AS DECRYPTED
RECOVER DATABASE;
ALTER DATABASE OPEN;

Also See:

Oracle RMAN