Remove a Database from a RMAN Recovery Catalog

There will come a time that you will need to remove a database from the recovery catalog. Removing a database from the recovery catalog removes all metadata for the database from the catalog.

This document will detail the steps to remove a database from the RMAN recovery catalog. The examples shown were performed using Oracle Database 11gR2 on Oracle Enterprise Linux.

While it is not required to connect to the target database in order remove the database from the recovery catalog it is easier and the method used in this document. Below we connect to the target database and the recovery catalog.

[oracle@odlinux ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 7 19:03:32 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: ODLIN11G (DBID=2071040209)

RMAN> connect catalog rcat@reccat

recovery catalog database Password: 
connected to recovery catalog database

RMAN> 

Next issue the UNREGISTER DATABASE command to un-register the database. The UNREGISTER DATABASE command removes the database metadata from the recovery catalog.

RMAN> unregister database;

database name is "ODLIN11G" and DBID is 2071040209

Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog

RMAN> 

After issuing the UNREGISTER command you can further verify that the database is no longer registered using LIST DB_UNIQUE_NAME ALL.

RMAN> list db_unique_name all;


RMAN> 

The UNREGISTER command does not remove physical backups it only removes the metadata from the repository. Even when using the recovery catalog to manage backups, backup information is still written to the control file. This can be verified by connecting to the database after un-registering the database with RMAN.

Below we exit out of the current RMAN session and reconnect to the target database and see that the control file still has backups registered.

RMAN> exit


Recovery Manager complete.
[oracle@odlinux ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 7 19:17:23 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: ODLIN11G (DBID=2071040209)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  A  A DISK        29-AUG-10       1       1       NO         TAG20100829T123046
2       B  F  A DISK        29-AUG-10       1       1       NO         TAG20100829T123050
3       B  A  A DISK        29-AUG-10       1       1       NO         TAG20100829T123246
4       B  F  A DISK        29-AUG-10       1       1       NO         TAG20100829T123247
5       B  F  A DISK        29-AUG-10       1       1       NO         BIANUAL_01
6       B  F  A DISK        29-AUG-10       1       1       NO         BIANUAL_01
7       B  A  A DISK        29-AUG-10       1       1       NO         BIANUAL_01
8       B  F  A DISK        29-AUG-10       1       1       NO         BIANUAL_01
9       B  A  A DISK        05-SEP-10       1       1       NO         TAG20100905T160832
10      B  F  A DISK        05-SEP-10       1       1       NO         TAG20100905T160840
11      B  A  A DISK        05-SEP-10       1       1       NO         TAG20100905T161046
12      B  F  A DISK        05-SEP-10       1       1       NO         TAG20100905T161048

RMAN> 

While the physical backups are not removed by the UNREGISTER command records of backups registered in the catalog that are older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database are lost. If those backups are needed the CONTROLFILE_RECORD_KEEP_TIME needs to be adjusted and the backups re-cataloged to the control file using the CATALOG command.

6 thoughts on “Remove a Database from a RMAN Recovery Catalog”

  1. Here’s a script that I wrote 10 years ago. It identifies all the incarnations of a DB and formats the the command to be used for deletion.

    The prompt at the end is to verify who they are before they start pasting the dbms_rcvcat…. line.

    HTH, Rich
    *****

    PROMPT Connecting to RMAN@CATDB… enter RMAN password:

    connect rman@CATDB

    set verify off pagesize 60;

    ACCEPT SID prompt ‘Enter the sid you need to purge from RMAN:’

    PROMPT

    SELECT rd.name, ‘execute dbms_rcvcat.unregisterdatabase(‘||rd.db_key||’,’||rd.dbid||’);’ “Unregister Command”,
    rd.RESETLOGS_TIME
    FROM RMAN.rc_database rd
    WHERE name = UPPER(‘&SID’);

    PROMPT
    PROMPT Execute ALL the “dbms_rcvcat.unregisterdatabase” command above while connected to
    PROMPT the Recovery Catalog (CATDB) as user RMAN.
    PROMPT
    set heading off

    PROMPT Current Database:
    select name from v$database;

    PROMPT Current User:
    SHOW USER;
    set heading on verify on ;

  2. Rich B, thanks for the handy-dandy script for generating
    the script to manually remove old incarnations.
    Mark.

  3. Hey All,
    What about this scenario:
    1. I created a database -including all post steps such as registering and scheduling backups.

    2. After running for several months, the project requirements change significantly so I decide to start all over from scratch with later db versions, etc. etc. So I recreate the database with the same database name.

    3. I go to run my backups and get the RMAN-20002 target already registered in catalog error. So I un-register it then re-register it again.

    4. I try to take my initial lvl0 backup again. I receive RMAN-06004 -error from recovery catalog db followed by RMAN-20001: target not found in recovery

    5. I try un-registering and re-registering again -same outcome. I look at incarnations and see that the Database is listed twice with the same DBID but the older incarnation from June has a status of Parent while the new one created last week has a status of CURRENT.

    6. Every time I re-register it the 2 incarnations are present.

    I took a look at the RMAN RESET DATABASE command but seems equivalent to reset logs and description usage of that reset command seems to imply that would only be useful if wanted to recover to a previous point in time. Obviously I do not want to do that since this is a brand new upgraded with totally different parameters database. It is also a 2 node RAC db.

    Any knowledge to share on how to resolve this? clear out ALL info in recovery catalog and start fresh -WITHOUT changing the database name or db_unique name as too many apps are already tied into it and I do not want to simply try to use a different db name but specific service name for this.

    Thanks.

  4. Hello Slac Kim, I was looking for the same process as you the below ORACLE link did the trick. Your question was a for last year , just adding for anyone thats looking for the answer.

    Example 2-154 Unregistering a Database That is Not Unique in Catalog

    http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta051.htm

    RMAN> CONNECT CATALOG rman@catdb

    recovery catalog database Password: password
    connected to recovery catalog database

    RMAN> SET DBID 28014364;

    executing command: SET DBID
    database name is “PROD” and DBID is 28014364

    RMAN> UNREGISTER DATABASE;

    Do you really want to unregister the database (enter YES or NO)? YES
    database unregistered from the recovery catalog

  5. I was curious if you ever thought of changing the layout
    of your blog? Its very well written; I love what youve got to say.

    But maybe you could a little more in the way of content so people could connect
    with it better. Youve got an awful lot of text for only
    having one or two images. Maybe you could space it out better?

Leave a Reply

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