Oracle-L: Re: Purging Managed Standby Database Archive Logs

From: <JApplewhite_at_austin.isd.tenet.edu>
Date: Thu, 12 Dec 2002 11:48:46 -0800
Message-ID: <F001.00518DBF.20021212114846@fatcity.com>

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_at_austin.isd.tenet.edu

                                                                                                           
                      "Orr, Steve"                                                                         
                      <sorr_at_rightnow.co        To:       Multiple recipients of list ORACLE-L              
                      m>                        <ORACLE-L_at_fatcity.com>                                     
                      Sent by:                 cc:                                                         
                      root_at_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:

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];
Cycle through the above list and remove the files;
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_at_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_at_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).

Received on Thu Dec 12 2002 - 13:48:46 CST