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: