Scenario 13: Monitoring a Data Guard Configuration

These steps demonstrate the tasks necessary to use the SHOW command and monitorable properties to identify and resolve a failure situation.

Monitoring a Configuration Task 1: Check the Configuration Status

The status of the broker configuration is an aggregated status of all databases and instances in the broker configuration.

The status of the broker configuration is an aggregated status of all configuration members in the broker configuration.

Check the configuration status first to determine whether or not any further action needs to be taken. A SUCCESS status indicates everything in the broker configuration is working correctly. A WARNING or ERROR status indicates something in the configuration is not working correctly or needs attention.

For the example, the standby database has multiple warnings:

DGMGRL> SHOW CONGIGURATION 

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  South_Sales - Primary database
    Warning: ORA-16627: No member available to support the protection mode.

    North_Sales - Physical standby database (disabled)
      ORA-16906: The member was shutdown.

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 31 seconds ago)
To resolve these warnings, restart standby database North_Sales and recheck the configuration status again.

DGMGRL> SHOW CONFIGURATION;

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  South_Sales - Primary database
    North_Sales - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 19 seconds ago)

Monitoring a Configuration Task 2: Check the Database Status

Sometimes the output from SHOW CONFIGURATION is not sufficient by itself to resolve the issue. To identify the warnings on the primary database, show its status using the SHOW DATABASE command. 

DGMGRL> SHOW CONFIGURATION;

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  South_Sales - Primary database
    Error: ORA-16810: Multiple errors or warnings detected for the member.

    North_Sales - Physical standby database 
      Warning: ORA-16857: member disconnected from redo source for longer than specified threshold

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 25 seconds ago)

In this example, SHOW CONFIGURATION identified multiple issues for primary database South_Sales. When there are multiple errors or warnings, use the SHOW DATABASE command to identify them: 

DGMGRL> SHOW DATABASE 'South_Sales';

Database - South_Sales

  Role:                PRIMARY
  Intended State:      TRANSPORT-ON
  Redo Rate:           (unknown)

  Instance(s):
    SouthSales
      Error: ORA-16738: Redo transport service for member "North_Sales" is not running.

  Database Warning(s):
    ORA-16629: Database reports a different protection level from the protection mode.

Database Status:
ERROR

Monitoring a Configuration Task 3: Check the LogXptStatus Monitorable Property

The SHOW DATABASE output in step 2 shows a Warning for error ORA-16737.

The LogXptStatus broker monitorable property can help to identify the specific source of the ORA-16738 redo transport error: 

DGMGRL> SHOW DATABASE 'South_Sales' LogXptStatus;
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME     STATUS                ERROR 
          SouthSales          North_Sales   DEFERRED

Redo transport to the standby database North_Sales is currently deferred. Examining the alert log for database South_Sales shows that redo transport to log_archive_dest_2 has been deferred: 

2022-12-20T02:42:38.960566+00:00
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
2022-12-20T02:42:41.650221+00:00
LGWR (PID:3620211): LAD:2 no longer supports SYNCHRONIZATION [krsl.c:7650]

Alternatively, the DGMGRL SHOW DATABASE … PARAMETER command can be used to check the state of the redo transport destinations configured for database South_Sales to identify which log archive destination is deferred: 

DGMGRL> CONNECT sys@south_sales
Password: password
Connected to "South_Sales"
Connected as SYSDBA.
DGMGRL> SHOW DATABASE 'South_Sales' PARAMETER log_archive_dest_state_2;
  log_archive_dest_state_2 = 'DEFER'
To resolve the problem, enable the deferred destination:
DGMGRL> SQL 'alter system set log_archive_dest_state_2=ENABLE scope=both';
Succeeded.
DGMGRL> SHOW CONFIGURATION

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  South_Sales - Primary database
    North_Sales - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 26 seconds ago)

Monitoring a Configuration Task 4: Check the InconsistentLogXptProps Monitorable Property

To identify the inconsistent values for the redo transport database property, ReopenSecs, you can use the InconsistentLogXptProps monitorable property.

This is useful, for example, for the warning shown in the SHOW DATABASE display in Step 2 is ORA-16715. 

DGMGRL> SHOW DATABASE 'North_Sales' 'InconsistentLogXptProps';
INCONSISTENT LOG TRANSPORT PROPERTIES
 INSTANCE_NAME  STANDBY_NAME  PROPERTY_NAME  MEMORY_VALUE  BROKER_VALUE 
   south_sales1   South_Sales     ReopenSecs           600           300

The current database memory value (600) is different from the Oracle 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 'South_Sales' SET PROPERTY 'ReopenSecs'=300;
Property "ReopenSecs" updated

You can also reenable the standby database or reset the state of the primary database to TRANSPORT-ON to fix this inconsistency.