Problem solve Get help with specific problems with your technologies, process and projects.

TSM database query too taxing

Ours is an Oracle-AIX RS6000 shop, and we backup our Oracle databases on a TSM server using LTO 2 3584 library. Everyday, we backup around 3 TB of data. From the Oracle side, we backup the database using an RMAN tool. We would like to identify which backups are going to which tapes.

Is there a way in TSM that we can identify which tape the backup files of a particular database are going to. This is required because very few tapes are ejected out of TSM library everyday.

I would like to know the SQL query that should be run on the TSM library to identify correlation between backup files and tapes.

There is a way to obtain this data by querying the TSM database, but it is a very resource intensive procedure and it is definitely not recommended. Using a command such as "Select volume_name, node_name, file_name from contents where node_name= '(name of the node)' and file_name= '(name of the file)'" will eventually provide the requested information but at a very high cost on performance and memory requirements (database buffers). In some cases, it could take hours before your TSM command prompt returns.

The reason why the number of tapes ejected daily may appear low in comparison to the amount of data backed up could be explained by the LTO2 drives compression capabilities. We have frequently observed LTO2 tapes storing up to 800 GB of database data with compression enabled (databases typically compress very well).

However, if there still is a requirement to keep track of which tapes contain database data, it might be advisable to create a separate tape pool for the RMAN backups. It would then be a little easier on the resources when running the SQL command mentioned above against tape volumes that store a low number of large files.

Dig Deeper on Backup and recovery software

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.