How do I restore using RMAN?

Start RMAN and connect to the target database. Run the STARTUP FORCE NOMOUNT command. Run the SET DBID command to distinguish this connected target database from other target databases that have the same name. Run the RESTORE CONTROLFILE command.

Which commands are used for RMAN database recovery?

The DBA enters the following commands in the RMAN client: RESTORE DATABASE; RECOVER DATABASE ; RMAN then queries the repository, which in this example is a recovery catalog. RMAN then decides which backup sets to restore, and which incremental backups and archived logs to use for recovery.

How do I restore an old RMAN backup?

Restore and Recover Options To use RMAN to restore or recover a database, we must first connect to the recovery catalog and then allocate channels to the tape or disk . The catalog has information about the database backup and backup set. A control file can be used for the same information.

What is RMAN restore?

RMAN restores each datafile to the location specified with SET NEWNAME , rather than its original location . After the RESTORE command but before the RECOVER command in your RUN block, use a SWITCH command to update the control file with the new filenames of the datafiles.

What is RMAN command?

Oracle Recovery Manager (RMAN) satisfies the most pressing demands of performant, manageable backup and recovery, for all Oracle data formats. RMAN provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems.

How to Restore Oracle Database using RMAN (with Examples) (2024)

As a Linux sysadmin, you might recover a system from backup, which may include Oracle Database.

So, it is essential for all admins to understand how to restore oracle database from backup.

Typically, DBAs will use Oracle RMAN utility to take a hot backup of the database.

This tutorial provides an introduction on how to restore an Oracle database from the RMAN backup.

If you are new to RMAN, you should first understand how to backup oracle database using RMAN.

For the impatient, here is a quick snippet of one particular rman restore scenario. Change this accordingly for your scenario. Read below to understand more details about these commands.

RMAN> SET DBID 12345;RMAN> STARTUP NOMOUNT;RMAN> RESTORE CONTROLFILE FROM "/backup/rman/ctl_c-12345-20141003-03"; RMAN> RESTORE DATABASE;RMAN> RECOVER DATABASE;RMAN> ALTER DATABASE OPEN RESETLOGS;

Verify Backup Location

Before the restore, verify the current RMAN configuration on the server where you’ll be performing the restore.

To connect to RMAN, execute the following rman command, which will take you to the RMAN> prompt. From here, you can execute all RMAN commands.

$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Mon Nov 17 11:17:11 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: DEVDB (DBID=821773)RMAN>

Execute “show all”, which will display all current RMAN configuration. As you see below, the current RMAN backup is located under “/backup/rman” directory.

RMNAN> SHOW ALL;CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;CONFIGURE BACKUP OPTIMIZATION ON;CONFIGURE DEFAULT DEVICE TYPE TO DISK;CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/ctl_%F';CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/data/rman-backup/full_%u_%s_%p' MAXPIECESIZE 4096 M;CONFIGURE MAXSETSIZE TO UNLIMITED;CONFIGURE ENCRYPTION FOR DATABASE OFF;CONFIGURE ENCRYPTION ALGORITHM 'AES128';CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE;CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backup/rman/snapcf_med.f';

On a high-level, the following three steps are performed in recovering the database from RMAN backup.

Restore controlfile from backup
Restore the databse
Recover the database

WARNING: Execute rman restore commands only on a test instance. If you try these restore commands in a production instance, and if something goes wrong, you’ll lose your production data.

Step 1: Restore ControlFile from Backup

First, you may want to restore the control file from the backup before you start the restore.

This step is required only if you are restoring the backup on a new server where the control file doesn’t exist. Or, if the control file on the system you are restoring is corrupted or missing.

Step 2: Restore the Database

To restore from the RMAN full backup that is located under the /backup/rman directory, execute the following command.

RMAN> RESTORE DATABASE;

Apart from the above straight forward restore database, there are also few variations of this command which are explained in the examples below. Use the one that is appropriate for your situation.

The following is a sample output of the above restore database command:

