We had a situation where in multi standby database environment , one of the standby database was lagging behind from the primary database. The log gap between primary and standby database was pretty huge. However, in primary we had the archives present so eventually after disabling/enabling LOG_ARCHIVE_DEST_3(As this was pointing to the 2nd standby site) the RFS process started transporting the archives to the 2nd standby database.
After executing following command the MRP process started applying logs in the standby site
SQL> alter database recover managed standby database disconnect from session;
However, when we checked in dataguard broker console, it was throwing a warning
ORA-16853: apply lag has exceeded specified threshold
| DGMGRL> show configuration; Configuration – jde_dg_config Protection Mode: MaxPerformance Members: prod – Primary database proddr – Physical standby database sbdb – Physical standby database Warning: ORA-16853: apply lag has exceeded specified threshold Fast-Start Failover: Disabled Configuration Status: WARNING (status updated 52 seconds ago) |
Solution:-
The reason of above warning is self explanatory, as one of our standby db (sbdb in this case) was out of sync for long time, the threshold value has been exceeded hence the warning.
| [oracle@server1]$ dgmgrl / DGMGRL for Linux: Release 19.0.0.0.0 – Production on Wed Jan 18 23:15:18 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type “help” for information. Connected to “PROD” Connected as SYSDG. DGMGRL> edit database sbdb set property ApplyLagThreshold=0; Property “applylagthreshold” updated DGMGRL> edit database sbdb set property TransportLagThreshold=0; Property “transportlagthreshold” updated |
| DGMGRL> show database verbose sbdb Database – sbdb Role: SNAPSHOT STANDBY Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 60 minutes 16 seconds (computed 1 second ago) Instance(s): PROD Properties: DGConnectIdentifier = ‘sbdb’ 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 = ‘az-psdvdb1’ StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SBDB_DGMGRL)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)))’ TopWaitEvents = ‘(monitor)’ SidName = ‘(monitor)’ Log file locations: Alert log : /u01/oracle/diag/rdbms/sbdb/PROD/trace/alert_PROD.log Data Guard Broker log : /u01/oracle/diag/rdbms/sbdb/PROD/trace/drcPROD.log Database Status: SUCCESS DGMGRL> |
So, we can resolve the warning by setting ApplyLagThreshold and TransportLagThreshold to Zero .
Leave a Reply