11-3.Verifying Integrity of Backups

Problem.How validate backup before restoring.
Solution
You can use either the restore ... validate or validate command to verify the availability.

These commands do not restore datafiles.

You can additionally specify the check logical clause to
restore ... validate:
RMAN> restore database validate;
RMAN> restore tablespace users validate ;
RMAN> restore database from tag MON_BCK validate;
RMAN> restore datafile 1 validate;
RMAN> restore archivelog all validate;
RMAN> restore controlfile validate;
If successful, you’ll see text similar to the following near the bottom of the output:
channel ORA_DISK_1: validation complete, elapsed time: 00:00:45
Finished restore at 10-AUG-06
By default, RMAN checks only for physical corruption when validating.
 You can alsoinstruct RMAN to check for logical corruption with the check logical clause:
RMAN> restore database validate check logical;
Assume that If recently taken backup piece is missing or corrupt, the restore ... validate command will auto-matically check for the availability of previously taken backups.

Using validate

When using the validate command, you need to know the primary key of the backup set that you want to validate. First, use the list backup command to find the appropriate primary key.

RMAN> list backup;
Here are the relevant lines from the output:
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
193 Full 129.48M DISK 00:01:05 16-AUG-06
BP Key: 193 Status: AVAILABLE Compressed: YES Tag: TAG20061014T13291

After determining the backup set key, validate as follows:

RMAN> validate backupset 193;

If the validate command works, you should see a message similar to this one at the bot-
tom of the message stack:
channel ORA_DISK_1: validation complete, elapsed time: 00:00:45

If the validation process discovers a problem, it will display an error message and stop pro-cessing.

By default the validate command checks only for physical corruption.

Use check logical parameter if you want the validation process to also check for logical corruption:

RMAN> validate backupset 193 check logical;

Physical corruption is when the block (contents)don’t match the physical format that Oracle expects.

By default, RMAN checks for physical corruption when backing up, restoring, or validating datafiles.

Logical corruption is when the block is in the correct format but the contents aren’t consistent with what Oracle expects.

Example of Logical corruption are corruption in a row piece or an index entry.

You can see whether a corrupt block is either physically or logically corrupt by queryin gthe CORRUPTION_TYPE column of the V$DATABASE_BLOCK_CORRUPTION view.

RMAN can perform block media recovery only on physically corrupt blocks.

Blocks tagged with type LOGICAL corruption cannot be recovered by RMAN (through block-level recovery).

To recover logically corrupt blocks, restore the datafile from a backup and perform media recovery.

When you issue command restore ... validate, RMAN will look into the repository, get the latest backup information, and look for the relevant backup pieces. (When RMAN can’t find a backup piece or )when RMAN detects corruption, it will then issue a “failover to previous backup” message and automatically search for a previously taken backup. (RMAN will stop looking when it finds a good backup or until it has searched through all known backups without finding one)

• (Note :RMAN’s behavior of searching sequentially back through backups until a good backup is found is) this behavior of rman is called "restore failover".

---------------+-++-------------
11-4. Testing Media Recovery

(Restore …. Validate command will only validate available backups means)Assume if one of the archive log is Missing then restore validate command  will not come to know about it. It just check integrity of backups. Use test command to check you have all the backups required for recover.

Problem

You need to perform a database recovery, but you suspect one of your archived redo log files is bad. You want to perform a test to see whether all of the redo is available and can be applied.


Solution

The recover ... test command instructs Oracle to apply the redo which are necessary( to perform recovery )but does not make the changes permanent in the datafiles instead rolls back the changes at the end of the process.

(When you recover in test tmode, Oracle applies the required redo but rolls back the changes at the end of the process.)

This example starts up the database in mount mode, restores the entire database, and then does a test recovery:

RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database test;

Here is a partial snippet of the output showing that the test recovery was successful:

ORA-10574: Test recovery did not corrupt any data block
ORA-10573: Test recover tested redo from change 847960 to 848243
ORA-10570: Test recovery complete

You can test a recovery with most recover commands. Here are some examples:

RMAN> recover tablespace users, tools test;
RMAN> recover datafile 1 test;



11-4. Testing Media Recovery


How It Works

(The test command allows you to test drive the redo application process without making any permanent changes to the datafiles. Running this command is particularly useful for diagnos-
ing problems that you’re having with the application of redo during the recovery process.)

For
example, you can use the test command with the until clause to test up to a specific trouble point:


RMAN> recover database until time 'sysdate – 1/48' test;
RMAN> recover database until scn 2328888 test;
RMAN> recover database until sequence 343 test

(The test command allows you to test drive the redo application process without making any permanent changes to the datafiles.

Running this command is particularly useful for diagnosing problems that you’re having with the application of redo during the recovery process.)



■Caution If you attempt to issue a recover tablespace until ... test, RMAN will attempt to per-form a tablespace point-in-time recovery (TSPITR)

If you’re missing archived redo log files or online redo log files that are needed for recovery,

you’ll receive a message similar to this:

ORA-06053: unable to perform media recovery because of missing log

If you can’t locate the missing redo, then you’ll most likely have to perform incomplete recovery.

ALLOWING CORRUPTION

In Oracle Database 10g and lower, the syntax recover ... test allow n corruption does not work from within RMAN.

If you want to run the test command with the allow n corruption clause, then you must issue that command from inside

SQL*Plus, as shown here:

SQL> connect sys/muft as sysdba
SQL> recover tablespace system UI test allow 5 corruption;

