Incomplete Recovery

Incomplete 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 the database. In other words you are restoring and recovering to a point in time in the past. For this reason incomplete recovery is also known as “database point in time recovery”.

Reason for performing Incomplete Recovery:

1. You don’t have all the redo required to perform the complete recovery

2. You purposely want to roll the database back to a point in time.( example: someone accidentally truncated a table).

Steps of incomplete recovery: Restore recovery and open with resetlogs

The restore process can be initiated from RMAN in several ways:

·         Restore database until

·         Restore tablespace until

·         Flashback database

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

·         SCN

·         Log sequence number

·         Restore point (Oracle 10g Release 2 and newer versions)

Note: You cannot perform incomplete recovery on a subset of your database’s online datafiles.

When performing incomplete recovery, all the checkpoint SCNs for all the online datafiles must be synchronized before you can open the 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:

SQL> SELECT file#,status,checkpoint_change#,to_char(checkpoint_time,’dd-mon-yyyy hh24:mi:ss’) from v$datafile_header;

SQL> SELECT checkpoint_change# FROM v$database; # To check SCN in control file

After you open the database with the resetlogs clause, the checkpoint SCN in the control files (v$database) will be synchronized with the checkpoint SCN in the datafile headers (v$datafile_header)

Exception to this rule is tablespace point in time recovery.

Steps follow by RMAN while recovery:

1. Apply any incremental backups available.

2. Apply any archived redo log files on disk

3. Attempt to retrieve archived redo log files from the backup set , (If archived redo log files are not available on disk )

Why should database be in MOUNT stage while incomplete recovery?

·         RMAN needs DB in mount stage to be able to read and write to the control file.

·         With an incomplete recovery,the system datafile is always one of the datafiles being recovered .The system datafile must be offline while it is beign recovered .Oracle will not allow your database to be open while this is happening.

Incomplete Recovery Scenarios:

Situation	RMAN
You know approximately what time you want to stop the recovery process	Restore database until time to_date(’05-oct-2012 14:00:00’,’dd-mon-yyyy hh24:mi:ss’); Recover database until time to_date(’05-oct-2012 14:00:00’,’dd-mon-yyyy hh24:mi:ss’);
You know the particular log file for which you want to stop the recovery process	Restore database until sequence 50; Recover database until sequence 50;               OR Restore database; Recover database until cancel; (provide the archive redo log filename manually)
You know the SCN at which you want to end the recovery process	Restore database until scn 950; Recover database until scn 950;
You want to restore to a defined restore point	Restore database until restore point MY_REP Recover database until restore point MY_REP

Situation: Time Based Recovery

You know approximately what time you want to stop the recovery process

Solution:

1. Specify the time as part of restore and recover command

RMAN>connect target /

RMAN>startup mount

RMAN> Restore database until time “to_date(’05-oct-2012   14:00:00’,’dd-mon-yyyy hh24:mi:ss’)”;

RMAN> Recover database until time “to_date(’05-oct-2012   14:00:00’,’dd-mon-yyyy hh24:mi:ss’)”;

RMAN>Alter database open resetlogs;

2. Use the SET UNTIL TIME command ,and then issue unqualified restore and recover commands.

RMAN>connect target /

RMAN>startup mount

RMAN> run{

RMAN> set until time “to_date(’05-oct-2012  14:00:00’,’dd-mon-yyyy hh24:mi:ss’)”;

RMAN> restore database;

RMAN>recover database;

RMAN>}

RMAN>Alter database open resetlogs;

RMAN will restore and recover the database upto but not including the specified time.

Situation: Log File Sequence based recovery

You know the particular log file for which you want to stop the recovery process

Solutions:

1. Specify until sequence as part of restore and recover commands

RMAN>connect target /

RMAN>startup mount

RMAN> Restore database until  sequence 50;

RMAN> Recover database until  sequence 50;

RMAN>Alter database open resetlogs;

2.Use the set until sequence command

RMAN>connect target /

RMAN>startup mount

RMAN> run{

RMAN> set until  sequence thread 1;

RMAN> restore database;

RMAN>recover database;

RMAN>}

RMAN>Alter database open resetlogs;

SELECT SEQUENCE#,first_change#,first_time,status FROM v$log ORDER BY 3 DESC;

SELECT SEQUENCE#,first_change#,first_time FROM v$log_history ORDER BY 3 DESC;

SELECT SEQUENCE#,first_change#,first_time,status FROM v$archived_log where SEQUENCE#=19715

 ORDER BY 3 DESC;

Situation: Cancel Based Recovery

You know the particular log file for which you want to stop the recovery process

You desire to perform a cancel-based incomplete database recovery first by 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.

RMAN>connect target /

RMAN>startup mount

RMAN> Restore database; #restore database from last backup

Once database is restored , you can start a SQL*PLUS session and initiate a cancel based recovery as shown below:

SQL>connect  / as sysdba

SQL>recover database until cancel;  #database should be in mount stage

You will be prompted by SQL*PLUS to manually apply each archived redo log file.The following is the prompt that you will see for each log file:

Specify Log: {<RET>=suggested|filename|AUTO|CANCEL}

Hit the enter key until you arrive at the archived redo log file where you want to stop the recovery process. After the recovery process is complete open the database with resetlog option.

