Oracle rman backups cleanup – database decommissioning

This simple step by step instruction shows how to get rid of the old database’s rman backups and unregister that db from RMAN. This is useful especially when you are decommissioning the databases.

Remember – leaving out deleting the backups from both the rman catalog and media library catalog, may lead to orphaned backups on your backup server.

If this is TSM, then this is the case for sure. One needs to delete backups while being conected to the TSM to allow TSM to mark the backup files as deleted. The TSM gets rid physically of the backup pieces during the maintenance routines.

There are two cases you can encounter. The first one is when you still have controlfile of that database available – this is quite simple. The second one – when you have no controlfiles left – more complicated but you still can do it 🙂

IF YOU STILL HAVE A CONTROLFILE

1. Startup mount using your controlfile – as usual

   SQL> startup mount;

2. Connect rman to the instance as a target and to the catalog

   # rman target / catalog rman/****@RMAN

3. Check if you are playing with the catalog of the right database, list backups etc.

   RMAN> list backup of datafile 1;

   Examine the output to confirm that you have the correct dbid and that the backups belong to the right database.

4. Delete all backupsBefore you confirm that the removal process should proceed, make sure if RMAN has properly allocated the tape channel – this is crucial.

   RMAN> allocate CHANNEL FOR MAINTENANCE DEVICE TYPE 'SBT_TAPE';
   RMAN> delete backup;

5. Unregister database from the RMAN catalog

   RMAN> unregister database;

    

IF YOU NO LONGER POSSES EVEN A SINGLE CONTROLFILE

1. If you have an old spfile you can use it or if not – you can create a basic init.ora file for the deleted database. Remember to include db_name parameter with a proper database name. All you need is :
   initDB_NAME.ora

   db_name=MYDB
   sga_target=1500M

2. Find the proper DBID of the database
   – by RMAN command – ‘list incarnation’
   – in the catalog view ‘rc_database’
   – in the old backup logs – when connecting to a target, RMAN prints its DBID
3. Startup nomount using prepared parameter file or your old spfile

   SQL> startup nomount;

4. Connect with RMAN to the newly started instance as target and to the catalog

   rman target / catalog rman/****@RMAN

5. Set the dbid to the correct dbid retrieved at point 2

   RMAN> set dbid 97794349576;

6. Check if you are playing with the right database’s catalog, list backups etc.

   RMAN> list backup of datafile 1;

   Examine the output to confirm that you have the correct dbid and that the backups belong to the right database.

7. Delete all backups of the databaseBefore you confirm that the removal process should proceed, make sure if RMAN has properly allocated the tape channel – this is crucial.

   RMAN> allocate CHANNEL FOR MAINTENANCE DEVICE TYPE 'SBT_TAPE';
   RMAN> delete backup;

8. Unregister database from the RMAN catalog

   RMAN> unregister database;

 

Remember to get rid of all the stuff that has been left on the disk and is not being used anymore (spfiles, init.ora files, password files, admin directories etc.).

That’s all folks!! Enjoy!

 

Maciej Tokar