ORA-16826: apply service state is inconsistent with the DelayMins property

DGMGRL> show configuration;

Configuration – cobra

Protection Mode: MaxAvailability

  Databases:
    cobra    – Primary database
    cobradup – (*) Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: ENABLED

Configuration Status:
WARNING

SOL:

Cause: This warning was caused by one of the following reasons: 
1. Apply service was started without specifying the real time apply option or without the NODELAY option while DelayMins is zero.

2. Apply service was started with the real-time apply option or with the NODELAY option while DelayMins is greater than zero.

Action: Reenable the standby database to allow the broker to restart the apply service with the apply options that are consistent with the specified value of the DelayMins property. 

In my case, i went through below approach:

First check which one is having problem primary or standby:

DGMGRL> SHOW DATABASE VERBOSE cobra—————–database status was OK

DGMGRL> SHOW DATABASE VERBOSE cobradup;

Database – cobradup

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       4 hours 50 minutes 41 seconds
  Real Time Query: ON
  Instance(s):
    cobradup

  Database Warning(s):
    ORA-16826: apply service state is inconsistent with the DelayMins property

  Properties:
    DGConnectIdentifier             = 'cobradup'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'SYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/data02/oradata/cobra/, /u01/oradata/cobradup/'
    LogFileNameConvert              = '/data02/oradata/cobra/, /u01/oradata/cobradup/'
    FastStartFailoverTarget         = 'cobra'
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'nsnora001.nat.myrio.net'
    SidName                         = 'cobradup'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nsnora001.nat.myrio.net)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cobradup_DGMGRL)(INSTANCE_NAME=cobradup)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/oradata/archives'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
WARNING

 Now check:

SQL> select DEST_NAME,DATABASE_MODE,RECOVERY_MODE,PROTECTION_MODE,STATUS,TYPE,DESTINATION from V$ARCHIVE_DEST_STATUS;
SQL> /

DEST_NAME                      DATABASE_MODE   RECOVERY_MODE           PROTECTION_MODE      STATUS    TYPE           DESTINATION
—————————— ————— ———————– ——————– ——— ————– ——————————
LOG_ARCHIVE_DEST_1             OPEN_READ-ONLY  MANAGED                 MAXIMUM PERFORMANCE  VALID     LOCAL          /u01/oradata/archives
LOG_ARCHIVE_DEST_2             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  VALID     UNKNOWN        cobra

LOG_ARCHIVE_DEST_3             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_4             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_5             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_6             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_7             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_8             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_9             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_10            UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_11            UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL

As we see in the above screenshot recovery mode on the standby database is “Managed” 
which means it is contradicting the DelayMins property of Data Guard configuration

Now we need to login to the Standby server and first stop the Redo Apply and then start the redo apply in real time mode.

alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect;

now log switch on primary and then check the recovery_mode in V$ARCHIVE_DEST_STATUS

SQL> select DEST_NAME,DATABASE_MODE,RECOVERY_MODE,PROTECTION_MODE,STATUS,TYPE,DESTINATION from V$ARCHIVE_DEST_STATUS;

DEST_NAME                      DATABASE_MODE   RECOVERY_MODE           PROTECTION_MODE      STATUS    TYPE           DESTINATION
—————————— ————— ———————– ——————– ——— ————– ——————————
LOG_ARCHIVE_DEST_1             OPEN_READ-ONLY  MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE  VALID     LOCAL          /u01/oradata/archives
LOG_ARCHIVE_DEST_2             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  VALID     UNKNOWN        cobra

LOG_ARCHIVE_DEST_3             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_4             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_5             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_6             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_7             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_8             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_9             UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_10            UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL
LOG_ARCHIVE_DEST_11            UNKNOWN         IDLE                    MAXIMUM PERFORMANCE  INACTIVE  LOCAL

Above solution is valid when we have Standby Redo Logs available at the standby database.

 When there is not standby redo logs @standby DB and it is configured with Delay parameter,use following command to @standby for nodelay:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY;

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply