Delete backups from old incarnation in rman

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.

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.

StackExchange Database Administrators Q#73237, answer score: 4