Re: One primary with two physical standbys exhibiting different behavior with regard to lag

From: Leng <lkaing_at_gmail.com>
Date: Thu, 10 Jan 2019 07:34:40 +1100
Message-Id: <0452A4F1-66AD-466A-B29F-844DA2AE979F_at_gmail.com>

You can only get real time apply if you standby log files. Real time apply should be turned on for dg so that you get near zero data loss.

If you have 2 standbys it would be advisable to have one set to have a lag... if a logical corruption (eg, deleted row) occurs at the primary then the lagging standby would not have had this transaction applied and you can still switchover or open it read-only to get the data.

Cheers,
Leng

> On 8 Jan 2019, at 6:04 am, Sandra Becker <sbecker6925_at_gmail.com> wrote:
> 
> Along these lines, does DG Broker require real time apply?  Disagreement between team members and I don't really know.
> 
>> On Mon, Jan 7, 2019 at 11:41 AM Sandra Becker <sbecker6925_at_gmail.com> wrote:
>> Just discovered that the DBA who created the two standbys started real time apply on one and not the other.  She didn't think it made a difference.  Team lead does not want to use real time apply to avoid accidentally turning on Active Dataguard, which we are not licensed to do.
>> 
>> 
>> 
>>> On Mon, Jan 7, 2019 at 3:44 AM Rakesh Ra <rakeshra.tr_at_gmail.com> wrote:
>>> Just to add starting 12.1 real time apply is the default mode..
>>> 
>>> Regards,
>>> Rakesh RA
>>> 
>>>> On Mon, Jan 7, 2019, 14:39 Hemant K Chitale <hemantkchitale_at_gmail.com> wrote:
>>>> How is the 
>>>> "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 
>>>> USING CURRENT LOGFILE"
>>>> command issued on the 2nd standby ?  Is it "USING CURRENT LOGFILE ?   That would be "Real Time Apply"
>>>> Else, "Real Time Apply" wouldn't be happening and the Standby would wait for the Archive Log.
>>>> 
>>>> Hemant K Chitale
>>>> 
>>>> 
>>>> 
>>>>