When using the recover ... test allow n corruption command, you can specify integers greater than 1 for n.

 If you are using recover ... allow n corruption (and not using the test
command), then n can be 1 only.



11-5. Performing Database-Level Recovery

Problem

You’ve lost all of your datafiles but still have your online redo log files. You want to perform complete recovery.


Solution:

You can perform a complete database-level recovery in this situation with either the current control file or a backup control file.

Use Current Control File 

You must first put your database in mount mode to perform a database-wide restore and recovery.

 This is because the system tablespace datafile(s) must be offline when being restored and recovered. Oracle won’t allow you to operate your database in open mode with the system datafile offline.

In this situation, we simply start up the database in mount mode, issue the restore and recover commands, and then open the database:

RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

Use Backup Control File

This solution uses an autobackup of the control file retrieved from the flash recovery area.

If you’re using a different strategy to back up your control file, then see Chapter 10 for details on restoring your control file.

In this example, we first restore the control file before issuing the restore and recover
database commands:

RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

If everything went as expected, the last message you should see is this:
database opened

■Note You are required to open your database with the open resetlogs command anytime you use a
backup control file during a recovery operation.

How It Works

The restore database command will restore every datafile in your database. The exception to this is when RMAN detects that datafiles have already been restored, then it will not restore them again.

If you want to override that behavior, then use the force command as explained
When you issue the recover database command, RMAN will automatically apply redo to any datafiles that need recovery. The recovery process includes applying changes found in the following:

• Incremental backup pieces (applicable only if using incremental backups)

• Archived redo log files (generated since the last backup or last incremental backup that is applied)

• Online redo log files (current and unarchived)

. If you restore from a backup control file, you are required to open your database with the open resetlogs command.

Complete database recovery works only if you have good backups of your database and have access to all redo generated after the backup was taken.

You need all the redo required to recover the database datafiles. If you don’t have all the required redo, then you’ll most likely have to perform an incomplete recovery

■Note : Your database has to be at least mounted to restore datafiles using RMAN.

This is because RMAN reads information from the control file during the restore and recovery process.

11-6. Performing Tablespace-Level Recovery

Problem

You’re seeing a media error associated with several datafiles contained in one tablespace.

You want to perform complete recovery on all datafiles associated with that problem tablespace.

Solution

Use the restore tablespace and recover tablespace commands to restore and recover all the datafiles associated with a tablespace.

You can either place the database in mount mode or have the database open. In the first scenario, we’ll place the database in mount mode for the restore and recovery.


Recover While Database Not Open

This solution works for any tablespace in your database. In this example, we restore the user_data and user_index tablespaces:

RMAN> connect target /
RMAN> startup mount;
RMAN> restore tablespace user_data, user_index;
RMAN> recover tablespace user_data, user_index;
RMAN> alter database open;

If everything was successful, the last message you should see is this:
database opened

Recover While Database Is Open

You can take a tablespace offline, restore, and recover it while your database is open.

This works for any tablespace except the system and undo tablespaces.

This example takes data_ts offline and then restores and recovers before bringing it back online:

RMAN> connect target /
RMAN> sql 'alter tablespace data_ts offline immediate';
RMAN> restore tablespace data_ts;
RMAN> recover tablespace data_ts;
RMAN> sql 'alter tablespace data_ts online';

After the tablespace is brought online, you should see a message similar to this:

sql statement: alter tablespace data_ts online

How It Works

The RMAN restore tablespace and recover tablespace commands will restore and recover all datafiles associated with the specified tablespace(s).

It’s appropriate to perform this type
of complete recovery when you only have datafiles from a tablespace or set of tablespaces missing.

If your database is open, then all datafiles in the tablespace(s) being recovered must
be offline.



11-7. Performing Datafile-Level Recovery


Problem

You have one datafile that has experienced media failure. You don’t want to restore and
recover the entire database or all datafiles associated with the tablespace. You just want to
restore and recover the datafile that experienced media failure.

Solution

Use the restore datafile and recover datafile commands to restore and recover one or
more datafiles. The database can be mounted or open to restore datafiles.

Recover While Database Not Open


In this scenario we mount the database and then restore and recover the missing datafile.

 You can restore and recover any datafile in your database while the database is not open.

This example shows restoring the datafile 1, which is associated to the system tablespace:

RMAN> connect target /
RMAN> startup mount;
RMAN> restore datafile 1;
RMAN> recover datafile 1;
RMAN> alter database open;

You can also specify the filename when performing a datafile recovery:


RMAN> connect target /
RMAN> startup mount;
RMAN> restore datafile '/ora01/brdstn/system_01.dbf';
RMAN> recover datafile '/ora01/brdstn/system_01.dbf';
RMAN> alter database open;


Recover While Database Open


For nonsystem and non-undo datafiles, you have the option of keeping the database open while performing the recovery. When your database is open,

you’re required to take offline
any datafiles you’re attempting to restore and recover.
RMAN> connect target /
RMAN> sql 'alter database datafile 3, 4 offline';
RMAN> restore datafile 3, 4;
RMAN> recover datafile 3, 4;
RMAN> sql 'alter database datafile 3, 4 online'



■Tip Use the RMAN report schema command to list datafile names and file numbers.

You can also query
the NAME and FILE# columns of V$DATAFILE to take names and numbers.

You can also specify the name of the datafile that you want to restore and recover:

RMAN> sql "alter database datafile ''/ora01/BRDSTN/data_ts01.dbf'' offline";