RMAN>Starting restore at 22-NOV-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=125 devtype=DISK...channel ORA_DISK_2: starting datafile backupset restorechannel ORA_DISK_2: specifying datafile(s) to restore from backup setrestoring datafile 00020 to /u01/oradata/devdb/dev01_1.dbfrestoring datafile 00021 to /u02/oradata/devdb/report_data.dbfrestoring datafile 00022 to /u01/oradata/devdb/analytics01.dbfchannel ORA_DISK_2: reading from backup piece /backup/rman/full_32qakgmpa_123456_1channel ORA_DISK_4: starting datafile backupset restore....

Step 3: Recover Database (and ResetLogs)

If you’ve restored the controlfile from the backup, you need to perform this step.

In the last step, recover the database, and then you should open the database with resetlogs options as show below:

RMAN> RECOVER DATABASE;RMAN> ALTER DATABASE OPEN RESETLOGS;

Restore Specific Tablespace

Instead of restoring the full database, you can also restore only specific tablespace as shown below.

The following will restore only the dev1 tablespace

RMNAN> RESTORE TABLESPACE dev1;

You can also restore more than one tablespace by separating them with commas as shown below. This will restore both dev1 and dev2 tablespace.

RMNAN> RESTORE TABLESPACE dev1, dev2;

Restore Specific Datafiles

You can also restore only a specific datafile from the backup using the restore datafile command as shown below.

The following will restore only the dev1_01.dbf datafile.

Restore the Archived Redo Logs

The following will restore the archive logs to the default location.

RMNAN> RESTORE ARCHIVELOG ALL;

If you want to restore the archive logs to a new directory, do the following:

RMNAN> SET ARCHIVELOG DESTINATION TO '/home/arc_logs_new/';RMNAN> RESTORE ARCHIVELOG ALL;

The following will restore only specific sequence number of the archive logs that are between 153 and 175.

RMNAN> RESTORE ARCHIVELOG FROM SEQUENCE 153 UNTIL SEQUENCE 175;

You can also restore archivelogs by specifying the starting point of the SCN number as shown below.

RMNAN> RESTORE ARCHIVELOG FROM SCN 56789;

Please note that when you issue the recover database as mentioned in the step 3 above, it will look for all required archive logs from the archive log destination and applies them to the oracle database datafiles.

Recover Specific Tablespace or Datafile

Similar to restoring specific tablespace and datafile, depending on the restore operation you did, perform the corresponding recover option. The following are few examples:

RMAN> RECOVER TABLESPACE dev1;RMAN> RECOVER TABLESPACE dev1, dev2;RMAN> RECOVER DATAFILE '/u01/oradata/devdb/dev1_01.dbf'RMAN> RECOVER DATAFILE 34, 35

Note: You can also append “DELETE ARCHIVELOG” option to the recover command, which will delete the restored archive logs from the disk which are not required anymore. For example:

RMNAN> RECOVER TABLESPACE dev1 DELETE ARCHIVELOG;

Preview the Restore

Before restoring the database, if you like to view the details of all the backupsets that will be used along with the SCNs that are part of the backup file, you can append “PREVIEW” to any of the restore databse command. Please note that the preview output will be similar to the rman list summary command output.

This really doesn’t do the restore. This will only provide the report. You can use this before you restore the database.

RMAN> RESTORE DATABASE PREVIEW;

Pelase note that if the output of the above PREVIEW command is too detailed, and you need only the summary, you can execute the following PREVIEW SUMMARY.

RMAN> RESTORE DATABASE PREVIEW SUMMARY;

The following is the sample output of the above preview summary:

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag------- -- -- - ----------- --------------- ------- ------- ---------- ---234587 B F A DISK 22-NOV-14 1 1 YES WEEKLY_FULL_BKUP234588 B F A DISK 22-NOV-14 1 1 YES WEEKLY_FULL_BKUP234589 B F A DISK 22-NOV-14 1 1 YES WEEKLY_FULL_BKUP234580 B F A DISK 22-NOV-14 1 1 YES WEEKLY_FULL_BKUP234581 B F A DISK 22-NOV-14 1 1 YES WEEKLY_FULL_BKUP..