>>>>> On Fri, Jan 4, 2019 at 10:37 PM Sandra Becker <sbecker6925_at_gmail.com> wrote:
>>>>> Yes, archiving the current log allows the standby to catch up.
>>>>>
>>>>> DGMGRL> show configuration verbose
>>>>>
>>>>> Configuration - itsdb_DG_CONFIG
>>>>>
>>>>> Protection Mode: MaxPerformance
>>>>> Members:
>>>>> itsdb1 - Primary database
>>>>> itsdb2 - Physical standby database
>>>>> fitsdb1 - Physical standby database
>>>>>
>>>>> Properties:
>>>>> FastStartFailoverThreshold = '30'
>>>>> OperationTimeout = '30'
>>>>> TraceLevel = 'USER'
>>>>> FastStartFailoverLagLimit = '30'
>>>>> CommunicationTimeout = '180'
>>>>> ObserverReconnect = '0'
>>>>> FastStartFailoverAutoReinstate = 'TRUE'
>>>>> FastStartFailoverPmyShutdown = 'TRUE'
>>>>> BystandersFollowRoleChange = 'ALL'
>>>>> ObserverOverride = 'FALSE'
>>>>> ExternalDestination1 = ''
>>>>> ExternalDestination2 = ''
>>>>> PrimaryLostWriteAction = 'CONTINUE'
>>>>>
>>>>> Fast-Start Failover: DISABLED
>>>>>
>>>>> Configuration Status:
>>>>> SUCCESS
>>>>>
>>>>> DGMGRL> show database verbose itsdb1
>>>>>
>>>>> Database - itsdb1
>>>>>
>>>>> Role: PRIMARY
>>>>> Intended State: TRANSPORT-ON
>>>>> Instance(s):
>>>>> its1
>>>>>
>>>>> Properties:
>>>>> DGConnectIdentifier = 'itsdb1'
>>>>> ObserverConnectIdentifier = ''
>>>>> LogXptMode = 'ASYNC'
>>>>> RedoRoutes = ''
>>>>> DelayMins = '0'
>>>>> Binding = 'optional'
>>>>> MaxFailure = '0'
>>>>> MaxConnections = '1'
>>>>> ReopenSecs = '300'
>>>>> NetTimeout = '30'
>>>>> RedoCompression = 'DISABLE'
>>>>> LogShipping = 'ON'
>>>>> PreferredApplyInstance = ''
>>>>> ApplyInstanceTimeout = '0'
>>>>> ApplyLagThreshold = '0'
>>>>> TransportLagThreshold = '0'
>>>>> TransportDisconnectedThreshold = '30'
>>>>> ApplyParallel = 'AUTO'
>>>>> StandbyFileManagement = 'AUTO'
>>>>> ArchiveLagTarget = '0'
>>>>> LogArchiveMaxProcesses = '30'
>>>>> LogArchiveMinSucceedDest = '1'
>>>>> DbFileNameConvert = '+DATA/ITSDB2/, +DATA/ITSDB1/'
>>>>> LogFileNameConvert = '+DATA/ITSDB2/, +DATA/ITSDB1/'
>>>>> FastStartFailoverTarget = ''
>>>>> InconsistentProperties = '(monitor)'
>>>>> InconsistentLogXptProps = '(monitor)'
>>>>> SendQEntries = '(monitor)'
>>>>> LogXptStatus = '(monitor)'
>>>>> RecvQEntries = '(monitor)'
>>>>> StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pitsdbs01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=itsdb1_DGMGRL)(INSTANCE_NAME=its1)(SERVER=DEDICATED)))'
>>>>> StandbyArchiveLocation = '/backup/itsdb/archives'
>>>>> AlternateLocation = ''
>>>>> LogArchiveTrace = '0'
>>>>> LogArchiveFormat = '%t_%s_%r.arc'
>>>>> TopWaitEvents = '(monitor)'
>>>>>
>>>>> Database Status:
>>>>> SUCCESS
>>>>>
>>>>> DGMGRL> show database verbose itsdb2
>>>>>
>>>>> Database - itsdb2
>>>>>
>>>>> Role: PHYSICAL STANDBY
>>>>> Intended State: APPLY-ON
>>>>> Transport Lag: 0 seconds (computed 0 seconds ago)
>>>>> Apply Lag: 0 seconds (computed 0 seconds ago)
>>>>> Average Apply Rate: 71.00 KByte/s
>>>>> Active Apply Rate: 450.00 KByte/s
>>>>> Maximum Apply Rate: 15.18 MByte/s
>>>>> Real Time Query: OFF
>>>>> Instance(s):
>>>>> its2
>>>>>
>>>>> Properties:
>>>>> DGConnectIdentifier = 'itsdb2'
>>>>> ObserverConnectIdentifier = ''
>>>>> LogXptMode = 'ASYNC'
>>>>> RedoRoutes = ''
>>>>> DelayMins = '0'
>>>>> Binding = 'optional'
>>>>> MaxFailure = '0'
>>>>> MaxConnections = '1'
>>>>> ReopenSecs = '300'
>>>>> NetTimeout = '30'
>>>>> RedoCompression = 'DISABLE'
>>>>> LogShipping = 'ON'
>>>>> PreferredApplyInstance = ''
>>>>> ApplyInstanceTimeout = '0'
>>>>> ApplyLagThreshold = '0'
>>>>> TransportLagThreshold = '0'
>>>>> TransportDisconnectedThreshold = '30'
>>>>> ApplyParallel = 'AUTO'
>>>>> StandbyFileManagement = 'AUTO'
>>>>> ArchiveLagTarget = '0'
>>>>> LogArchiveMaxProcesses = '30'
>>>>> LogArchiveMinSucceedDest = '1'
>>>>> DbFileNameConvert = '+DATA/ITSDB1/, +DATA/ITSDB2/'
>>>>> LogFileNameConvert = '+DATA/ITSDB1/, +DATA/ITSDB2/'
>>>>> FastStartFailoverTarget = ''
>>>>> InconsistentProperties = '(monitor)'
>>>>> InconsistentLogXptProps = '(monitor)'
>>>>> SendQEntries = '(monitor)'
>>>>> LogXptStatus = '(monitor)'
>>>>> RecvQEntries = '(monitor)'
>>>>> StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pitsdbs02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=itsdb2_DGMGRL)(INSTANCE_NAME=its2)(SERVER=DEDICATED)))'
>>>>> StandbyArchiveLocation = '/backup/itsdb/archives'
>>>>> AlternateLocation = ''
>>>>> LogArchiveTrace = '0'
>>>>> LogArchiveFormat = '%t_%s_%r.arc'
>>>>> TopWaitEvents = '(monitor)'
>>>>>
>>>>> Database Status:
>>>>> SUCCESS
>>>>>
>>>>> The standby below is the one that lags.
>>>>> DGMGRL> show database verbose fitsdb1
>>>>>
>>>>> Database - fitsdb1
>>>>>
>>>>> Role: PHYSICAL STANDBY
>>>>> Intended State: APPLY-ON
>>>>> Transport Lag: 12 minutes 45 seconds (computed 12 seconds ago)
>>>>> Apply Lag: 12 minutes 45 seconds (computed 12 seconds ago)
>>>>> Average Apply Rate: 79.00 KByte/s
>>>>> Active Apply Rate: 60.79 MByte/s
>>>>> Maximum Apply Rate: 61.21 MByte/s
>>>>> Real Time Query: OFF
>>>>> Instance(s):
>>>>> fits1
>>>>>
>>>>> Properties:
>>>>> DGConnectIdentifier = 'fitsdb1'
>>>>> ObserverConnectIdentifier = ''
>>>>> LogXptMode = 'ASYNC'
>>>>> RedoRoutes = ''
>>>>> DelayMins = '0'
>>>>> Binding = 'optional'
>>>>> MaxFailure = '0'
>>>>> MaxConnections = '1'
>>>>> ReopenSecs = '300'
>>>>> NetTimeout = '30'
>>>>> RedoCompression = 'DISABLE'
>>>>> LogShipping = 'ON'
>>>>> PreferredApplyInstance = ''
>>>>> ApplyInstanceTimeout = '0'
>>>>> ApplyLagThreshold = '0'
>>>>> TransportLagThreshold = '0'
>>>>> TransportDisconnectedThreshold = '30'
>>>>> ApplyParallel = 'AUTO'
>>>>> StandbyFileManagement = 'AUTO'
>>>>> ArchiveLagTarget = '0'
>>>>> LogArchiveMaxProcesses = '30'
>>>>> LogArchiveMinSucceedDest = '1'
>>>>> DbFileNameConvert = '+DATA/ITSDB2/, +DATA/FITSDB1/'
>>>>> LogFileNameConvert = '+DATA/ITSDB2/, +DATA/FITSDB1/'
>>>>> FastStartFailoverTarget = ''
>>>>> InconsistentProperties = '(monitor)'
>>>>> InconsistentLogXptProps = '(monitor)'
>>>>> SendQEntries = '(monitor)'
>>>>> LogXptStatus = '(monitor)'
>>>>> RecvQEntries = '(monitor)'
>>>>> StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fitsdbs1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=fitsdb1_DGMGRL)(INSTANCE_NAME=fits1)(SERVER=DEDICATED)))'
>>>>> StandbyArchiveLocation = '/backup/itsdb/archives'
>>>>> AlternateLocation = ''
>>>>> LogArchiveTrace = '0'
>>>>> LogArchiveFormat = '%t_%s_%r.arc'
>>>>> TopWaitEvents = '(monitor)'
>>>>>
>>>>> Database Status:
>>>>> SUCCESS
>>>>>
>>>>>
>>>>> I may be missing something, but I didn't see any differences in the config between the two standbys.
>>>>>
>>>>> Sandy
>>>>>

