TSM / TDPO reporting

[Axr2]

Anyone (psst..Jodie) know how to generate some decent consolidated reports off TSM? We backup some 20 databases for a particular customer using TDPO. The customer would like to see a daily consolidated report from TSM that tells him when the daily backup for each database started..and when it finished..along with sizes.

I see a bunch of 3rd party tools available in the market that do this stuff. I'm not familiar with most. Would be good if someone could post if they use something in particular. Also, are there any freebie tools that do this?

PS : I realize that it's not really an Oracle database issue. But moderators - please be kind and leave my post in here.

[jodie]

Well, we don't do any reports off TSM, so not sure of any that are available.

But why couldn't you just report off of the RMAN catalog.

Here's a script to look at the backup sets.

BREAK ON DATABASE skip 1
col elapsed for 99,999.9
set lines 120
SELECT name "DataBase", bs_key, set_count, status,
       DECODE(backup_type,'D','DATABASE','L','ARCHIVE LOG', I','INCREMENTAL') TYPE,
       incremental_level "Level",
       TO_CHAR(start_time, 'MM/DD/YY-HH24:MI') "Start Time",
       TO_CHAR(a.completion_time, 'MM/DD/YY-HH24:MI') "End Time",
       elapsed_seconds/60 "Elapsed"
  FROM rc_backup_set a, rc_database b
 WHERE start_time > sysdate - 14
   AND name LIKE UPPER('&DataBase%')
   AND a.db_key = b.db_key
   AND elapsed_seconds BETWEEN 0 AND 86399
ORDER BY 1, 7

Here's a script to see backup sizes of datafiles:

col mbytes for 9,999,999.99
  SELECT db_name, bs_key, sum((blocks*block_size)/(1024*1024)) MBytes
    FROM rc_backup_datafile
   WHERE db_name like upper('&database%')
GROUP BY db_name, bs_key
/

* You can use RC_Archived_log to get similar information on Archive Log backups.

These should get you in the right direction to get the information you need.

Good luck!
Jodie

[Axr2]

Sorry, I didn't put it across correctly. I'm also interested in getting reports of scheduled TSM filesystem backups (in addition to the database).

PS : Guess some smart moderator shoved this into obfuscation anyway. Guess english doesn't get through to folks sometimes.

[jodie]

I don't do any reporting off TSM, so can't really help you there. Sorry.

But don't forget, TSM info is kept in tables that can be queried. (I think you have some queries already) If you can get a schema definition, I'm sure you can get the info you need with a few SQL statements. Not "Pretty", but it would get you the info quickly and cheaply!

Good Luck
Jodie

[Horace]

Hi Jodie,

That code looks very useful. BUT....

I can't find the 2 objects referenced, i.e.,

rc_backup_set a, rc_database b

Where are they ? They're not in my rman cat schema

[jodie]

Are you sure?

They are 2 of the views in the rman catalog schema. Version 8i and 9i. There are actually 27 views created.

Hmmm..