SkillBuilders | Oracle 10g: RMAN | Lesson 14: RMAN Case Studies

Instructor's Notes

(If applicable)

Lesson 14 – Oracle RMAN Case Studies (Four failure scenarios and solutions designed to reinforce and expand upon what you have learned in this course.)
Author: Dave Anderson, SkillBuilders
www.skillbuilders.com
Last Update: October 17, 2007

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The objective of this lesson is to – through four distinct case studies – reinforce and review the knowledge we have gained from the previous lessons in this course (Backup and Recovery with Oracle RMAN).
Each case study presents to the student a problem situation and informs the student how to reproduce this situation. If the student has a test database, the student can reproduce the problem and use RMAN restore and recovery operations to correct the problem.
Warning
- Do not attempt to reproduce these case studies on a database you cannot afford to lose. Though every precaution has been taken in the instructions and solutions provided in the lesson, there is a significant chance you will not be able to recover the database used in these case studies.
Warning
- Before proceeding, shutdown your test database, start in mount mode, and take a full backup, including archive logs and controlfile, of your database.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

In this scenario, our database cannot be started because somehow we have lost all copies of our control file. When attempts are made to restart the database, the following error is returned:
RMAN> startup
connected to target database (not started)
Oracle instance started
RMAN-00571: ===========================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
RMAN-00571: ===========================================
RMAN-03002: failure of startup command at 12/03/2003 23
ORA-00205: error in identifying controlfile, check alert
RMAN> shutdown immediate
Oracle instance shut down
RMAN>
Our mission in this study is to recover the most recent control file without losing and data.
Anytime the control file is recovered, we are forced to also recover the database – to get the control file and the datafiles back in sync. Also, anytime you start with a backup version of the control file, you must use RESETLOGS on the startup command.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The solution includes these steps:

Start the database in NOMOUNT mode. NOMOUNT is necessary because the control file is not available (MOUNT mode opens the control file).
Restore the control file from the latest backup. If the recovery catalog is available (as in this case study), RECOVER CONTROLFILE FROM AUTOBACKUP can be used; there is no logical difference.
Recover the database. This puts the recovered control file in sync with the current structure of the database.
Open the database with RESETLOGS.

Problem You May Encounter
- If your instance name exists more than once in the RMAN.RC_DATABASE view, you will need to use the SET DBID command prior to the RESTORE CONTROLFILE command to identify which entry RMAN should use. Failure to do so will generate an RMAN error:

RMAN-20005 target database name is ambiguous
Cause: Two or more databases in the recovery catalog match the DB_NAME initialization parameter of the instance and the target database is not mounted, so RMAN cannot get its DBID.

This can occur, for example, because several different servers containing like-named instances have been registered in a single catalog. It could also occur if you recreated your instructional (classroom) database for any reason and used the same instance name.

Other Text:
(Examples or comments displayed on slide, if any).

C:\OracleScripts\RMAN>rman target system/dave catalog rman/rman@class RMAN> startup nomount RMAN> restore controlfile; RMAN> mount database; RMAN> recover database; RMAN> alter database open resetlogs; “FROM AUTOBACKUP” option can also be used in this case

Instructor's Notes

(If applicable)

In this problem scenario, we have lost all copies of the control file plus the RMAN catalog.
Luckily we have backups of our database that included control file autobackups; this is good because we have not only lost all control files, but the recovery catalog as well. Our mission is to recover the control file without losing any production data.
In this case – because the recovery catalog is not available, and this is where the database DBIDs are stored – the DBA will have to know the DBID of the database to be recovered. Therefore I recommend keeping all the DBIDs for your databases in a safe place – even on your remote computers or PDA’s if you do remote administration tasks. The LIST INCARNATION command will reveal the DBIDs of your databases.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The solution involves:

Connecting to the target database.
Using SET DBID command to tell RMAN the DBID of the database we wish to work with. This is necessary because there is no control file or catalog available from which to obtain this information.
Startup in NOMOUNT mode.
Restore the control file. Use of the FROM AUTOBACKUP option is necessary in this case – RMAN needs to look in a default location on the server to find the autobackup. If you moved the autobackups for some reason, move them back to their default location prior to executing this command. The default location can be found with the SHOW CONTROLFILE AUTOBACKUP FORMAT command.
Mount the database.
Recover the database. Note that a restore of the database is unnecessary – we have not lost any datafiles, just the control file.
Open the database with RESETLOGS.
Take a consistent backup (database mounted, not open). (Not shown.)

See supplied file “CASE_STUDY_RECOVER_CONTROLFILE_NOCAT.TXT” for an example of a working solution to this problem.

Other Text:
(Examples or comments displayed on slide, if any).

C:\OracleScripts\RMAN>rman target system/dave RMAN> set dbid = 549349079; executing command: SET DBID RMAN> startup nomount; RMAN> restore controlfile from autobackup; RMAN> mount database; database mounted RMAN> recover database; RMAN> alter database open resetlogs; database opened

