ORA-16853 and ORA-16855
After resuming data guard through broker, we saw ORA-16853 and ORA-16855 when showing the status of standby database: in DGMGRL.
DGMGRL> show database orclstb
Database - orclstb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 5 minutes 2 seconds (computed 1 second ago)
Average Apply Rate: 18.91 MByte/s
Real Time Query: OFF
Instance(s):
ORCLSTB1 (apply instance)
ORCLSTB2
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
ORA-16855: transport lag has exceeded specified threshold
Database Status:
WARNING
As we can see, the status is WARNING, furthermore, the real-time Apply Lag is increasing as time goes by.
In my judgement, the data guard is transporting data, however, it urges DBA to intervene.
Solutions
To fix ORA-16853 and ORA-16855, we can simply set both values of ApplyLagThreshold and TransportLagThreshold to zero.
DGMGRL> edit database orclstb set property ApplyLagThreshold=0;
Property "applylagthreshold" updated
DGMGRL> edit database orclstb set property TransportLagThreshold=0;
Property "transportlagthreshold" updated
To get real-time apply back, we disabled warnings by setting those thresholds to 0 seconds.
Let's see the result.
DGMGRL> show database verbose orclstb
Database - orclstb
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: 6.85 MByte/s
Active Apply Rate: 158.90 MByte/s
Maximum Apply Rate: 179.05 MByte/s
Real Time Query: OFF
Instance(s):
ORCLSTB1 (apply instance)
ORCLSTB2
Properties:
DGConnectIdentifier = 'orclstb'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
The Apply Lag is back to zero.
Setting a meaningful DelayMins, e.g. 15 minutes, is also an option to get rid of errors, but it does have some effects. I means it's not real-time apply anymore.
A bit confused with this statement:
“To get real-time apply back, we disabled warnings by setting those thresholds to 0 seconds.”
Are you saying real-time apply is not in place if both of these thresholds are non-zero? I thought having them at 0 only suppresses the ORA- error. Or does it do something more than that.
The first, setting zero is to raise the threshold to unlimit so as to turn off those warnings, you can set any higher value as long as they are large enough.
The second, I know those threshold should be only warnings, but I found real-time apply did not work if those warnings presented. So I had to solve errors to make it move.