RMAN Performing Incomplete

Incomplete database recovery consists of two steps: restore and recovery. The restore step will
re-create 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

recover database using backup controlfile;

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 [SCN])
• Log sequence number
• Restore point (Oracle Database 10g Release 2 and newer versions)

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

can view the datafile header SCNs and the status of each datafile via this SQL query:

SQL> select file#, status, checkpoint_change#,
2 to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss')
3 from v$datafile_header;

Performing Time-Based Recovery

Problem

You want to restore your database to a previous date and time.

Solution

You can restore your database to a previous time in one of two ways:
• Specify the time as part of the restore and recover commands.
• Use the set until time command, and then issue unqualified restore and recover
commands.

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('05-oct-2006 14:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database until time
2> "to_date('05-oct-2006 14:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database open resetlogs;

You can also specify the time by using the set until time command. This command and
the subsequent restore and recover must be executed from within a run{} block:

RMAN> connect target /
RMAN> startup mount;
RMAN> run{
RMAN> set until time "to_date('05-oct-2006 14:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> restore database;
RMAN> recover database;
RMAN> }
RMAN> alter database open resetlogs;


Performing Log Sequence–Based Recovery


Problem
You want to use RMAN to restore up to, but not including, a certain archived redo log file.

Solution
RMAN allows you to apply redo up to (but not including) a specific archived redo log file by specifying its sequence number when restoring and recovering. You can do this in one of two ways:

• Specify until sequence as part of the restore and recover commands.
• Use the set until sequence command.

The following example restores and recovers the target database up to, but not including, log sequence number 50:

RMAN> connect target /
RMAN> startup mount;
RMAN> restore database until sequence 50;
RMAN> recover database until sequence 50;
RMAN> alter database open resetlogs;


You can also use the set until command from within a run{} block to perform a log
sequence–based recovery. The following examples restores and recovers up to but not including log sequence number 125 of thread 1:


RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set until sequence 125 thread 1;
3> restore database;
4> recover database;
5> }
RMAN> alter database open resetlogs;

You can query sequence number information from V$LOG_HISTORY, as shown here:

SQL> select sequence#, first_change#, first_time
2 from v$log_history
3 order by first_time;

And here’s the corresponding query for V$ARCHIVED_LOG:

SQL> select sequence#, first_change#, first_time
2 from v$archived_log
3 order by first_time;

select sequence#, first_change#, first_time
from v$log_history
where first_time > '23/09/2009'
order by first_time
/

RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set until scn 950;
3> restore database;
4> recover database;
5> }
RMAN> alter database open resetlogs;

Performing Cancel-Based Recovery

Problem

You desire to perform a cancel-based incomplete database recovery first using RMAN to
restore the datafiles and then using SQL*Plus to recover the datafiles. A cancel-based recovery is one that proceeds until you manually stop it.

Solution

The following example restores from the latest RMAN backup in preparation for a SQL*Plus cancel-based recovery:

RMAN> connect target /
RMAN> startup mount;
RMAN> restore database; # restore database from last backup

Once your database is restored, you can start a SQL*Plus session and initiate a cancelbased recovery, as shown here:

SQL> connect / as sysdba
SQL> recover database until cancel;
SQL> alter database open resetlogs;

Using LogMiner to Find an SCN

Problem

A user accidentally dropped a table. You want to find the SCN associated with that drop statement so that you can restore the database to the SCN just prior to the accidental drop.

Solution
Here are the steps for instructing LogMiner to analyze a specific set of archived redo log files for an SCN associated with a SQL statement:

1. Specify a set of archived redo log files for LogMiner to analyze.
2. Start LogMiner, and specify a data dictionary.
3. Perform analysis.
4. Stop the LogMiner session.

First you need to tell LogMiner which online redo log files or archived redo log files you want to analyze. In this scenario, we know the SQL statement that we’re looking for is in the
archived redo log file with a sequence number of 7.

SQL> connect sys/foo as sysdba
SQL> exec dbms_logmnr.add_logfile(-
logfilename=>'/ora01/BRDSTN/arc00007_0605867201.001', -
options=>dbms_logmnr.addfile);

