Archive log not deleting within retention policy

I was managing an ORACLE RAC database configured with Oracle Data Guard when I discovered the archive log was not being deleted after the retention policy of 2 days in our backup configuration. Therefore, I started the investigation by collecting some information from the alert log. And the first initiative I took was to verify if the database had been deleting old archives when there was a space pressure in the recovery area as below:


oracle@techdb2 trace]$ grep "Delete" alert_*
alert_tech2.log.19:Deleted Oracle managed file +DATA/tech2/ARCHIVELOG/2023_03_15/thread_1_seq_134192.1349.1131494435
alert_tech2.log.19:Deleted Oracle managed file +DATA/tech2/ARCHIVELOG/2023_03_15/thread_2_seq_100177.20113.1131494437
alert_tech2.log.19:Deleted Oracle managed file +DATA/tech2/ARCHIVELOG/2023_03_15/thread_1_seq_134193.6581.1131494609
alert_tech2.log.19:Deleted Oracle managed file +DATA/tech2/ARCHIVELOG/2023_03_15/thread_1_seq_134195.4276.1131494953
alert_tech2.log.19:Deleted Oracle managed file +DATA/tech2/ARCHIVELOG/2023_03_15/thread_2_seq_100178.2643.1131494955
alert_tech2.log.19:Deleted Oracle managed file +DATA/tech2/ARCHIVELOG/2023_03_15/thread_1_seq_134196.19395.1131495119
alert_tech2.log.19:Deleted Oracle managed file +DATA/tech2/ARCHIVELOG/2023_03_15/thread_1_seq_134197.8226.1131495269
alert_tech2.log.19:Deleted Oracle managed file +DATA/tech2/ARCHIVELOG/2023_03_15/thread_1_seq_134198.23007.1131495443
alert_tech2.log.19:Deleted Oracle managed file +DATA/tech2/ARCHIVELOG/2023_03_15/thread_2_seq_100179.6055.1131495445
alert_tech2.log.19:Deleted Oracle managed file +DATA/tech2/ARCHIVELOG/2023_03_15/thread_1_seq_134199.7598.1131495623 

It is a cheerful and ordinary Oracle database approach to automatically deleting unneeded files.

The next step is to verify if obsolete files have been deleted. Therefore, It is essential to highlight the difference between OBSOLETE and EXPIRED, for that I quote the explanation by Brian Peasland “EXPIRED, RMAN uses this terminology to say that the tape management system has expired the file. It is no longer available. Therefore, the backup piece is expired as well. A status of “obsolete” means the backup piece is still available, but it is no longer needed.”. However, in this specific environment, I noticed that the current retention policy deleted obsolete files older than two days (see 3 directories in the evidence below), which is the current retention policy. 


ASMCMD> cd ARCHIVELOG/
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'

Type Redund Striped Time Sys Name
Y 2023_03_23/
Y 2023_03_24/
Y 2023_03_25/
ASMCMD> pwd
+DATA/tech2/ARCHIVELOG 

To achieve the result of keeping the files deleted after they are no longer needed, I added the command “delete noprompt archivelog until time ‘sysdate-2’ device type disk;” in our backup routine after a crosscheck of the backups and before a crosscheck of the archives. Therefore an example of a FULL DATABASE BACKUP level 0 script for this solution is presented in the following script below:


sql 'alter system archive log current';
backup as compressed backupset section size 40000M database skip readonly format '/techdbbackupdisk/LVL_0_BACKUP_%d_set_%s_piece%p_%T_%U' tag "lvl_0_backup";
backup as compressed backupset archivelog all format '/techdbbackupdisk/LVL_0_BACKUP_%d_set_%s_piece%p_%T_%U' tag "archive_logs";
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
delete noprompt archivelog until time 'sysdate-2' device type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;

I hope this post helps you!

Related posts

Leave a Comment