DataGuard Monitorable (Read-Only) Database Properties

Posted By Sagar Patil
Configuration details below are for a 2 Node RAC as Primary (PROD1,PROD2) & 2 node RAC(STDBY1,STDBY2) as Physical Standby.

DGMGRL> show database 'PROD';
Database - PROD
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    PROD1
    PROD2
Database Status:
SUCCESS

DGMGRL> show database 'STDBY';
Database - STDBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       6 seconds
  Real Time Query: OFF
  Instance(s):
    STDBY1 (apply instance)
    STDBY2
Database Status:
SUCCESS
DGMGRL> show instance verbose "PROD1";
Instance 'PROD1' of database 'PROD'
  Host Name: Primary-Server
  PFILE:
  Properties:
    SidName                         = 'PROD1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Primary-Server)(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=PROD_DGMGRL)(INSTANCE_NAME=PROD1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/mnt/logs/oradata/PROD/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'
Instance Status:
SUCCESS

DGMGRL> show instance verbose "STDBY2";
Instance 'STDBY2' of database 'STDBY'
  Host Name : Standby-Server
  PFILE:
  Properties:
    SidName                         = 'STDBY2'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Standby-Server)(PORT=1529))(CONNECT_DATA=(SERVICE_NAME=STDBY_DGMGRL)(INSTANCE_NAME=STDBY2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/mnt/logs/oradata/PROD/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'
Instance Status:
SUCCESS
Properties for Physical Standby Database :
1.  InconsistentLogXptProps (Inconsistent Redo Transport Properties)
2.  InconsistentProperties (Inconsistent Database Properties)
3.  LogXptStatus (Redo Transport Status)
4.  RecvQEntries (Receive Queue Entries)
5.  SendQEntries (Send Queue Entries)
6.  StatusReport (Status Report)
7.  TopWaitEvents

Properties for Logical Standby Database
1.  LsbyFailedTxnInfo (Logical Standby Failed Transaction Information)
2.  LsbyParameters (Logical Standby Parameters)
3.  LsbySkipTable (Logical Standby Skip Table)
4.  LsbySkipTxnTable (SQL Apply Skip Transaction Table)

1.  InconsistentLogXptProps (Inconsistent Redo Transport Properties)
    The InconsistentLogXptProps monitorable database property returns a table that shows all properties related to redo transport services whose values are inconsistent between the broker configuration file and the runtime value in the database.
	DGMGRL> show instance "PROD1" 'InconsistentLogXptProps';
2.  InconsistentProperties (Inconsistent Database Properties)
DGMGRL> show instance "PROD1" 'InconsistentProperties';
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
DGMGRL> show instance "STDBY1" 'InconsistentProperties';
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
3.  LogXptStatus (Redo Transport Status)
    The LogXptStatus monitorable database property returns a table that contains the error status of redo transport services for each of the enabled standby databases. This property pertains to the primary database.
	DGMGRL> show instance "PROD1" 'LogXptStatus';
		LOG TRANSPORT STATUS
		PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS
             PROD1              STDBY
             PROD2              STDBY
4.  RecvQEntries (Receive Queue Entries)
    The RecvQEntries monitorable database property returns a table indicating all log files that were received by the standby database but have not yet been applied.
    If no rows are returned, it implies all log files received have been applied. This property pertains to a standby database.
	DGMGRL> show instance "STDBY1" 'RecvQEntries';
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs)
         NOT_APPLIED        732278995                2                 7733  09/13/2011 17:35:56  09/13/2011 17:36:07       7611896792       7611961172            39968
         NOT_APPLIED        732278995                2                 7734  09/13/2011 17:36:07  09/13/2011 17:36:16       7611961172       7612024484            39969
5. SendQEntries (Send Queue Entries)
   The SendQEntries monitorable database property returns a table that shows all log files on the primary database that were not successfully archived to one or more standby databases.
   This property pertains to the primary database.
	DGMGRL> show instance "PROD1" 'SendQEntries';
   PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs)
             STDBY     ARCHIVED        732278995                1                 7796  09/13/2011 17:37:31  09/13/2011 17:38:01       7612501278       7612699830              177
             STDBY     ARCHIVED        732278995                2                 7744  09/13/2011 17:37:46  09/13/2011 17:37:55       7612594908       7612657357            39970
             STDBY     ARCHIVED        732278995                2                 7745  09/13/2011 17:37:55  09/13/2011 17:38:05       7612657357       7612719596            39968
                       CURRENT         732278995                1                 7797  09/13/2011 17:38:01                            7612699830                                56
                       CURRENT         732278995                2                 7746  09/13/2011 17:38:05                            7612719596                             10491
6. StatusReport (Status Report)
   DGMGRL> SHOW DATABASE 'PROD1' 'StatusReport' ;
7. TopWaitEvents
   The TopWaitEvents monitorable database property specifies the 5 events with the longest waiting time in the specified instance. The events and their waiting time are retrieved from V$SYSTEM_EVENT.
DGMGRL> show instance "PROD1" 'TopWaitEvents';
TOP SYSTEM WAIT EVENTS
               Event            Wait Time
SQL*Net message from client   202752791770
   rdbms ipc message          65593825038
EMON slave idle wait          12015781443
  gcs remote message          5594728345
      DIAG idle wait          5590025706

DGMGRL> show instance "STDBY1" 'TopWaitEvents';
TOP SYSTEM WAIT EVENTS
               Event            		Wait Time
   rdbms ipc message          			16335113847
parallel recovery slave next change     5446704641
SQL*Net message from client           	4758412882
        PX Idle Wait           			2533146430
  gcs remote message           			1361762939

	

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu