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.
Hi,
Can we drop the catalog after this ?
Very good tutorial.
Cheers,
Raff
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 ;
Rich B, thanks for the handy-dandy script for generating
the script to manually remove old incarnations.
Mark.
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.
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
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?