11g |Monitoring DataGuard using Broker Commands

DGMGRL> show database PROD;
Object “prod” was not found

** 11.2.0.2 – You may see errors at dgmgrl if you don’t include database name in quotes

Use : DGMGRL> show database ‘prod’;

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

DGMGRL> show configuration
Configuration – dataguard
Protection Mode: MaxPerformance
Databases:
stdby – Primary database
prod  – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

2   Check the database status  :

DGMGRL> show database  STDBY;
Database – prod
Role:            PHYSICAL STANDBY
Intended State:  APPLY-ON
Transport Lag:   0 seconds
Apply Lag:       1 hour(s) 3 minutes 6 seconds (Note Apply Lag of an hour)
Real Time Query: OFF
Instance(s):
PROD1 (apply instance)
PROD2
Database Status:
SUCCESS

We can also run an SQL to locate the lag between Primary and Standby

set linesize 200;
set pagesize 2000;
COLUMN NAME FORMAT A30;
COLUMN value FORMAT A20;
COLUMN UNIT FORMAT A20;
COLUMN time_computed FORMAT A20;
select name
 , value
 , unit
 , time_computed
 from v$dataguard_stats;
NAME                           VALUE                UNIT                 TIME_COMPUTED
------------------------------ -------------------- -------------------- --------------------
transport lag                  +00 00:22:37         day(2) to second(0)  06/09/2011 12:06:09
 interval
apply lag                      +00 00:22:40         day(2) to second(0)  06/09/2011 12:06:09
 interval
apply finish time              +00 00:00:00.035     day(2) to second(3)  06/09/2011 12:06:09
 interval
estimated startup time         18                   second               06/09/2011 12:06:09

3. Check the monitorable property StatusReport
To identify which database has the failure, you need to go through all of the databases in the configuration one by one.

DGMGRL> show database prod statusreport;
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database stdby statusreport;
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT

4   Check the monitorable property LogXptStatus
To identify exact log transport errors, we can use monitorable property LogXptStatus

DGMGRL> show database prod logxptstatus
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS

5.    Check the monitorable property InconsistentProperties
If you see warning ORA-16714 reported, to identify inconsistent values for property LogArchiveTrace we can use property InconsistentProperties

DGMGRL> SHOW DATABASE prod InconsistentProperties;
INCONSISTENT PROPERTIES
INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

6.    Check the monitorable property InconsistentLogXptProps
To identify the inconsistent values for the redo transport property use monitorable property InconsistentLogXptProps:

DGMGRL> show database prod InconsistentLogXptProps
INCONSISTENT PROPERTIES
INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE