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

We received the OEM alert by complaining DataGuard status with error as “ORA-16826: apply service state is inconsistent with the DelayMins property”.

Oracle official explanation is as below :

$oerr ora 16826
16826, 0000, "apply service state is inconsistent with the DelayMins property"
// *Cause: This warning was caused by one of the following reasons:
// - The apply service was started without specifying the real-time
// apply option or without the NODELAY option when the DelayMins
// property was set to zero.
// - The apply service was started with the real-time apply option or
// with the NODELAY option when the DelayMins property was set to
// a value 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.

It seems the “DelayMins” property is not matching the option used in “alter database recover managed standby database …..”. Let’s have a detail check.

DGMGRL> show configuration;
..
Protection Mode: MaxPerformance
 Databases:
 PRIMYDB - Primary database
 STDBYDB - Physical standby database
 Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED
Configuration Status:
WARNING

Primary database side is good, the warning is from standby database side.

DGMGRL> show database verbose "STDBYDB";

Database - STDBYDB

 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 13 minutes 1 second (computed 0 seconds ago)
 Apply Rate: 61.33 MByte/s
 Real Time Query: OFF
 Instance(s):
 STDBYDB1
 STDBYDB2 (apply instance)
 
Database Warning(s):
 ORA-16826: apply service state is inconsistent with the DelayMins property
DGMGRL> show database "STDBYDB" "DelayMins"
 DelayMins = '0'
DGMGRL>

On standby database applying instance STDBYDB2. From alert.log, we can see the standby database “not using Real Time Apply”.

...
..
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (STDBYDB2)
Thu Jan 12 16:00:06 2017
MRP0 started with pid=55, OS id=66078
MRP0: Background Managed Standby Recovery process started (STDBYDB2)
 started logmerger process
Thu Jan 12 16:00:11 2017
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 32 slaves
Thu Jan 12 16:00:15 2017
Block change tracking file is current.
Starting background process CTWR
Thu Jan 12 16:00:15 2017
CTWR started with pid=89, OS id=66416
Block change tracking service is active.
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +FRA/stdbydb/archivelog/2017_01_12/thread_1_seq_328339.32376.933091003
Media Recovery Log +FRA/stdbydb/archivelog/2017_01_12/thread_2_seq_555739.2592.933091137
Thu Jan 12 16:00:17 2017
Completed: alter database recover managed standby database disconnect from session
...
..

Move on to any  primay database instance and run below query, we can see the same — the standby database “not using Real Time Apply”

SQL> select DEST_NAME,STATUS,DATABASE_MODE,RECOVERY_MODE from V$ARCHIVE_DEST_STATUS where status!='INACTIVE';

DEST_NAME           STATUS      DATABASE_MODE   RECOVERY_MODE 
------------------- ----------- --------------  --------------
LOG_ARCHIVE_DEST_1  VALID       OPEN IDLE
LOG_ARCHIVE_DEST_2  VALID       MOUNTED-STANDBY MANAGED

On standby database, stop the Redo Apply and then start the redo apply in real time mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Check standby database alert.log again, we can see standby database in “Real Time Apply” by using standby online logs:

...
..
Thu Jan 12 23:01:56 2017
Managed Standby Recovery Canceled (STDBYDB2)
Completed: alter database recover managed standby database cancel
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (STDBYDB2)
Thu Jan 12 23:02:00 2017
MRP0 started with pid=54, OS id=106637
MRP0: Background Managed Standby Recovery process started (STDBYDB2)
 started logmerger process
Thu Jan 12 23:02:05 2017
Managed Standby Recovery starting Real Time Apply
...
..
Thu Jan 12 23:02:09 2017
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Thu Jan 12 23:02:39 2017
Media Recovery Log +FRA/stdbydb/archivelog/2017_01_12/thread_4_seq_273573.33789.933116469
Media Recovery Waiting for thread 1 sequence 328381 (in transit)
Recovery of Online Redo Log: Thread 1 Group 101 Seq 328381 Reading mem 0
 Mem# 0: +DATA2/stdbydb/onlinelog/group_101.1037.903651087
....
...

On any primary database instance, run below sql, we can see the same — standby database in “Real Time Apply” by using standby online logs:

SQL> select DEST_NAME,STATUS,DATABASE_MODE,RECOVERY_MODE from V$ARCHIVE_DEST_STATUS where status!='INACTIVE';

DEST_NAME           STATUS      DATABASE_MODE   RECOVERY_MODE 
------------------- ----------- --------------  --------------
LOG_ARCHIVE_DEST_1  VALID       OPEN IDLE
LOG_ARCHIVE_DEST_2  VALID       MOUNTED-STANDBY REAL TIME APPLY

Finally check DataGuard configurations:

DGMGRL> show configuration;

...

Protection Mode: MaxPerformance
 Databases:
 PRIMYDB - Primary database
 STDBYDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database verbose "STDBYDB";

Database - STDBYDB

 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 0 seconds (computed 0 seconds ago)
 Apply Rate: 8.93 MByte/s
 Real Time Query: OFF
 Instance(s):
 STDBYDB1
 STDBYDB2 (apply instance)
 
Database Warning(s):
SUCCESS
Advertisements
This entry was posted in DataGuard, GI and RAC, ORA- ERRORS and tagged , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s