RMAN> restore datafile '/ora01/BRDSTN/data_ts01.dbf';

RMAN> recover datafile '/ora01/BRDSTN/data_ts01.dbf';

RMAN> sql "alter datafile ''/ora01/BRDSTN/data_ts01.dbf'' online";

■Note When using the RMAN sql command, if there are single quote marks within the SQL statement,
then you are required to use double quotes to enclose the entire SQL statement and then also use two single
quote marks where you would ordinarily just use one quote mark.


How It Works

A datafile-level restore and recovery works well when you want to specify which datafiles you
want recovered. With datafile-level recoveries, you can use either the datafile number or the
datafile name. For nonsystem and non-undo datafiles, you have the option of restoring and
recovering while the database is open. While the database is open, you have to first take offline
any datafiles being restored and recovered.

11-8. Restoring Datafiles to Nondefault Locations

Problem

You’ve just experienced a serious media failure and won’t be able to restore datafiles to their
original locations.

 In other words, you need to restore datafiles to a nondefault location.

Solution


Use the set newname and switch commands to restore datafiles to nondefault locations.


Both of these commands must be run from within an RMAN run{} block.


This example changes the
location of datafiles 4 and 5, which are in the data_ts tablespace:


RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set newname for datafile 4 to '/ora01/BRDSTN/data_ts01.dbf';
3> set newname for datafile 5 to '/ora01/BRDSTN/data_ts02.dbf';
4> restore tablespace data_ts;
5> switch datafile all; # Updates repository with new datafile location.
6> recover tablespace data_ts;
7> alter database open;
8> }


This is a partial listing of the output:
Starting recover at 08-FEB-07
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:37
Finished recover at 08-FEB-07


database opened

If the database is open, you can place the datafiles offline and then set their new names for restore and recovery:


RMAN> run{
2> sql 'alter database datafile 4, 5 offline';
3> set newname for datafile 4 to '/ora01/BRDSTN/data_ts01.dbf';
4> set newname for datafile 5 to '/ora01/BRDSTN/data_ts02.dbf';
5> restore datafile 4, 5;
5> switch datafile all; # Updates repository with new datafile location.
6> recover datafile 4, 5;
7> sql 'alter database datafile 4, 5 online';
8> }
starting media recovery
media recovery complete, elapsed time: 00:00:57
Finished recover at 08-FEB-07
sql statement: alter database datafile 4, 5 online
■Tip Use the RMAN report schema command to list datafile names and file numbers. You can also query
the NAME and FILE# columns of V$DATAFILE to take names and numbers.
You can also use datafile names instead of numbers. However, you have to be careful about
which name you use and where it comes in the script. This is because the control file doesn’t
consider the new location to be the current location until you issue the switch command.
RMAN> run{
2> sql "alter database datafile ''/ora02/BRDSTN/data_ts01.dbf'' offline';
3> set newname for datafile '/ora02/BRDSTN/data_ts01.dbf'
4> to '/ora01/BRDSTN/data_ts01.dbf';
5> restore datafile '/ora02/BRDSTN/data_ts01.dbf';
6> switch datafile all; # Updates repository with new datafile location.
7> recover datafile '/ora01/BRDSTN/data_ts01.dbf';
8> sql "alter database datafile ''/ora01/BRDSTN/data_ts01.dbf'' online";
9> }
■Tip When using the RMAN sql command, if there are single quote marks in the SQL command, then you
are required to use double quotes to enclose the entire SQL statement and use two single quote marks
where you would ordinarily use just one quote mark.
How It Works
You can use a combination of the set newname and switch commands to restore and recover a
datafile to a nondefault location. You must run the set newname command and the switch
command from within a run{} block.
The switch command updates the target database control file with the new location of
the datafile. It’s OK to use switch datafile all, which updates all datafile locations. The only
datafile names that will actually change are the ones that you have specified. Alternatively, you
can use switch datafile <number> to update the repository with a specific datafile number.
■Note If you don’t run the switch command, then RMAN marks the restored datafile to be a valid datafile
copy that can be used for subsequent restore operations.



11-9. Performing Block-Level Recovery

Problem

When performing daily backups, you notice that RMAN is reporting in your target database alert.log file that there is a corrupt block in a large datafile.

It could take a significant amount
of time to perform the traditional restore and recover of a large datafile.

You wonder whether
there is a method for just recovering the corrupt block and not the entire datafile.

Solution

If you’re using Oracle Database 11g or newer, use the recover datafile ... block command to recover individual blocks within a datafile.

■Note If you’re using Oracle Database 10g or Oracle9i Database, then use the blockrecover command
to perform block media recovery.


 The block recovery examples in this recipe use the recover command.
