droach
ADSM.ORG Senior Member
I just cannot seem to get a handle on what I call orphaned TDP backups. Orphaned TDP backups are backups for SQL or Oracle databases that never get marked as 'inactive' so TSM never deletes them. I won't go into detail about how these come to exist, but believe me if you have a lot of DB servers using TDP for backup I can almost guarantee that you have orphaned backups way beyond your retention policies.
You can find them with this command, just change the node names in the where clause and change the number of days to match your retention policy. Ideally, this command should return nothing:
IBM has not been much help. IBM says you should manually mark these backup objects as inactive when you delete or rename a database (one of the biggest contributors to orphaned TDP backups). Their recommendation is to use CLI tools like TDPSQLC INACTIVATE for SQL, and TDPOSYNC for Oracle. I have not been successful asking our DBA's to perform these steps, and in reality this really isn't an option...think SharePoint and the way it creates and delete databases without DBA involvement.
So then the problem becomes how to automate the task. If you blindly schedule these CLI utilities to mark everything 'inactive' if it is older than XX days you could inactivate your only copy of a database backups. Not an option.
So, before I embark on the task of scripting something to query for existing databases, then query for existing database backups, then manipulating the lists to produce a list of orphans, and then produce a list of commands to mark them inactive, I am asking the experts on this forum: "How do you deal with your orphaned TDP backups?"
You can find them with this command, just change the node names in the where clause and change the number of days to match your retention policy. Ideally, this command should return nothing:
select node_name, HL_NAME, ll_name, backup_date, state from backups where (node_name like '%%_TDP' or node_name like '%%_SQL') and backup_date < (current_timestamp - 36 days) and state = 'ACTIVE_VERSION' order by node_name,backup_date
IBM has not been much help. IBM says you should manually mark these backup objects as inactive when you delete or rename a database (one of the biggest contributors to orphaned TDP backups). Their recommendation is to use CLI tools like TDPSQLC INACTIVATE for SQL, and TDPOSYNC for Oracle. I have not been successful asking our DBA's to perform these steps, and in reality this really isn't an option...think SharePoint and the way it creates and delete databases without DBA involvement.
So then the problem becomes how to automate the task. If you blindly schedule these CLI utilities to mark everything 'inactive' if it is older than XX days you could inactivate your only copy of a database backups. Not an option.
So, before I embark on the task of scripting something to query for existing databases, then query for existing database backups, then manipulating the lists to produce a list of orphans, and then produce a list of commands to mark them inactive, I am asking the experts on this forum: "How do you deal with your orphaned TDP backups?"