sodummy
asked on
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/h
CONFIGURE DEVICE TYPE DISK PARALLELISM 5;
run {
BACKUP format '/u01/backup/rman_backup/h
database plus archivelog format '/u01/backup/rman_backup/h
BACKUP as copy device type DISK TAG = 'hcmpro_spfile' FORMAT '/u01/backup/rman_backup/h
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
db_recovery_file_dest_size
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
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
Fri Dec 2 20:05:31 2011
Deleted Oracle managed file /u01/backup/flash_recovery
Deleted Oracle managed file /u01/backup/flash_recovery
Deleted Oracle managed file /u01/backup/flash_recovery
Deleted Oracle managed file /u01/backup/flash_recovery
Deleted Oracle managed file /u01/backup/flash_recovery
Fri Dec 2 20:05:31 2011
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/h
CONFIGURE DEVICE TYPE DISK PARALLELISM 5;
run {
BACKUP format '/u01/backup/rman_backup/h
database plus archivelog format '/u01/backup/rman_backup/h
BACKUP as copy device type DISK TAG = 'hcmpro_spfile' FORMAT '/u01/backup/rman_backup/h
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
db_recovery_file_dest_size
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
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
Fri Dec 2 20:05:31 2011
Deleted Oracle managed file /u01/backup/flash_recovery
Deleted Oracle managed file /u01/backup/flash_recovery
Deleted Oracle managed file /u01/backup/flash_recovery
Deleted Oracle managed file /u01/backup/flash_recovery
Deleted Oracle managed file /u01/backup/flash_recovery
Fri Dec 2 20:05:31 2011
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi
please disregard this line
AND
CONFIGURE RETENTION POLICY TO REDUNDANCY
please disregard this line
AND
CONFIGURE RETENTION POLICY TO REDUNDANCY
ASKER
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 ??
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 ??
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.
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.
ASKER
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/h
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.
ASKER
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
If deleted from cron I don't think that alert log captured that information.
This tells from the alert log file which reads below like that :----
Alert Log:----------
Deleted Oracle managed file /u01/backup/flash_recovery
If deleted from cron I don't think that alert log captured that information.
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
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
ASKER
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";
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";
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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,
V$FLASH_RECOVERY_AREA_USAG
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,
V$FLASH_RECOVERY_AREA_USAG
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
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
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.