Database incarnation represents a distinct version of a database that originates from a specific RESETLOGS operation. Each time a database is opened with RESETLOGS, Oracle creates a new incarnation, which fundamentally changes how recovery operations work across different database versions.
Official Documentation Overview
According to Oracle's Release 19c Backup and Recovery User's Guide (Section 14.3.2.2), incarnation relationships define the hierarchical connection between different database versions created through resetlogs operations.
Incarnation Hierarchy
First Incarnation (Incarnation 1):
The initial black horizontal line begins at SCN1 and progresses through SCN1000 to SCN2000. This represents the database's original incarnation, where Incarnation 1 serves as the current incarnation during this period.
Second Incarnation (Incarnation 2):
When performing a point-in-time recovery within Incarnation 1 and specifying SCN1000 as the recovery target, then opening the database with RESETLOGS, the second incarnation emerges. This incarnation appears as a 45-degree angled black line starting from SCN1000 and continuing to SCN3000. In this scenario, Incarnation 1 becomes the parent incarnation, while Incarnation 2 becomes the current incarnation.
Third Incarnation (Incarnation 3):
Within Incarnation 2, if another point-in-time recovery is executed trageting SCN2000, and the database opens with RESETLOGS, Incarnation 3 appears as the upper horizontal black line starting from SCN2000. Here, Incarnation 2 serves as the parent incarnation, Incarnation 1 becomes the ancestor incarnation, and Incarnation 3 becomes the current incarnation.
Practical Scenario
Consider a practical scenario involving table operations:
The gray block contains eight operations:
- Insert 1 into table A
- Insert 2 into table A
- Insert 3 into table A
- Insert 666 into table B
- Drop table B
- Insert 4 into table A
- Insert 5 into table A
- Insert 6 into table A
After completing the sixth insertion into table A (A-6 state), the table B was accidentally dropped. Since flashback technology is not applicable here, a point-in-time incomplete recovery becomes necessary. After using RESETLOGS to open the database, the second incarnation appears.
All subsequent operations now occur within Incarnation 2. Later, if restoring back to the A-6 state is desired, the question arises: Will this restoration succeed?
The answer is yes, but with a critical prerequisite.
The A-4, A-5, and A-6 states belong to the original first incarnation. However, the database currently operates on Incarnation 2. Without explicitly specifying the incarnation in RMAN, restoration would apply operations from Incarnation 2 only. One must explicitly indicate the desired recovery direction—either the horizontal path (Incarnation 1) or the 45-degree angled path (Incarnation 2).
If recovery targets the horizontal direction to reach A-6 and the database opens with RESETLOGS, the third incarnation emerges while the second incarnation becomes an ORPHAN incarnation.
Laboratory Demonstration
Initial Setup
Query the current database incarnation:
SQL> SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, STATUS
FROM v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS
------------ ----------------- -------------------------- -------
1 1 2011-09-17 09:46:04 PARENT
2 995548 2019-02-23 16:01:17 CURRENT
Switch archive logs and examine archive file naming:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> SELECT NAME FROM v$archived_log;
NAME
--------------------------------------------
/u01/app/oracle/archive1/1_54_1001001677.dbf
/u01/app/oracle/archive1/1_55_1001001677.dbf
/u01/app/oracle/archive1/1_56_1001001677.dbf
/u01/app/oracle/archive1/1_57_1001001677.dbf
The archive log name contains 1001001677, defined by the log_archive_format parameter. The default format is %t_%s_%r.dbf:
- %t: Thread number (1 in single instance)
- %s: Log sequence number (increments with each log switch)
- %r: Resetlogs ID—this controls the incarnation. Each RESETLOGS changes this ID, and the sequence number restarts from 1. This mechanism ensures unique archive log names across multiple incarnations.
Simulating the Scenario
Create test tables and execute operations:
SQL> CREATE TABLE a (id NUMBER);
Table created.
SQL> CREATE TABLE b (id NUMBER);
Table created.
SQL> INSERT INTO a VALUES (1);
1 row created.
SQL> INSERT INTO a VALUES (2);
1 row created.
SQL> INSERT INTO a VALUES (3);
1 row created.
SQL> INSERT INTO b VALUES (666);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT SYSDATE FROM dual;
SYSDATE
-------------------
2019-05-28 19:57:50
SQL> SELECT CURRENT_SCN FROM v$database;
CURRENT_SCN
-----------
1520977
SQL> DROP TABLE b PURGE;
Table dropped.
SQL> INSERT INTO a VALUES (4);
1 row created.
SQL> INSERT INTO a VALUES (5);
1 row created.
SQL> INSERT INTO a VALUES (6);
1 row created.
SQL> COMMIT;
Commit complete.
Recovering Table B and Opening the Database
Restore older datafiles:
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp /u01/app/oracle/backup/*.dbf /u01/app/oracle/oradata/PROD1/
Perform incomplete recovery:
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2232920 bytes
Variable Size 591400360 bytes
Database Buffers 234881024 bytes
Redo Buffers 2416640 bytes
Database mounted.
SQL> SELECT FILE#, CHANGE# FROM v$recover_file;
FILE# CHANGE#
---------- ----------
1 1519163
2 1519163
3 1519163
4 1519163
5 1519163
6 1519163
7 1519163
8 1519163
9 1519163
10 1519163
10 rows selected.
SQL> RECOVER DATABASE UNTIL CHANGE 1520977;
ORA-00279: change 1519163 generated at 05/28/2019 19:29:20 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive1/1_55_1001001677.dbf
ORA-00280: change 1519163 for thread 1 is in sequence #55
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
Examining the New Incarnation
After point-in-time recovery, query the incarnation:
SQL> SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, STATUS
FROM v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS
------------ ----------------- -------------------------- -------
1 1 2011-09-17 09:46:04 PARENT
2 995548 2019-02-23 16:01:17 PARENT
3 1520978 2019-05-28 20:35:03 CURRENT
The current incarnation is now 3. Switch logs and observe the archive naming:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> SELECT NAME FROM v$archived_log;
NAME
--------------------------------------------
/u01/app/oracle/archive1/1_54_1001001677.dbf
/u01/app/oracle/archive1/1_55_1001001677.dbf
/u01/app/oracle/archive1/1_56_1001001677.dbf
/u01/app/oracle/archive1/1_57_1001001677.dbf
/u01/app/oracle/archive1/1_58_1001001677.dbf
/u01/app/oracle/archive1/1_1_1009485303.dbf
/u01/app/oracle/archive1/1_2_1009485303.dbf
/u01/app/oracle/archive1/1_3_1009485303.dbf
8 rows selected.
After RESETLOGS, the incarnation in archive log names changed from 1001001677 to 1009485303, with the sequence number restarting from 1.
Cross-Incarnation Recovery Capability:
Oracle 11g supports cross-incarnation recovery. Archive logs from incarnation 1001001677 (sequence 54) can be applied through incarnation 1009485303 (sequance 4).
Example output showing log application across incarnations:
ORA-00279: change 1519163 generated at 05/28/2019 19:29:20 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive1/1_55_1001001677.dbf
...
ORA-00279: change 1520978 generated at 05/28/2019 20:35:03 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive1/1_1_1009485303.dbf
Restoring to A-6 State
Query the current incarnation:
SQL> SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, STATUS
FROM v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS
------------ ----------------- -------------------------- -------
1 1 2011-09-17 09:46:04 PARENT
2 995548 2019-02-23 16:01:17 PARENT
3 1520978 2019-05-28 20:35:03 CURRENT
The database currently uses Incarnation 3, meaning recovery follows the numbered path. To restore to A-6, the recovery path must change by resetting to Incarnation 2.
Shutdown and restore datafiles:
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp /u01/app/oracle/backup/*.dbf /u01/app/oracle/oradata/PROD1/
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2232920 bytes
Variable Size 591400360 bytes
Database Buffers 234881024 bytes
Redo Buffers 2416640 bytes
Database mounted.
Connect to RMAN and reset the incarnation:
[oracle@henry ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue May 28 21:35:33 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2222344843, not open)
RMAN> RESET DATABASE TO INCARNATION 2;
using target database control file instead of recovery catalog
database reset to incarnation 2
Verify the incarnation:
SQL> SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, STATUS
FROM v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS
------------ ----------------- -------------------------- -------
1 1 2011-09-17 09:46:04 PARENT
2 995548 2019-02-23 16:01:17 CURRENT
3 1520978 2019-05-28 20:35:03 ORPHAN
The database now uses Incarnation 2, while the original Incarnation 3 becomes an ORPHAN. Recovery will follow the horizontal numbered path to restore A-6.
Perform recovery:
SQL> RECOVER DATABASE;
ORA-00279: change 1519163 generated at 05/28/2019 19:29:20 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive1/1_55_1001001677.dbf
...
ORA-00279: change 1519941 generated at 05/28/2019 19:40:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive1/1_58_1001001677.dbf
Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
Verify recovered data:
SQL> SELECT * FROM a;
ID
----------
1
2
3
4
5
6
6 rows selected.
Final Incarnation Status
Query the incarnation one more time:
SQL> SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, STATUS
FROM v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS
------------ ----------------- -------------------------- -------
1 1 2011-09-17 09:46:04 PARENT
2 995548 2019-02-23 16:01:17 PARENT
3 1520978 2019-05-28 20:35:03 ORPHAN
4 1521714 2019-05-28 21:50:58 CURRENT
The fourth incarnation is now current, with the third incarnation remaining as an ORPHAN.