Data Guard ORA-16843 and ORA-16839 Solutions

It was difficult to find a solution for this issue so I thought I’d write about it.

Error: ORA-16843: errors discovered in diagnostic repository
and
ORA-16839: one or more user data files are missing

We had a standby database that showed the ORA-16843 error when running show configuration in Data Guard Manager. Then, while running show database verbose I got the ORA-16839 error which states “one or more user data files are missing”. I searched for a long time to find out which data file was missing and constantly thought to myself that I must really be doing something wrong because I could not find any missing data files.

NOTE: All notes below are on Oracle Database version 12.1.0.1.0. I have not tested this on other versions.

Symptom

DGMGRL> show configuration

Configuration - orcl_configuration

  Protection Mode: MaxPerformance
  Databases:
  orclb - Primary database
    orcla - Physical standby database
      Error: ORA-16843: errors discovered in diagnostic repository

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

Also, when issuing the show database verbose command I got an ORA-16839 error

DGMGRL> show database verbose orcla

Database - orcla

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     0 seconds (computed 0 seconds ago)
  Apply Lag:         0 seconds (computed 0 seconds ago)
  Apply Rate:        173.00 KByte/s
  Real Time Query:   OFF
  Instance(s):
    ORCL

  Database Error(s):
    ORA-16839: one or more user data files are missing

The ORA-16843 error does not include the word Automatic, but it is telling us there is an error in the Automatic Diagnostic Repository (ADR). Much research led me to the following solution.

Solution

Finding the least invasive solution took a little while, but it was certainly educational in terms of learning about the ADR in more detail. As it turns out the least invasive solution was to delete the HM_FINDING.ams file in the metadata directory.

Find the ADR Home directory

set linesize 80
column value format a80

select value from v$diag_info where name = 'ADR Home';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcla/ORCL

The following can also be used to provide the linux command to rename the file

set linesize 160
column mv_cmd format a160

select 'mv ' || value || '/metadata/HM_FINDING.ams '
       || value || '/metadata/HM_FINDING.ams.bad ' mv_cmd
from   v$diag_info
where  name = 'ADR Home';

After renaming the HM_FINDING.ams file a new HM_FINDING.ams file will be created while running the show configuration command and the ORA-16843 error should be gone.

DGMGRL> show configuration

Configuration - orcl_configuration

  Protection Mode: MaxPerformance
  Databases:
  orclb - Primary database
    orcla - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

2 Comment(s)

  1. Thank you for posting! Nothing noted in MyOracleSupport under this other than ‘you hit a bug’.
    Your post saved the day.
    Much appreciated. Keep posting.
    Sandi

    Sandi Reddick | Jul 11, 2016 | Reply

  2. Thank you! Been banging my head for an hour trying to figure this out. My OEM-based standby build failed during the Broker configuration phase, after having run for 10 hours, didn’t want to throw it all away and start over.

    Mary E. | Jul 16, 2016 | Reply

Post a Comment