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.