You can substitute the blockrecover command for the recover command in the examples in this recipe if
you’re using Oracle Database 10g or Oracle9i Database.
You can instruct RMAN to recover blocks in two ways:
• Use the corruption list clause.
• Specify individual datafiles and blocks.
When RMAN detects corrupt blocks, it writes an error to the alert.log file and also popu-
lates the V$DATABASE_BLOCK_CORRUPTION view. You can instruct RMAN to recover the
blocks listed as corrupt in that view as follows:
RMAN> recover corruption list;
The other way to recover blocks is to specify particular datafiles and blocks. Here are sev-
eral examples:
RMAN> recover datafile 5 block 24;
RMAN> recover datafile 7 block 22 datafile 8 block 43;
RMAN> recover datafile 5 block 24 from tag=tues_backup;
RMAN> recover datafile 6 block 89 restore until sequence 546;
RMAN> recover datafile 5 block 32 restore until 'sysdate-1';
RMAN> recover datafile 5 block 65 restore until scn 23453;
If you attempt to use the recover command on a datafile that RMAN does not know
about, you’ll receive an error similar to this:
RMAN-06023: no backup or copy of datafile 6 found to restore
If you have a user-managed backup of the datafile that you’ve taken outside RMAN, you
can use the catalog datafilecopy command to create metadata about the file in the RMAN
repository, as shown here:
RMAN> catalog datafilecopy '/orabackups/BRDSTN/index_ts01.dbf';
If all the required redo (online or archived) is available, you can now issue the recover
command, as shown here:
RMAN> recover datafile 6 block 25;
How It Works
Block-level corruption is rare and is usually caused by some sort of I/O error. However, if you
do have an isolated corrupt block within a large datafile, it’s nice to have the option of per-
forming a block-level recovery. Block-level recovery is useful when a small number of blocks
are corrupt within a datafile. Block recovery is not appropriate if the entire datafile needs
media recovery.
RMAN will automatically detect corruption in blocks whenever a backup or backup
validate command is issued. These blocks are reported as corrupt in the alert.log file and
the V$DATABASE_BLOCK_CORRUPTION view.
Here are the various locations that Oracle will record block-level corruption:
• RMAN backup populates V$DATABASE_BLOCK_CORRUPTION.
• Trace files.
• Alert.log file.
• Output of dbverify utility.
• Output of SQL analyze ... validate structure command.
• V$BACKUP_CORRUPTION and V$COPY_CORRUPTION will list corrupt blocks in
backup piece files.
Your database can be either mounted or open when performing block-level recovery. You
do not have to take the datafile being recovered offline. Block-level media recovery allows you
to keep your database available and also reduces the mean time to recovery since only the
corrupt blocks are offline during the recovery.
Your database must be in archivelog mode for performing block-level recoveries. In
Oracle Database 11g, RMAN can restore the block from the flashback logs (if available). If
the flashback logs are not available, then RMAN will attempt to restore the block from a full
backup, a level 0 backup, or an image copy backup generated by backup as copy command.
After the block has been restored, any required archived redo logs must be available to recover
the block. RMAN can’t perform block media recovery using incremental level 1 (or higher)
backups.
CREATING AND FIXING BLOCK CORRUPTION
The purpose of this sidebar is to show you how to corrupt a block so that you can test recovering at the block
level. Do not perform this test exercise in a production environment.
In a Unix environment, you can corrupt a specific block in a datafile using the dd command. For exam-
ple, the following dd command populates the 20th block of the tools01.dbf datafile with zeros:
$ dd if=/dev/zero of=tools01.dbf bs=8k conv=notrunc seek=20 count=1
Now if we attempt to back up the tools tablespace using RMAN, we receive an error indicating there is a
corrupt block:
RMAN> backup tablespace tools;
RMAN-03009: failure of backup command on ORA_DISK_1 channel
ORA-19566: exceeded limit of 0 corrupt blocks for file /ora01/BRDSTN/tools01.dbf
We additionally use the dbverify utility to validate that the tools01.dbf datafile has a corrupt block:
$ dbv file=/ora01/BRDSTN/tools01.dbf blocksize=8192
Here is the partial output of the dbverify command:
DBVERIFY - Verification starting : FILE = tools01.dbf
Page 20 is marked corrupt
Corrupt block relative dba: 0x01400014 (file 5, block 20)
The dbverify utility indicates that block 20 in file 5 is corrupt. We can corroborate this by viewing the
contents of V$DATABASE_BLOCK_CORRUPTION, as shown here:
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 20 1 0 ALL ZERO
We can now use the RMAN recover command to restore block 20 in datafile 5, as shown here (if
you’re using Oracle Database 10g or Oracle9i, then use the RMAN blockrecover command):
RMAN> recover datafile 5 block 20;
11-10. Recovering Read-Only Tablespaces
Problem
You issued a restore database command and notice that the datafiles associated with read-
only tablespaces were not restored.
Solution
Use the check readonly command to instruct RMAN to restore datafiles associated with read-
only tablespaces.
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database check readonly;
RMAN> recover database;
RMAN> alter database open;
Another alternative is to explicitly restore the read-only tablespaces after you have
restored the regular datafiles. In this example, two read-only tablespaces are restored after the
entire database has been restored:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> restore tablespace MAR05DATA, JUN05DATA;
RMAN> recover database;
RMAN> alter database open;
How It Works
By default, the restore command skips datafiles associated with read-only tablespaces. If you
want read-only tablespaces restored, then you must use the check readonly command or
explicitly restore each read-only tablespace.
■Note If you are using a backup that was created after the read-only tablespace was placed into read-only
mode, then no recovery is necessary for the read-only datafiles. In this situation, there is no redo that has
been generated for the read-only tablespace since it was backed up.
11-11. Restoring Temporary Tablespaces
Problem
RMAN doesn’t back up locally managed temporary tablespace tempfiles, and you want to
ensure that they’re restored as part of your backup strategy.
Solution
Starting with Oracle Database 10g, you don’t have to restore or re-create missing locally
managed temporary tablespace tempfiles. When you open your database for use, Oracle
automatically detects and re-creates locally managed temporary tablespace tempfiles.
When Oracle automatically re-creates a temporary tablespace, it will log a message to
your target database alert.log file similar to the following:
Re-creating tempfile <your temporary tablespace filename>
How It Works
When you open your database, Oracle will check to see whether any locally managed tempo-
rary tablespace tempfiles are missing. If Oracle detects missing temporary tempfiles, it will
automatically re-create them using information from the control files.
■Note Oracle’s feature of automatically re-creating temporary tablespace tempfiles applies only to missing
locally managed temporary tablespace tempfiles. This feature does not apply to a dictionary-managed tem-
porary tablespace.
If for any reason your temporary tablespace becomes unavailable, you can also re-create
it yourself. Since there are never any permanent objects in temporary tablespaces, you can
simply re-create them as needed. Here is an example of how to create a locally managed tem-
porary tablespace:
SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE
2 '/ora03/oradata/BRDSTN/temp01.dbf' SIZE 5000M REUSE
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
If your temporary tablespace exists but the temporary datafiles are missing, you can sim-
ply add the temporary datafile(s) as shown here:
SQL> alter tablespace temp
2 add tempfile '/ora03/oradata/BRDSTN/temp01.dbf' SIZE 5000M REUSE;
11-12. Forcing RMAN to Restore a File
Problem
As part of a test exercise, you attempt to restore a datafile twice and receive this RMAN
message:
restore not done; all files readonly, offline, or already restored
In this situation, you want to force RMAN to restore the datafile again.
Solution
Use the force command to restore datafiles and archived redo log files even if they already
exist in a location. This command forces RMAN to restore files, even if RMAN determines that
they don’t need to be restored. This first example uses the force to restore the obiwan01.dbf
datafile:
RMAN> restore datafile '/ora01/yoda/obiwan01.dbf' force;
You should see a message similar to this at the bottom of your RMAN messages stack:
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 09-FEB-07
Or if you know the particular datafile number, you can use the force command this way:
RMAN> restore datafile 42 force;
Similarly, you can use the force command on a tablespace. Here we use the force com-
mand to restore all datafiles associated with the star_wars tablespace:
RMAN> restore tablespace star_wars force;
To force RMAN to restore all datafiles in the database, issue this command:
RMAN> restore database force;
By default, RMAN won’t restore archived redo log files if they already exist on disk. You can
override this behavior as follows:
RMAN> restore archivelog from sequence 343 force;
How It Works
By default, RMAN will not restore a datafile that is in the correct location and contains the
expected information in the datafile header. This is known as restore optimization. To override
RMAN’s default behavior, use the power of the force command. The force command works
with any restore command.
11-13. Restoring from an Older Backup
Problem
You want to specifically instruct RMAN to restore from a backup set that is older than the last
backup that was taken.
Solution
You can restore an older backup a couple of different ways: using a tag name or using the
restore ... until command.
Specify a Tag Name
Use the list backup to find the tag name of the backup set. Every backup set has a tag name,
either the default or one you specified. For example, here’s the partial output of a list backup
command that shows the desired tag name:
BP Key: 159 Status: AVAILABLE Compressed: NO Tag: MON_BACK
Once you’ve identified the tag, you can instruct RMAN to use that as follows:
RMAN> startup mount;
RMAN> restore database from tag MON_BACK;
RMAN> recover database;
RMAN> alter database open;
You can also use a tag to restore specific tablespaces or datafiles as follows:
RMAN> restore tablespace users from tag INCUPDATE;
RMAN> restore datafile 2, 3 from tag AUG_FULL;
Using restore ... until
You can also tell RMAN to restore datafiles from a point in the past using the until clause of
the restore command in one of the following ways:
• Until SCN
• Until sequence
• Until restore point
• Until time

