11gR2 RAC-Dataguard Sync issue Between Primary & Standby
I have a 2 node RAC -DG setup between 2 remote data centres. After building DataGuard between them I am now coming across stange latency stats.
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:02:21 day(2) to second(0) 06/15/2010 13:12:36 interval apply lag +00 00:02:23 day(2) to second(0) 06/15/2010 13:12:36 interval apply finish time +00 00:00:01.846 day(2) to second(3) 06/15/2010 13:12:36 interval estimated startup time 16 second 06/15/2010 13:12:36
The lag stats range from 2 mins to upto 3 hours.
select name , value , unit , time_computed from v$dataguard_stats; NAME VALUE UNIT TIME_COMPUTED ------------------------------ -------------------- -------------------- -------------------- transport lag +00 00:53:10 day(2) to second(0) 06/15/2010 14:03:16 interval apply lag +00 00:53:12 day(2) to second(0) 06/15/2010 14:03:16 interval apply finish time +00 00:00:01.846 day(2) to second(3) 06/15/2010 14:03:16 interval estimated startup time 16 second 06/15/2010 14:03:16 DGMGRL> show database 'STDBY'; Database - STDBY Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 52 minutes 21 seconds Apply Lag: 52 minutes 23 seconds Real Time Query: OFF Instance(s): STDBY1 (apply instance) STDBY2 Database Status: SUCCESS DGMGRL> show database 'PROD' statusreport; STATUS REPORT INSTANCE_NAME SEVERITY ERROR_TEXT DGMGRL> show database 'STDBY' statusreport; STATUS REPORT INSTANCE_NAME SEVERITY ERROR_TEXT DGMGRL> show configuration verbose; Configuration - dataguard Protection Mode: MaxPerformance Databases: PROD - Primary database STDBY - Physical standby database Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> show database verbose 'PROD'; show database verbose 'STDBY'; Database - PROD Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): PROD1 PROD2 Properties: DGConnectIdentifier = 'PROD' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '5' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS DGMGRL> Database - STDBY Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 28 minutes 40 seconds Apply Lag: 1 hour(s) 13 minutes 18 seconds Real Time Query: OFF Instance(s): STDBY1 (apply instance) STDBY2 Properties: DGConnectIdentifier = 'STDBY' ObserverConnectIdentifier = '' LogXptMode = 'ARCH' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '5' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS DGMGRL> show database 'PROD' StatusReport show database 'STDBY' StatusReport STATUS REPORT INSTANCE_NAME SEVERITY ERROR_TEXT DGMGRL> STATUS REPORT INSTANCE_NAME SEVERITY ERROR_TEXT DGMGRL> show database 'PROD' InconsistentProperties show database 'STDBY' InconsistentProperties INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE DGMGRL> INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE DGMGRL> DGMGRL> show database 'PROD' InconsistentLogXptProps show database 'STDBY' InconsistentLogXptPropsINCONSISTENT LOG TRANSPORT PROPERTIES INSTANCE_NAME STANDBY_NAME PROPERTY_NAME MEMORY_VALUE BROKER_VALUE DGMGRL> show database 'PROD' 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 4924 11/15/2010 09:29:54 11/15/2010 11:00:15 405196462 405290625 25968 CURRENT 732278995 1 4925 11/15/2010 11:00:15 405290625 6667 CURRENT 732278995 2 5016 11/15/2010 10:15:37 405250777 14538 DGMGRL> show database 'PROD' LogXptStatus LOG TRANSPORT STATUS PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS PROD1 STDBY PROD2 STDBY DGMGRL> show database 'STDBY' RecvQEntries STANDBY_RECEIVE_QUEUE STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs) PARTIALLY_APPLIED 732278995 1 4924 11/15/2010 09:29:54 11/15/2010 11:00:15 405196462 405290625 25968
Conclusion:
The stats above show archives are sent over immediately so there is no issue with the transport or the apply on the Standby.
The archiving to the standby is done by the Archiver, so this is only when a logswitch occures. This is why there is a ‘lag’:
Using LGWR instead of ARCH will minimize the ‘Lag’ values
Leave a Reply
You must be logged in to post a comment.