ORA-16839: one or more user data files are missing

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s