How to work around RMAN-08120: warning: archived log not deleted, not yet applied by standby

Problem scenario:

* You have a primary database with two standby databases.
* One of them is functional, the other one is about to be rebuilt.
* Archived redo logs are piling up on the primary.

You have already set the log_archive_dest_n to DEFERRED on the primary site, and this will prevent logs from being shipped to the standby database that is about to be rebuilt.

The reason for this is explained in Doc ID 1380368.1 on My Oracle Support: Oracle will, by default, also consider deferred locations, before deleting archive logs from the primary.

Solution:
This can be changed by setting a hidden parameter, _deferred_log_dest_is_valid, from TRUE to FALSE.

To verify that this worked as intended, I listed a couple of the archivelogs on the primary:

RMAN> list archivelog sequence between 110880 and 110881;

List of Archived Log Copies for database with db_unique_name PRODDB01
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
173550  1    110880  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc

173551  1    110881  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc

Now try to delete them from the primary server:

RMAN> delete archivelog sequence between 110880 and 110881;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=78 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=723 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=14 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=295 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc thread=1 sequence=110880
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc thread=1 sequence=110881

Oracle doesn't allow you to delete them, since they haven't been applied to all standby destinations.

To work around this, set the hidden parameter:

alter system set "_deferred_log_dest_is_valid" = FALSE scope=both;

Try deleting them once more shows that Oracle now allows you to do so:

RMAN>  delete archivelog sequence between 110880 and 110881;

List of Archived Log Copies for database with db_unique_name proddb01
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
173550  1    110880  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc

173551  1    110881  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc RECID=173550 STAMP=1014076575
Deleted 1 objects

deleted archived log
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc RECID=173551 STAMP=1014076710
Deleted 1 objects

Use

delete noprompt archivelog sequence between 110880 and 110881;

to avoid having to confirm the delete operation.