Recently my colleague complaint about incarnation change on the DR server. Incarnation change was recorded in alert log and MRP process was interrupted. Now if you try to start the MRP on changed incarnation DR database then it will fail with “datafile 1 belongs to an orphan incarnation“
We have database sync status report. And it showed us our db was in lag.
Table of Contents
How to resolve ORA-19906: recovery target incarnation changed during recovery
First of all, check the alert log on standby database and identify what has happened. Record what has happened –
2022-09-15T13:36:32.889424-04:00 Setting recovery target incarnation to 29 Setting recovery target incarnation to 29 2022-09-15T13:36:36.912070-04:00 PR00 (PID:16404): MRP0: Incarnation has changed! Retry recovery... 2022-09-15T13:36:36.912990-04:00 Errors in file /oradump/diag/rdbms/dbworld/DBWORLD1/trace/DBWORLD1_pr00_16404.trc: ORA-19906: recovery target incarnation changed during recovery Recovery interrupted! 2022-09-15T13:36:37.612871-04:00
Incarnation of Production database
Next thing we have to check if what is the current incarnation of database on Production database.
RMAN> list incarnation of database; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- ------- -------------- ---------- 1 1 DBWORLD 3843890844 CURRENT 16958728283189 26-JUL-22 2 2 DBWORLD 3843890844 ORPHAN 17031443062039 12-AUG-22 3 3 DBWORLD 3843890844 ORPHAN 17052542615550 18-AUG-22 4 4 DBWORLD 3843890844 ORPHAN 17074738145466 25-AUG-22 RMAN> exit
Close the production session once you recorded the details. And now we have to reset the database to production database’s incarnation key.
Step by Step how to change Incarnation of standby database
Step1 Check the status of database – database was running in Read only mode
SQL> select name,open_mode,database_role from gv$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- DBWORLD READ ONLY PHYSICAL STANDBY DBWORLD READ ONLY PHYSICAL STANDBY SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.14.0.0.0
Step 2 Shutdown the database and start only one node in mount mode
ORACLEDBWORLD:/u01/oracle$ srvctl stop database -d DBWORLD ORACLEDBWORLD:/u01/oracle$ srvctl start instance -d DBWORLD -i DBWORLD1 -o mount
Step 3 Check incarnation of standby database as follows –
ORACLEDBWORLD:/u01/oracle$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 15 20:46:06 2022 Version 19.14.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: DBWORLD (DBID=3843890844, not open) RMAN> list incarnation of database; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- ------ ------------- ---------- 2 2 DBWORLD 3843890844 PARENT 1 19-DEC-06 1 1 DBWORLD 3843890844 PARENT 16958728283189 26-JUL-22 3 3 DBWORLD 3843890844 ORPHAN 16961089235094 27-JUL-22 4 4 DBWORLD 3843890844 ORPHAN 16964888022405 27-JUL-22 5 5 DBWORLD 3843890844 ORPHAN 16969239321026 29-JUL-22 6 6 DBWORLD 3843890844 ORPHAN 16974468000226 30-JUL-22 7 7 DBWORLD 3843890844 ORPHAN 16977774761812 30-JUL-22 8 8 DBWORLD 3843890844 ORPHAN 16986054066700 02-AUG-22 9 9 DBWORLD 3843890844 ORPHAN 16988916109554 03-AUG-22 10 10 DBWORLD 3843890844 ORPHAN 16998045744263 05-AUG-22 11 11 DBWORLD 3843890844 ORPHAN 17005354332461 07-AUG-22 12 12 DBWORLD 3843890844 ORPHAN 17014552858375 09-AUG-22 13 13 DBWORLD 3843890844 ORPHAN 17025206676674 11-AUG-22 14 14 DBWORLD 3843890844 ORPHAN 17029461482521 12-AUG-22 15 15 DBWORLD 3843890844 ORPHAN 17033015728240 13-AUG-22 16 16 DBWORLD 3843890844 ORPHAN 17036123705830 14-AUG-22 17 17 DBWORLD 3843890844 ORPHAN 17041181256061 16-AUG-22 18 18 DBWORLD 3843890844 ORPHAN 17045401641227 17-AUG-22 19 19 DBWORLD 3843890844 ORPHAN 17050071642027 18-AUG-22 20 20 DBWORLD 3843890844 ORPHAN 17054196940042 19-AUG-22 21 21 DBWORLD 3843890844 ORPHAN 17057475681420 20-AUG-22 22 22 DBWORLD 3843890844 ORPHAN 17060046721305 20-AUG-22 23 23 DBWORLD 3843890844 ORPHAN 17066993535768 23-AUG-22 24 24 DBWORLD 3843890844 ORPHAN 17070004330264 24-AUG-22 25 25 DBWORLD 3843890844 ORPHAN 17077795107447 25-AUG-22 26 26 DBWORLD 3843890844 ORPHAN 17095714376879 31-AUG-22 27 27 DBWORLD 3843890844 ORPHAN 17100573238852 01-SEP-22 28 28 DBWORLD 3843890844 CURRENT 17142866235777 15-SEP-22 RMAN>
Or you can also use following view to identify –
v$database_incarnation
Step 4 Now change the incarnation of Standby database to match it with production – Please note here 1 is Inc Key.
RMAN> RESET DATABASE TO INCARNATION 1; database reset to incarnation 1 RMAN> exit Recovery Manager complete.
Step 5 Check the incarnation is changed for the standby database –
ORACLEDBWORLD:/u01/oracle$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 15 20:46:46 2022 Version 19.14.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: DBWORLD (DBID=3843890844, not open) RMAN> list incarnation of database; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- --------------- ------ -------------- ---------- 2 2 DBWORLD 3843890844 PARENT 1 19-DEC-06 1 1 DBWORLD 3843890844 CURRENT 16958728283189 26-JUL-22 3 3 DBWORLD 3843890844 ORPHAN 16961089235094 27-JUL-22 4 4 DBWORLD 3843890844 ORPHAN 16964888022405 27-JUL-22 5 5 DBWORLD 3843890844 ORPHAN 16969239321026 29-JUL-22 6 6 DBWORLD 3843890844 ORPHAN 16974468000226 30-JUL-22 7 7 DBWORLD 3843890844 ORPHAN 16977774761812 30-JUL-22 8 8 DBWORLD 3843890844 ORPHAN 16986054066700 02-AUG-22 9 9 DBWORLD 3843890844 ORPHAN 16988916109554 03-AUG-22 10 10 DBWORLD 3843890844 ORPHAN 16998045744263 05-AUG-22 11 11 DBWORLD 3843890844 ORPHAN 17005354332461 07-AUG-22 12 12 DBWORLD 3843890844 ORPHAN 17014552858375 09-AUG-22 13 13 DBWORLD 3843890844 ORPHAN 17025206676674 11-AUG-22 14 14 DBWORLD 3843890844 ORPHAN 17029461482521 12-AUG-22 15 15 DBWORLD 3843890844 ORPHAN 17033015728240 13-AUG-22 16 16 DBWORLD 3843890844 ORPHAN 17036123705830 14-AUG-22 17 17 DBWORLD 3843890844 ORPHAN 17041181256061 16-AUG-22 18 18 DBWORLD 3843890844 ORPHAN 17045401641227 17-AUG-22 19 19 DBWORLD 3843890844 ORPHAN 17050071642027 18-AUG-22 20 20 DBWORLD 3843890844 ORPHAN 17054196940042 19-AUG-22 21 21 DBWORLD 3843890844 ORPHAN 17057475681420 20-AUG-22 22 22 DBWORLD 3843890844 ORPHAN 17060046721305 20-AUG-22 23 23 DBWORLD 3843890844 ORPHAN 17066993535768 23-AUG-22 24 24 DBWORLD 3843890844 ORPHAN 17070004330264 24-AUG-22 25 25 DBWORLD 3843890844 ORPHAN 17077795107447 25-AUG-22 26 26 DBWORLD 3843890844 ORPHAN 17095714376879 31-AUG-22 27 27 DBWORLD 3843890844 ORPHAN 17100573238852 01-SEP-22 28 28 DBWORLD 3843890844 ORPHAN 17142866235777 15-SEP-22 RMAN>
Final Step to Start the database in read only mode ( if it was in read only for you) And start the MRP of database
ORACLEDBWORLD:/u01/oracle$ srvctl stop database -d DBWORLD ORACLEDBWORLD:/u01/oracle$ srvctl start database -d DBWORLD -o "read only"
SQL> select name,open_mode,database_role from gv$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- DBWORLD READ ONLY PHYSICAL STANDBY DBWORLD READ ONLY PHYSICAL STANDBY SQL> alter database recover managed standby database disconnect from session;
Now monitor the alert log until it start applying the archive log on the database.