Instructor's Notes
(If applicable)
- Lesson 7 – Restore and Recovery with RMAN
- Author: Dave Anderson, SkillBuilders
- www.skillbuilders.com
- Last Update: October 17, 2007
- This lesson will cover the administration following topics:
- What are we protected against?
- Learn how to use recovery-related RMAN commands such as RESTORE, RECOVER, SET NEWNAME and more.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- This lesson is a critical piece of your RMAN learning curve because it is here that we will learn how to recover the database. We will learn the purpose and practical use of the critical commands. We will, through case studies and hands-on workshops, learn how to recover the database in several failure scenarios.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The real purpose of RMAN is to provide easier methods of recovering your database after something bad happens. “Something bad” might be:
- Loss (failure) of a disk device. In this case you typically want to recover to the most recent commit point, called a complete recovery.
- Bad data being entered into the database by an application. In this case you may want to recover to a point in time just before the application ran – called an point-in-time (or incomplete) recovery.
- A DBA dropping an object that should not be dropped. Again, in this case you may want to recover to a point in time just before the DROP occurred.
- Disaster could be the complete loss of a server or data center. In the event of a lost data center, we would need offsite storage of the backups. In this scenario, once we have a working Oracle instance, we would perform a point-in-time recovery to the last available archived log.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The RESTORE and RECOVER commands are the most common recovery-related RMAN commands. However, there are several additional recovery- related commands that we will see examples of in this lesson. Specifically, we will see examples of the following commands in this lesson:
- RESTORE – Restore any of the following files: SPFILE, Control file, datafile, archivelog, image copy.
- RECOVER – Apply incremental backups and/or redo to datafiles to bring the file up to the most recent commit (complete recovery) or up to the specified log sequence number, SCN or time (point-in-time recovery).
- SET NEWNAME – Restore files to an alternate location.
- SWITCH – Update the control file to reflect new file location.
- SET UNTIL – Use in point-in-time (PIT) recoveries to stop recovery at log sequence, SCN or time.
- Notes for this slide continue on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- ALTER DATABASE OPEN RESETLOGS – Use in PIT recoveries to open database and reset the log sequence number back to zero. This creates a new “incarnation” of the database and prevents logs from the old incarnation from being applied to this new incarnation. The RESETLOGS option is required if complete recovery was not used. (It is notable that when using RMAN with a recovery catalog, recovery across a resetlog operation is supported. This is because the RMAN catalog knows about earlier incarnations of the database and knows which logs belong to those incarnations. Refer to the Oracle9i Recovery Manager User’s Guide, Chapter 10, “Recovering Through a Resetlogs Operation” for more information.)
- RESET DATABASE – Record the new incarnation of database in recovery catalog. Use after OPEN RESETLOGS.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The two most basic commands for recovering a database in RMAN are RESTORE and RECOVER. I discuss the purpose and basic functionality for these commands, then we will look at some examples.
- The RESTORE command reads the backup sets or image copies containing the files specified in the restore operation and writes the files back to the same or different location (see the SET NEWNAME command later in this lesson for an example of restoring to a different location.) Note that RMAN will overwrite files without warning. For best performance of datafile recovery, RMAN will use image copies of the datafiles instead of backup sets, if image copies are available (because image copies are purely a copy of a datafile, it requires less work to restore and thus is faster to restore; backup sets contain commingled pieces of datafiles and there’s work involved in reconstructing a datafile from a backup set).
- RESTORE never restores tempfiles (because BACKUP never backups up tempfiles), so we will have to manually add tempfiles in some restore/recovery scenarios. We will see and example of the ALTER TABLESPACE x ADD TEMPFILE command later in this lesson.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- RESTORE is affected by (i.e. respects) the time or SCN specified in the SET UNTIL command, if specified. SET UNTIL is used in point-in-time (PIT) recoveries (sometimes called “incomplete” recoveries because not all redo is applied). If the SET UNTIL command is executed with ‘SYSDATE – 1’, then RESTORE will restore files from a backup taken more than 1 day ago. Then the RECOVER command can be used to recover the datafiles up to one day ago.
- Finally, note that, for performance, the Oracle9i RESTORE command will not restore the file if it detects a suitable file already in place. If RESTORE detects that the datafile already exists, it compares the datafile header to the information in the control file. If the information matches, it skips the RESTORE. Since this is not always desired (perhaps the file contains corrupt blocks), we can specify the FORCE option to override this behavior.
- Learning Tip
- I strongly recommend that the learner take a moment now to review the RESTORE command syntax in the Oracle9i Recovery Manager Reference manual.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- After a RESTORE operation, the RECOVER command is used to apply incremental backups or redo (it favors incremental backups, for speed) to bring the restore files up to the most recent commit, or whatever you specify in the SET UNTIL command. Redo is supplied by archived logs. If you have backed up and deleted the original archive logs, RMAN will automatically restore the logs during the recover process.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Let’s examine a practical situation, i.e. a scenario where a restore and recovery is required. For this scenario, lets assume the following situation exists:
- We have lost all of the database datafiles and the tempfile (OS file associated with the temporary tablespace; query the DBA_TEMP_FILES view) via an accidental file delete at the OS level. However, the control files and online redo logs are intact.
- Our goal is to restore the database to the most recent commit point.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The recovery solution is this scenario involves:
- Mounting the database. You’ll always need to have the control file open (which is what STARTUP MOUNT does) to restore and recover a datafile.
- Restoring the missing datafiles with the RESTORE command. RMAN will use the most recent image copy or backup set to restore the missing datafiles.
- Recover the restored datafiles with the RECOVER command. By default, RECOVER will recover to the last commit point and to the original location.
- Open the database so that we can execute the ALTER TABLESPACE command.
- Use the ALTER TABLESPACE ADD TEMPFILE command to create temporary files for the database. (Remember, RMAN never backs up or recovers tempfiles – you have to create new ones manually.)
- Test the database.
- Shutdown, startup in mount mode, then do a full backup.
Other Text:
(Examples or comments displayed on slide, if any).
See the example, next page<br>
Instructor's Notes
(If applicable)
- This slide contains the actual RMAN commands that I used as a solution to this problem. For the commands with command output, see the supplied file “RECOVER_LOST_FILES.TXT”.
- A note regarding the ALTER TABLESPACE command used in this example: Since my database utilizes OMF, I was able to leave the TEMPFILE parameter off of the ALTER TABLESPACE command. If you haven’t seen OMF before (a 9i feature), here is how you implement:
- system@TEST> alter system set db_Create_file_dest='/opt/oracle/oradata/test/' scope=both;
- System altered.
- Be sure to read up on the details of OMF before implementing.
- The controlfile will contain an entry for the original tempfile. This can be removed with the ALTER DATABASE TEMPFILE ‘file’ DROP statement.
Other Text:
(Examples or comments displayed on slide, if any).
RMAN> startup mount
<br>RMAN> restore database;
<br>RMAN> recover database;
<br>RMAN> open database;
<br>RMAN> sql 'alter tablespace temp add tempfile';
<br>RMAN> shutdown
<br>RMAN> startup mount
<br>RMAN> backup database plus archivelog;<br>If the catalog is not available during the backup, reconnect and issue RESYNC CATALOG command when the catalog becomes available.<br>Create temporary tablespace tempfile<br>
Instructor's Notes
(If applicable)
- When recovering just a tablespace or datafile, the database can remain open. Just take the affected part of the database offline – as shown above. Example with RMAN output:
- RMAN> run {
- 2> allocate channel d1 type disk;
- 3> restore tablespace 'TEST';
- 4> recover tablespace 'TEST';
- 5> }
- allocated channel: d1
- channel d1: sid=9 devtype=DISK
- Starting restore at 15-MAY-02
- channel d1: starting datafile backupset restore
- channel d1: specifying datafile(s) to restore from backup set
- restoring datafile 00004 to C:\ORACLE\ORADATA\PROD\TEST.DBF
- channel d1: restored backup piece 1
- piece handle=C:\ORACLE\ORA81\DATABASE\0VDOC3SB_1_1 tag=null params=NULL
- channel d1: restore complete
- Finished restore at 15-MAY-02
- Starting recover at 15-MAY-02
- starting media recovery
- media recovery complete
- Finished recover at 15-MAY-02
- released channel: d1
Other Text:
(Examples or comments displayed on slide, if any).
sql 'alter tablespace test offline';
<br>run {
<br> allocate channel d1 type disk;
<br> restore tablespace 'TEST';
<br> recover tablespace 'TEST';
<br> }
<br>sql 'alter tablespace test online';<br>
Instructor's Notes
(If applicable)
- In this scenario we have lost one of the drives containing one of the database datafiles. We have a backup available so all should be OK.
- Our mission is to is restore the tablespace to a new disk or mount point without losing any production data.
- The RMAN commands that will assist us here are:
- SET NEWNAME to set the new (path) name for the file to be restored.
- SWITCH DATAFILE to update the control file with the new datafile name. The SWITCH command is equivalent to the ALTER DATABASE RENAME FILE statement.
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- The solution to this problem involves:
- Using SET NEWNAME to change to location destination of the RESTORE command. This command cause the subsequent RESTORE to restore the specified datafile to the new location. (It actually can be the same location, just a different filename; however, that’s not its primary use.)
- Restoring the missing tablespace (TOOLS, in this example) to the location specified in the SET NEWNAME command.
- Updating the control file to point to the new file name with the SWITCH command. (Note that SWITCH DATAFILE ‘file’ could have been used; SWITCH DATAFILE ALL is just easier in this case.) Remember that the SWITCH command is equivalent to the ALTER DATABASE RENAME FILE statement.
- Recovering the tablespace to the last commit point (i.e. complete recovery).
- See the supplied file “RESTORE_NEW_LOCATION.RMAN” for a working example of this code.
Other Text:
(Examples or comments displayed on slide, if any).
run {
<br>set newname for datafile
<br> 'C:\ORACLE\ORADATA\DAVE\TOOLS01.DBF' to
<br> 'C:\oracle\oradata\dave\test\tools01.dbf';
<br>restore tablespace tools;
<br>switch datafile all;
<br>recover tablespace tools; }<br>
Instructor's Notes
(If applicable)
- An Incomplete Recovery (also known as a point-in-time, or PIT recovery) means that we will not recover the database to the most recent commit. In this example, we are recovering to the current time minus 1 day. Note that you are required to open the database with the RESETLOGS option. This creates a new “incarnation” of the database.
- Note that if the ALTER DATABASE OPEN RESETLOGS command is not run from RMAN (e.g. SQL*Plus), then record the new incarnation of the database in the RMAN catalog by returning to RMAN and executing the following command:
Other Text:
(Examples or comments displayed on slide, if any).
shutdown immediate
<br>startup mount
<br>run {
<br>set until time 'sysdate - 1';
<br>restore database;
<br>recover database;
<br>}
<br>alter database open resetlogs;<br>
Instructor's Notes
(If applicable)
- This example is very similar to the previous example in that we desire to recover the database to some point in time, and all datafiles are recovered and the database must be mounted, not open. However, the difference is that we are using a date/time string in the SET UNTIL command.
- We can recover the database to the specified:
- Time - set until time 'sysdate - 5‘
- SCN - set until scn 291112;
- Log Sequence Number – set until sequence 12;
- Note 1: ALTER DATABASE OPEN is a supported SQL command within RMAN; the prefix “sql” is not necessary.
- Note 2: If you attempt to recover the database to a point in time that is earlier than the start date of the incarnation of the database you will receive the following error message (the incarnation starts when you use the REGISTER DATABASE command or when an OPEN RESETLOGS followed by RESET DATABASE is issued):
- RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time.
- If this occurs, refer to the Oracle9i Recovery Manager User’s Guide, Chapter 10, “Recovering Through a Resetlogs Operation”, for the recovery steps.
Other Text:
(Examples or comments displayed on slide, if any).
shutdown immediate
<br>startup mount
<br>run {
<br>set until time = "to_Date('08/22/2002 16:15:00',
<br> 'mm/dd/yyyy hh24:mi:ss')";
<br>restore database;
<br>recover database;
<br>}
<br>alter database open resetlogs;
<br>reset database;<br>
Instructor's Notes
(If applicable)
- In this scenario, we would like to do a complete recovery, but we have lost the online logs. Therefore, we can only recover up to the last available archive log.
- Use the ARCHIVE LOG LIST command (from SQL*Plus) or the RMAN LIST BACKUP OF ARCHIVELOG command to find the last available log.
Other Text:
(Examples or comments displayed on slide, if any).
RMAN> list backup of archivelog from time 'sysdate-1';<br>SQL> archive log list<br>
Instructor's Notes
(If applicable)
- After determining the last available archived log (log sequence 12, thread 1 in this example), use the SET UNTIL command to specify this log.
- Then, the subsequent RESTORE and RECOVER commands understand and respect your request to recover only up to that log.
- See the supplied file “RESTORE_UPTO_LAST_ARCHIVE_LOG.TXT” for a demonstration of this recovery scenario.
Other Text:
(Examples or comments displayed on slide, if any).
RMAN> run {
<br>2> set until sequence 12 thread 1;
<br>3> restore controlfile;
<br>4> restore database;
<br>5> mount database;
<br>6> recover database; }
<br>
<br>executing command: SET until clause
<br>
<br>Starting restore at 16-FEB-04<br>
Instructor's Notes
(If applicable)
- With Oracle9i, RMAN has the ability to recover the database even if you have lost all control files – if you have used the control file AUTOBACKUP feature.
- The steps are:
- If you have a disk-based AUTOBACKUP, and the AUTOBACKUP piece is not in the default location, copy the file to the default location or use the RESTORE CONTROLFILE FROM ‘location/name’ command instead of the RESTORE CONTROLFILE FROM AUTOBACKUP command. (If you have a tape-based AUTOBACKUP, see the procedure shown later in this lesson.) The default location is ORACLE_HOME/dbs on a UNIX / Linux platform and ORACLE_HOME\database on the Windows platform.
- Steps continue on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
Instructor's Notes
(If applicable)
- Continued from the previous page:
- Start RMAN – without any additional parameters. (Not shown.)
- Start the database in NOMOUNT mode. Remember, without the control file, you cannot mount the database.
- Use the SET DBID command set the database identifier. A DBID uniquely identifies the target database. The SET DBID command is necessary in this case because there is no metadata available to provide the DBID – remember, in this scenario, the control file is not available (which is the primary source of RMAN metadata) and the RMAN catalog is also not available. You must record the DBID’s of the target databases you are supporting because most methods of discovering the DBID are not available when you are trying to restore the control file. (For example, the DBID will be displayed by connecting RMAN to the target, LIST INCARNATION command, the V$DATABASE view and the RC_DATABASE view.)
- Connect to the un-mounted target database.
- Notes for this slide continue on the next page…
Other Text:
(Examples or comments displayed on slide, if any).
RMAN> startup nomount
<br>RMAN> set dbid 4167529595;
<br>RMAN> connect target sys/change_on@prod
<br>RMAN> restore controlfile from autobackup;
<br>RMAN> mount database;
<br>RMAN> recover database;
<br>RMAN> alter database open resetlogs;
<br>RMAN> sql 'alter tablespace temp add tempfile';
<br> ===> now take a full consistent backup <===<br>c:\> rman target system/dave@beatles catalog rman/rman
<br>RMAN> reset database;
<br>new incarnation of database registered in recovery catalog<br>“Reset” when catalog becomes available<br>
Instructor's Notes
(If applicable)
- Continued from the previous page:
- Restore the control file from the AUTOBACKUP. In this example, RMAN searches the default disk directory for an AUTOBACKUP backup piece. (See the next slide for an example of restoring a tape-based backup.) The RESTORE command restores the control file to the first location specified in the CONTROL_FILES parameter, then replicates the control file to all locations specified in the CONTROL_FILES parameter.
- Mount the database. This step opens the restored control file and provides RMAN metadata. RMAN can now restore the database.
- Restore the database files.
- Recover the database files.
- Open the database. Since you have performed a control file recovery, you will need to open the database with the RESETLOGS option.
- Add a tempfile to the temporary tablespace. This is required because Oracle removes entries for tempfiles when a control file is restored. (I guess it has to do with the fact that tempfiles are never backed up.)
- If you had this database registered in a catalog, then, when the catalog becomes available again, use the RESET DATABASE command to record the new incarnation of the database in the catalog.
- After testing the database, you should always perform a FULL database backup after a RESETLOGS operation. This provides a base backup for the new incarnation of the database. (This step is not shown in the slide.)