Or if you know the log sequence number that you want to restore up to, the syntax is as
follows:
RMAN> startup mount;
RMAN> restore database until sequence 17;
RMAN> recover database;
RMAN> alter database open;
If you’ve created restore points, then you can also use the restore point name as follows:
RMAN> startup mount;
RMAN> restore database until restore point FRI_RS;
RMAN> recover database;
RMAN> alter database open;
You can also specify a point in time from which you want RMAN to restore an older
backup. This example instructs RMAN to retrieve the first backup it finds that is more than
10 days old:
RMAN> startup mount;
RMAN> restore database until time 'sysdate – 10';
RMAN> recover database;
RMAN> alter database open;
Here we’re specifically instructing RMAN to restore from a date and time. Since we don’t
instruct RMAN to recover to a point in time, this example will perform a complete recovery:
RMAN> startup mount;
RMAN> restore database until time
2> "to_date('05-oct-2006 14:00:00', dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database;
RMAN> alter database open;
How It Works
You can easily instruct RMAN to restore from backups older than the most recent backup set.
You can do this by specifying a tag or using the restore ... until command. In versions prior
to Oracle Database 10g, this was the only way you could instruct RMAN to restore from a
backup older than the most recent one recorded in the repository.
New with Oracle Database 10g, by default RMAN will look in older backups if it can’t find
a backup piece or if corruption is detected. RMAN will search through backup history until it
locates a good backup or until it exhausts all possibilities. This feature is called restore failover.
In this example, RMAN cannot find the expected backup piece and automatically
searches for a prior backup. Here is the backup operation and its corresponding partial output
indicating that RMAN is initiating a restore failover:
RMAN> restore database;
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot the file specified.
failover to previous backup
11-14. Recovering Through Resetlogs
Problem
You recently performed an incomplete recovery that required you to open your database with
the open resetlogs command. Before you could back up your database, you experienced
another media failure. Prior to Oracle Database 10g, it was extremely difficult to recover using
a backup of a previous incarnation of your database. You now wonder whether you can get
your database back in one piece.
Solution
Beginning with Oracle Database 10g, you can restore a backup from a previous incarnation
and recover through a resetlogs command. You simply need to restore and recover your data-
base as required by the type of failure. In this example, the control files and all datafiles are
restored:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
When you issue the recover command, you should see redo being applied from the
previous incarnation of the database and then the current incarnation. In this example,
the previous incarnation has logs 77 through 79, and the current incarnation has logs 1 and 2:
archive log filename … thread=1 sequence=77
archive log filename … thread=1 sequence=78
archive log filename … thread=1 sequence=79
archive log filename … thread=1 sequence=1
archive log filename … thread=1 sequence=2
How It Works
Anytime you perform an incomplete recovery or recover with a backup control file, you are
required to open your database with an open resetlogs command. Prior to Oracle Database
10g, you were required to take a backup of your database immediately after you reset the
online redo log files. This is because resetting the online redo log files creates a new incarna-
tion of your database and resets your log sequence number back to 1. Any backups taken
before resetting the logs could not be easily used to restore and recover your database.
Starting with Oracle Database 10g, there is a new feature known as simplified recovery
through resetlogs. This feature allows you to restore from a backup from a previous incarnation
of your database and issue restore and recovery commands as applicable to the type of failure
that has occurred.
Oracle keeps track of log files from all incarnations of your database. The V$LOG_HISTORY
view is no longer cleared out during a resetlogs operation and contains information for the current incarnation as well as any previous incarnations.
The default format of the archived redo log files is now arch_%R_%T_%S.arc. The format
character %R is a resetlogs identifier that ensures that unique names are used for the archived
redo log files across different database incarnations.
You can view the incarnation of your database using the list incarnation command:
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1109210542 PARENT 3605284 25-AUG-06
2 2 ORCL 1109210542 PARENT 7349364 07-FEB-07
3 3 ORCL 1109210542 CURRENT 7652413 08-FEB-07
11-15. Restoring the Spfile
Problem
You might need to restore the spfile for one of several reasons:
• You accidentally deleted your server parameter file.
• You want to view an old copy of the spfile.
• You can’t start your instance with the current spfile.
Solution
First you need to have enabled the autobackup of your control file. For details on enabling
the autobackup of your control file, see recipe 5-4. Once autobackup of your control file is
enabled, then you can restore your spfile from an autobackup. All of the following examples
assume that there is not an spfile located in the default location. The approach varies slightly
depending on whether you’re using a recovery catalog, using a flash recovery area, or using
default locations for backups of the spfile.
■Note If you can’t start your instance with the current spfile, first rename or move your spfile and then
restore the spfile from a backup.
Using a Recovery Catalog
If you’re using a recovery catalog, then restoring the spfile is fairly straightforward. This exam-
ple connects to the recovery catalog and then restores the spfile:
RMAN> connect target /
RMAN> connect catalog rmancat/rmancat@rcat
RMAN> startup nomount;
starting Oracle instance without parameter file for retrieval of spfile
RMAN> restore spfile;
RMAN> startup force; # startup using restored spfile
Not Using a Recovery Catalog, RMAN Autobackup in Default Location
If you aren’t using a recovery catalog, then you need to know your database identifier before
you can proceed. See recipe 10-3 for details about determining your DBID.
This recipe assumes that you have configured your autobackups of the spfile to go to the
default location. The default location depends on your operating system. For Unix, the default
location is ORACLE_HOME/dbs. On Windows systems, it’s ORACLE_HOME\database or
ORACLE_HOME\dbs.
RMAN> connect target /
RMAN> shutdown immediate;
RMAN> startup force nomount; # start instance for retrieval of spfile
RMAN> set dbid 260150593;
RMAN> restore spfile from autobackup;
RMAN> startup force; # startup using restored spfile
You should now see your instance start normally:
Oracle instance started
database mounted
database opened
When the autobackup is located in the default location, you can use the parameters
maxseq and maxdays to alter the default behavior of RMAN. These parameters also apply to
control file restores from the default location. See recipe 10-4 for examples on how to use
maxseq and maxdays.
Not Using a Recovery Catalog, RMAN Autobackup Not in Default Location
If you’re either using a flash recovery area (FRA) or have the autobackup of your control file
configured to a nondefault location, then the spfile will not be backed up to what Oracle calls
the default location. In these situations, you have to specifically tell RMAN where to retrieve
the backup from.
If you’re using a FRA, your spfiles will be backed up in an autobackup directory in your
flash recovery area. You’ll have to find that directory and backup piece name before you can
restore your spfile. You’ll also need to know your database identifier before you can proceed.
See recipe 10-3 for determining your DBID. Once you know your DBID, you can restore the
spfile as follows
RMAN> connect target /
RMAN> shutdown immediate;
RMAN> set dbid 260150593;
RMAN> startup force nomount; # start instance for retrieval of spfile
RMAN> restore spfile from '/ora02/FRA/BRDSTN/autobackup/2006_10_02/o1_mf_s_62.bkp';
RMAN> startup force; # startup using restored spfile
You should now see your instance start normally:
Oracle instance started
database mounted
database opened
How It Works
If you’re using an spfile, then you can have it automatically backed up for you by enabling the
autobackup of the control file. If you’re using a recovery catalog, restoring the spfile is simple.
The recovery catalog maintains information about what backup piece contains the latest copy
of the spfile.
If the autobackups have been configured to create a backup piece in the default location,
then you need to set the DBID and issue the restore from autobackup command. When the
autobackup is in the default location, you can also use the values maxseq and maxdays to direct
RMAN to look at specific ranges of backup files.
If you’re not using a recovery catalog and the autobackups are created either in the FRA
or in a nondefault location, then you will specifically set your DBID and tell RMAN where the
backup files are located. This is because when you start your database in nomount mode and
if it doesn’t have access to a parameter file, there is no way for RMAN to know where the FRA is
located. If RMAN doesn’t know where the FRA is located, then there is no way for it to deter-
mine where the autobackups are stored.
11-16. Restoring Archived Redo Log Files
Problem
RMAN will automatically restore any archived redo log files that it needs during a recovery
process. You almost never need to manually restore archived redo log files. However, you may
want to manually restore the archived redo log files if any of the following situations apply:
• You want to restore archived redo log files in anticipation of later performing a recov-
ery; the idea is that if the archived redo log files are already restored, this will speed up
the recovery operation.
• You need to restore the archived redo log files to a nondefault location, either because
of media failure or because of storage space issues.
• You need to restore specific archived redo log files because you want to inspect them
via LogMiner.
Solution
This recipe is divided into two sections: restoring to the default location and restoring to a
nondefault location.
Restoring to Default Location
The following command will restore all archived redo log files that RMAN has backed up:
RMAN> restore archivelog all;
If you want to restore from a specified sequence, use the from sequence clause. This
example restores all archived redo log files from sequence 50:
RMAN> restore archivelog from sequence 50;
If you want to restore a range of archived redo log files, use the from sequence and until
sequence clauses or the sequence between clause, as shown here. These commands restore
archived redo log files from sequence 5170 through 5178 using thread 1.
RMAN> restore archivelog from sequence 5170 until sequence 5178 thread 1;
RMAN> restore archivelog sequence between 5170 and 5178 thread 1;
By default, RMAN won’t restore an archived redo log file if it is already on disk. You can
override this behavior via the force option:
RMAN> restore archivelog from sequence 1 force;
Restoring to Nondefault Location
Use the set archivelog destination clause if you want to restore archived redo log files to a
different location than the default. The following example restores to the nondefault location
of /ora01/archrest. The set command must be run from within the RMAN run{} block.
RMAN> run{
2> set archivelog destination to '/ora01/archrest';
3> restore archivelog from sequence 5200;
4> }
How It Works
If you’ve enabled a flash recovery area, then RMAN will by default restore archived redo log
files to the destination defined by the initialization parameter db_recovery_file_dest. Other-
wise, RMAN uses the log_archive_dest_1 initialization parameter to determine where to
restore the archived redo log files.
If you restore archived redo log files to a nondefault location, RMAN knows the location
they were restored to and automatically finds these files when you issue any subsequent
recover commands. RMAN will not restore archived redo log files that it determines are
already on disk. Even if you specify a nondefault location, RMAN will not restore an archived
redo log file to disk if it already exists. In this situation, RMAN will simply return a message
stating that the archived redo log file has already been restored. Use the force command to
override this behavior.
If you are uncertain of the sequence numbers to use during a restore of log files, you
can query the V$LOG_HISTORY view or issue an RMAN list backup command for more
information.
■Note When restoring archived redo log files, your database can be either mounted or open.
11-17. Recovering Datafiles Not Backed Up
Problem
You recently added a datafile to a tablespace and had a failure before the datafile was backed
up. You wonder how you’re going to restore and recover a datafile that was never backed up.
Solution
For this solution to work, you need to have a good baseline backup of your database and any
subsequently generated redo up to the point where the datafile was created. If you have your
current control file, then you can restore and recover at the datafile, tablespace, or database
level. If you’re using a backup control file that has no information about the datafile, then you
must restore and recover at the database level.
■Note In Oracle9i Database and previous releases, the DBA had to perform some manual steps to restore
a datafile not backed up yet. This usually involved re-creating the missing datafile and then restarting the
recovery process.
Using a Current Control File
In this example, we use the current control file and are recovering the user_idx01.dbf datafile
in the user_idx tablespace:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore tablespace user_idx;
You should see a message like the following in the output as RMAN re-creates the datafile:
creating datafile fno=5 name=/ora01/oradata/BRDSTN/user_idx01.dbf
Now issue the recover command and open the database:
RMAN> recover tablespace user_idx;
RMAN> alter database open;
Using a Backup Control File
This scenario is applicable anytime you use a backup control file to restore and recover a
datafile that has not yet been backed up. First, we restore a control file from an older backup:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from '/orafra/BRDSTN/autobackup/2006_10_11/01_mfn_.bkp';
When the control file has no record of the datafile, RMAN will throw an error if you
attempt to recover at the tablespace or datafile level. In this situation, you must use the
restore database and recover database commands as follows:
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
Next, you should see quite a bit of RMAN output. Near the end of the output you should
see a line similar to this indicating that the datafile has been re-created:
creating datafile fno=9 name=/ora02/BRDSTN/data_ts06.dbf
Since you restored using a backup control file, you are required to open the database with
the resetlogs command:
RMAN> alter database open resetlogs;
How It Works
Starting with Oracle Database 10g, there is enough information in the redo stream for RMAN
to automatically re-create a datafile that was never backed up. It doesn’t matter whether the
control file has a record of the datafile. It doesn’t matter whether the datafile was added as part
of a create database datafile command or a create tablespace command.
Prior to Oracle Database10g, manual intervention from the DBA was required to recover a
datafile that had not been backed up yet. If Oracle identified that a datafile was missing that
had not been backed up, the recovery process would halt, and you would have to identify the
missing datafile and re-create it. After re-creating the missing datafile, you had to manually
restart the recovery session.
In Oracle Database 10g and newer, this is no longer the case. RMAN automatically detects
that there isn’t a backup of a datafile being restored and re-creates the datafile from informa-
tion retrieved from the control file and/or redo information as part of the restore and recovery
operations.

