Re: Purging Managed Standby Database Archive Logs

RE: Purging Managed Standby Database Archive LogsFor pre 9i release2 there is following Note on metalink

        Doc ID:  Note:190311.1 
        Subject:  ** How to delete an Operating System file from PL/SQL 

  This uses a java stored procedure

  HTH
  Shaleen

• Original Message ----- From: Yechiel Adar To: Multiple recipients of list ORACLE-L Sent: Monday, December 16, 2002 12:38 AM Subject: Re: Purging Managed Standby Database Archive Logs

    Hello Steve

    If you are on 9.2 you can use utl_file.fremove directly from the sql.

    Yechiel Adar
    Mehish

• Original Message ----- From: Orr, Steve To: Multiple recipients of list ORACLE-L 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:jcarlson_at_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_at_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_at_CJ.COM 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Shaleen
  INET: shgarg_orafaq_at_hotmail.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_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).