Oracle 10g Database deleted the Archive Logs itself Not considering the RMAN Retention Policy

1. I am at Orale 10g on Linux redhet  4.
2. RMAN backup is used for Backup and Archive log Deletion
3. We have to keep the archive logs for 7 days to the disk .Archive log NOT to be deleted before 7 days (sysdate-7 ) but the archive logs are actually deleted by itself. ON Dec 02nd Archive logs Deleted which are created on the same Day( i.e DEC 02  See the Alert log Entry). Why the archive logs deleted where it is consdered to keep for sydate-7 Days as per RMAN configuration ???
4. I checked the RMAN backup logs and it is not deleted from the RMAN backup and it appears that it is getting deleted by itself. I am using the flashback destination for archive logs as well as flash back recovery.

Here is the RMAN retension Policy:-
CONFIGURE RETENTION POLICY TO REDUNDANCY 7;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/rman_backup/hcmpro/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 5;
run {
BACKUP format '/u01/backup/rman_backup/hcmpro/DB_%d_%T_%U'
database plus archivelog format '/u01/backup/rman_backup/hcmpro/arc_%d_%s_%p_%t';
BACKUP as copy device type DISK TAG = 'hcmpro_spfile' FORMAT '/u01/backup/rman_backup/hcmpro/spfile_%d_t%t_s%s_p%p' spfile;
restore database validate;
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt archivelog until time "sysdate-7";
delete noprompt obsolete;
-----------------------------------------------------------------------------------------
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST

SQL>desc db_recovery_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/backup/flash_recovery_area
db_recovery_file_dest_size           big integer 400G
SQL>
-----------------------------------------------------------------------------
RMAN BACKUP LOGS:-

channel ORA_DISK_2: starting piece 1 at 02-DEC-11
channel ORA_DISK_3: starting compressed archive log backupset
channel ORA_DISK_4: specifying archive log(s) in backup set
input archive log thread=1 sequence=39238 recid=70250 stamp=964807728
input archive log thread=1 sequence=39239 recid=70253 stamp=964809039
input archive log thread=1 sequence=39240 recid=70257 stamp=964809527
input archive log thread=1 sequence=39241 recid=70258 stamp=964809528
input archive log thread=1 sequence=39242 recid=70260 stamp=964811331
input archive log thread=1 sequence=39243 recid=70262 stamp=964811457

Alert Log:--
-------------------------------------------------------------------------------------------------------
Thread 1 cannot allocate new log, sequence 42015
Checkpoint not complete
  Current log# 3 seq# 42014 mem# 0: /u01/oracle/oradata/hcmpro/redo03.log
Fri Dec  2 20:05:30 2011
Thread 1 advanced to log sequence 42015 (LGWR switch)
  Current log# 1 seq# 42015 mem# 0: /u01/oracle/oradata/hcmpro/redo01.log
Fri Dec  2 20:05:31 2011
Deleted Oracle managed file /u01/backup/flash_recovery_area/hcmpro/archivelog/2011_12_02/o1_mf_1_39240_7fkdn7qh_.arc
Deleted Oracle managed file /u01/backup/flash_recovery_area/hcmpro/archivelog/2011_12_02/o1_mf_1_39241_7fkdn81m_.arc
Deleted Oracle managed file /u01/backup/flash_recovery_area/hcmpro/archivelog/2011_12_02/o1_mf_1_39242_7fkgdm70_.arc
Deleted Oracle managed file /u01/backup/flash_recovery_area/hcmpro/archivelog/2011_12_02/o1_mf_1_39243_7fkgjkob_.arc
Deleted Oracle managed file /u01/backup/flash_recovery_area/hcmpro/archivelog/2011_12_02/o1_mf_1_39244_7fkj8tjv_.arc
Fri Dec  2 20:05:31 2011


sodummyAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>db_recovery_file_dest_size           big integer 400G

By definition, Oracle Managed Files will delete files when it needs to.  It is 'managing'.  By chance did it need to free up space?  How close to 400 Gig are you in the FRA?
0
 
Aaron ShiloChief Database ArchitectCommented:
hi

it seems that you didnt configure the RETENTION POLICY for seven days but for REDUNDANCY 7

this will keep data for 7 days.
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
which defines a window of time extending backward from the present. The database must be recoverable to any point of time in that window.
AND
 CONFIGURE RETENTION POLICY TO REDUNDANCY

once you use the : "delete noprompt obsolete;" then all obsolete files are deleted and since you use the "database plus archivelog " then your archivelogs are backedup and left obsolete.

slightwv : oracle will not delete archive log files when it runs out of room.
                 you will receive an error that the archiver failed to archive since there isent space left.
                 and of-course the system will basically freeze.
0
 
Aaron ShiloChief Database ArchitectCommented:
hi

please disregard this line

AND
 CONFIGURE RETENTION POLICY TO REDUNDANCY

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
sodummyAuthor Commented:
So, ashilo,
You are telling that "CONFIGURE RETENTION POLICY TO REDUNDANCY 7;" will not care and will delete the archive logs on the same day ?

and that is the root cause which deletes the archive logs ??? You are not closer to my question.
The issue is I am trying to find that why the archives deleted on the same day ??
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
i won't think the files from FRA deteles automatically it will only set it to obsolete based on the redundancy you have set.

can you provide the output of show all from rman i.e
rman>show all

and also see the what has been set for rentention policy to recovery window in rman.

there are two things in retention policy i.e redundancy and recovery window

pls check this doc for more details.

http://docs.oracle.com/cd/B14117_01/server.101/b10735/setup.htm

Also check if it is unix system see any cron job has been set for deleting the archivelogs.

0
 
sodummyAuthor Commented:

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 7;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/rman_backup/hcmpro_backup/%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 5;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/product/10.2.0/dbs/snapcf_hcmpro.f'; # default

0
 
sodummyAuthor Commented:
There is no cron job which is deleting it.
This tells from the alert log file which reads below like that :----

Alert Log:----------
Deleted Oracle managed file /u01/backup/flash_recovery_area/hcmpro/archivelog/2011_12_02

If deleted from cron I don't think that alert log captured that information.
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
I think you have not configured the retention policy recovery window which will tell the rman to keep the old backups for no of days i.e 7.

and you are forcing the rman to delete the obsolete archive logs, so it is deleteing the same days log after backup, has it will be marked obsolete.

And also check the space in FRA has slightwv pointed out

check this docs for difference between redundancy and recovery window of rman retention policy.

http://docs.oracle.com/cd/B19306_01/backup.102/b14191/advmaint.htm

http://dbaforums.org/oracle/index.php?showtopic=15040
0
 
sodummyAuthor Commented:
RMAN has not deleted it after the backup and my RMAN script is working fine.
RMAN writes in the log file if deleted by RMAN backup and I don't see these logs deleted by RMAN backup.Every night it only deletes the archive logs which are older than 7 days and it writes it in the RMAN backup logs what archive it deletes.At that day it deleted some old archive logs only and all are older than 7 days.

I am keeping the archive logs for sysdate-7 , Please see below:-

delete noprompt archivelog until time "sysdate-7";

0
 
absnegConnect With a Mentor Commented:
From Oracle docs: "Using a flash recovery area simplifies the ongoing administration of your database by automatically naming recovery-related files, retaining them as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose."

Given that archive log list shows you are using the FRA to store you archivelogs, Oracle deleted them as indicated above. If you really want to keep your archivelogs safe, I suggest you change your retention policy in RMAN and move your archivelogs out to disk.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
SQL > ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='NEW_DISK_DIR';

0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
>>I am keeping the archive logs for sysdate-7 <<

this will work untill you configured your retention policy propely, we all have pointed out to you to go through the oracle doc about FRA and the rman configurations for retention.

The REDUNDANCY parameter of the CONFIGURE RETENTION POLICY command specifies how many full or level 0 backups of each datafile and control file that RMAN should keep.

Archived redo log files in the fast recovery area, the database retains them as long as possible and automatically deletes eligible logs when additional disk space is required. so please check that there is enough space in FRA.

Select File_Type, Percent_Space_Used, Percent_Space_Reclaimable, Number_Of_Files From
V$FLASH_RECOVERY_AREA_USAGE;
0
 
slightwv (䄆 Netminder) Commented:
Experts,

The message in the alert log "Deleted Oracle managed file"  is NOT coming from RMAN.  Therefore it has nothing to do with the RMAN side of the house.

This is an Oracle Managed File message.  RMAN isn't the one deleting them.

The other important text from http:#a37269893 that should be BOLD:

... and space is needed for some other backup and recovery-related purpose
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.