Oracle Database Incarnation : Open Resetlogs , SCN

Q. What happens when you run ALTER DATABASE OPEN RESETLOGS ?
The current online redo logs are archived, the log sequence number is reset to 1, new database incarnation is created, and the online redo logs are given a new time stamp and SCN.
.

Q. In what scenarios open resetlogs required ?

An ALTER DATABASE OPEN RESETLOGS statement is required after incomplete recovery (Point in Time Recovery) or recovery with a backup control file.
.

Q. What is SCN (System Change Number) ?

The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.

Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file.
.

Q. What is Database Incarnation ?

database incarnation

Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”.

Database incarnation falls into following category Current, Parent, Ancestor and Sibling

i) Current Incarnation : The database incarnation in which the database is currently generating redo.

ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.

iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.

iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.
.

Q. How to view incarnation history of Database ?

Using SQL> select * from v$database_incarnation;
Using RMAN>LIST INCARNATION;

However, you can use the RESET DATABASE TO INCARNATION command to specify that SCNs are to be interpreted in the frame of reference of another incarnation.

•For example my current database INCARNATION is 3 and now I have used
FLASHBACK DATABASE TO SCN 3000;then SCN 3000 will be search in current incarnation which is 3. However if I want to get back to SCN 3000 of INCARNATION 2 then I have to use,

RMAN> RESET DATABASE TO INCARNATION 2;
RMAN> RECOVER DATABASE TO SCN 3000;
.

References/Related

About the Author Masroof Ahmad

Leave a Comment:

7 comments
Partha Pramanik says November 18, 2009

Atul
I need some inputs from you on the following:
1. Identifying the areas related to Database Performance issues – e.g., memory, CPU, SQLs, locks, Disk space,Tablespace etc, how to identify the problem area.
2. First level of needful once the cause is identified – What To Do
3. Different common scenarios we encounter on daily basis and their identification and solution.
Please help.

Reply
gautham says May 15, 2010

Hi Atul,

We had a similar scenario where we had to do PITR.
DB Ver. 10.2.0.4
database was opened in reset logs.

My question is…
Due to new incarnation..
The v$log_history shows up the last SCN that was applied..

I found few info where they have mentioned that the v$log_history records wont get cleared in 10g after resetlogs option is used.

do we have a way so that the v$log_history shows up SCN from the current INCARNATION..

Thanks,

Reply
Rupesh says July 18, 2012

Hello,

Please brief me about HIGH SCN and LOW SCN concept.

Thanks,

Reply
gln sarma says March 15, 2013

Hi

Thanks for your explanation

From master we are copying the files to slave and doing the recovery using the hotbackup method.

we are restoring db on slave using the backup control file and we do alter database open reset logs

this we do on daily basis.

Everything works fine. slave we are using for reporting purpose.

However incarnation taking more time some times 30 min.

as slave get refreshed with above method.

If we do not want incarnation happen on the slave what should be done.

dml operations are happening on the slave as we generate various reports.

pl. let us know.

Reply
Oracle DBA Interview Questions and Answers | shanojkumar says November 1, 2013

[…] with a backup control file.3. recovery with a control file recreated with the reset logs option.http://onlineappsdba.com/index.php/2009/09/11/oracle-database-incarnation-open-resetlogs-scn/http://web.njit.edu/info/limpid/DOC/backup.102/b14191/osrecov009.htmWhenever you perform incomplete […]

Reply
murali says March 25, 2016

Very good explaination

Reply
Add Your Reply