Pro Oracle Database 11g Administration (88 page)

When you’re connected to the recovery catalog, you can view backup information about your control files even while your target database is in NOMOUNT mode. To list backups of your control files, use the LIST command as shown here:

RMAN> connect target /

RMAN> connect catalog rcat/rcat@recov

RMAN> startup nomount;

RMAN> list backup of controlfile;

If you are missing all of your control files and you are using a recovery catalog, then issue STARTUP

NOMOUNT and issue the RESTORE CONTROLFILE command. In this example, the recovery catalog owner and password are both
rcat
and the name of the recovery catalog is
recov
. You’ll have to change those values to match the username/password@service in your environment.

RMAN> connect target /

RMAN> connect catalog rcat/rcat@recov

RMAN> startup nomount;

RMAN> restore controlfile;

RMAN restores the control files to the location defined by your CONTROL_FILES initialization parameter. You should see a message indicating that your control files have been successfully copied back from an RMAN backup piece. Here’s a partial listing of RMAN’s message stack after a successful control file restore:

527

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: reading from backup piece

channel ORA_DISK_1: restore complete, elapsed time: 00:00:05

You can now alter your database into mount mode and perform any additional restore and recovery commands required for your database.

■
Note
When you restore a control file from a backup, you are required to perform media recovery on your entire database and open your database with the OPEN RESETLOGS command, even if you didn’t restore any datafiles.

You can determine whether your control file is a backup by querying the CONTROLFILE_TYPE column of the V$DATABASE view.

Using an Autobackup to Restore

When you enable the autobackup of your control file and are using a fast recovery area, restoring your control file is fairly simple. First, connect to your target database, then issue a STARTUP NOMOUNT

command, and lastly issue the RESTORE CONTROLFILE FROM AUTOBACKUP command like so: RMAN> connect target /

RMAN> startup nomount;

RMAN> restore controlfile from autobackup;

RMAN restores the control files to the location defined by your CONTROL_FILES initialization parameter. You should see a message indicating that your control files have been successfully copied back from an RMAN backup piece. Here is a partial snippet of the output: allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

database name (or database unique name) used for search: ORCL

channel ORA_DISK_1: autobackup found in the recovery area

You can now alter your database into mount mode and perform any additional restore and recovery commands required for your database.

Specifying a Filename

When restoring a database to a different server, the first few steps in the process usually are: take a backup of the target database, copy to the remote server, and then restore the control file from the RMAN backup. In these scenarios, I usually know the name of the backup piece that contains the control file. Here is an example where you instruct RMAN to restore a control file from a specific backup piece file:

RMAN> startup nomount;

RMAN> restore controlfile from '/ora01/O11R2/rman/c-3453199553-20100923-07.bk'; The control file will be restored to the location defined by the CONTROL_FILES initialization parameter.

528

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

Incomplete Recovery

Incomplete database recovery
means that you cannot recover all committed transactions. Incomplete means that you do not apply all redo to restore to the point of the last committed transaction that occurred in your database. In other words, you are restoring and recovering to a point in time in the past. For this reason, incomplete database recovery is also called
database point-in-time recovery
(DBPITR). Usually you perform incomplete database recovery because of one of the following reasons:

• You don’t have all the redo required to perform a complete recovery. You’re missing either the archived redo log files or online redo log files that are required for complete recovery. This situation could arise because the required redo files are damaged or missing.

• You purposely want to roll the database back to a point in time. For example, you would do this if somebody accidentally truncated a table and you intentionally wanted to roll the database back to just before the truncate table command was issued.

■
Tip
To minimize the chance of failure with your online redo log files, I recommend you multiplex them with at least two members in each group and have each member on separate physical devices governed by separate controllers.

Incomplete database recovery consists of two steps: restore and recovery. The restore step will recreate datafiles, and the recover step will apply redo up to the specified point in time. The restore process can be initiated from RMAN in several ways:

• RESTORE DATABASE UNTIL

• RESTORE TABLESPACE UNTIL

• FLASHBACK DATABASE

For the majority of incomplete database recovery circumstances, you use the RESTORE DATABASE

UNTIL command to instruct RMAN to retrieve datafiles from the RMAN backup files. This type of incomplete database recovery is the main focus of this chapter. The UNTIL portion of the RESTORE

DATABASE command instructs RMAN to retrieve datafiles from a point in the past based on one of the following methods:

• Time

• Change (sometimes called
system change number
or SCN)

• Log sequence number

• Restore point

The RMAN RESTORE DATABASE UNTIL command will retrieve all datafiles from the most recent backup set or image copy. RMAN will automatically determine from the UNTIL clause which backup set contains the required datafiles. If you omit the UNTIL clause of the RESTORE DATABASE command, RMAN

will retrieve datafiles from latest available backup set or image copy. In some situations, this may be the 529

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

behavior you desire. I recommend you use the UNTIL clause to ensure that RMAN restores from the correct backup set. When you issue the RESTORE DATABASE UNTIL command, RMAN will determine how to extract the datafiles from any of the following:

• Full database backup

• Incremental level 0 backup

• Image copy backup generated by the BACKUP AS COPY command

You cannot perform an incomplete database recovery on a subset of your database’s online datafiles. When performing incomplete database recovery, all of the checkpoint SCNs for all online datafiles must be synchronized before you can open your database with the alter database open resetlogs command. You can view the datafile header SCNs and the status of each datafile via this SQL

query:

select

file#

,status

,fuzzy

