On a 19.5 DB, I've opened the Standby in Read Write mode (ACTIVATED not SNAPSHOT mode) for testing purpose for 24 hours, which make it lag too way behind the primary and needs shipping of 700 GB of Archivelogs which will take ages to complete, So I followed the new feature of recovering the standby DB from RMAN incremental backup using the following one command:
RMAN> RECOVER STANDBY DATABASE FROM SERVICE PRC;
Problem:
After the completion of the recovery, --which took a couple of hours, I got this error in the DB alert log:
2022-10-05T18:05:52.215176+00:00
Errors in file /u01/oracle/diag/rdbms/prs/prs/trace/prs_mz00_15686.trc:
ORA-01110: data file 93: '+DATA/PRS/DATAFILE/p_data.278.1068357141'
2022-10-05T18:05:52.321877+00:00
When trying to start the recovery, it crashed, and I got the following error in the alertlog:
SQL> recover managed standby database using current logfile nodelay disconnect;
Excerpt from the alertlog:
2022-10-05T17:57:23.619258+00:00
PR00 (PID:11782): Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 6629958934149) is orphaned on incarnation#=2
PR00 (PID:11782): MRP0: Detected orphaned datafiles!
2022-10-05T17:57:23.804939+00:00
Errors in file /u01/oracle/diag/rdbms/prs/prs/trace/prs_pr00_11782.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DATA/PRS/DATAFILE/system.378.1040043713'
PR00 (PID:11782): Managed Standby Recovery not using Real Time Apply
stopping change tracking
Analysis:
As the error mentioning the incarnation, I went to RMAN to check all incarnations of the DB:
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 PRD 1352250453 PARENT 1 17-APR-19
2 2 PRD 1352250453 PARENT 1920977 07-MAY-20
3 3 PRD 1352250453 ORPHAN 6472651405014 12-MAY-20
4 4 PRD 1352250453 ORPHAN 6473945178918 15-JUN-20
5 5 PRD 1352250453 ORPHAN 6475796134931 07-JUL-20
6 6 PRD 1352250453 ORPHAN 6476092131516 10-JUL-20
7 7 PRD 1352250453 ORPHAN 6476262305573 12-JUL-20
8 8 PRD 1352250453 ORPHAN 6476996897342 22-JUL-20
9 9 PRD 1352250453 ORPHAN 6476997075925 22-JUL-20
10 10 PRD 1352250453 ORPHAN 6476998329789 22-JUL-20
11 11 PRD 1352250453 ORPHAN 6477049189900 22-JUL-20
12 12 PRD 1352250453 ORPHAN 6477792328248 03-AUG-20
13 13 PRD 1352250453 ORPHAN 6477896848188 04-AUG-20
14 14 PRD 1352250453 ORPHAN 6477898103437 04-AUG-20
15 15 PRD 1352250453 ORPHAN 6477898886110 04-AUG-20
16 16 PRD 1352250453 ORPHAN 6477907643794 04-AUG-20
17 17 PRD 1352250453 ORPHAN 6477908164587 04-AUG-20
18 18 PRD 1352250453 ORPHAN 6477908493572 04-AUG-20
19 19 PRD 1352250453 ORPHAN 6477908951719 04-AUG-20
20 20 PRD 1352250453 ORPHAN 6478017068793 05-AUG-20
21 21 PRD 1352250453 ORPHAN 6478101320713 06-AUG-20
22 22 PRD 1352250453 ORPHAN 6478223633690 07-AUG-20
23 23 PRD 1352250453 ORPHAN 6478226677036 07-AUG-20
24 24 PRD 1352250453 ORPHAN 6478261288240 07-AUG-20
25 25 PRD 1352250453 ORPHAN 6478262694239 07-AUG-20
26 26 PRD 1352250453 ORPHAN 6478358471117 08-AUG-20
27 27 PRD 1352250453 ORPHAN 6478434623641 09-AUG-20
28 28 PRD 1352250453 ORPHAN 6478438625990 09-AUG-20
29 29 PRD 1352250453 ORPHAN 6478446183062 09-AUG-20
30 30 PRD 1352250453 ORPHAN 6480950862854 31-AUG-20
31 31 PRD 1352250453 ORPHAN 6482153817265 09-SEP-20
32 32 PRD 1352250453 ORPHAN 6482271564751 10-SEP-20
33 33 PRD 1352250453 ORPHAN 6482286544445 10-SEP-20
34 34 PRD 1352250453 ORPHAN 6482723618944 14-SEP-20
35 35 PRD 1352250453 ORPHAN 6482800530717 15-SEP-20
36 36 PRD 1352250453 ORPHAN 6482918772612 16-SEP-20
37 37 PRD 1352250453 ORPHAN 6483074592719 17-SEP-20
38 38 PRD 1352250453 ORPHAN 6483533227915 21-SEP-20
39 39 PRD 1352250453 ORPHAN 6485299063993 05-OCT-20
40 40 PRD 1352250453 ORPHAN 6486322720680 13-OCT-20
41 41 PRD 1352250453 ORPHAN 6489734338575 10-NOV-20
42 42 PRD 1352250453 ORPHAN 6491112740659 19-NOV-20
43 43 PRD 1352250453 ORPHAN 6491676585105 23-NOV-20
44 44 PRD 1352250453 ORPHAN 6493754498541 07-DEC-20
45 45 PRD 1352250453 ORPHAN 6505194885552 16-FEB-21
46 46 PRD 1352250453 ORPHAN 6506134480796 21-FEB-21
47 47 PRD 1352250453 ORPHAN 6506147964368 21-FEB-21
48 48 PRD 1352250453 ORPHAN 6506415997452 23-FEB-21
49 49 PRD 1352250453 ORPHAN 6516084726181 21-APR-21
50 50 PRD 1352250453 ORPHAN 6516942842956 26-APR-21
51 51 PRD 1352250453 ORPHAN 6520146079148 23-MAY-21
52 52 PRD 1352250453 ORPHAN 6522049248024 06-JUN-21
53 53 PRD 1352250453 ORPHAN 6529792338188 28-JUL-21
54 54 PRD 1352250453 ORPHAN 6533931540996 22-AUG-21
55 55 PRD 1352250453 ORPHAN 6541654583957 27-SEP-21
56 56 PRD 1352250453 ORPHAN 6547395262155 20-OCT-21
57 57 PRD 1352250453 ORPHAN 6549851396517 31-OCT-21
58 58 PRD 1352250453 ORPHAN 6549859042608 31-OCT-21
59 59 PRD 1352250453 ORPHAN 6554974463446 21-NOV-21
60 60 PRD 1352250453 ORPHAN 6555002692454 21-NOV-21
61 61 PRD 1352250453 ORPHAN 6560037620145 12-DEC-21
62 62 PRD 1352250453 ORPHAN 6572404510789 07-FEB-22
63 63 PRD 1352250453 ORPHAN 6573278923413 10-FEB-22
64 64 PRD 1352250453 ORPHAN 6573612206898 11-FEB-22
65 65 PRD 1352250453 ORPHAN 6574219150557 14-FEB-22
66 66 PRD 1352250453 ORPHAN 6574446599929 15-FEB-22
67 67 PRD 1352250453 ORPHAN 6574733753234 16-FEB-22
68 68 PRD 1352250453 ORPHAN 6575284306973 18-FEB-22
69 69 PRD 1352250453 ORPHAN 6575932360856 21-FEB-22
70 70 PRD 1352250453 ORPHAN 6576184619358 22-FEB-22
71 71 PRD 1352250453 ORPHAN 6576483271333 23-FEB-22
72 72 PRD 1352250453 ORPHAN 6576483271333 23-FEB-22
73 73 PRD 1352250453 ORPHAN 6576488218054 23-FEB-22
74 74 PRD 1352250453 ORPHAN 6576751793528 24-FEB-22
75 75 PRD 1352250453 ORPHAN 6576760598850 24-FEB-22
76 76 PRD 1352250453 ORPHAN 6577014030463 25-FEB-22
77 77 PRD 1352250453 ORPHAN 6577276447034 26-FEB-22
78 78 PRD 1352250453 ORPHAN 6577498266856 27-FEB-22
79 79 PRD 1352250453 ORPHAN 6577655247878 28-FEB-22
80 80 PRD 1352250453 ORPHAN 6577922958840 01-MAR-22
81 81 PRD 1352250453 ORPHAN 6578182486068 02-MAR-22
82 82 PRD 1352250453 ORPHAN 6578472039163 03-MAR-22
83 83 PRD 1352250453 ORPHAN 6578790664228 04-MAR-22
84 84 PRD 1352250453 ORPHAN 6578791447776 04-MAR-22
85 85 PRD 1352250453 ORPHAN 6579600980093 08-MAR-22
86 86 PRD 1352250453 ORPHAN 6580043914718 09-MAR-22
87 87 PRD 1352250453 ORPHAN 6581184664237 15-MAR-22
88 88 PRD 1352250453 ORPHAN 6585156574467 29-MAR-22
89 89 PRD 1352250453 ORPHAN 6585320699260 30-MAR-22
90 90 PRD 1352250453 ORPHAN 6594564672284 17-MAY-22
91 91 PRD 1352250453 ORPHAN 6601496376855 16-JUN-22
92 92 PRD 1352250453 CURRENT 6629485928030 04-OCT-22
There are tons of orphan incarnations found, but there are only 2 PARENT incarnations and 1 CURRENT, definitely the CURRENT incarnation is not working for my DB, so I've to set the DB incarnation to one of the PARENT ones.
Solution:
Reset the incarnation of the database to the most recent "Reset SCN" PARENT incarnation, from above output incarnation# 2 is the one with most recent "Reset SCN"
RMAN> reset database to incarnation 2;
database reset to incarnation 2
References:
Roll Forward Physical Standby Using RMAN Incremental Backup in Single Command (Doc ID 2431311.1)
Thank you for sharing this solution. This happened for the first time when I restored files for my standby after opening up a previous copy for a DR test.
ReplyDeleteKind Regards,
KC