How do you deal with orphaned TDP backups?

droach

ADSM.ORG Senior Member
Joined
Jan 7, 2008
Messages
239
Reaction score
13
Points
0
Location
Cut and Shoot, Texas
PREDATAR Control23

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:

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?"
 
PREDATAR Control23

Im working though the same problem presently & have yet to find a good answer.

Assuming the DBA can tell you if a DB has been removed you could conceivably run a del fil against the Database FSID? I have not tested if this breaks anything.

Alternatively you may be able to give an alternate system proxy access to the DB node & run the inactivate steps from there
 
PREDATAR Control23

You probably know this, but it's the nature of database backups. Each backup has a unique name stored on the Spectrum Protect server, which means they are always active, therefore never expire. So it has to be done on the client side. So it's up to the application (TDP) and DBAs to mark them as inactive.

For Oracle, you can use the DEACTIVATE DATA command to delete backups older than X.

For TDP SQL, if you follow this: https://www.ibm.com/support/knowledgecenter/SSTFZR_7.1.4/db.sql/t_dps_cfg_autoexpire.html , you may avoid this problem in the future. In the meantime, the DBAs can use DELETE BACKUP to remove the old backups: https://www.ibm.com/support/knowled...bm.itsm.db.sql.doc/r_dpsql_opt_delbackup.html

This thread may help too for legacy backups: https://adsm.org/forum/index.php?threads/making-sql-backup-script-need-some-help.30705/
 
Top