Oracle RMAN – How to Delete Backups from Old Incarnation

oraclerman

I did a refresh on a non-production database and these are the incarnations:

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time


1       1       ORCL     1355555557       PARENT  1          20-DEC-11

2       2       ORCL     1355555557       CURRENT 12         17-JUL-14

How do I delete the backups from the old incarnation since they're not needed anymore?
Any help would be greatly appreciated.
Thanks.

Best Answer

I couldn't find any clause to the REPORT or LIST commands which would allow you to specify the incarnation of interest.

You could however correlate your latest incarnation reset time with backup completion time to identify the backups completed before your current incarnation began.

First, let's set the environment variables so that RMAN output also includes time.

$ NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
$ NLS_TIMESTAMP_FORMAT='DD.MM.YYYY HH24:MI:SS'
$ export NLS_DATE_FORMAT
$ export NLS_TIMESTAMP_FORMAT

Now let's examine which incarnations we have in RMAN repository.

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- ---     ---------- ----------
1       1       OCP      2737702586       PARENT  1          25.08.2013 05:18:32
2       2       OCP      2737702586       PARENT  635002     05.03.2014 10:49:33
3       3       OCP      2737702586       PARENT  750347     13.03.2014 18:08:15
4       4       OCP      2737702586       PARENT  750727     18.03.2014 11:37:57
5       5       OCP      2737702586       CURRENT 1137478    05.08.2014 08:19:38

It's shown in the output that our current incarnation's reset time is 5 August 2014 8:19 AM (05.08.2014 08:19:38).

I have some backups completed before and after the current incarnation began.

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28      B  F  A DISK        05.08.2014 08:21:56 1       1       YES        TAG20140805T082051
29      B  F  A DISK        05.08.2014 08:12:43 1       1       YES        TAG20140805T081243
30      B  F  A DISK        05.08.2014 08:10:36 1       1       YES        TAG20140805T081036
31      B  A  A DISK        05.08.2014 08:21:59 1       1       YES        TAG20140805T082159
32      B  F  A DISK        05.08.2014 08:13:48 1       1       YES        TAG20140805T081243
33      B  A  A DISK        05.08.2014 08:20:49 1       1       YES        TAG20140805T082048
34      B  F  A DISK        05.08.2014 08:20:51 1       1       YES        TAG20140805T082051
35      B  F  A DISK        05.08.2014 08:11:51 1       1       YES        TAG20140805T081036
36      B  A  A DISK        05.08.2014 08:20:48 1       1       YES        TAG20140805T082048

Now we can list backups which were completed before the current incarnation began.

RMAN> list backup summary
2> completed before
3> "to_date('05.08.2014 08:19:38', 'dd.mm.yyyy hh24:mi:ss')";    

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
29      B  F  A DISK        05.08.2014 08:12:43 1       1       YES        TAG20140805T081243
30      B  F  A DISK        05.08.2014 08:10:36 1       1       YES        TAG20140805T081036
32      B  F  A DISK        05.08.2014 08:13:48 1       1       YES        TAG20140805T081243
35      B  F  A DISK        05.08.2014 08:11:51 1       1       YES        TAG20140805T081036

As you can see, only backups with completion time before the current incarnation's reset time are shown in the output.

After you narrowed down the backups to the ones whose completion time is before your current incarnation's reset time using COMPLETED subclause of the LIST command, you can specify the same condition to the DELETE command.

RMAN> delete backup
2> completed before
3> "to_date('05.08.2014 08:19:38', 'dd.mm.yyyy hh24:mi:ss')";

There are also AFTER and BETWEEN specifiers to the COMPLETED subclause. See the section Listing Selected Backups and Copies in Database Backup and Recovery User's Guide. An example of BETWEEN specifier usage:

RMAN> list copy of datafile 2 completed between '10-DEC-2002' and '17-DEC-2002';

UPDATE

You can switch between incarnations using RESET DATABASE TO INCARNATION n command (check the scenarios of usage), but you'll always end up with the same output from the LIST and REPORT commands with respect to backup sets, no matter which incarnation you switch to.

You can search Database Backup and Recovery Reference for the word "incarnation", to see when you can specify the incarnation and to which RMAN commands.

Related Question