Incarnations are explained in [an answer](https://dba.stackexchange.com/a/213315/1396) to [another question](https://dba.stackexchange.com/q/8879/1396) on this site. The answer mentions 'orphaned' incarnations:
> …There are other factors that result in ORPHANED incarnations and OBSOLETE backups…
I see [from the Oracle docs](https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1075.htm#REFRN30049) that `V$DATABASE_INCARNATION` includes a `STATUS` column which can have values of `ORPHAN`, `CURRENT` or `PARENT`, which must be related.
What are 'orphaned' incarnations, and what steps would result in a row with `STATUS`=`ORPHAN` in `V$DATABASE_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][L1] to the question [Can anyone explain to me the concept “incarnation” in Oracle database in an easy-to-understand way?][L2]
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...
[L1]: https://dba.stackexchange.com/a/213315/15356
[L2]: https://dba.stackexchange.com/q/8879/15356
