Message-ID: <F001.00518DBF.20021212114846@fatcity.com>
Date: Thu, 12 Dec 2002 11:48:46 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: JApplewhite@austin.isd.tenet.edu
Subject: Re: Purging Managed Standby Database Archive Logs
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


Steve,

It's pretty simple.  No need to record anything - see the Order By
Descending in the SQL below.

Here are a batch file and a SQL script I used on an 8.1.7 Standby DB under
Win2k.  It ran reliably for months as a Scheduled Task once every hour and
got rid of all applied logs, assuming that there would never be any more
than 400 archived redo logs sent over from the main DB in a single hour.
You could easily change it to shell scripts under UNIX, as well as the
formatting of your archived redo log names.

Delete_Applied_Archived_Redo_Logs_main.bat
-----------------------------------------------------------------------------------------------------------------------
Set ORACLE_SID=MySID

SQLPlus internal @C:
\Oracle\Admin\MySID\Delete_Applied_Archived_Redo_Logs_sub.sql

C:\Oracle\Admin\MySID\Delete_Applied_Archived_Redo_Logs_Delete.bat
-----------------------------------------------------------------------------------------------------------------------


Delete_Applied_Archived_Redo_Logs_sub.sql
-----------------------------------------------------------------------------------------------------------------------
Set FeedBack Off
Set LineSize 200
Set PageSize   0
Set TrimSpool On

Spool C:\Oracle\Admin\MySID\Delete_Applied_Archived_Redo_Logs_delete.bat

Select 'Del U:\Oracle\OraData\MySID\Archive\ARC' || Trim(v.Seq) || '.LOG'
>From   (
        Select To_Char(Sequence#,'09999') Seq
        From   v$Log_History
        Order By Sequence# Desc
       ) v
Where  RowNum < 401
;

Spool Off

Exit
-----------------------------------------------------------------------------------------------------------------------

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
JApplewhite@austin.isd.tenet.edu


                      "Orr, Steve"                                                                         
                      <sorr@rightnow.co        To:       Multiple recipients of list ORACLE-L              
                      m>                        <ORACLE-L@fatcity.com>                                     
                      Sent by:                 cc:                                                         
                      root@fatcity.com         Subject:  Purging Managed Standby Database Archive Logs     
                                                                                                           
                                                                                                           
                      12/12/2002 10:04                                                                     
                      AM                                                                                   
                      Please respond to                                                                    
                      ORACLE-L                                                                             
                                                                                                           
                                                                                                           
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: 
  INET: JApplewhite@austin.isd.tenet.edu

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).