How to fix ORA-16853: apply lag has exceeded specified threshold in 19c

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 2^nd standby site) the RFS process started transporting the archives to the 2^nd 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 .