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:
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.