Deleting backup Sets from RMAN

[fraze]

I've got an RMAN repository at 8.0.5 (UNIX) and we've got some databases registered with the repostory that no longer exist. Can anyone shed any light on how I get rid of the backupsets for these databases from the repository? (no control files exist)

[kiltz]

Do you use a catalog?

If yes, you can delete the datafiles of the backupset on OS level an than run the following commands on RMAN:

For this operations a connection to the catalog is necessary.

ALLOCATE CHANNEL FOR MAINTENANCE TYPE DISK;
CROSSCHECK BACKUP;
DELETE EXPIRED BACKUP;
RELEASE CHANNEL;
LIST BACKUPS; (

If somebody knows a way which is more clean, please let me know it.

Chris

[fgping]

This is from the Oracle documentation:

To unregister a database:

Start RMAN and connect to your target database. Note down the DBID value that is displayed when you use RMAN to connect to your target database. For example, enter:

% rman target sys/change_on_install@prod1 nocatalog


RMAN-06005: connected to target database: RMAN (DBID=1231209694)


List the copies and backup sets recorded in the control file (see "Generating Lists"). For example, enter:

RMAN> list backup of database;
RMAN-03022: compiling command: list

List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
989 1 368895909 0 368895908 1 23-JUN-99

List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- ------------------------
990 1 1 AVAILABLE 23-JUN-99 /vobs/oracle/dbs/01avppt4_1_1

List of Datafiles Included
File Name LV Type Ckp SCN Ckp Time
---- ------------------------------------- -- ---- ---------- -------------
2 /vobs/oracle/dbs/tbs_02.f 0 Full 34968 23-JUN-99

Issue change ... delete statements to delete all backups from the operating system (see "Deleting Backups and Copies and Updating Their Status in the RMAN Repository"). For example, enter:

allocate channel for maintenance type disk;
change backupset 989 delete;

RMAN-03022: compiling command: change
RMAN-08073: deleted backup piece
RMAN-08517: backup piece handle=/vobs/oracle/dbs/01avppt4_1_1 recid=1 stamp=368895908
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete

Execute another list command to confirm that RMAN removed all backups:

list backup;
RMAN-03022: compiling command: list

Use SQL*Plus to connect to your recovery catalog database and execute the following query in the recovery catalog to find the correct row of the DB table, setting DB_ID equal to the value you obtained from step 1. For example, enter:

SQL> SELECT db_key, db_id FROM db WHERE db_id = 1231209694;

This query should return exactly one row.

DB_KEY DB_ID
---------- ----------
1 1237603294
1 row selected.

While still connected to the recovery catalog, enter the following, where DB_KEY and DB_ID are the corresponding columns from the row you got from the query in step 5:

SQL> EXECUTE dbms_rcvcat.unregisterdatabase(db_key, db_id)

For example, enter:

SQL> EXECUTE dbms_rcvcat.unregisterdatabase(1 , 1237603294)

--------------------------------------------------------------------------------
Note:
The DBMS_RCVCAT.UNREGISTERDATABASE package works on Oracle release 8.0 and higher.

--------------------------------------------------------------------------------


If your databases have been blown away I don't know how you get the DBID, but there has to be a way to get that out of the catalog.

hope this helps,

Frank