Home » RDBMS Server » Backup & Recovery » RMAN Backups are not deleting (Window 2012 Oracle 11 .2. 0.4)
RMAN Backups are not deleting [message #679008] Tue, 28 January 2020 07:12 Go to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
Hi,

I have problem with deleteing the old RMAN backups .

My retention policy is

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

I am taking level 0 backup on fridays and other days level 1 backup and I need to delete all the backups before the level 0 backup

Below is my backup script

crosscheck backup;
crosscheck copy;
crosscheck archivelog all;
delete noprompt expired backup ;
delete noprompt obsolete;
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-1' backed up 1 times TO DEVICE TYPE DISK;
sql 'alter system archive log current';
run {
backup incremental level = 0 database tag LEVELONE format 'F:\Backup\%d_%T_%s_%p_WEEKFULL';
backup current controlfile;
backup archivelog all not backed up 1 times tag LEVELONE format 'F:\Backup\%d_%T_%s_%p_ARCFULL';
}
Exit

=====

crosscheck backup;
crosscheck copy;
crosscheck archivelog all;
delete noprompt expired backup ;
delete noprompt obsolete;
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-1' backed up 1 times TO DEVICE TYPE DISK;
sql 'alter system archive log current';
run {
backup incremental level = 0 database tag LEVELONE format 'F:\Backup\%d_%T_%s_%p_WEEKFULL';
backup current controlfile;
backup archivelog all not backed up 1 times tag LEVELONE format 'F:\Backup\%d_%T_%s_%p_ARCFULL';
}
Exit


Kindly help
Re: RMAN Backups are not deleting [message #679009 is a reply to message #679008] Tue, 28 January 2020 07:14 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
You are running the DELETE before the BACKUP. Try changing the order.
Re: RMAN Backups are not deleting [message #679011 is a reply to message #679008] Tue, 28 January 2020 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why didn't you feedback in your previous topic?

Re: RMAN Backups are not deleting [message #679013 is a reply to message #679011] Tue, 28 January 2020 08:18 Go to previous messageGo to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
Hi,

Realy Sorry for that. I have modified the query to imporve the performance ,Replaced NOT IN with NOT EXISTS


Re: RMAN Backups are not deleting [message #679014 is a reply to message #679009] Tue, 28 January 2020 08:20 Go to previous messageGo to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
Hi,

Still the backups are not deleted , There is no expired backup found in the repositiry

Thanks,
Raj
Re: RMAN Backups are not deleting [message #679018 is a reply to message #679014] Tue, 28 January 2020 08:47 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
rajesh_b wrote on Tue, 28 January 2020 08:20
Hi,

Still the backups are not deleted , There is no expired backup found in the repositiry

Thanks,
Raj
That's because you have recovery window of 7 days. A backup is not considered obsolete if it is still needed for recovery, regardless of its age.

============================================================================

ALL recovery MUST begin with a full (full or incremental 0) backup. That means that the most recent full backup cannot be obsolete, no matter how old it is. Neither can the most recent full backup that precedes the beginning of your recovery window

If you take a full backup on Jan 1, and then only incremental level 1 backups the rest of the year, that full backup from Jan 1 will never be obsolete.

rman obsolescence deletion

Day 1 - Level 0
Day 2 - Level 1
Day 3 - Level 1
Day 4 - Level 1
Day 5 - Level 1
Day 6 - Level 1
Day 7 - Level 1
Day 8 - Level 0
Day 9 - Level 1
Day 10 - Level 1
Day 11 - Level 1
Day 12 - Level 1
Day 13 - Level 1
Day 14 - Level 1

On day 14, your 7 day window is back to day 7. A recovery to that day must begin with the restore of the Day 1 level 0 backup. So it cannot be obsolete. Some of the level 1 backups between Day 1 and Day 6 could be obsolete, depending on if they are differential or cumulative, and what is also going on with archivelogs.

If you have access to MOS, you might want to review notes 462978.1 and 463875.1.

============================================================================
Re: RMAN Backups are not deleting [message #679019 is a reply to message #679008] Tue, 28 January 2020 08:52 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
Also, I find your script statement:

delete noprompt archivelog all completed before 'sysdate-1' backed up 1 times TO DEVICE TYPE DISK;
to be bothersome. I never do an rman DELETE based on sysdate. Doing so means I'm highly dependent on my job always running on schedule. While it will run on schedule 99.999999% of the time, that one time it doesn't is a problem I can do without. Instead, why not just

backup archivelog all delete all input;
Re: RMAN Backups are not deleting [message #679021 is a reply to message #679019] Tue, 28 January 2020 09:00 Go to previous messageGo to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
Thank you for the update.

In my scenario I am doing level 0 backup on Friday (For exampe 17th Jan 2020)
and level 1 on Sat,Sun,Mon,Tues,Wed,Thursday.
 
And the next Friday (24 Jan 2020 )I am doing again Level 0. At that time whether the First Level 0 (on 17th Jan) will be obsolete so that I can 
delete the old backup?

Re: RMAN Backups are not deleting [message #679023 is a reply to message #679021] Tue, 28 January 2020 10:16 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
rajesh_b wrote on Tue, 28 January 2020 09:00
Thank you for the update.

In my scenario I am doing level 0 backup on Friday (For exampe 17th Jan 2020)
and level 1 on Sat,Sun,Mon,Tues,Wed,Thursday.
 
And the next Friday (24 Jan 2020 )I am doing again Level 0. At that time whether the First Level 0 (on 17th Jan) will be obsolete so that I can 
delete the old backup?

No, when you take that second level 0, the first is NOT obsolete, as I explained earlier. Your retention policy is " RECOVERY WINDOW OF 7 DAYS". When you take a level 0 backup, you STILL have to have those earlier backups, including the previous level 0, in order to be able to recover to any time in the last 7 days, as specified by your retention policy. Go back and re-read my first post on this. And trust Uncle Oracle to know when an rman backup is no longer needed and thus becomes obsolete and eligible for deletion by DELETE OBSOLETE.

And why are you doing the CROSSCHECK and DELETE EXPIRED? Do you understand the implications? Do you understand what those two commands actually do?

Re: RMAN Backups are not deleting [message #679045 is a reply to message #679023] Thu, 30 January 2020 08:53 Go to previous messageGo to next message
rajesh_b
Messages: 20
Registered: December 2019
Junior Member
 Thank you for the update . Now I have got little idea about the RECOVERY WINDOW and RETENTION POLICY

 CROSSCHECK and DELETE EXPIRED  I have given  to delete the backup from the disk ( I dont have enough space in the drives so I dont want to keep any obsolete backups .)


Re: RMAN Backups are not deleting [message #679052 is a reply to message #679045] Thu, 30 January 2020 12:56 Go to previous message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
rajesh_b wrote on Thu, 30 January 2020 08:53
 Thank you for the update . Now I have got little idea about the RECOVERY WINDOW and RETENTION POLICY

 CROSSCHECK and DELETE EXPIRED  I have given  to delete the backup from the disk ( I dont have enough space in the drives so I dont want to keep any obsolete backups .)


DELETE EXPIRED does not delete any files at all.
CROSSCHECK compares the record of backups, as recorded in the control file, with the files that are actually on disk. If a backup file recorded in the repository is found to no longer exist, its entry in the repository is marked 'expired'. Then, DELETE EXPIRED simply deletes those records from the repository. It does not delete any actual files because and "expired" file, by definition, does not exist to be deleted. There is no connection whatsoever between 'expired' and 'obsolete'.

The only way a backup file becomes 'expired' is if you manually delete it with an OS command instead of letting rman handle it. And you shouldn't be doing that. As for "I dont want to keep any obsolete backups", that's what the rman 'delete obsolete' is for. That's why you'd run 'delete obsolete' as part of every backup script. If you are short on disk space, you need to change your retention policy, affecting when a backup becomes 'obsolete', or buy more disk. Disk is cheap. Especially when measured against the cost of not being able to recover your database because you though you knew more than Oracle about when to delete a backup file.

And if you are thinking about shortening your recovery window (say from 7 days to 4 days), you need to adjust the schedule of when you take your level 0 backups. If your recovery window goes back to a point prior to your most recent level 0, then the previous level 0 will never be obsolete, regardless of how old it is.
Previous Topic: setting up RMAN for newly deployed RAC
Next Topic: Restore 10.2.0.4 RAC Backup to 11.2.0.4 dbhome
Goto Forum:
  


Current Time: Mon Sep 28 04:10:42 CDT 2020