>>>>>> On Fri, Jan 4, 2019 at 3:11 AM Neil Chandler <neil_chandler_at_hotmail.com> wrote:
>>>>>> Sandy,
>>>>>> 
>>>>>> So if you do a manual "alter system archive log current" on the Primary, it catches up?
>>>>>> 
>>>>>> I would look at the config parameters to compare the configuration. Could you share the configuration info (db/server names suitably redacted):
>>>>>> 
>>>>>> dgmgrl> show configuration verbose
>>>>>> dgmgrl> show database verbose <primary|standby2|standby2>
>>>>>> 
>>>>>> 
>>>>>> regards
>>>>>> 
>>>>>> Neil Chandler
>>>>>> Database Guy, Knows Things.
>>>>>>  
>>>>>> From: Sandra Becker <sbecker6925_at_gmail.com>
>>>>>> Sent: 03 January 2019 22:49
>>>>>> To: John Thomas
>>>>>> Cc: Andrew Kerber; Neil Chandler; oracle-l
>>>>>> Subject: Re: One primary with two physical standbys exhibiting different behavior with regard to lag
>>>>>>  
>>>>>> My understanding is the network setup is the same between the standbys.   I didn't look at the network right away.  :-)   I made the changes suggested by Neil, but I'm still seeing the delay.  Before I did a manual log switch, the delay was over 30 minutes.  Not good for this critical production standby.
>>>>>> 
>>>>>> Sandy
>>>>>> 
>>>>>> On Thu, Jan 3, 2019 at 3:36 PM John Thomas <jt2354_at_gmail.com> wrote:
>>>>>> There's no excessive delay between your primary and the lagging standby is there? Smaller pipe? Lots of network retries?
>>>>>> 
>>>>>> Probably the second thing you checked...
>>>>>> 
>>>>>> Regards, 
>>>>>> 
>>>>>> John 
>>>>>> 
>>>>>> On Thu, 3 Jan 2019 at 22:10, Sandra Becker <sbecker6925_at_gmail.com> wrote:
>>>>>> Thanks, Andrew.  That's one of the first things I checked.  It's the same on both standbys.
>>>>>> 
>>>>>> Sandy
>>>>>> 
>>>>>> On Thu, Jan 3, 2019 at 2:47 PM Andrew Kerber <andrew.kerber_at_gmail.com> wrote:
>>>>>> Neil most likely spotted the problem.  But you should also check to make sure that the protection mode is the same on both standbys.If the instance that is behind is using the maximum performance (async) mode it can run a ways behind the primary.
>>>>>> 
>>>>>> On Thu, Jan 3, 2019 at 3:36 PM Neil Chandler <neil_chandler_at_hotmail.com> wrote:
>>>>>> Sandy,
>>>>>> 
>>>>>> Have you checked the Standby Redo logs? There's a slight (annoying) change in Oracle 12.1 onwards which means that Standby Redo logs get created with Thread 0 instead of Thread 1 by default (for a single instance database). Redo can only use Standby Redo when the threads are the same. If this is RAC you need Standby Redo for each thread - and you must have 1 more Standby Redo than Online Redo for each thread.
>>>>>> 
>>>>>> By coincidence, I wrote a blog post about this 10 minutes ago.
>>>>>> 
>>>>>> https://chandlerdba.com/2019/01/03/data-guard-unexpected-lag/
>>>>>> 
>>>>>> regards
>>>>>> 
>>>>>> Neil Chandler
>>>>>> Database Guy. Knows Things.
>>>>>> 
>>>>>>  
>>>>>> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Sandra Becker <sbecker6925_at_gmail.com>
>>>>>> Sent: 03 January 2019 20:29
>>>>>> To: oracle-l
>>>>>> Subject: One primary with two physical standbys exhibiting different behavior with regard to lag
>>>>>>  
>>>>>> Oracle 12.1.0.2
>>>>>> RHEL7
>>>>>> 
>>>>>> To begin with, I have not worked much at all with standby databases, so my knowledge is somewhat lacking.
>>>>>> 
>>>>>> For business reasons, we have a primary database with two physical standbys.  Everything is configured in dgmgrl and enabled.  Monitoring with EM13c is reporting the lag times, so all looks good for basic setup and monitoring.  We seem to have significant lag at times on one of the standbys, as much as 20 minutes.  When looking at v$managed_standby, we see the status as "WAIT_FOR_LOG".   The other standby never seems to be more that a few seconds behind, if at all, and the status is "APPLYING_LOG".  
>>>>>> 
>>>>>> Is this normal?  I've been researching, but haven't found an answer yet.  I didn't create or start the standby databases, so I don't have any idea what was actually done that could be causing this behavior.  Any suggestions would be appreciated.
>>>>>> 
>>>>>> Thank you,
>>>>>> 
>>>>>> -- 
>>>>>> Sandy B.
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> -- 
>>>>>> Andrew W. Kerber
>>>>>> 
>>>>>> 'If at first you dont succeed, dont take up skydiving.'
>>>>>> 
>>>>>> 
>>>>>> -- 
>>>>>> Sandy B.
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> -- 
>>>>>> Sandy B.
>>>>>>

>>>>>
>>>>>
>>>>> --
>>>>> Sandy B.
>>>>>

>> 
>> 
>> -- 
>> Sandy B.
>> 
> 
> 
> -- 
> Sandy B.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 09 2019 - 21:34:40 CET

This message: [ Message body ]
Next message: Leng: "Re: One primary with two physical standbys exhibiting different behavior with regard to lag"
Previous message: Jared Still: "Re: Oracle Net Configuration File Locations"
In reply to: Sandra Becker: "Re: One primary with two physical standbys exhibiting different behavior with regard to lag"
In reply to Hemant K Chitale: "Re: One primary with two physical standbys exhibiting different behavior with regard to lag"
Next in thread: Leng: "Re: One primary with two physical standbys exhibiting different behavior with regard to lag"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message