date:20021216

Re: Purging Managed Standby Database Archive Logs

2002-12-16 Thread Yechiel Adar

Title: RE: Purging Managed Standby Database Archive Logs

Hello Steve
If you are on 9.2 you can use utl_file.fremove directly
from the sql.
Yechiel AdarMehish

- Original Message -
From:
Orr, Steve

To: Multiple recipients of list ORACLE-L
Sent: Thursday, December 12, 2002 9:48
PM
Subject: RE: Purging Managed Standby
Database Archive Logs

Hi John,
Keeping things simple sometimes complicates things when they
go haywire. I thought about the simple shotgun approach but wanted something a
little more robust and I didn't want to risk deleting an archive log that
hadn't been applied and then having to go through the hassle of finding in on
tape. (We use RMAN on production server and "archivelog all delete input"
every night.) A lot of logs could be zapped if the standby got hosed just
before a 3-4 day weekend. I want to avoid having to rebuild the standby but I
don't want to keep multiple days worth of logs around either. I just want to
remove the logs that are no longer needed and the archive info is in the data
dictionary views so why not use it?
Well after waiting on the list a while nobody did my job for
me so I got busy and rolled my own... :-) --
#!/usr/bin/ksh # File:
sb_log_maint.sh ... Steve Orr, 12/12/2002 # Purpose:
Purge standby database archive logs which have been applied. ORAUBIN="/u01/app/oracle/bin" SEQ_FILE="$ORAUBIN/last_purged_seqno.txt" LAST_PURGED_SEQ=`cat ${SEQ_FILE}`
getAppliedLogs() { sqlplus -s "/ as sysdba" @$ORAUBIN/login.sql <
set feed off pages 0 echo off wra off select sequence#, trim(name) from v\$archived_log where sequence# > $LAST_PURGED_SEQ and
archived='YES' order by sequence# ; exit EOSQL }

getAppliedLogs | while read SEQNO FNAME do echo "Removing file
$FNAME" echo $SEQNO > $SEQ_FILE
if [ -f $FNAME ]; then rm $FNAME fi done --

...it seems to work and I wasn't in a PERLescent mood.

Steve Orr Bozeman, Montana

-Original Message- From: John
Carlson [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 12, 2002 10:19 AM To: Multiple recipients of list ORACLE-L Subject: Re: Purging Managed Standby Database Archive Logs

Keep it simple. If you are on unix put this in your
crontab.
30 * * * * find /archive/oradata/YOURSID -type f -name
"1_*.arc" -mtime +0 -exec rm -f {} \;
This will run every hour on the half hour and purge everything
over 24 hours old.
Modify as you like. (-mtime +1 gives you 48 hours)
Yes, I know this could delete files that haven't been
applied but I figure if your standby is 24 hours
behind, it will need to be re-established anyway.
HTH, John
>>> [EMAIL PROTECTED] 12/12/02 08:04AM
>>> Any one have a ready-made routine to
purge the unneeded archives which have
been automagically applied to a managed standby
database?
I figure it needs to:
1. Query v$archived_log and v$log_history to get a list of the
archive logs (v$archived_log.name) where sequence# > [the max number you purged
the last time];
2. Cycle through the above list and remove the files;

3. Record the max(sequence#) from v$log_history for the next
purge.
Any other ideas/suggestions?
AtDhVaAnNkCsE!!! Steve Orr
Standing by in Bozeman, Montana --
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- Author: John Carlson
INET: [EMAIL PROTECTED]

75 matches

Mail list logo