RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

My database is oracle12c(12.1.0.2.0) version. One of my database obsolete archive log files were not getting deleted and getting the below error..

RMAN>delete noprompt force archivelog until time 'sysdate-14'; RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=+DATA/XXX_AZ/ARCHIVELOG/2016_09_28/thread_2_seq_12062.22129.923740541 thread=2 sequence=12062 RMAN>

I followed the temp solution to delete the old archive log files manually.

Here is the current RMAN setting...

RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name FRONTEND_AZ are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u04/rmanbackup/FRONTEND1/auto_%d_%F.ctl'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+data/xxx_az/controlfile/snap_XXX.ctl'; RMAN>

Temporary  solution :

Step 1  Change the Archive log deletion policy to STANDBY.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY; new RMAN configuration parameters: CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY; new RMAN configuration parameters are successfully stored RMAN-08591: WARNING: invalid archived log deletion policy

Step 2  Delete the obsolete archive log files.

RMAN>delete noprompt force archivelog until time 'sysdate-14'; archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12858.23739.924517385 RECID=73224 STAMP=924517385 deleted archived log archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12859.23741.924518285 RECID=73230 STAMP=924518284 deleted archived log archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12860.23743.924519185 RECID=73236 STAMP=924519185 deleted archived log archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12861.23745.924520085 RECID=73242 STAMP=924520084 deleted archived log archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12862.23747.924520985 RECID=73248 STAMP=924520985 deleted archived log archived log file name=+DATA/XXXXXXX_AZ/ARCHIVELOG/2016_10_06/thread_2_seq_12863.23749.924521885 RECID=73254 STAMP=924521884 Deleted 21176 objects RMAN-08591: WARNING: invalid archived log deletion policy RMAN>

Now archive log files are getting deleted successfully!!

Step 3  Change the RMAN deletion policy back to original.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; old RMAN configuration parameters: CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY; new RMAN configuration parameters: CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; new RMAN configuration parameters are successfully stored

Step 4  Verify the RMAN settings.

RMAN> show all; RMAN configuration parameters for database with db_unique_name XXXXXXX_AZ are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u04/rmanbackup/XXXXXXX1/auto_%d_%F.ctl'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+data/XXXXXXX_az/controlfile/snap_XXXXXXX.ctl'; RMAN>

Parmanent   solution :  The golden gate was implemented previously for data replication to upgrade to  Oracle 12c. But Golden Gate is no more used and still there are couple of Golden Gate extract process is running and it caused the archive log deletion issue.

The currently running Golden Gate extract process stopped and archive log deletion job started deleting all the obsolete archive log files.

Here are the steps i followed to currently running extract process :

GGSCI (hostname) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT ABENDED FEEXT 00:00:00 5234:47:30 EXTRACT RUNNING FEPMP 00:00:00 00:00:05 EXTRACT STOPPED GWEXT 00:00:02 4671:09:27 EXTRACT STOPPED GWPMP 00:00:00 4671:09:09 EXTRACT STOPPED S 00:00:01 6650:16:15

ggsci> delete extract FEEXT  ggsci> stop extract FEPMP  ggsci> delete extract FEPMP  ggsci> delete extract GWEXt  ggsci> delete extract GWPMP  ggsci> delete extract S  ggsci> stop mgr  It will prompt (y/n)---- y and continue (press enter)  ggsci> delete mgr  ggsci> info all