11-18. Deleting Archived Redo Log Files During Recovery
Problem
You know that you’re going to be applying many archived redo log files during a recovery
process. You want RMAN to automatically delete the archived redo logs after they’re applied.
Solution
Use the recover ... delete archivelog command as shown here:
RMAN> recover database delete archivelog;
You should see a message like the following in the output after RMAN successfully applied
an archived redo log:
archived log file name=/usr/oracle/flash_recovery_area/DB11G/archivelog
/2007_04_20/o1_mf_1_740_32jmotgx_.arc thread=1 sequence=740
channel default: deleting archived log(s)
archived log file name=/usr/oracle/flash_recovery_area/DB11G/archivelog
/2007_04_20/o1_mf_1_740_32jmotgx_.arc RECID=1724 STAMP=620357134
How It Works
If you know you’re going to restore and apply many archived redo log files, then you can use
the delete archivelog clause of the recover command. This will cause RMAN to automatically
remove any archived redo log files that have been applied and are not needed for recovery any
longer. RMAN will not delete any archived redo log files that were already on disk at the time the
recover command was issued.
■Note If your archived redo log files are restored to a flash recovery area, then RMAN will automatically
enable the delete archivelog feature.
You can also instruct RMAN to use a specified amount of space for keeping restored
archived redo logs on disk. For example, if you want RMAN to use at most 500MB disk space
for restored archived redo log files, then you would specify that maximum size, as shown here:
RMAN> recover database delete archivelog maxsize 500m;
If you don’t specify a maximum size, then RMAN deletes archived redo log files after they
are applied. If you do specify a maximum size, then be careful not to specify a size smaller
than your largest archived redo log file. RMAN will throw an error if it encounters an archived
redo log file smaller than the specified maximum size and halt the restore process, as shown
here:
RMAN-06557: unable to restore archived log thread 1, sequence 361
RMAN-06558: archived log size of 7546 kb is bigger than available space of 5120 kb

