Time for action – basic monitoring with Data Guard broker

Now we'll see how to perform basic Data Guard monitoring using the broker interface DGMGRL.

  1. Check the configuration status with the following command. It provides the overall health status of the Data Guard configuration. If the Configuration Status resulted to SUCCESS, it means that the Data Guard configuration is working properly. Output can also be WARNING or ERROR as follows:
    DGMGRL> show configuration;
Configuration - PACKT
  Protection Mode: MaxPerformance
  Databases:
    turkey_un - Primary database
      Error: ORA-16778: redo transport error for one or more databases
    INDIA_UN  - Physical standby database
      Error: ORA-01031: insufficient privileges
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
  2. Check the database status to find out if there are any warnings or errors in the databases of the Data Guard configuration. Use the following command from the DGMGRL utility:
    DGMGRL> show database turkey_un;
Database - turkey_un
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    TURKEY
      Error: ORA-16737: the redo transport service for standby database "INDIA_UN" has an error
Database Status:
ERROR
  3. Check the redo transport status. LogXptStatus is the database property that returns an output containing the status of the redo transport services to each of the enabled standby databases. This property is applicable to the primary database as shown in the following command line:
    DGMGRL> show database turkey_un 'LogXptStatus';
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME                STATUS
TURKEY             INDIA_UN ORA-01031: insufficient privileges
  4. Check Status Report. This is the database property that returns a list of errors or warnings about the status of the database. In RAC databases, it includes the status of all the running instances as follows:
    DGMGRL> show database turkey_un 'StatusReport';
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT
TURKEY      ERROR ORA-16737: the redo transport service for standby database "INDIA_UN" has an error
  5. Check Inconsistent Properties. This will return an output that shows all the database properties whose values are contained in the broker configuration file and are inconsistent with the values in the database. In RAC databases, a database-specific property may be inconsistent only on some instances as shown in the following line:
    DGMGRL>  show database turkey_un InconsistentProperties;
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME   MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
TURKEY        LogArchiveTrace 255          00
  6. Check the TopWaitEvents property that specifies the top five events that waited for the most amount of time in the specified instance as follows:
    DGMGRL> show instance 'TURKEY' 'TopWaitEvents';
TOP SYSTEM WAIT EVENTS
Event            Wait Time
rdbms ipc message            162825637
DIAG idle wait             15930581
SQL*Net message from client   15074233
jobq slave wait             12516954
Streams AQ: qmn slave idle wait  7973917
  7. Gather the same information using SQL*Plus as from the v$system_event view follows:
    SQL> select event,TIME_WAITED from v$system_event order by time_waited desc;
EVENT                                    TIME_WAITED
---------------------------------------- -----------
rdbms ipc message                          162816106
DIAG idle wait                              15929381
SQL*Net message from client                 15069275
jobq slave wait                             12516954
Streams AQ: qmn slave idle wait              7973917
  8. Check the SendQEntries database property. The following output shows all the logfiles of the primary database that were not successfully archived to standby databases as shown in the following command line:
    DGMGRL>  show database turkey_un 'SendQEntries';
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs)
            INDIA_UN     ARCHIVED        788992101                1                  227  09/01/2012 01:48:13  09/01/2012 01:48:14          2107092          2107097                1
            INDIA_UN     ARCHIVED        788992101                1                  228  09/01/2012 01:48:14  09/01/2012 01:48:16          2107097          2107101                2
            INDIA_UN     ARCHIVED        788992101                1                  229  09/01/2012 01:48:16  09/01/2012 01:48:17          2107101          2107104                1
                          CURRENT        788992101                1                  230  09/01/2012 01:48:17                               2107104                                 1
  9. Check the RecvQEntries database property that reports on all the logfiles that were received by the standby database but not yet applied. If there are no rows, it means that all the logfiles have been applied as follows:
    DGMGRL> show database 'INDIA_UN'  'RecvQEntries';
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs)
         NOT_APPLIED        788992101                1                  238  09/01/2012 01:55:31  09/01/2012 01:56:04          2107788          2107823               20
         NOT_APPLIED        788992101                1                  239  09/01/2012 01:56:04  09/01/2012 01:56:05          2107823          2107826                1
         NOT_APPLIED        788992101                1                  240  09/01/2012 01:56:05  09/01/2012 01:56:07          2107826          2107831                2
         NOT_APPLIED        788992101                1                  241  09/01/2012 01:56:07  09/01/2012 01:56:07          2107831          2107834                1

Tip

To get the status of the database or configuration with the previous commands, you can connect DGMGRL from the primary or standby database servers or even from the observer system if it exists.

What just happened?

We have seen how to connect to the Data Guard broker configuration and check the configuration status, database status, status of the instance with properties, and property values using the DGMGRL command-line utility.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset