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)
[EMAIL PROTECTED]
"Orr, Steve"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L
m> <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
