Tuesday, March 21, 2023

ORA-19910: can not change recovery target incarnation in control file

If you are trying to resolve ORA-19909 on your physical standby database which is out of sync with primary because of ORA-19909, and you face ORA-19910, the probably cause is that redo apply process is still active. This was exactly the case with me when I tried to reset the incarnation number of my physical primary database. In the following you can see the exact command and error message returned.

RMAN> list incarnation;
 
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       MYDB007  4736485959       PARENT  1            13-APR-19
2       2       MYDB007  4736485959       PARENT  1724702      20-NOV-19
3       3       MYDB007  4736485959       CURRENT 32923260052  20-FEB-23
 
RMAN> reset database to incarnation 2;
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of reset database command on default channel at 03/21/2023 06:21:14
ORA-19910: can not change recovery target incarnation in control file

Stop the redo apply process and retry setting incarnation number, and this time it should work.
If you are using dataguard broker configuration, use following command to stop redo apply process.

DGMGRL> edit database MYDB007_set state='LOG-APPLY-OFF'

Or stop using SQLPLUS command as follows if you are not using dataguard broker.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 Now retry resetting incarnation number.

RMAN> reset database to incarnation 2;
 
database reset to incarnation 2
 
RMAN> list incarnation ;
 
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       MYDB007  4736485959       PARENT  1          13-APR-19
2       2       MYDB007  4736485959       CURRENT 1724702    20-NOV-19
3       3       MYDB007  4736485959       ORPHAN  32923260052 20-FEB-23

Start redo apply process again using either of the following command.

DGMGRL> edit database MYDB007_set state='APPLY-ON'

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;


No comments:

Post a Comment

Popular Posts - All Times