Backup Central

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?

Thanks in advance
Andreas.

Hi,
May be need to run "crosscheck backup" and "delete expire" command from RMAN.


Best Regards,


Wira Chinwong

SCSi Co., Ltd.

2521/69 Biztown Ladprao Rd.,
Klongchaokhunsingha, Wangthonglang, Bangkok 10310
Tel 02-9559873-4 Fax 02-9559875 ext 5
Mobile: 086-9092992
E-mail: wira < at > scsi.co.th


-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L < at > VM.MARIST.EDU] On Behalf Of TSM
Sent: Tuesday, March 22, 2011 1:29 PM
To: ADSM-L < at > VM.MARIST.EDU
Subject: [ADSM-L] how to delete old oracle backups at the tsm server

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?

Thanks in advance
Andreas.

I am using next script:

#!/bin/sh
#
# Cross-check Recovery Catalog and TSM database
#
# Input parameters:
# 1 - database SID.
#
export ORACLE_SID=$1
export ORACLE_HOME=`grep "$1:" /etc/oratab | head -n1 | cut -f2 -d":"`
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
#
#######################################################################
# Remove expired datafiles, control files and archived redo log files #
#######################################################################
rman <<EOF
#
# Connect to Recovery Catalog database
connect catalog $RC_USER/$RC_PASSWORD < at > $RC_SERVICE;
#
# Connect to target database
connect target;
#
# Allocate channel for maintenance
allocate channel for maintenance type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/home/oracle/admin/tdpo/$1.opt)';
#
# Set date and time format
sql 'alter session set NLS_DATE_FORMAT = "YYYY-MM-DD:HH24:MI:SS"';
#
# Resynchronize catalog
resync catalog;
#
# List database incarnations
list incarnation;
#
# List backups
list backup summary;
#
# Crosscheck backups
crosscheck backup;
#
# List expired backups
list expired backup summary;
#
# Delete expired backups
delete noprompt expired backup;
#
# Delete obsolete backups
delete noprompt obsolete;
#
# Release channel
release channel;
#
# Allocate channel for maintenance
allocate channel for maintenance type disk;
#
# Check archived logs on disk
crosscheck archivelog all;
#
# Remove obsolete archived redo logs
delete noprompt expired archivelog all;
#
# Release channel
release channel;
#
exit;
EOF

exit 0

It is for AIX, but maybe it will help you, at least in RMAN part of script.

Grigori G. Solonovitch


-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L < at > VM.MARIST.EDU] On Behalf Of TSM
Sent: Tuesday, March 22, 2011 9:29 AM
To: ADSM-L < at > VM.MARIST.EDU
Subject: [ADSM-L] how to delete old oracle backups at the tsm server

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?

Thanks in advance
Andreas.


Please consider the environment before printing this Email.

CONFIDENTIALITY AND WAIVER: The information contained in this electronic mail message and any attachments hereto may be legally privileged and confidential. The information is intended only for the recipient(s) named in this message. If you are not the intended recipient you are notified that any use, disclosure, copying or distribution is prohibited. If you have received this in error please contact the sender and delete this message and any attachments from your computer system. We do not guarantee that this message or any attachment to it is secure or free from errors, computer viruses or other conditions that may damage or interfere with data, hardware or software.

Hello,

Use tdposync for synchronize items in TSM server and RMAN catalog.
Check TSM policy for oracle. It mus be set to 0 for verdeleted and
retonly, because you can't restore inactive objects from TSM using
RMAN.
Also check that backdelete set to yes in Oracle node properties.
Run expiration.

--
Efim

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=