Monday, June 18, 2007

Oracle RMAN Catalogue Cleanup

This is an update to a story posted back in August 2005

Why do people love Oracle? When I hear mention of Oracle I think of Luke Skywalker when he saw the Millennium Falcon, "What a piece of junk!" Like the Falcon it looks clunky, breaks down easily, and has the most temperamental behavior. When it's running, however, it screams. The problem is that DBA's and/or the RMAN catalogue sometimes don't do appropriate cleanup. If the DBA's are doing their job they should be using the tdposync syncdb command to do cleanup with TSM. If you cannot get them to do this or it doesn't seem to be working correctly you can use a manual process on the TSM side. 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=[TDP NODENAME] and
backup_date < '2007-06-01 00:00:00'

This can be redirected to a file then used later to delete with an undocumented delete command. If there is data going further back than your retention requirements state then you have a problem with the DB cleanup.

I am posting the undocumented TSM individual backup object delete command here, but remember any deletion from the TSM DB is done AT YOUR OWN RISK!

delete object 0 [Object ID Number]

It's unsupported because Tivoli doesn't trust you to not screw stuff up, and although I don't think you will, I understand their concern. Put this into a shell script and you can process thousands of objects and do a large amount of cleanup in a short ammount of time.


  1. do you have something simillar for TDPSQL
    wanted to delete some old backups objects

  2. Very useful Chad - thanks. To streamline this, I generated a macro from a single select statement - for example:

    >>> set sqldisplaymode wide

    >>> select 'delete object 0 ' ||cast(object_id as char(15)) from backups where node_name='nodename' and backup_date < '2007-06-01 00:00:00' > /tmp/remlist.mac

    I then edited the file to remove the opening couple of lines, and then, from the admin prompt, simply ran as a macro:

    >>> macro /tmp/remlist.mac

    David McClelland
    London, UK

  3. Has anyone tried this method on a v6.3 TSM server?

  4. A 16 year old post helped me clean up several Tb of old oracle data that had been around for years!!

    1. Glad I could help. I have been out of the TSM/Spectrum Protect realm for a couple years now so it's nice to see my old knowledge is still paying off.