Wednesday, August 03, 2005

Oracle RMAN Catalogue Cleanup

Why do people love Oracle? When I hear mention of Oracle I think of Luke Skywalker when he saw the Millenium Falcon, "What a piece of junk!" Like the Falcon it looks clunky, breaks down easily, and has the most tempermental behavior. When it's running, however, it screams. The problem is that the RMAN catalogue sometimes doesn't do appropriate cleanup. If you want to make sure that a particular node is performing cleanup within TSM run the following select command -

select object_id from backups where node_name= and
backup_date < '2005-07-01 00:00:00'

This can be redirected to a file then used later to delete with an undocumented delete command. I will give the delete command out to those who need it, but remember any deletion from the TSM DB is done AT YOUR OWN RISK! It's unsupported because Tivoli doesn't trust you to not screw stuff up, and although I don't think you will, it's better safe then sorry.


  1. The problem lies with older versions of RMAN that would delete their catalogue info but it would not tell TSM they were removed. The command comes in handy when we have low scratch and know that we can safely remove anything older than 31 days. Here is the delete script.

    #! /bin/ksh

    while read LINE
    dsmadmc -id=admin -pa=password delete object 0 $LINE
    echo $LINE Object Deleted
    done < $1

    This script will process a list of object id's and delete their records from the DB. Basically its like an individual backup file delete rather than having to delete a whole filespace to delete data. Used with the select command it can give you more flexibility when it comes to expiring data. Of course I only use it because old Oracle instances leave data on my servers but it can be used in other ways that could be helpful.

  2. Did this replace the 'delete record' command only accessible by the ibm id?

  3. I'm not familiar with a delete record command, although I believe they are adding the ability to delete individual files from backup. This command came to us from support when we explained our problem of stranded data due to a bug in RMAN. Since then we have used it to do cleanup on older DB backups that did not get removed or the DBA did not run cleanup regularly.