Ask the Expert

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.

Requires Free Membership to View

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.

This was first published in April 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: