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.
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 ;