RE: One primary with two physical standbys exhibiting different behavior with regard to lag
Date: Fri, 4 Jan 2019 14:29:06 -0500
Message-ID: <025201d4a463$c29f14b0$47dd3e10$_at_rsiz.com>
Given that you are 12.1.0.2, I *think* the relevant document starting point is:
https://docs.oracle.com/database/121/SBYDB/log_transport.htm#SBYDB00400
This has definitely evolved over versions, starting with “roll your own” in 6.0.36 and various changes through the route to productization.
I’m not confident any lease after 8 failed to involve some significant change (other than “roll your own” still being a process of shipping and applying the logs as if you were recovering while periodically cancelling recovery and making a clone/rename to test whether your continuous recovery could actually be warm started and opened and then mounting and resuming recovery of the standby thread.)
I’d focus on how to do it right from the document before you try to figure out what is wrong, since several pairs of eyes have failed to quickly find that needle. Burn the haystack and the needle will appear.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Friday, January 04, 2019 12:46 PM
Is one of these applying from lgwr and the other by shipping logs? (Sorry no time to read the details.) That would explain both the lag and the faster maximum apply rate, because after the log switches and finally is shipped it applies very quickly locally with zero network ack/nak of success application.
Before it was possibly to write to the standby from lgwr, that was the way it worked for all the standbys.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of John Thomas
Sent: Friday, January 04, 2019 12:27 PM
To: jt2354_at_gmail.com; 'Sandra Becker'
Cc: 'Neil Chandler'; 'Andrew Kerber'; 'oracle-l'
Subject: RE: One primary with two physical standbys exhibiting different behavior with regard to lag
To: Sandra Becker
Cc: Neil Chandler; Andrew Kerber; oracle-l
Subject: Re: One primary with two physical standbys exhibiting different behavior with regard to lag
If that's not the source of your problems, you need to eliminate it before looking further.
The max and average apply rates are very different for the two standby's. (But the slow one's faster. )
Transport and apply lag on fitsdb1... maybe this is just the mismatch between redo and standby log sizes. They should all be the same size and you should have one more standby log than the number of redo logs on each. But I guess you know that already.
I should've asked for the ping times., but it's probably not relevant.
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
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
Regards
Join
On Fri, 4 Jan 2019, 15:41 Sandra Becker <sbecker6925_at_gmail.com wrote:
I had an error pop up that indicated a problem with a size difference between the standby redo logs and the online redo logs. Standby on the primary was 10x the size of online redo logs. All the logs on the standby were set up the same as the online redo. I've corrected that just to be consistent and follow best practices. While checking correcting the sizes, I noticed that the standby that lags never assigns a standby redo log. Will be researching that next.
Sandy
On Fri, Jan 4, 2019 at 7:35 AM 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
Properties:
Fast-Start Failover: DISABLED
Configuration Status:
Database - itsdb1
Properties:
Database Status:
Database - itsdb2
Properties:
Database Status:
DGMGRL> show database verbose fitsdb1
Database - fitsdb1
Properties:
Database Status:
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
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/
Members:
itsdb1 - Primary database
itsdb2 - Physical standby database
fitsdb1 - Physical standby database
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
SUCCESS
DGMGRL> show database verbose itsdb1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
its1
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)'
SUCCESS
DGMGRL> show database verbose 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
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)'
SUCCESS
The standby below is the one that lags.
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
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)'
SUCCESS
I may be missing something, but I didn't see any differences in the config between the two standbys.
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
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 04 2019 - 20:29:06 CET