ORA-19909: datafile 1 belongs to an orphan incarnation

I love to read Oracle related blogs, forum posts and mailing lists much more often than books. Why? Because there many Oracle DBA’s and developers share their experiences, problems, "best practices",... which are very valuable to me.

It's great that we have so big and active Oracle community.

Today I noticed mail from Oracle-L list where someone asked for help with recovery after overwriting production controlfiles. Check Oracle-L for more info.

It reminded me that I haven’t played with controlfile recoveries for a while.
Mistakes or various disasters could happen when you least expect it. When problem occurs it is essential that DBA is confident with recovery procedure.
Confidence comes only with practice. Create test environment and enjoy in destroying/recovering databases.
Of course - be creative with disasters :)


So I’ve imagined one scenario and decided to share it in form of blog post.


Create backup of current controlfile.

RMAN> backup current controlfile;

Starting backup at 17.07.2014 13:45:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 17.07.2014 13:45:30
channel ORA_DISK_1: finished piece 1 at 17.07.2014 13:45:33
piece handle=/u01/oradata/fra/ORAKL/backupset/2014_07_17/o1_mf_ncnnf_TAG20140717T142921_9whjf2sc_.bkp tag=TAG20140717T134529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 17.07.2014 13:45:33

After that I’ve performed incomplete recovery and opened database with resetlogs option.

...
...
...
SQL> alter database open resetlogs;
Database altered.

Create small status table and insert one row.

SQL> create table admin.test (datum date);
Table created.

SQL> insert into admin.test values(sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> select to_char(datum,'dd.mm.yyyy hh24:mi:ss') from admin.test;

TO_CHAR(DATUM,'DD.M
-------------------
17.07.2014 14:44:44

Where are my controlfiles:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/orakl/orakl/control01.ctl
/u01/oradata/orakl/orakl/control02.ctl
/u01/oradata/orakl/orakl/control03.ctl

Overwrite all controlfiles with random file to simulate problem.

$ cp o1_mf_s_853163282_9whfzmk1_.bkp /u01/oradata/orakl/orakl/control01.ctl
$ cp o1_mf_s_853163282_9whfzmk1_.bkp /u01/oradata/orakl/orakl/control02.ctl
$ cp o1_mf_s_853163282_9whfzmk1_.bkp /u01/oradata/orakl/orakl/control03.ctl

After that instance crashed with error in alertlog:

Thu Jul 17 14:45:30 2014
Errors in file /u01/app/oracle/diag/rdbms/orakl/orakl/trace/orakl_ckpt_8103.trc:
ORA-00201: control file version  incompatible with ORACLE version
ORA-00202: control file: '/u01/oradata/orakl/orakl/control01.ctl'
CKPT (ospid: 8103): terminating the instance due to error 201
Instance terminated by CKPT, pid = 8103

To perform quick recovery I’ve restored previously backed up controlfile and mounted database.

RMAN> run
2> {
3> restore controlfile from '/u01/oradata/fra/ORAKL/backupset/2014_07_17/o1_mf_ncnnf_TAG20140717T142921_9whjf2sc_.bkp';
4> }

Starting restore at 17.07.2014 14:46:34
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/oradata/orakl/orakl/control01.ctl
output file name=/u01/oradata/orakl/orakl/control02.ctl
output file name=/u01/oradata/orakl/orakl/control03.ctl
Finished restore at 17.07.2014 14:46:37

RMAN> alter database mount;
database mounted

Recover database using backup controlfile.

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/oradata/orakl/orakl/system01.dbf'

Recovery failed with ORA-19909 error.
If you remember I’ve performed incomplete recovery after creating backup of controlfile.
Controlfile backup belongs to another incarnation, and I don’t have fresh controlfile backup.


I will use this “old” controlfile and create SQL script to reproduce new control file.

SQL> alter database backup controlfile to trace as '/tmp/ctlfile.sql';

Database altered.

Delete unnecessary lines and create SQL script "/tmp/ctlfile.sql":

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORAKL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/orakl/orakl/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/oradata/orakl/orakl/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/oradata/orakl/orakl/redo03.log'  SIZE 50M
DATAFILE
  '/u01/oradata/orakl/orakl/system01.dbf',
  '/u01/oradata/orakl/orakl/sysaux01.dbf',
  '/u01/oradata/orakl/orakl/undotbs01.dbf',
  '/u01/oradata/orakl/orakl/users01.dbf'
CHARACTER SET UTF8
;

Shutdown instance and create controlfile.

SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL>
SQL> @/tmp/ctlfile.sql
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2162280 bytes
Variable Size             171966872 bytes
Database Buffers          444596224 bytes
Redo Buffers                7602176 bytes

Control file created.

Use information from redologs for recovery.

SQL> select member, status from v$logfile;

MEMBER                                                                 STATUS
---------------------------------------------------------------------- ---------
/u01/oradata/orakl/orakl/redo03.log                                    STALE
/u01/oradata/orakl/orakl/redo02.log                                    STALE
/u01/oradata/orakl/orakl/redo01.log                                    STALE

SQL> recover database using backup controlfile;
ORA-00279: change 7015200 generated at 07/17/2014 14:43:54 needed for thread 1
ORA-00289: suggestion :
/u01/oradata/fra/ORAKL/archivelog/2014_07_17/o1_mf_1_1_%u_.arc
ORA-00280: change 7015200 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/orakl/orakl/redo01.log
Log applied.
Media recovery complete.

Open database with resetlogs.

SQL> alter database open resetlogs;

Database altered.

Check status table.

SQL> select to_char(datum,'dd.mm.yyyy hh24:mi:ss') from admin.test;

TO_CHAR(DATUM,'DD.M
-------------------
17.07.2014 14:44:44

Check incarnations:

RMAN> list incarnation;

using target database controlfile instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2       2       ORAKL    3724279545       PARENT  641001     12.10.2013 15:30:46
1       1       ORAKL    3724279545       PARENT  7015197    17.07.2014 14:43:44
3       3       ORAKL    3724279545       CURRENT 7015386    17.07.2014 14:55:50

Cheers!