archivelogs deletion

[satish]

Dear Erman,

Need your help.In our environment,archivelogs are not getting deleted automatically.
we are on 12.1.0 and we dont use flashback technology and we dont use flashrecoveryarea

Recent changes:
changed redundancy to recovery window

configure retention policy to redundancy 15; --->changed as below
configure retention policy to recovery window of 20 DAYS;
please find the below L0 and L1 backup scripts attached

Thanks for all the support

scripts.txt

[ErmanArslansOracleBlog]

Your script has the following lines for deleting the backups;

DELETE FORCE NOPROMPT OBSOLETE;
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 3 TIMES to disk;

So , you are deleting obsolete backups in each backup run..
+ you are deleting archivelog backups if they are already backed up 3 times..

You have level0 + level1 backup and you have a rman retention policy, which will directly effeect the deletion of obsoletes.. (configure retention policy to recovery window of 20 DAYS;)

So probably, your backup schedule is not aligned with your deletion policy.

1)Check your scheduling accordingly..
2)Also , ensure that, your backups are completed successfully.
3)If you have a standby db, this may also prevent archivelog deletion.. In default conf, iF archivelogs are not applied to standby, they won't be deleted...

[satish]

Thanks for the updates erman.so our scripts has no issues

We dont have standby
backups are completing successfully(verified the logs)
Below is our backup schedule

00 01 * * 0 sh /home/rman/scripts/rman_backup_DB.sh >> /home/rman/scripts/rman_cron.log
00 01 * * 1,2,3,4,5,6 /home/rman/scripts/rman_incr_backup_DB.sh >> /home/rman/scripts/rman_cron.log
*/40 03-23 * * * sh /home/rman/scripts/rman_backup_arch.sh >> /home/rman/scripts/rman_cron.log


Thank you

[ErmanArslansOracleBlog]

I already send you 3 things to check.

Backups are never obsolete if they are still needed to meet the retention policy..

For a recovery window based retention policy; the backups are retained if they are needed to satisfy the recovery window.

For a redundancy based retention policy; the backups are retained as long as they are required to satisfy the redundancy requirement.

So please check your schedule and retention policy accordingly.. They should be in align, compatible..

Also send me the log file of a backup run of yours.. (a backup run, which should delete the obsolete archivelogs)

[satish]

Thanks for the update erman.Please find the log

backup.zip

[ErmanArslansOracleBlog]

Hi Satish,

your archivelogs are not delete because of -> RMAN-08138: WARNING: archived log not deleted - must create more backups.

It is related with your retention policy.

What is the output of "show ARCHIVELOG DELETION POLICY;"?

 

[satish]

Thanks for the update erman.

In scripts,we have below

DELETE FORCE NOPROMPT OBSOLETE;
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 3 TIMES to disk;



retention policy

[oraprod@node2 rman]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 9 15:17:01 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRODDB (DBID=683872528)

RMAN> show ARCHIVELOG DELETION POLICY;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PRODDB are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

RMAN>

[ErmanArslansOracleBlog]

Hi,

You have 2 lines for deleting backups.

in the first line, your scripts  says delete all obsolete backups (including archivelogs)
in the second line, your scripts says delete archivelogs backed up 3 times..

So, even if you don't have any obsolete archivelog, your script may still delete some archivelogs (if you have additional 3 backups for those archivelogs).

According your log, you don't have 3 backups for archivelogs and that's why they are not getting deleted.

[satish]

There should be something wrong

The atchivelogs are from JUL 5th and my recovery window is only 20 days

[ErmanArslansOracleBlog]

Those archivelogs are still needed for recovery.. (according to your recovery window)

Maybe you don't have a proper database backup since Jul 5th.. That 's why, maybe those archivelogs are still needed for being align with your recovery window.. did you check that? did you check the health of your database backups?

So probably, those archivelogs are not obsoleted (since you don't have a proper backup since Jul 5)
Also, you probably don't have 3 more backups for those archivelogs, that's why , even your additional "backed-up 3 times" style backup command can not delete them..

Please check your env accordingly.

[satish]

Dear Erman,

Thanks for the update.

SQL> sho parameter CONTROL_FILE_RECORD_KEEP_TIME

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     16
SQL>


and the configuration for retention policy is as below

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 20 DAYS;

Is this configuration related to this issue?

i think control_file_record_keep_time   should be more than recoverywindow right


Thanks for all the support

[ErmanArslansOracleBlog]

yes..

But please do your analysis based on my last update:

Those archivelogs are still needed for recovery.. (according to your recovery window)

Maybe you don't have a proper database backup since Jul 5th.. That 's why, maybe those archivelogs are still needed for being align with your recovery window.. did you check that? did you check the health of your database backups?

So probably, those archivelogs are not obsoleted (since you don't have a proper backup since Jul 5)
Also, you probably don't have 3 more backups for those archivelogs, that's why , even your additional "backed-up 3 times" style backup command can not delete them..

[satish]

Thanks for the update erman.

We have done the analysis.We have changed the value from 16 to 28.Then we have ran report obsolete but the old backup pieces are not shown.Then we have cataloged all pieces and again ran report obsolete which then showed all the old backup pieces which are eligible for deletion.

we then ran delete obsolete and the old backup pieces were deleted.

Thanks for all the support

[ErmanArslansOracleBlog]

So those backups were not on your catalog right? If that 's the case, your issue is normal..

[satish]

Dear Erman,

Thanks for the udpate.You are right,those are not on catalog

When setting CONTROL_FILE_RECORD_KEEP_TIME, keep in mind that level 0 backups can go beyond the recovery window period to keep integrity

what does this statement mean?could you please clarify

Thanks for all the support

[ErmanArslansOracleBlog]

It is easy..

If you get a full level0 backup once in every week and if you have a retention policy as recovery windows of
14 days, then you would have a control_file_record_keep_time of 22 ( 14+7+1).

I m closing this topic..

If you need to know more, read Oracle Support .. Here -> Relation between RMAN retention period and control_file_record_keep_time [ID 397269.1]