On Mar 22, 2011, at 1:29 AM, TSM wrote:

> Hello,
> 
> We use tdp for oracle with no catalog database.
> We set the oracle parameter control_file_record_keep_time too low.
> So the "delete obsolete" removed nothing, because oracle removed the
> entries earlier from the controlfiles.
> 
> Now we have to delete a lot of oracle backups at the tsm server, which
> oracle doesn't know.
> Any ideas how to delete the old backups?

I have a Plan B for this which doesn't rely on having access to the client, but 
it's not for the faint of heart, and it works better with TSM V5 than with V6.

Do you collocate your client data by node or, even better, by file system? If 
so, this might work for you.

The basic premise is that if you can find tapes (volumes) that are full of 
database backups you no longer want, you can run DELETE VOLUME (DISCARDDATA on 
them. The trick comes in determining if a volume has only Oracle backups that 
are old enough that you don't want them any more. 

Q NODEDATA will tell you which volumes you want want to examine. 

The CONTENTS table will tell you who owns the objects on a volume, which file 
system (DB instance, if it's a DB) that it came out of, if it's an archive or a 
backup (Oracle doesn't use archives, but DB2 does), and even the size of the 
object and which segment of the object it is. It also has an object ID, which 
you need to read the BACKUPS table to get the backup date, if it's active or 
inactive, and one or two other details, but backup date and active/inactive are 
the ones I care about. 

BACKUPS is a beast to work with, but in TSM V5, it has enough indices on it 
that you can pull only the backup objects for a particular file system on a 
particular node and store the backup dates and active/inactive states. I use a 
perl hash (%name/$name{object_id}) has that keys on the object ID, but I'm sure 
there are other techniques as well. (TSM 6.1 and 6.2 don't have indices on 
BACKUPS, making this technique impractical.)

If a volume only has active objects from the file system you expect (a constant 
for Oracle) from the node you expect that aren't archives, you might want to 
discard this whole volume. If you have DB backups that span volumes, you might 
delete more than one volume with that command -- which, in this case, is what 
you want. 

The smaller your volumes, the more likely a volume will have only your DB 
backups on it. (100 GB volumes? Possible, but not certain. 12 GB volumes? Much 
more likely. And so forth.)

You might have rules in your shop saying to never use DELETE VOLUME 
(DISCARDDATA. That's why this technique isn't for the faint of heart. And, as I 
said, for TSM V6 systems, at this time, you don't have the indices you want to 
make working with BACKUPS table feasible. But, depending on how much old Oracle 
data you have, and how cooperative your Oracles DBAs are, this might be a good 
technique. 

If you have DB2 clients, you can avoid using the BACKUPS table and capitalize 
on the fact that DB2 object names imbed a timestamp in their object names. But 
you didn't ask about DB2, so that's just a digression.

Finally, yes, I'll share my perl script for this if you send me e-mail 
off-list. (I can't emphasize the "off-list" part enough.) But, of course, it 
would be strictly without warranty, merely as an example of how this might be 
done if you were desperate enough to want to try. 

> Thanks in advance
> Andreas.

BT, DT,

Nick