Open Resetlogs in RMAN

Found this during googling.. and thought of something to share..


During one of our backup recovery testing, we discovered an interesting issue.

After we have recover the database, we have the option of running "alter database open resetlogs" using RMAN shell or SQL shell.

One of my mates decided to run it on the RMAN shell and this is what happens.


oracle@siep01:/users/oracle [GENIM]
$ rman target / catalog rman@catalogdb

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Feb 21 15:07:22 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: GENIM (DBID=3698867579)
recovery catalog database Password:
connected to recovery catalog database

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- ------- ---------- ----------
59661   59662   GENIM    3698867579       CURRENT 1          06-APR-11


RMAN> alter database open resetlogs;

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- ------- ---------- ----------
59661   59662   GENIM    3698867579       CURRENT 1          06-APR-11
59661   8124276 GENIM    3698867579       ORPHAN  6671390302 20-FEB-12 <-- ORPHAN!!


Lesson Learned!

Unless you plan to reset the database, use the SQL*Plus to open the database!

Quoting from Oracle Document :

"Purpose 
To reset the incarnation of the target database in the RMAN repository, which means to do either of the following actions:

• Inform RMAN that the SQL statement ALTER DATABASE OPEN RESETLOGS has been executed and that a new incarnation of the target database has been created. Note that if you run the RMAN command ALTER DATABASE OPEN RESETLOGS (not the SQL statement with the same keywords), then RMAN resets the target database automatically so that you do not have to run RESET DATABASE. By resetting the database, RMAN considers the new incarnation as the current incarnation of the database."

Determining the SCN for Incomplete Recovery After Restore

Because the restored database will not have the online redo logs of the production database, perform incomplete recovery up to the lowest SCN of the most recently archived log in each thread and then open with the RESETLOGS option. Obtain the SCN for recovery termination by finding the lowest SCN among the most recent archived logs for each thread.

Start SQL*Plus and use the following query to determine the necessary SCN:

SQL> SELECT MIN(maxnc) FROM 
 (SELECT MAX(a.NEXT_CHANGE#) maxnc 
      FROM V$ARCHIVED_LOG a, V$THREAD t
      WHERE a.THREAD# = t.THREAD#
        AND a.ARCHIVED='YES' 
        AND t.ENABLED='DISABLED'
      GROUP BY a.THREAD#);

==================================================

Caution on the Use of RESETLOGS

Caution should be used when recovering an Oracle database to a point-in-time using the Point In Time option in the Recover tab of the Oracle Advanced Restore Options dialog box. The reasoning is that this action executes a powerful RMAN statement called "ALTER DATABASE OPEN RESETLOGS" which will reset the SCN (System Change Number) and time stamp on every object of the database (i.e., datafiles and control files).

Archived redo logs have these two values (SCN and time stamp) in their headers, and Oracle will not apply an archived redo log to a datafile unless the RESETLOGS SCN and time stamps match, to prevent you from corrupting your datafiles with old archived logs.

Execution of the "ALTER DATABASE OPEN RESETLOGS" statement has the effect of recovering the database to a time that is not current, hence being reset with old data (i.e., an old incarnation). This is a very useful operation if the point-in-time to which you are trying to recover is certain and known, but can be counterproductive if you are guessing at the point-in-time.

If the point-in-time is in question, it is recommended that you select the Restore Data option in the Advanced Restore Options (Restore) tab and/or the Restore Control File option in the Advanced Restore Options (Ctrl & SP Files) tab instead of the Point In Time option in the Recover tab. These options can either be used separately or together, depending on the situation (if used together with point-in-time, both must use the same point-in-time). This method will allow you to restore the database to a state that you can make the determination whether or not you have achieved the correct point-in-time, without invoking the "ALTER DATABASE OPEN RESETLOGS" statement that would reset SCNs and time stamps on the database objects.

After determining the correct point-in-time through this method, the Point In Time option in the Recover tab can be safely applied to reset your Oracle database to the desired incarnation.

Sample scripts are provided below for your Oracle database administrator to use as reference for developing custom scripts that you can run from the RMAN command line, to perform special operations apart from the CommCell Console.


Sample Script for Resetting a Database After RESETLOGS

The following example resets a database after performing an incomplete media recovery:

run {
allocate channel dev1 type disk;
set until logseq 1234 thread 1;
restore database skip tablespace readonly;
recover database;
sql "ALTER DATABASE OPEN RESETLOGS";
release channel dev1;
}
reset database;


Sample Script for Resetting the Database to an Old Incarnation

The following command makes an old incarnation of database PROD1 current again:

# obtain primary key of old incarnation
list incarnation of database prod1; List of Database Incarnations


DB Key
------
Inc Key
-------
DB Name
-------
DB ID
-----
CUR
---
Reset SCN
---------
Reset Time
----------
1
2
PROD1
1224038686
NO
1
02-JUL-98
1
582
PROD1
1224038686
YES
59727
10-JUL-98


shutdown immediate;

# reset database to old incarnation

reset database to incarnation 2;

# recover it
run {
allocate channel dev1 type disk;
restore controlfile;
startup mount;
restore database;
recover database;
sql "ALTER DATABASE OPEN RESETLOGS";
release channel dev1;
}