11-19. Restoring from Uncataloged Backup Pieces in Oracle
Database 10g and Newer
Problem
You had to re-create your control file and you are not using a recovery catalog. Afterward, you
attempted to restore datafiles using RMAN but received the following errors:
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
You want to restore control files, datafiles, and archived redo logs from RMAN backup
pieces, but your control file now contains no information whatsoever about previously taken
backups.
Solution
Use the catalog command to add RMAN metadata directly to your control file about backup
pieces.
Using a Flash Recovery Area
You can have RMAN repopulate the control file with all file information in the flash recovery.
The following command will catalog all backup sets, datafile copies, and archived redo log
files located in the flash recovery area:
RMAN> catalog recovery area;
Using a Directory
You can also instruct RMAN to catalog all the backup pieces and image copies located under a
starting directory path. This example instructs RMAN to record metadata in the repository for
any backup pieces and image copies located under the /oradump01/FRA directory:
RMAN> catalog start with '/oradump01/FRA';
Using a Backup Piece
For a backup set to be usable, you must catalog all backup pieces in the backup set. In this
example, there is only one backup piece in the backup set:
RMAN> catalog backuppiece
2> 'C:\FRA\ORCL\BACKUPSET\2007_01_03\O1_MF_NNNDF_TAG20070103T160632_2SRFQSG2_.BKP';
This writes metadata information about that backup piece into the control file. If success-
ful, you should see output similar to this:
cataloged backuppiece
backup piece handle=C:\FRA\ORCL\BACKUPSET\
2007_01_03\O1_MF_NNNDF_TAG20070103T160632_2SRFQSG2_.BKP recid=2 stamp=610911396
How It Works
New with Oracle Database 10g, you can now add metadata about backup pieces directly
to your control file via the catalog command. If you’re not using a recovery catalog, this
can be particularly useful if you ever have to re-create your control file. This is because when
re-creating the control file, all of your RMAN information is wiped out.
You can use the catalog command to add the following types of information to your
control file:
• Backup pieces
• Archived redo log files
• Control file copies
• Datafile copies
• Files in the flash recovery area
If you’re using a database version prior to Oracle Database 10g, then see recipe 11-20 on
how to use DBMS_BACKUP_RESTORE to extract files from backup pieces for which your con-
trol file has no information.
■Note You cannot use the catalog command for backup pieces on a tape device.
11-20. Restoring from Uncataloged Backup Pieces in Oracle9i
Database and Older
Problem
You had to re-create your control file, and you are not using a recovery catalog. You want to
restore control files, datafiles, and archived redo logs from RMAN backup pieces, but your
control file now contains no information whatsoever about previously taken backups.
■Note If you are using Oracle Database 10g or newer, we strongly recommend that you use the catalog
command and do not use DBMS_BACKUP_RESTORE. See recipe11-19 for details on how to add metadata to
your control file for uncataloged backup pieces.