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
