Cause:
I have two node RAC with standby database configured. After configuration of dg broker in physical standby database, i get datafiles consistency error due to unknown reasons
DGMGRL> show database dbx01
Database - dbx01
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 18.00 KByte/s
Real Time Query: OFF
Instance(s):
dbx01
Database Error(s):
ORA-16839: one or more user data files are missing
Database Status:
ERROR
I checked for failures from RMAN for datafile issues and found one
RMAN> advise failure all;
using target database control file instead of recovery catalog
Database Role: PHYSICAL STANDBY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1258 HIGH OPEN 21-SEP-21 Datafiles are mutually inconsistent
Mandatory Manual Actions
========================
1. Ensure the primary database is up and recover database using ALTER DATABASE RECOVER MANAGED STANDBY DATABASE UNTIL CONSISTENT command. Check alert log for potential failures that may cause recovery not to run. Potential problems may include missing archived logs or datafiles being in an orphaned incarnation.
2. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
no automatic repair options available
As per the advisory , i manually followed the steps mentioned under Mandatory Manual Actions
I cancelled MRP and ran the MRP with ‘until consistent’ clause to recover the logs
kish@STDBY>alter database recover managed standby database cancel;
Database altered.
kish@STDBY>alter database recover managed standby database until consistent;
Database altered.
Enable MRP again
kish@STDBY>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
Now failure is not reflecting after the recommendation action
RMAN> advise failure all;
using target database control file instead of recovery catalog
Database Role: PHYSICAL STANDBY
no failures found that match specification
Workaround:
Still after fixing the failure, i face the same error. As per Doc ID 21495155.8 , the error is a reported bug Bug 21495155 in RAC
I straced dgmgrl utility and couldnot find any cause
read(7, "\23\0\210\23\0\0z\0\211\23\0\0\217\0\212\23\0\0\224\0\213\23\0\0\244\0\214\23\0\0\275\0"..., 512) = 512
close(7) = 0
write(1, "Database Error(s):\n", 19Database Error(s):
) = 19
open("/u01/oracle/product/12.1.0/db/rdbms/mesg/oraus.msb", O_RDONLY) = 7
fcntl(7, F_SETFD, FD_CLOEXEC) = 0
lseek(7, 0, SEEK_SET) = 0
read(7, "\25\23\"\1\23\3\t\t\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 256) = 256
lseek(7, 512, SEEK_SET) = 512
read(7, "\3\31\0243-A\311a\232u\250\204\254\217\247\256\16\331\377\377\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(7, 1024, SEEK_SET) = 1024
read(7, "\30\0$\0.\09\0B\0N\0Y\0d\0l\0v\0\203\0\231\0\244\0\316\0\327\0\336\0"..., 512) = 512
lseek(7, 1536, SEEK_SET) = 1536
read(7, "\36\31&\31.\31l\31v\31\203\31\217\31\231\31\241\31\253\31\264\31\306\31\320\31\335\0315\32N\32"..., 512) = 512
lseek(7, 2048, SEEK_SET) = 2048
read(7, ";3G3X3d3\2053\2213\2323\2443\2543\2673\3173\3303\3443\3603\0324#4"..., 512) = 512
lseek(7, 2560, SEEK_SET) = 2560
read(7, "4A<AEAMAUA]AdAkAuA\201A\212A\222A\233A\243A\254A\264A"..., 512) = 512
lseek(7, 3072, SEEK_SET) = 3072
read(7, "\21b\31b\"b*b3b<bEbWb`bfbnbvb}b\204b\213b\224b"..., 512) = 512
lseek(7, 3584, SEEK_SET) = 3584
read(7, "\245u\266u\315u\341u\353u\372unv\205v\216v\226v\237v\250v\261v\271v\336v\347v"..., 512) = 512
lseek(7, 4096, SEEK_SET) = 4096
read(7, "\260\204\267\204\277\204\312\204\321\204\332\204\343\204\352\204\362\204\372\204\6\205\20\205\36\205(\2054\205@\205"..., 512) = 512
lseek(7, 4608, SEEK_SET) = 4608
read(7, "\300\217\312\217\321\217\332\217\344\217\352\217\362\217\n\220\24\220&\2201\220<\220V\220\\\220d\220l\220"..., 512) = 512
lseek(7, 5120, SEEK_SET) = 5120
read(7, "\273\256\305\256\315\256\324\256\353\256\0\257\f\257\30\2579\257A\257F\257N\257Y\257a\257j\257\230\257"..., 512) = 512
lseek(7, 5632, SEEK_SET) = 5632
read(7, "0\3319\331B\331I\331O\331U\331\\\331c\331\237\331_\332i\332t\332~\332\207\332\270\334\302\334"..., 188) = 188
lseek(7, 408576, SEEK_SET) = 408576
read(7, "\n\0\303A\0\0D\0\304A\0\0\210\0\305A\0\0\261\0\306A\0\0\332\0\307A\0\0\3\1"..., 512) = 512
close(7) = 0
write(1, " ORA-16839: one or more user "..., 55 ORA-16839: one or more user data files are missing
) = 55
write(1, "\n", 1
) = 1
open("/u01/oracle/product/12.1.0/db/rdbms/mesg/dgmus.msb", O_RDONLY) = 7
fcntl(7, F_SETFD, FD_CLOEXEC) = 0
lseek(7, 0, SEEK_SET) = 0
read(7, "\25\23\"\1\23\3\t\t\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 256) = 256
lseek(7, 512, SEEK_SET) = 512
read(7, "\211B\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(7, 1024, SEEK_SET) = 1024
read(7, "\232\23\265\23\316\23\337\23\360\23\0\24\r\24 \24/\24\tB\24B\"B+B8BIBUB"..., 42) = 42
lseek(7, 1536, SEEK_SET) = 1536
read(7, "\23\0\210\23\0\0z\0\211\23\0\0\217\0\212\23\0\0\224\0\213\23\0\0\244\0\214\23\0\0\275\0"..., 512) = 512
close(7) = 0
write(1, "Database Status:\n", 17Database Status:
) = 17
open("/u01/oracle/product/12.1.0/db/rdbms/mesg/dgmus.msb", O_RDONLY) = 7
fcntl(7, F_SETFD, FD_CLOEXEC) = 0
lseek(7, 0, SEEK_SET) = 0
read(7, "\25\23\"\1\23\3\t\t\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 256) = 256
lseek(7, 512, SEEK_SET) = 512
read(7, "\211B\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(7, 1024, SEEK_SET) = 1024
read(7, "\232\23\265\23\316\23\337\23\360\23\0\24\r\24 \24/\24\tB\24B\"B+B8BIBUB"..., 42) = 42
lseek(7, 1536, SEEK_SET) = 1536
read(7, "\23\0\210\23\0\0z\0\211\23\0\0\217\0\212\23\0\0\224\0\213\23\0\0\244\0\214\23\0\0\275\0"..., 512) = 512
close(7) = 0
write(1, "ERROR\n", 6ERROR
) = 6
write(1, "\n", 1
) = 1
write(1, "DGMGRL> ", 8DGMGRL> ) = 8
Move the HM_FINDING.ams file to a backup file
[oracle@dc01x02 ~]$ mv /u01/oracle_base/diag/rdbms/dbx01/DBX01_1/metadata/HM_FINDING.ams /u01/oracle_base/diag/rdbms/dbx01/DBX01_1/metadata/HM_FINDING.ams.bak
[oracle@dc01x02 ~]$ mv /u01/oracle_base/diag/rdbms/dbx00/dbx00_1/metadata/HM_FINDING.ams /u01/oracle_base/diag/rdbms/dbx00/dbx00_1/metadata/HM_FINDING.ams.bak
New file will be created automatically after moving the HM_FINDING file. After checking the logfiles i am able to see some errors during recovery of a failure
ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [448] [HM_FINDING]
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '+RECOC1/DBX00/ONLINELOG/group_3.259.1083714915'
ORA-17503: ksfdopn:2 Failed to open file +RECOC1/DBX00/ONLINELOG/group_3.259.1083714915
ORA-15001: diskgroup "RECOC1" does not exist or is not mounted
ORA-00450: background process 'ASMB' did not start
ORA-00444: background process "ASMB" failed while starting
ORA-01089: immediate shutdown or close in progress - no operations are permitted
ORA-00312: online log 3 thread 1: '/apps01/base/oradata/dbx01//onlinelog/group_3.264.1083714915'
ORA-27037: unable to obtain file status
After moving the file, there is a message display as below in alert logfile
Thu Oct 07 12:42:41 2021
Create Relation HM_FINDING
Issue is resolved
DGMGRL> show database dbx01
Database - dbx01
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: OFF
Instance(s):
dbx01
Database Status:
SUCCESS
