Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gBD0BuZ11787 for ; Thu, 12 Dec 2002 18:11:56 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gBD0Bt311782 for ; Thu, 12 Dec 2002 18:11:56 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id MAA54017; Thu, 12 Dec 2002 12:54:47 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00518DDA; Thu, 12 Dec 2002 11:48:47 -0800 Message-ID: Date: Thu, 12 Dec 2002 11:48:47 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Orr, Steve" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Orr, Steve" Subject: RE: Purging Managed Standby Database Archive Logs Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2A217.4A479D30" ------_=_NextPart_001_01C2A217.4A479D30 Content-Type: text/plain; charset="iso-8859-1" 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 < $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----- Sent: Thursday, December 12, 2002 10:19 AM To: Multiple recipients of list ORACLE-L 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 >>> sorr@rightnow.com 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: jcarlson@CJ.COM ------_=_NextPart_001_01C2A217.4A479D30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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=3D"/u01/app/oracle/bin"
SEQ_FILE=3D"$ORAUBIN/last_purged_seqno.txt"
LAST_PURGED_SEQ=3D`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=3D'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:jcarlson@CJ.COM]
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


>>> sorr@rightnow.com 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: jcarlson@CJ.COM

------_=_NextPart_001_01C2A217.4A479D30-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: sorr@rightnow.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).