If you want to mine multiple online redo log files, you can add more using
DBMS_LOGMNR.ADD_LOGFILE as follows:

SQL> exec dbms_logmnr.add_logfile(-
logfilename=>'/ora01/BRDSTN/arc00008_0605867201.001', -
options=>dbms_logmnr.addfile);

■Note You can view which log files will be analyzed by the current LogMiner session by querying the V$LOGMNR_LOGS view.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

SQL> select operation, scn
2 from v$logmnr_contents
3 where table_name='PAYROLL'
4 and operation='DROP';

OPERATION SCN
--------- -------
DROP 1047474

SQL> exec dbms_logmnr.end_logmnr();

How It Works

You can use LogMiner to find SCNs associated with DML and DDL statements. LogMiner
requires supplemental logging to be enabled to be able to display information about
DML
statements. Enable supplemental logging by issuing the following SQL:

SQL> connect sys/foo as sysdba
SQL> alter database add supplemental log data;

Performing Change/SCN-Based Recovery


Problem

You want to perform an incomplete database recovery to a particular database SCN.

Solution

After establishing the SCN to which you want to restore, use the until scn clause to restore up
to, but not including, the SCN specified. The following example restores all transactions that
have an SCN that is less than 950:

RMAN> connect target /
RMAN> startup mount;
RMAN> restore database until scn 950;
RMAN> recover database until scn 950;
RMAN> alter database open resetlogs;


Recovering to a Restore Point

ProblemYou want to restore and recover to a restore point.

Solution

Before you can restore to a restore point, you must have previously created a restore point via the create restore point command. Once you’ve done this, you can use the until restore point clause of the restore command.


CREATING A NORMAL RESTORE POINT
There are two types of restore points: normal and guaranteed. Guaranteed restore points require that you have the flashback database feature enabled. See Chapter 13 for more details on how to use flashback database
with guaranteed restore points.

You can create a normal restore point using SQL*Plus as follows:

SQL> create restore point MY_RP;

This command creates a restore point named MY_RP that is associated with the SCN of
the database at the time the command was issued. You can view the current SCN of your database as shown here:

SQL> select current_scn from v$database;

You can also view the SCN assigned to your restore point(s):

SQL> select name, scn from v$restore_point;

This example restores and recovers to the MY_RP restore point:

RMAN> connect target /
RMAN> startup mount;
RMAN> restore database until restore point MY_RP;
RMAN> recover database until restore point MY_RP;
RMAN> alter database open resetlogs;


RMAN> connect target /
RMAN> startup mount;
RMAN> run{
RMAN> set until restore point MY_RP;
RMAN> restore database;
RMAN> recover database;
RMAN> }
RMAN> alter database open resetlogs;

Restoring a Noarchivelog Mode Database

Problem
You used RMAN to back up a database in noarchivelog mode. You now need to restore this database from an RMAN backup.

Solution

When you restore a noarchivelog database, you can choose to use a backup control file or the current control file. You can run the following query to verify the type of control file you used
to mount your database:

SQL> select open_mode, controlfile_type from v$database;
OPEN_MODE CONTROL
---------- -------
MOUNTED CURRENT

Using Backup Control File
Our recommended approach is to first restore the control file that was backed up at the same time your noarchivelog mode database was backed up. This way the control file has an SCN that is consistent with the datafile SCNs. After you restore the control file, you can then restore the datafiles and open your database with the open resetlogs command. For example:

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

Using Current Control File

If you don’t restore your control file, you will have to perform a few extra steps. This example does not restore the control file and uses SQL*Plus to cancel out of the recovery session:

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

Recovering to a Previous Incarnation

ENABLING AUTOBACKUP OF CONTROL FILE

Enabling the autobackup of the control file is fairly straightforward. To enable this feature, use the configure command as follows:


RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup until time
2> "to_date('03-sep-2006 00:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database mount;
RMAN> list incarnation of database;

RMAN> reset database to incarnation 1;

RMAN> restore database until time
2> "to_date('03-sep-2006 00:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database until time
2> "to_date('03-sep-2006 00:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database open reset logs;

RMAN> configure controlfile autobackup on;

Performing Tablespace Point-in-Time Recovery

Problem

