The Geek Diary

Error: ORA-16810: multiple errors or warnings detected for the database

by

If you’re seeing the ORA-16810 on standby database, here is a step by step monitoring guide. The ora error description is as follows:

Error code: ORA-16810
Description: multiple errors or warnings detected for the database
Cause: The broker detected multiple errors or warnings for the database.
Action: To get a detailed status report, check the status of the database specified using either Enterprise Manager or the DGMGRL CLI SHOW DATABASE command.

Monitoring a Data Guard Configuration

The scenario in this section demonstrates how to use the SHOW command and monitorable database properties to identify and resolve a failure situation.

1. Check the configuration status

The status of the broker configuration is an aggregated status of all databases and instances in the broker configuration. You can check the configuration status first to determine whether or not any further action needs to be taken. If the configuration status is SUCCESS, everything in the broker configuration is working fine. However, if you see the following error, it means something is wrong in the configuration:

DGMGRL> SHOW CONFIGURATION;
Configuration
 Name:                DRSolution
 Enabled:             NO
 Protection Mode:     MaxPerformance
 Fast-Start Failover: DISABLED
 Databases:
    SALESPRD    - Primary database
    SALESDR     - Physical standby database

Current status for "DRSolution":
Warning: ORA-16607: one or more databases have failed

In this case, you need to continue on to Step 2 to determine the actual failure.

2. Check the database status

To identify which database has the failure, you need to go through all of the databases in the configuration one by one. In this example, the error happens to be on the primary database SALESPRD:

DGMGRL> SHOW DATABASE 'SALESPRD';

The command returns the following output:

Database
  Name:            SALESPRD
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  TRANSPORT-ON
  Instance(s):
    sales1

Current status for "SALESPRD":
Error: ORA-16810: multiple errors or warnings detected for the database

3. Check the StatusReport monitorable database property

When you see message ORA-16810, you can use the StatusReport monitorable database property to identify each of the errors or warnings:

DGMGRL> SHOW DATABASE 'SALESPRD' 'StatusReport';
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
              sales1      ERROR ORA-16737: the redo transport service for
standby "SALESDR" has an error
             sales1    WARNING ORA-16714: the value of property
LogArchiveTrace is inconsistent with the database setting
             sales1    WARNING ORA-16715: redo transport-related property
ReopenSecs of standby database " SALESDR" is inconsistent

4. Check the LogXptStatus monitorable database property

You see error ORA-16737 in the previous status report in Step 3. To identify the exact log transport error, you can use LogXptStatus monitorable database property:

DGMGRL> SHOW DATABASE 'SALESPRD' 'LogXptStatus';
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS
              sales1              SALESDR ORA-12541: TNS:no listener

Now you know the exact reason why redo transport services failed. To fix this error, start the listener for the physical standby database SALESDR.

5. Check the InconsistentProperties monitorable database property

You also see warning ORA-16714 reported in Step 3. To identify the inconsistent values for property LogArchiveTrace, you can use the InconsistentProperties monitorable database property:

DGMGRL> SHOW DATABASE 'SALESPRD' 'InconsistentProperties';

INCONSISTENT PROPERTIES
   INSTANCE_NAME   PROPERTY_NAME    MEMORY_VALUE    SPFILE_VALUE    BROKER_VALUE
          sales1   LogArchiveTrace           255            0            0

It seems that the current database memory value (255) is different from both the server parameter file (SPFILE) value (0) and Data Guard broker’s property value (0). If you decide the database memory value is correct, you can update Data Guard broker’s property value using the following command:

DGMGRL> EDIT DATABASE 'SALESPRD' SET PROPERTY 'LogArchiveTrace'=255;
Property "LogArchiveTrace" updated

In the previous command, Data Guard broker also updates the spfile value for you so that value for LogArchiveTrace is kept consistent.

6. Check the InconsistentLogXptProps monitorable database property

Another warning you see in the status report returned in Step 3 is ORA-16715. To identify the inconsistent values for the redo transport configurable database property, ReopenSecs, you can use the InconsistentLogXptProps monitorable database property.

DGMGRL> SHOW DATABASE 'SALESPRD' 'InconsistentLogXptProps';

INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME    STANDBY_NAME   PROPERTY_NAME    MEMORY_VALUE    BROKER_VALUE
          sales1         SALESDR      ReopenSecs             600             300

The current database memory value (600) is different from the Data Guard broker’s property value (300). If you think the broker’s property value is correct, you can fix the inconsistency by re-editing the property of the standby database with the same value, as shown in the following example:

DGMGRL> EDIT DATABASE 'SALESDR' SET PROPERTY 'ReopenSecs'=300;
Property "ReopenSecs" updated

You can also reenable the standby database or reset the primary database state to TRANSPORT-ON to fix the inconsistency, but re-editing the property is the simplest.

You May Also Like