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!
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)