DBA Interview Questions.

1. 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.


2. 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.


3. 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.


4. What is 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.


5. 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;