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

When checking the configuration status in DGMGRL receiving the below error.

DGMGRL> show configuration

Configuration - DG_PROD

  Protection Mode: MaxPerformance
  Databases:
    Prod- Primary database
    Stdby - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> show database stdby

Database - Stdby 

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   (unknown)

  Apply Lag:       21 hours 51 minutes 5 seconds (computed 40 seconds ago)

  Apply Rate:      (unknown)

  Real Time Query: OFF

  Instance(s):

    ENTP1

  Database Warning(s):

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

Database Status:

WARNING

As per the metalink document 1608166.1 we need to follow below to resolve the issue.

Solution:

*******

You need to restart the MRP with real-time apply mode.

DGMGRL> edit database stdby set state=apply-off;

DGMGRL> edit database stdby set state=apply-on;

or

SQL>alter database recover managed standby database cancel;

SQL> alter database recover managed standby database using current logfile disconnect FROM SESSION PARALLEL 10;

Here we need to start the MRP with parallel as there is a gap between standby and primary. And make sure Standby redo logs files are created on standby.

But we followed the below simple approach to resolve the issue. Just disable and again enable the configuration that will start the MRP with parallel.

DGMGRL> show configuration

Configuration - DG_PROD

  Protection Mode: MaxPerformance

  Databases:

    Prod- Primary database

    Stdby - Physical standby database

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

Fast-Start Failover: DISABLED

Configuration Status:

WARNING

 

DGMGRL> disable configuration;

Disabled.

DGMGRL> show configuration;

Configuration - DG_PROD

  Protection Mode: MaxPerformance

  Databases:

    Prod - Primary database

    Stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED 

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration;

Configuration - DG_PROD

  Protection Mode: MaxPerformance

  Databases:

    Prod - Primary database

    Stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

Warning: ORA-16610: command ‘Broker automatic health check’ in progress

DGMGRL> show configuration;

Configuration - DG_PROD

  Protection Mode: MaxPerformance

  Databases:

    Prod - Primary database

    Stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

Alert log:

***********

Only allocated 111 recovery slaves (requested 128)

Parallel Media Recovery started with 111 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Mon Jun 13 21:11:07 2016

Media Recovery Log /oraEMC/prod/arch1/Prod_1_89006_51323279.ARC

Mon Jun 13 21:11:07 2016

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE 

Happy Learning :)

How to delete the flashback logs in Oracle

SQL> select name,open_mode,flashback_on from v$database;

NAME      OPEN_MODE            FLASHBACK_ON
--------- -------------------- ------------------
Prod   READ WRITE           YES


SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                                    0                         0               0          0
REDO LOG                                            0                         0               0          0
ARCHIVED LOG                                   0                         0               0          0
BACKUP PIECE                                     0                         0               0          0
IMAGE COPY                                         0                         0               0          0
FLASHBACK LOG                          49.53                     39.52           27         0
FOREIGN ARCHIVED LOG                 0                         0               0          0
AUXILIARY DATAFILE COPY            0                         0               0          0

8 rows selected.

SQL> col name for a20
select name, (space_limit/1024/1024/1024) ||'GB' as Space_Limit, (space_used/1024/1024/1024)||'GB' as Space_Used from v$recovery_file_dest;
SQL>
NAME                 SPACE_LIMIT                                SPACE_USED
-------------------- ------------------------------------------ ------------------------------------------
+FEDEX_FLASH         100GB                                      49.52GB

SQL> select * from v$restore_point;

no rows selected

SQL> select sum(FLASHBACK_SIZE)/1024/1024/1024 from V$FLASHBACK_DATABASE_LOG;

SUM(FLASHBACK_SIZE)/1024/1024/1024
----------------------------------
                              49.5

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FLASH/prod/
Oldest online log sequence     825
Next log sequence to archive   832
Current log sequence           832

Reduce the space for FRA so it will delete the flashback logs automatically the same you can observe in alertlog.

SQL>  alter system set db_recovery_file_dest_size=19g scope=both sid='*';

System altered.

Alertlog Details:
*****************
Sat Apr 30 06:04:29 2016
ALTER SYSTEM SET db_recovery_file_dest_size=19G SCOPE=BOTH SID='*';
Sat Apr 30 06:04:29 2016
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_1.3351.896398699
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_4.8287.896533879
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_2.9436.896398703
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_5.7796.897271709
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_6.7771.897273413
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_7.7810.897275073
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_8.7823.897283099
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_9.4000.897289741
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_12.9334.910404199
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_13.9355.910405725


SQL> select sum(FLASHBACK_SIZE)/1024/1024/1024 from V$FLASHBACK_DATABASE_LOG;

SUM(FLASHBACK_SIZE)/1024/1024/1024
----------------------------------
                              17.5

Happy Learning :)

RMAN-08591: WARNING: invalid archived log deletion policy

Receiving the below error in backup log

Error Details:
--------------
RMAN-08591: WARNING: invalid archived log deletion policy

Cause:
------
If archive log location is set to FRA, then there might be a chance of the deleting the archives automatically when the space pressure in FRA.
In that case at least one of the destination standby must be set as a "MANDATORY" destination.

Solution:
----------

To eliminate the RMAN warning message, at least one archive destination must be set as a mandatory destination.

1. Log in to the broker command line utility

[oracle@prd-srv01 log]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration - prod_dg

  Protection Mode: MaxPerformance
  Members:
  prod - Primary database
    stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 7 seconds ago)

2. For the standby site check the "Binding" property

DGMGRL> show database verbose stdby

Database - stdby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      11 minutes (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 518.00 KByte/s
  Active Apply Rate:  457.00 KByte/s
  Maximum Apply Rate: 17.62 MByte/s
  Real Time Query:    ON
  Instance(s):
    stdby

  Properties:

----------
----------
 Binding        = 'optional'
----------
----------

3. Set the "Binding" property to MANDATORY

DGMGRL> edit database stdby set property Binding='mandatory';
Property "binding" updated

DGMGRL> exit

Once the broker configuration has set a standby site as a mandatory destination, then RMAN will not report this error again.

Reference:
----------
Data Guard Physical Standby - RMAN configure archivelog deletion policy reports RMAN-08591 (Doc ID 1984064.1)

Happy Learning :)