OEM alert for DataGuard status is “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
Make DBA Life Easy 