SQL>Alter database open resetlogs;

Situation: Change/SCN based recovery

You know the SCN at which you want to end the recovery process

How to find the correct SCN information??

1.       Using LogMiner to find an SCN: (Refer : RMAN Book Chapter 12 for detail page 368)

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 accident drop.

Steps for instructing LogMiner to analyze a specific set of archived redo log files for an SCN associated with a SQL statement:

·         Specify a set of archived redo log files for LogMiner to analyze

·         Start LogMiner, and specify a data dictionary

·         Perform analysis

·         Stop the LogMiner session

2. You can look in the alert.log file

3. You can look in the trace files

4. You can query the FIRST_CHANGE# column of the v$LOG,V$LOG_HISTORY,v$ARCHIVED_LOG views.

 SELECT SEQUENCE#,first_change#,first_time,status FROM v$log ORDER BY 3 DESC;

SELECT SEQUENCE#,first_change#,first_time FROM v$log_history ORDER BY 3 DESC;

SELECT SEQUENCE#,first_change#,first_time,status FROM v$archived_log ORDER BY 3 DESC;

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.

RMAN>connect target /

RMAN>startup mount

RMAN> Restore database until  SCN 950;

RMAN> Recover database until  SCN 950;

RMAN>Alter database open resetlogs;

2.Use the set until sequence command

RMAN>connect target /

RMAN>startup mount

RMAN> run{

RMAN> set until   scn 950;

RMAN> restore database;

RMAN>recover database;

RMAN>}

RMAN>Alter database open resetlogs;

Situation:Recovery to a restore point

You want to restore to a defined restore point

Creating a restore point:

There are two restore points: normal and guaranteed .Guarenteed restore points require that you have the flashback database features enable.

SQL> create restore point my_rp;

SQL> Select current_scn from v$database;   # to check scn associated with my_rp restore point.

SQL>select name, scn from v$restore_point;  # To check SCn associated with your 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;

2.Use the set until sequence command

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;

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 <specified point>;

2. recover database until <specified point>;

3. alter database open resetlogs;

The specified point in steps 1 and 2 should be identical. The specified point can be an SCN, a time, a log sequence number, or a restore point.

When performing incomplete recovery ,all the checkpoint SCNs for all the online datafiles must be synchronized before you can open the 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:

SQL> SELECT file#,status,checkpoint_change#,to_char(checkpoint_time,’dd-mon-yyyy hh24:mi:ss’) from v$datafile_header;

SQL> SELECT checkpoint_change# FROM v$database; # To check SCN in control file

After you open the database with the resetlogs clause, the checkpoint SCN in the control files (v$database)  will be synchronized with the checkpoint SCN in the datafile headers (v$datafile_header)

Media Recovery is Required or Not ?

When you start your database, Oracle uses the SCN information in the control files and

 datafile headers to determine which one of the following will occur:

• Starting up normally

• Performing crash recovery

• Determining that media recovery is required

 On start-up, Oracle checks the instance thread status to determine whether crash recovery is required. When the database is open for normal operations, the thread status is OPEN. When Oracle is shut down normally (normal, immediate, or transactional), a checkpoint takes place, and the instance thread status is set to CLOSED.

 When your instance abnormally terminates (such as from a shutdown abort command), the thread status remains OPEN because Oracle didn’t get a chance to update the status to CLOSED. On start-up, when Oracle detects that an instance thread was abnormally left open, the system monitor process will automatically perform crash recovery. This query demonstrates how a single instance of Oracle would determine whether crash recovery is required:

SELECT

a.THREAD#, b.open_mode, a.status,

CASE

WHEN ((b.open_mode='MOUNTED') AND (a.status='OPEN')) THEN 'Crash Recovery req.'

WHEN ((b.open_mode='MOUNTED') AND (a.status='CLOSED')) THEN 'No Crash Rec. req.'

WHEN ((b.open_mode='READ WRITE') AND (a.status='OPEN')) THEN 'Inst. already open'

ELSE 'huh?'

END STATUS

FROM v$thread a,

v$database b,

v$instance c

WHERE a.THREAD# = c.THREAD#;

 Oracle will start up normally if the SCN information in the control files matches the SCNs in the corresponding datafiles. If the checkpoint SCN in the datafile is less than the corresponding SCN in the control file, Oracle will throw a media recovery error. For example, if you restored a datafile from a backup, Oracle would detect that the SCN in the datafile is less than the corresponding SCN in the control file. Therefore, a recovery is required to apply changes to the datafile to catch it up to the SCN in the control file.

The following SQL query demonstrates the internal checks that Oracle performs to determine

whether media recovery is required:

SELECT a.NAME,a.checkpoint_change#,b.checkpoint_change#,

CASE

WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'

WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Recovery'

WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'

ELSE 'what the ?'

END STATUS,

CASE

WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'No action Required'

WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Perform Media Recovery'

WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Restore a newer control file'

ELSE 'what the ?'

END DBA_ACTION

FROM v$datafile a, -- control file SCN for datafile

v$datafile_header b -- datafile header SCN

WHERE a.FILE# = b.FILE#;

---The V$DATAFILE_HEADER view uses the physical datafile on disk as its source.

The V$DATAFILE view uses the control file as its source.