,error

,checkpoint_change#,

to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss') as checkpoint_time

from v$datafile_header;

■
Note
The FUZZY column of V$DATAFILE_HEADER refers to a datafile that contains one or more blocks that have an SCN value greater than or equal to the checkpoint SCN in the datafile header. If datafile is restored and has a FUZZY value of YES, then media recovery is required.

The only exception to this rule of not performing an incomplete recovery on a subset of online database files is a tablespace point-in-time recovery (TSPITR), which uses the RECOVER TABLESPACE UNTIL

command. TSPITR is used in rare situations; it restores and recovers only the tablespace(s) you specify.

The recovery portion of an incomplete database recovery is always initiated with the RECOVER

DATABASE UNTIL command. RMAN will automatically recover your database to the point specified with the UNTIL clause. Just like the RESTORE command, you can recover until time, change/SCN, log sequence number, or restore point. When RMAN reaches the specified point, it will automatically terminate the recovery process.

■
Note
Regardless of what you specify in the UNTIL clause, RMAN will convert that into a corresponding UNTIL

SCN clause and assign the appropriate SCN. This is to avoid any timing issues, particularly those caused by daylight saving time.

530

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

During a recovery, RMAN will automatically determine how to apply redo. First, RMAN will apply any incremental backups available. Next, any archived redo log files on disk will be applied. If the archived redo log files do not exist on disk, then RMAN will attempt to retrieve them from a backup set. If you want to apply redo as part of an incomplete database recovery, the following conditions must be true:

• Your database must be in archivelog mode.

• You must have a good backup of all datafiles.

• You must have all redo required to restore up to the specified point.

■
Tip
Starting with Oracle Database 10g, you can perform parallel media recovery by using the RECOVER

DATABASE PARALLEL command.

When performing an incomplete database recovery with RMAN, you must have your database in mount mode. RMAN needs the database in mount mode to be able to read and write to the control file.

Also, with an incomplete database recovery, the system datafile is always one of the datafiles being recovered. The SYSTEM tablespace's datafile(s) must be offline while it is being recovered. Oracle will not allow your database to be open while this is happening.

■
Note
After incomplete database recovery is performed, you are required to open your database with the ALTER

DATABASE OPEN RESETLOGS command.

Depending on your scenario, you can use RMAN to perform a variety of incomplete recovery methods. The next section discusses how to determine what type of incomplete recovery to perform.

Determining the Type of Incomplete Recovery

Time-based restore and recovery is commonly used when you know the approximate date and time to which you want to recover your database. For example, you may know approximately the time you want to stop the recovery process but not a particular SCN.

Log sequence–based and cancel-based recovery work well in situations where you have missing or damaged log files. In such scenarios, you can recover only up to your last good archived redo log file.

SCN-based recovery works well if you can pinpoint the SCN at which you want to stop the recovery process. You can retrieve SCN information from views such as V$LOG and V$LOG_HISTORY. You can also use tools such as LogMiner to retrieve the SCN of a particular SQL statement.

Restore point recoveries work only if you have established restore points. In these situations, you restore and recover up to the SCN associated with the specified restore point.

Tablespace point-in-time recovery is used in situations where you can restore and recover just a few tablespaces. You can use RMAN to automate many of the tasks associated with this type of incomplete recovery.

531

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

■
Note
Flashing back your database works only if you have enabled the flashback database feature (see Chapter 16 for details).

Performing Time-Based Recovery

To restore and recover your database back to a point in time, you can use either the UNTIL TIME clause of the RESTORE and RECOVER commands or the SET UNTIL TIME clause within a run{} block. RMAN will restore and recover the database up to, but not including, the specified time. In other words, RMAN will restore any transactions committed prior to the time specified. RMAN automatically stops the recovery process when it reaches the time you specified.

The default date format that RMAN expects is YYYY-MM-DD:HH24:MI:SS. However, I recommend using the TO_DATE function and specifying a format mask. This eliminates ambiguities with different national date formats and having to set the operating system NLS_DATE_FORMAT variable. The following example specifies a time when issuing the restore and recover commands:

RMAN> connect target /

RMAN> startup mount;

RMAN> restore database until time

2> "to_date('04-sep-2010 14:00:00', 'dd-mon-rrrr hh24:mi:ss')"; RMAN> recover database until time

2> "to_date('04-sep-2010 14:00:00', 'dd-mon-rrrr hh24:mi:ss')"; RMAN> alter database open resetlogs;

If everything went well, you should now see output similar to this:

Database altered

Performing Log Sequenced-Based Recovery

Usually this type of incomplete database recovery is initiated because you have a missing or damaged archived redo log file. If that’s the case, you can recover only up to your last good archived redo log file, because you cannot skip a missing archived redo log.

How you determine which archived redo log file to restore up to (but not including) will vary by situation. For example, if you are physically missing an archived redo log file and if RMAN can’t find it in a backup set, then you’ll receive the following message when trying to apply the missing file: RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of log thread 1 seq 45 lowscn 2149069 found to restore Based on the previous error message, you would restore up to (but not including) log sequence 45.

RMAN> connect target /

RMAN> startup mount;

RMAN> restore database until sequence 45;

RMAN> recover database until sequence 45;

RMAN> alter database open resetlogs;

If everything went well, you should now see output similar to this:

Database altered

532

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

■
Note
Log sequenced-based recovery is similar to user-managed cancel based recovery. See Chapter 16 for details on a user-managed cancel based recovery.