----- Original Message -----
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
<<EOSQL
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]
