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.
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 2014
Copyright (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 ; # default
CONFIGURE 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.
RMAN> SET DBID 12345;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM "/backup/rman/ctl_c-12345-20141003-03";
RMAN> ALTER DATABASE MOUNT;
Before you start the RMAN restore process, do the following:
- Set the DBID. You can get the dbid from the name of the control file. This is the number that comes after “ctrl_c-” and the next hyphen. Please note that this depends on the controlfile format that was set on your system. Do a show all to view the format. For this example, the format was: “ctl_%F”
- Startup the database in nomount option
- Restore the controlfile form the backup. In this example, the RMAN backup is located under /backup/rman directory. Under this directory, you may have multiple control files. Based on the tiemstamp pick the appropriate one for which you have the full backup.
- After restoring the control file, mount the database.
The following is an example output of the restore controlfile command:
Starting restore at 22-NOV-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=124 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/oradata/devdb/control01.ctl
output filename=/u02/oradata/devdb/control02.ctl
output filename=/u03/oradata/devdb/control03.ctl
Finished restore at 22-NOV-14
When the RMAN backup was taken, if a tag was specified, you can also restore controlfile based a tag name as shown below.
RMNAN> RESTORE CONTROLFILE FROM TAG 'WEEKLY_FULL_BKUP';
You can also use the autobackup option to restore the controlfile as shown below:
RMNAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
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-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=125 devtype=DISK
...
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00020 to /u01/oradata/devdb/dev01_1.dbf
restoring datafile 00021 to /u02/oradata/devdb/report_data.dbf
restoring datafile 00022 to /u01/oradata/devdb/analytics01.dbf
channel ORA_DISK_2: reading from backup piece /backup/rman/full_32qakgmpa_123456_1
channel 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.
RMNAN> RESTORE DATAFILE '/u01/oradata/devdb/dev1_01.dbf'
You can also restore more than one datafile by separating them with commas as shown below. This will restore both dev1_01 and dev1_02 datafiles
RMNAN> RESTORE DATAFILE '/u01/oradata/devdb/dev1_01.dbf', '/u01/oradata/devdb/dev1_02.dbf'
Instead of the datafile name, you can also specify the datafile number.
RMNAN> RESTORE DATAFILE 34, 35
The datafile number (file_id) can be found in the dba_data_files table:
SQL> select file_id, file_name from dba_data_files
FILE_ID FILE_NAME
---------- -------------------------------
34 /u01/oradata/devdb/dev1_01.dbf
35 /u01/oradata/devdb/dev1_02.dbf
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_BKUP
234588 B F A DISK 22-NOV-14 1 1 YES WEEKLY_FULL_BKUP
234589 B F A DISK 22-NOV-14 1 1 YES WEEKLY_FULL_BKUP
234580 B F A DISK 22-NOV-14 1 1 YES WEEKLY_FULL_BKUP
234581 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-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=123 devtype=DISK
...
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_2: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /backup/rman/full_3abcde4po_123456_1
channel ORA_DISK_2: reading from backup piece /backup/rman/full_32qakgmpa_123456_1
channel ORA_DISK_1: restored backup piece 1
...
channel ORA_DISK_2: validation complete, elapsed time: 00:53:11
Finished 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 NOMOUNT
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 10/03/2014 11:04:19
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional 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-03
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signaled during parse
RMAN-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:25
ORA-19870: error while restoring backup piece /backup/rman/ctl_c-12345-20141003-03
ORA-19504: failed to create file "/u01/oradata/devdb/control01.ctl"
ORA-27040: file create error, unable to create file
Linux-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/devdb
mkdir -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 backup
ORA-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;