The following are also valid preview option. You can append SUMMARY to all of the following commands:

RESTORE TABLESPACE dev1 PREVIEW;RESTORE DATAFILE '/u01/oradata/devdb/dev1_01.dbf' PREVIEW;RESTORE ARCHIVELOG ALL PREVIEW;RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;

Validate the Backup Before Restore (Dry-run)

Before you perform the restore, you might want to really validate the backup to make sure that the backup itself is not corrupted, and all the file required to perform the backup is actually present in the backup directory.

RMAN> RESTORE DATABASE VALIDATE;

Depending on the size of the database this operation might take some time to complete. Technically, this is same as restoring the database except that this doesn’t do the real restore, and it performs only the Dry-run. The validate operation will really read all the blocks in the RMAN backup to make sure they are valid.

The following is a sample output of the restore validate command:

Starting restore at 22-NOV-14allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=123 devtype=DISK...channel ORA_DISK_1: starting validation of datafile backupsetchannel ORA_DISK_2: starting validation of datafile backupsetchannel ORA_DISK_1: reading from backup piece /backup/rman/full_3abcde4po_123456_1channel ORA_DISK_2: reading from backup piece /backup/rman/full_32qakgmpa_123456_1channel ORA_DISK_1: restored backup piece 1...channel ORA_DISK_2: validation complete, elapsed time: 00:53:11Finished restore at 22-NOV-14

RMAN Restore Common Error Messages

The following are some of the most common RMAN restore error messages:

Error 1: Start-up mount might give the following RMAN-04014 error:

RMAN> STARTUP NOMOUNTRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of startup command at 10/03/2014 11:04:19RMAN-04014: startup failed: ORA-09925: Unable to create audit trail fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 9925

Solution 1: Create the audit trial directory. Change the path to match your system.

mkdir -p $ORACLE_BASE/admin/devdb/adump

Error 2: Restore controlfile might give the following RMAN-00558, RMAN-01006, or RMAN-02001 error.

RMAN> RESTORE CONTROLFILE FROM /backup/rman/ctl_c-12345-20141003-03RMAN-00558: error encountered while parsing input commandsRMAN-01006: error signaled during parseRMAN-02001: unrecognized punctuation symbol "/"

Solution 2: Make sure to include the whole controlfile patch within quotes as shown below.

RMAN> RESTORE CONTROLFILE FROM "/backup/rman/ctl_c-12345-20141003-03";

Error 3: Restore controlfile (or restore database) might give the following RMAN-03002, ORA-19870, or ORA-27040 error along with “Linux-x86_64 Error: 2: No such file or directory”.

RMAN> RESTORE CONTROLFILE FROM "/backup/rman/ctl_c-12345-20141003-03"; RMAN-03002: failure of restore command at 10/03/2014 11:12:25ORA-19870: error while restoring backup piece /backup/rman/ctl_c-12345-20141003-03ORA-19504: failed to create file "/u01/oradata/devdb/control01.ctl"ORA-27040: file create error, unable to create fileLinux-x86_64 Error: 2: No such file or directory

Solution 3: In most cases it is missing directory. Create the appropriate directories accordingly. You might also get not found messsage for flash_recovery_area directory. So, create them both. Or, create whatever directory the above error message is complaining about.

mkdir -p $ORACLE_BASE/oradata/devdbmkdir -p $ORACLE_BASE/flash_recovery_area/devdb/

Error 4: When you alter database open resetlogs, you might get the following ORA-01152 error message:

RMAN> ALTER DATABASE OPEN RESETLOGS;ERROR at line 1:ORA-01152: file 1 was not restored from a sufficiently old backupORA-01110: data file 1: '/u01/oradata/devdb/system01.dbf'

Solution 4: You can try couple of things. First, try to recover database until cancel as shown below. If that doesn’t help, remove the “UNTIL CANCEL” from the following command, and specify the redo log file, when it asks for “Specify log:” during the recover database command:

RMAN> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;