A rogue developer thought they were in a test environment and issued commands to delete data from several tables in one tablespace. It turns out they were in the production environment.

You want to use tablespace point-in-time recovery (TSPITR) to restore your tablespace
to the point in time just before the erroneous DML was issued.

Solution

This recipe shows how to perform fully automated RMAN tablespace point-in-time recovery. Here are the steps:

1. Determine and resolve any dependencies to objects in tablespaces not included in the
TSPITR.
2. Determine whether there are objects that will not be recovered.
3. Create a destination on disk to temporarily hold the auxiliary database.
4. Run the recover tablespace until command.
5. Back up the restored tablespace and alter it online.

Step 1: Determine and Resolve Dependencies

SQL> SELECT *
2 FROM sys.ts_pitr_check
3 WHERE(ts1_name = 'USERS' AND ts2_name != 'USERS')
4 OR (ts1_name != 'USERS' AND ts2_name = 'USERS');

Step 2: Determine Which Objects Will Not Be Recovered

You can query the TS_PITR_OBJECTS_TO_BE_DROPPED view to help identify objects
that need to be preserved. This query identifies objects created after the time to which the TSPITR will be performed:


SQL> SELECT owner, name, tablespace_name
2 FROM ts_pitr_objects_to_be_dropped
3 WHERE tablespace_name ='USERS'
4 AND creation_time > to_date('12-nov-2006 16:00:00','dd-mon-rrrr hh24:mi:ss');


Step 3: Create an Auxiliary Destination
First ensure that you have an area on disk that will serve as a temporary container for an auxiliary database. This area will need enough space for a system, undo, and temporary tablespace.

We recommend you have at least 1GB of space in your auxiliary destination.
C:\> mkdir c:\auxx

Step 4: Run the recover Command
You can now perform a fully automated TSPITR. You can restore until a time, SCN, or
sequence. Notice that your database is open during the TSPITR. In this example, we restore the users tablespace up to, but not including, the time specified:


Caution Make sure you use the recover command (and not restore)! You can recover to an SCN, log
sequence number, or time.

RMAN> connect target /
RMAN> recover tablespace users until time
2> "to_date('12-nov-2006 16:00:00','dd-mon-rrrr hh24:mi:ss')"
3> auxiliary destination 'c:\auxx';

Step 5: Back Up the Tablespace and Alter It Online
Once the TSPITR completes, you must back up the recovered tablespace and bring it online:

RMAN> backup tablespace users;
RMAN> sql 'alter tablespace users online';

For example, say you have two tablespaces, p_dt and p_idx, and all of the objects in those two tablespaces are owned by prod_own. If there were undesirable DML statements that were issued against tables owned by prod_own, then you could use TSPITR to restore and recover to just prior to when the bad SQL was run. In this example, we restore the two tablespaces back to just before SCN 1432:

C:\> mkdir c:\auxx

RMAN> connect target /
RMAN> recover tablespace p_dt, p_idx until SCN 1432 auxiliary destination 'c:\auxx';

You should now see quite a number of RMAN messages displaying the status of each
operation. Once complete, back up the recovered tablespaces and bring them online:

RMAN> backup tablespace p_dt, p_idx;
RMAN> sql 'alter tablespace p_dt online';
RMAN> sql 'alter tablespace p_idx online';

Recovering a Subset of Datafiles

RMAN> connect target /
RMAN> startup mount;

Use the RMAN report schema command to identify which datafiles you do not want to
restore and recover. You can also query V$DATAFILE for the datafile details. In this

example,
the datafiles 7, 8, and 9 are taken offline and are not restored and recovered:

RMAN> sql 'alter database datafile 7, 8, 9 offline for drop';
RMAN> restore database until SCN 314159;
RMAN> recover database until SCN 314159;
RMAN> alter database open resetlogs;

Troubleshooting Incomplete Recovery

Problem

You’re attempting to perform an incomplete recovery, and RMAN is returning the following error:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
You wonder how to go about determining what is wrong.

Solution

In many situations, problems with incomplete recovery are caused by omitting one of the required steps. Here is the correct sequence of steps for most incomplete recovery scenarios:

1. restore database until ;
2. recover database until ;
3. alter database open resetlogs;