Oracle Faq

Hi All,

Recently One of our clients observed that archive logs were automatically
getting deleted  from the flash receovery area of the database.
The alert log of the database reported the deleteion of the archive log
as shown below

Tue Aug 7 01:37:12 2007
Deleted Oracle managed file +CNDB2/cndb/archivelog/2007_07_12/thread_2_seq_2001.2595.627775693
Tue Aug 7 02:57:12 2007
Thread 2 advanced to log sequence 2813
Current log# 13 seq# 2813 mem# 0: +CNDB2/cndb/onlinelog/group_13.3352.627502381
Current log# 13 seq# 2813 mem# 1: +CNDB2/cndb/onlinelog/group_13.3350.627502381
Tue Aug 7 02:57:13 2007
Deleted Oracle managed file +CNDB2/cndb/archivelog/2007_07_12/thread_1_seq_1448.2593.627775467
Tue Aug 7 02:57:13 2007
Deleted Oracle managed file +CNDB2/cndb/archivelog/2007_07_12/thread_2_seq_2002.2592.627780809
Tue Aug 7 04:31:39 2007
Thread 2 advanced to log sequence 2814
Current log# 15 seq# 2814 mem# 0: +CNDB2/cndb/onlinelog/group_15.3342.627502483
Current log# 15 seq# 2814 mem# 1: +CNDB2/cndb/onlinelog/group_15.3341.627502483
Thread 2 advanced to log sequence 2815
Current log# 16 seq# 2815 mem# 0: +CNDB2/cndb/onlinelog/group_16.3336.627502501
Current log# 16 seq# 2815 mem# 1: +CNDB2/cndb/onlinelog/group_16.3334.627502503
Tue Aug 7 04:31:44 2007
Deleted Oracle managed file +CNDB2/cndb/archivelog/2007_07_12/thread_2_seq_2003.2591.627782261
Tue Aug 7 04:31:44 2007
Deleted Oracle managed file +CNDB2/cndb/archivelog/2007_07_12/thread_1_seq_1449.2589.627782033
Tue Aug 7 04:31:45 2007
Thread 2 advanced to log sequence 2816
Current log# 14 seq# 2816 mem# 0: +CNDB2/cndb/onlinelog/group_14.3347.627502455
Current log# 14 seq# 2816 mem# 1: +CNDB2/cndb/onlinelog/group_14.3346.627502455
Tue Aug 7 04:31:46 2007
Deleted Oracle managed file +CNDB2/cndb/archivelog/2007_07_13/thread_2_seq_2004.2588.627791415
Tue Aug 7 04:31:50 2007
Thread 2 cannot allocate new log, sequence 2817
Checkpoint not complete
Current log# 14 seq# 2816 mem# 0: +CNDB2/cndb/onlinelog/group_14.3347.627502455
Current log# 14 seq# 2816 mem# 1: +CNDB2/cndb/onlinelog/group_14.3346.627502455
Thread 2 advanced to log sequence 2817
Current log# 13 seq# 2817 mem# 0: +CNDB2/cndb/onlinelog/group_13.3352.627502381
Current log# 13 seq# 2817 mem# 1: +CNDB2/cndb/onlinelog/group_13.3350.627502381
Tue Aug 7 04:31:52 2007
Deleted Oracle managed file +CNDB2/cndb/archivelog/2007_07_13/thread_2_seq_2005.2583.627800641
Tue Aug 7 04:31:53 2007
Thread 2 advanced to log sequence 2818
Current log# 15 seq# 2818 mem# 0: +CNDB2/cndb/onlinelog/group_15.3342.627502483
Current log# 15 seq# 2818 mem# 1: +CNDB2/cndb/onlinelog/group_15.3341.627502483
Tue Aug 7 04:31:54 2007
Deleted Oracle managed file +CNDB2/cndb/archivelog/2007_07_13/thread_2_seq_2006.2579.627809439
Tue Aug 7 04:32:01 2007
Thread 2 advanced to log sequence 2819
Current log# 16 seq# 2819 mem# 0: +CNDB2/cndb/onlinelog/group_16.3336.627502501
Current log# 16 seq# 2819 mem# 1: +CNDB2/cndb/onlinelog/group_16.3334.627502503
Tue Aug 7 04:32:02 2007
Deleted Oracle managed file +CNDB2/cndb/archivelog/2007_07_13/thread_1_seq_1451.2578.62781049

For this database rman was configured to backup the database everyday and the configuartion for rman is as shown

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO ‘%F’; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE 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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/app/oracle/product/dbs/snapcf_CNDB2.f’; # default

From above rman configuration it is clear that archive deletion policy is set none .Also as we know in Oracle 10g, files in flash recovery area get automatically deleted if there is space constraint on flash recovery area.However in this case there was no space constarint and close to 80-90Gb space was available .
However the automatic deletion archive log files was mainly
due to retention policy configured in rman.
As seen from above the rman retention policy
was configured to recovery windows of 5 days.
So rman assumes only the archive log files which are required for the recovery uptil 5 days back are needed
and remaining can to be deleted and so it deletes the OMF archive files prior to five days

Also this overrides the archive log deletion policy of rman.

So, with Oracle 10g database with OMF and flash recovery area configured and with rman configured for backups,

the archive log gets deleted when

1) If there is space pressure in flash recovery area (However there are number of BUGS which does not cause the files to be deleted even when FRA gets full)

2) When rman archive log deletion policy is configured

3) when retention policy of recovery window is configured and when all the unwanted archive log are backed up

4) When archive logs are deleted by rman commands or backup with delete input command is used