asked on

purging the rman catalog

Oracle 9i,
When I view the rc_backup_piece table via SQLPLUS, my backupsets goes as far back as Jan 2002. My retention policy is set to 366 days. When I issue the delete obsolete command in Rman it works but does not remove the backups created in 2002- 2004. I assume that the old backups are not known to Oracle Rman. Is there a way to cleanup the catalog by removing these backupset through SQLPLUS? I thought maybe I can change the Status to "Delete" and then run the prgrmanc.sql script. (But which table would I change).
Also this catalog contains backupsets from 28 different instances/database.

Any thoughts???????????????

MikeOM_DBA

CROSCHECK plus DELETE OBSOLETE will do it.

dedean01

ASKER

When I execute crosscheck and other rman commands such as
RMAN> list backup of database summary; I don't see the backupsets

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
40903 B 1 X DISK 31-MAY-07 1 1 THU_05_31_07_19_00_INCR_W_1
40904 B 1 X DISK 31-MAY-07 1 1 THU_05_31_07_19_00_INCR_W_1

But when I go to sqlplus and execute queries against the tables, I have old backupsets that are still in the tables.

select db_key,BS_KEY,db_id,backup_type,start_time,status,INCREMENTAL_LEVEL,backup_type
from rc_backup_piece where db_id = &databaseId order by start_time desc
DB_KEY BS_KEY DB_ID B START_TIM S INCREMENTAL_LEVEL B
---------- ---------- ---------- - --------- - ----------------- -
123765 136739 2808756428 D 19-JAN-02 X D
123765 136735 2808756428 D 18-JAN-02 X D
123765 136732 2808756428 D 18-JAN-02 X D
123765 136733 2808756428 D 18-JAN-02 X D
123765 136734 2808756428 D 18-JAN-02 X D
123765 136728 2808756428 D 17-JAN-02 X D
123765 136730 2808756428 D 17-JAN-02 X D
123765 136731 2808756428 D 17-JAN-02 X D
123765 136729 2808756428 D 17-JAN-02 X D
123765 136726 2808756428 D 16-JAN-02 X D
123765 136725 2808756428 D 16-JAN-02 X D

DB_KEY BS_KEY DB_ID B START_TIM S INCREMENTAL_LEVEL B
---------- ---------- ---------- - --------- - ----------------- -
123765 136727 2808756428 D 16-JAN-02 X D
123765 136724 2808756428 D 16-JAN-02X D
123765 136720 2808756428 D 15-JAN-02X D
123765 136723 2808756428 D 15-JAN-02 X D
123765 136722 2808756428 D 15-JAN-02 X D
123765 136721 2808756428 D 15-JAN-02 X D
123765 136716 2808756428 D 14-JAN-02 X D
123765 136719 2808756428 D 14-JAN-02 X D
123765 136717 2808756428 D 14-JAN-02 X D
123765 136718 2808756428 D 14-JAN-02 X D

241 rows selected.

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 366 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;

dedean01

ASKER

This is what I get when I run the "OBSOLETE COMMAND"
RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 366 days
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=17 devtype=DISK
no obsolete backups found

RMAN>

MikeOM_DBA

Then try DELETE EXPIRED...

dedean01

ASKER

The Delete Expired will delete all of my backupsets that are not stored on disk. After 2 weeks the backupsets are removed from disk and they become expired. I still need them in the catalog for one year. I would like to fine out how to remove them from the catalog database via Sqlplus, because I believe that the data was stored in those tables in error. Maybe a previous upgrade or something of that nature.

MikeOM_DBA

Connect to rman catalog with SQL*Plus and get the DB_KEY and the DB_ID number of the backups you with to remove:

SQL> select * from db;

DB_KEY DB_ID CURR_DBINC
---------- ---------- ----------
1 2472695131 2

1 row selected.

Then execute :

SQL> execute dbms_rcvcat.unregisterdatabase(DB_KEY,DB_ID);
ie execute dbms_rcvcat.unregisterdatabase(1,2472695131);

dedean01

ASKER

From my understanding, executing dbms_rcvcat.unregisterdatabase(DB_KEY,DB_ID); will unregister my database and remove all of my backups for that particular database, and I do need to keep the backupsets that are less then a year old.

Thanks

MikeOM_DBA

It will only unregister those database which you no longer wish to keep catalog entries, for example those which you have 'cloned' and only changed the dbid.

Other wise, you need to do the CROSSTAB/DELETE EXPIRED thing.

RMAN keeps backup entries by DBID independent of the database name.

ASKER CERTIFIED SOLUTION

dedean01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dedean01

ASKER

I reslove the issue myself. No points should be given out.

Thanks

Vee_Mod

Closed, 500 points refunded.
Vee_Mod
Community Support Moderator