Instructor's Notes

(If applicable)

In this problem scenario, our DBA has mistakenly dropped a production tablespace. Our mission is to recover this dropped tablespace to the most recent commit possible.
Your Mission
- If you have a test database you can afford to lose AND have a valid backup and an RMAN catalog, then, before turning the page, drop the TOOLS tablespace, then try to recover it. In this case, you will need to recover the database to just prior to the DROP operation. Note that the tablespace point-in-time recovery (TSPITR) technique can be used to recover the tablespace – without effecting the rest of the database data. TSPITR is outside the scope of this course. Refer to Chapter 8 of the Oracle 10g Backup and Recovery Advanced User’s Guide or Oracle9i Recovery Manager User’s Guide and Metalink Note:223543.1 for more information on Tablespace Point-in Time Recovery (TSPITR).

Other Text:
(Examples or comments displayed on slide, if any).

SQL> drop tablespace tools; Tablespace dropped.

Instructor's Notes

(If applicable)

The solution to this problem is an full-database point-in-time recovery. We’ll recover to a point-in-time just prior to the DROP TABLESPACE operation.
This involves:

Restoring the controlfile to a version earlier than the DROP. As shown above, this step involves:

Starting the database in NOMOUNT phase. The database cannot be mounted because we want to restore (overwrite) the controlfile.
Using the SET UNTIL TIME command to prevent RMAN from restoring a too-recent version of the controlfile.
Allocating a channel.
Restoring the controlfile. Note that the RESTORE CONTROLFILE FROM AUTOBACKUP command cannot be used in this case because that command always restores the most recent version of the controlfile.

Mounting the database with the restored controlfile.
Restoring the database to a point in time earlier than the DROP.
Opening the database with the RESETLOGS option – always required with PIT recoveries. See the supplemental notes below for more information on RESETLOGS.

Notes continue…

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> startup nomount RMAN> run { 2> set until time 'sysdate-1/24' ; 3> allocate channel d1 type disk; 4> restore controlfile;} RMAN> alter database mount; RMAN> run {set until time 'sysdate-1/24' ; 2> allocate channel d1 device type disk; 3> restore force database; 4> recover database; } RMAN> alter database open resetlogs; Take full backup after RESETLOGS Do not use FROM AUTOBACKUP

Instructor's Notes

(If applicable)

Taking a full consistent (database in mount mode) backup. (Not shown.) This is always recommended anytime the database is opened with the RESETLOGS option in order to get a backup of the new incarnation of the database.

See the supplied file “CASE_STUDY_RECOVER_DROPPED_TSPACE.TXT” for a working example of this study.

Supplemental Notes on RESETLOGS
- The Oracle9i SQL Reference does a good job of explaining the RESETLOGS option. Note this quote from the section on ALTER DATABASE command:

“Specify RESETLOGS to reset the current log sequence number to 1 and discard any redo information that was not applied during recovery, ensuring that it will never be applied. This effectively discards all changes that are in the redo log, but not in the database.”

“You must specify RESETLOGS to open the database after performing media recovery with an incomplete recovery using the RECOVER clause or with a backup control file. After opening the database with this clause, you should perform a complete database backup.”

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Note: In order to test a solution to this case study, you will need 4 available tape drives.
In this scenario we have been using 1 tape drive for our weekly full backups. We have now been given 3 additional tape drives to use. We will need to revise our weekly full backup script to take advantage of these devices.
Remember that asynchronous IO, compression and parallelism are often big factors in RMAN tuning.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The primary component of this solution is to allocate one channel for every available output device (i.e. set parallelism equal to number of devices). Other factors:

Enable compression for each tape channel (use NSR_COMPRESSION=TRUE with Legato).
Control multiplexing. I set MAXOPENFILES 8 to accomplish this. You will need to test what works best on your system.

This example uses the home-grown script RUNSTATS_PKG.RS_START and RS_STOP (modified versions of Tom Kyte’s scripts) to capture performance data. See the lesson Tuning RMAN for more information on how to use these procedures.

Other Text:
(Examples or comments displayed on slide, if any).

alter system set backup_tape_io_slaves = true scope = spfile; startup force #show parameter backup_tape #show parameter large_pool sql 'begin runstats_pkg.rs_start; end;'; run { allocate channel t1 type sbt MAXOPENFILES 8 parms='ENV=(NSR_COMPRESSION=TRUE)'; allocate channel t2 type sbt MAXOPENFILES 8 parms='ENV=(NSR_COMPRESSION=TRUE)'; allocate channel t3 type sbt MAXOPENFILES 8 parms='ENV=(NSR_COMPRESSION=TRUE)'; allocate channel t4 type sbt MAXOPENFILES 8 parms='ENV=(NSR_COMPRESSION=TRUE)'; set command id to 'dave test'; backup full force tag='tapetest' database; } sql "begin runstats_pkg.rs_stop(P_OP_NAME=>''tapetest''); end;"; sql 'commit';