What is an orphan incarnation?
Following is a short graphic which I will be using to explain when orphans are created in the incarnations of a database. It is a variation of the graphic I used to explain incarnations in my answer to the question Can anyone explain to me the concept “incarnation” in Oracle database in an easy-to-understand way?
I hope you enjoy the journey.
restore db +-----+ +-----+ +-----+
recover db | 2>3 | --> | 3 | --> | 3 | --> ...
resetlogs +-----+ +-----+ +-----+ ^
^ Incarn 3 3 | 3
/ SCN # 500 600 | 700
/ |
/ |
restore db +-----+ +-----+ +-----+ |
recover db | 1>2 | -------> | 2 | --> | 2 | --> ... |
resetlogs +-----+ +-----+ +-----+ ^ |
^ Incarn. 2 \ 2 | 2 |
/ SCN # 300 \ 400 | 500 |
/ \ | |
/ + --------------------+ |
+-----+ +-----+ +-----+ | \ +-----+ | +-----+
--> | 1 | --> | 1 | --> | 1 | --> ... | +-> | 2>4 | --> | 4 |
+-----+ +-----+ +-----+ ^ | restore db +-----+ | +-----+
Incarn. 1 1 1 | 1 2 | recover db | 4
SCN # 100 200 300 | 400 400 | resetlogs | 400
| | |
Backup 11:00 ----- 12:00 ----- 13:00 ----- 14:00 ----- 15:00 ----- 16:00 ----- 17:00 ----- 18:00
| | |
Restore/ (1) (2) (3)
Recovery
Restoring the Database to Point in Time (1)
Somewhere slightly after 13:00 (1pm) somebody decides that the database has to be restored to 12:00 (12 o'clock midday). The DBA either sets off a bunch of RMAN commands to restore the database to that point in time or clicks his way through a fantastic GUI to initiate a restore/recovery from a 3rd-party vendor.
RMAN retrieves the FULL backup of the database and all Archive Log backups from disk/tape and restores them to the disk. In the recovery phase RMAN will check that all relevant information is available and roll forward all finished transactions to the Point in Time and roll back all unfinished transactions to the Point in Time, to ensure the database is in a consistent state.
Before the database can be opened to the general public, the database has to ensure that all future backups don't conflict with the previous backups. This is when a new incarnation should be created and it happens when you execute the following command to open the database:
ALTER DATABASE OPEN RESETLOGS;
You can run the following script against your instance to retrieve a hierarchical view of your (current) incarnations:
set pages 50 --- repeat header every 50 records
set lines 230 --- set lines(ize) length to 230
column path format a40 --- set column path to alpha-numeric 40
alter sessiosn set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
--- set date format of date columns to something more detailed
select
INCARNATION#,
PRIOR_INCARNATION#,
RESETLOGS_CHANGE#,
RESETLOGS_TIME,
STATUS,
SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path
FROM v$database_incarnation
WHERE LEVEL >=1 START WITH INCARNATION# = '1'
CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION#
ORDER BY LEVEL, Path, RESETLOGS_TIME;
The current incarnation of the database will be similar to this:
INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
1 0 1 2017-03-08 15:57:31 PARENT -> 1
2 1 200 2018-07-27 13:20:00 CURRENT -> 1 -> 2
Using the graphic we can see that we have moved from the path containing the incarnation 1 to the path with the incarnation 2, because we have opened the database with RESETLOGS and the database has created a new incarnation.
Restoring the Database to Point in Time (2)
Let's again assume the database keeps on running after the first restore/recovery action and slightly after 15:00 (3pm) somebody decides there needs to ba a new restore/recovery back to full hour at 15:00 (3pm) the same day.
RMAN will restore the files, recover the database and set off an ALTER DATABASE OPEN RESETLOGS to bring the database back online. The INCARNATION# will be now set to 3 and the first backup at 16:00 will contain the information:
INCARNATION# 3
SCN# 500
Time......... 16:00
If we query the incarnations in the database using the above script we will get something like this:
INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
1 0 1 2017-03-08 15:57:31 PARENT -> 1
2 1 200 2018-07-27 13:20:00 PARENT -> 1 -> 2
3 2 400 2018-07-27 15:20:00 CURRENT -> 1 -> 2 -> 3
Restoring the Database to Point in Time (3)
Let's again assume the database keeps on running after the second restore/recovery action and slightly after 17:00 (5pm) somebody decides there needs to be a new restore/recovery back to 14:00 (2pm) the same day.
RMAN will restore the files, recovery the database and set off an ALTER DATABASE OPEN RESETLOGS to bring the database back online. The INCARNATION# will be now set to 4 and the first backup at 18:00 will contain the information:
INCARNATION# 4
SCN# 400
Time......... 18:00
If we query the incarnations in the database using the above script we will get something like this:
INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
1 0 1 2017-03-08 15:57:31 PARENT -> 1
2 1 200 2018-07-16 13:20:00 PARENT -> 1 -> 2
3 2 400 2018-07-17 15:20:00 ORPHAN -> 1 -> 2 -> 3
4 2 300 2018-07-17 17:20:00 CURRENT -> 1 -> 2 -> 4
What has happened? We have an orphan!
Orphaned Incarnations...
If you look at the graphic, we are currently standing on the square at 18:00 (6pm) with the Incarnation 4 and the SCN 400. Now if you follow that line back to the beginning, you can see that we would go from incarnation 4 back up to incarnation 2 and then back down to incarnation 1, which is when the database was created.
This also corresponds to the (simplified) output of my scripts.
INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
4 2 300 2018-07-17 17:20:00 CURRENT -> 1 -> 2 -> 4
So what happened with incarnation 3? Is the Incarnation 3 bad or stale or what gives?
Answer
No, the incarnation 3 isn't bad, it's just orphaned.
On a larger scale with more time between backups and restores, you could still restore/recover the database to a point in time in the lineage of incarnation 3. You would set off the following command:
RESET DATABASE TO INCARNATION 3;
...and then restore/recover the database to that point in time like you would other restore/recover a database.
What the ORPHAN status does tell you, is that the incarnation 3 is no longer related to the current state of the database with the current incarnation 4. The orphaned incarnation 3 is no longer required to restore/recover the database along the current timeline.
...Result in Obsolete Backups
Now looking at the database backups in relation to the orphaned incarnation, well RMAN determines that the orphaned incarnation's backups are OBSOLETE. But that is a story for a different Q & A...
RC_DATABASE_INCARNATION
ORPHAN if this is a noncurrent incarnation that is not a direct ancestor of the current incarnation.
Steps to reproduce:
SQL> select incarnation#, status from v$database_incarnation;
INCARNATION# STATUS
------------ -------
1 PARENT
2 CURRENT
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3393014
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 394265912 bytes
Database Buffers 662700032 bytes
Redo Buffers 8146944 bytes
Database mounted.
SQL> flashback database to scn 3393014;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select incarnation#, status from v$database_incarnation;
INCARNATION# STATUS
------------ -------
1 PARENT
2 PARENT
3 CURRENT
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3393975
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 394265912 bytes
Database Buffers 662700032 bytes
Redo Buffers 8146944 bytes
Database mounted.
SQL> flashback database to scn 3393200;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select incarnation#, status from v$database_incarnation;
INCARNATION# STATUS
------------ -------
1 PARENT
2 PARENT
3 PARENT
4 CURRENT
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 394265912 bytes
Database Buffers 662700032 bytes
Redo Buffers 8146944 bytes
Database mounted.
SQL> flashback database to scn 3393014;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select incarnation#, status from v$database_incarnation;
INCARNATION# STATUS
------------ -------
1 PARENT
2 PARENT
3 ORPHAN
4 ORPHAN
5 CURRENT