11gR2 RAC-Dataguard Sync issue Between Primary & Standby

Posted By Sagar Patil

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.

Top of Page

Top menu