RMAN Catalog Housekeeping: how to purge the old incarnations

First, let me apologize because every post in my blog starts with a disclaimer… but sometimes it is really necessary. 😉

Disclaimer: this blog post contains PL/SQL code that deletes incarnations from your RMAN recovery catalog. Please DON’T use it unless you deeply understand what you are doing, as it can compromise your backup and recovery strategy.

Small introduction

You may have a central RMAN catalog that stores all the backup metadata for your databases. If it is the case, you will have a database entry for each of your databases and a new incarnation entry for each duplicate, incomplete recovery or  flashback (or whatever).

You should also have a delete strategy that deletes the obsolete backups from either your DISK or SBT_TAPE media. If you have old incarnations, however, after some time you will notice that their information never goes away from your catalog, and you may end up soon or later to do some housekeeping. But there is nothing more tedious than checking and deleting the incarnations one by one, especially if you have average big numbers like this catalog:

Where db, dbinc, bdf and brl contain reslectively the registered databases, incarnations, datafile backups and archivelog backups.

Different incarnations?

Consider the following query:

You can run it safely: it returns the list of incarnations hierarchically connected to their parent, by database name, key and level.

Then you have several types of behaviors:

  • Normal databases (created once, never restored or flashed back) will have just one or two incarnations (it depends on how they are created):

They are usually the ones that you may want to keep in your catalog, unless the database no longer exist: in this case perhaps you omitted the deletion from the catalog when you have dropped your database?

  • Flashed back databases (flashed back multiple times) will have as many incarnations as the number of flashbacks, but all connected with the incarnation prior to the flashback:

Here, despite you have several incarnations, they all belong to the same database (same DB_KEY and DBID), then you must also keep it inside the recovery catalog.

  • Non-production databases that are frequently refreshed from the production database (via duplicate) will have several incarnations with different DBIDs and DB_KEY:

This is usually the most frequent case: here you want to delete the old incarnations, but only as far as there are no backups attached to them that are still in the recovery window.

  • You may also have orphaned incarnations:

In this case, again, it depends whether the DBID and DB_KEY are the same as the current incarnation or not.

What do you need to delete?


  • Incarnations of databases that no longer exist
  • Incarnations of existing databases where the database has a more recent current incarnation, only if there are no backups still in the retention window

How to do it?

In order to be sure 100% that you can delete an incarnation, you have to verify that there are no recent backups (for instance, no backups more rercent than the current recovery window for that database). If the database does not have a specified recovery window but rather a default “CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default”, it is a bit more problematic… in this case let’s assume that we consider “old” an incarnation that does not backup since 1 year (365 days), ok?

Getting the last backup of each database

Sadly, there is not a single table where you can verify that. You have to collect the information from several tables. I think bdf, al, cdf, bs would suffice in most cases.

When you delete an incarnation you specify a db_key: you have to get the last backup for each db_key, with queries like this:

Putting together all the tables:

Getting the  recovery window

The configuration information for each database is stored inside the conf table, but the retention information is stored in a VARCHAR2, either ‘TO RECOVERY WINDOW OF % DAYS’ or ‘TO REDUNDANCY %’

You need to convert it to a number when the retention policy is recovery windows, otherwise you default it to 365 days wher the redundancy is used. You can add a column and a join to the query:

and eventually, either display if it the incarnation is no more used or filter by usage:

Delete the incarnations!

You can delete the incarnations with this procedure:

This procedure will raise an exception (-20001, ‘Database not found’) when a database does not exist anymore (either already deleted by this procedure or by another session), so you need to handle it.

Putting all together:

I have used this procedure today for the first time and it worked like a charm.

However, if you have any adjustment or suggestion, don’t hesitate to comment it 🙂


The following two tabs change content below.


Principal Product Manager at Oracle
Ludovico is a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Oracle Data Guard, Flashback technologies, and Cloud MAA.

8 thoughts on “RMAN Catalog Housekeeping: how to purge the old incarnations

  1. Worked like a charm, once I changed the ‘rman.dbinc’ to my schema owner ( rmcatowner.dbinc).
    Good job. Thanks, I was shuddering to think of having to do that manually.

  2. What to do, when dbkey and dbid are the same, when i did a DB restore for disaster recovery? This was my fault… But how to fix it?

    List of Database Incarnations
    DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
    ——- ——- ——– —————- — ———- ———-
    4822340 4822341 PROD 3891024022 NO 1 24-FEB-16
    4822340 4142096 PROD 3891024022 YES 65715648058 26-SEP-22

    the “good” is first one, 24-feb-16, and the “bad one” restored into different enviroment is second one – 26-sep-22. This one should be deleted.

    RMAN gives an error:
    RMAN-06004: ORACLE error from recovery catalog database:
    RMAN-20011: target database incarnation is not current in recovery catalog

    • Hi Darius,

      you need to set the incarnation to the correct one prior to doing a restore.
      There is no need to delete the other one from the catalog.

  3. Hi Ludo,

    Wouldn’t this unregister all those databases instead of just deleting their old/orphaned incarnation from catalog?
    Do you register the database back or there’s something I’m missing!

    dbms_rcvcat.unregisterdatabase deletes based on db_key and db_id. It doesn’t see dbinc_key, so both parent and current incarnation will be gone.

    How can only old (parent) incarnation be deleted?

    • Hi Bish,
      You don’t want to delete the parent incarnation, only the orphaned or unused ones. The parent incarnation cannot be safely deleted if you have a child incarnation still active.

  4. Post server migration performed clone of production into production RMAN details cannot be listed.

    As part of server migration from IBM power7 to power9 server, clone of production into production is performed.

    Database was shifted to No Archive log mode. Shifted to Archive log mode. Previous rman backup metadata is not available.
    RMAN> list backup of archivelog all summary;
    specification does not match any backup in the repository

    How to get the metadata of the old incarnation using rman?

  5. Pingback: RMAN Catalog Housekeeping: how to purge the old incarnations - Ludovico Caldara - Blogs - triBLOG

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.