3

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.

migrated from stackoverflow.com Aug 5 '14 at 20:58

This question came from our site for professional and enthusiast programmers.

  • If you do 'list expired', is that old inccarnation listed? – Mark J. Bobak Aug 4 '14 at 17:02
  • no, noting shows up. – mike smith Aug 4 '14 at 17:09
3

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.

1

You can set a retention policy to redundancy or recovery window and just make another backup of your database so that your existing backups completed before your current incarnation began become obsolete according to the defined retention policy. Surely, you can also configure automatic backup of your database according to your backup strategy.

If you set your retention policy to redundancy.

RMAN> configure retention policy to redundancy 1;

And then backup your database, you can immediately delete obsolete backups, which in this case all previous backups including those completed before your current incarnation.

If you set your retention policy to recovery window.

RMAN> configure retention policy to recovery window of 7 days;

You can just wait at least a week (making database backups in between) before existing backups are considered obsolete, and you can attempt to delete them with delete obsolete command.

If you configured the Fast Recovery Area in your database, you can just set retention policy and Oracle will automatically delete obsolete backups when the space is needed for the new backups and there's space pressure in the recovery area. See Configuring the Fast Recovery Area section in Oracle Database Backup and Recovery User's Guide.

1

In RMAN, connected to your target

rman catalog rman/*** @rmanRMAN target /

RMAN> list incarnation of database ;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
11349   11384   myDB     2790586635       PARENT  1          31.03.14
11349   11385   myDB     2790586635       PARENT  733005     31.03.14
11349   11350   myDB     2790586635       CURRENT 750963     31.03.14
1179854 1179855 myDB     2800799356       CURRENT 1          25.07.14

FROM rman catalog db

sqlplus rman@RMAN 

SQL>  SELECT db_key, dbid, name FROM rman.rc_database WHERE name ='myDB' ORDER BY DBID ;

    DB_KEY       DBID NAME
---------- ---------- --------
     11349 2790586635 myDB
   1179854 2800799356 myDB

SQL> EXECUTE rman.dbms_rcvcat.unregisterdatabase(11349,2790586635) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy