Delete old archive logs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Delete old archive logs

  1. #1
    Join Date
    Nov 2001
    Location
    SC
    Posts
    23
    I need a script to delete archive logs that are more than 7 days. I will like this script to run once a week. I am running Oracle 8.1.7 on Windows 2k and 7.3.4 on NT4.0. Any assistance will be appreciated.
    Thanks
    :)

  2. #2
    Join Date
    Nov 2000
    Posts
    440
    Hey, i did a script that do this, but delete older than 30 days, just modify my script.


    Here's a way to delete your archive logs files automaticaly.
    This script delete archive logs that have more than 30 days.
    It is very useful when you need the disk space.
    Of course, make sure that you have valid backups,
    cuse you could delete archive that you need.


    First, i give grant to objects owned by the user sys.
    Then, i create a directory to use the bfilename function.
    After, i create a procedure that extract the name of the archive
    and one that finds old archive logs and delete them.


    You can execute that procedure manualy, or create a job that will execute
    that procedure periodicaly.

    ---------------------------------------------------------------------------------------
    connect sys/YOUR_SYS_PASSWORD


    grant select on v_$archived_log to YOUR_USER;
    grant execute on dbms_backup_restore to YOUR_USER;


    connect YOUR_USER/USER_PASSWORD

    create or replace directory ARCHIVEDIR as 'd:\ora8i\orant\oradata\archive';

    create or replace function proc_nom_fich(P_chain IN Varchar2) return varchar2 IS
    l_posi number;
    l_nom_fich varchar2(100);

    begin
    l_posi := length(P_chain);
    loop
    if substr(P_chain,l_posi,1) in ('/','\') then
    l_nom_fich := substr(P_chain,l_posi + 1);
    exit;
    else
    l_posi := l_posi - 1;
    if l_posi < 0 then
    exit;
    end if;
    end if;
    end loop;

    return(l_nom_fich);
    end;
    /

    create or replace procedure proc_dele_arch_log is
    arch_file bfile;
    arch_exis boolean;
    arch_file_name varchar2(100);

    cursor sel_archive is
    select name
    from v$archived_log
    where completion_time < sysdate - 30;

    begin
    for list in sel_archive loop
    arch_exis := FALSE;
    arch_file_name := proc_nom_fich(list.name);
    arch_file := bfilename('ARCHIVEDIR',arch_file_name);
    arch_exis := dbms_lob.fileexists(arch_file) = 1;

    if arch_exis then
    sys.dbms_backup_restore.deleteFile(list.name);
    end if;
    end loop;
    end;
    /


    ---------------------------------------------------------------------------------------

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    I wrote a winbatch that does this for me. There is also an Oracle package called DBMS_BACKUP_RESTORE which has some functions for purging archive files. But the functions do not work. They will however clear out the sys.v_$archived_log table quite well. Winbatch is a windows scripting tool that you can buy from http://www.winbatch.com. I have had good success using it.

  4. #4
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  5. #5
    Join Date
    Nov 2000
    Posts
    440
    DBMS_BACKUP_RESTORE.DELETEFILE work fine!

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    I do it in the foll. way.

    DELARCH.SQL
    ---------------------
    [code]
    REM *=============================================================================+
    REM | Notes: This SQL script is a program called by command script 'delarch.cmd' |
    REM | It generates spool file 'delgen.cmd' with DOS DEL command in it to |
    REM | delete Archive log files from the local machine's hard disk. |
    REM +=============================================================================+
    REM |
    REM | FILENAME
    REM | delarch.sql
    REM |
    REM | DESCRIPTION
    REM | Script used to delete archive log files from local PC. It deletes the files
    REM | which have been generated before the last friday's cold backup.
    REM |
    REM | USAGE
    REM | This script is called from delarch.cmd file.
    REM +============================================================================+
    set term off
    set feedback off
    set heading off
    spool on
    spool delgen.cmd
    SELECT 'DEL '||NAME FROM V$ARCHIVED_LOG
    WHERE
    TRUNC(COMPLETION_TIME) >= NEXT_DAY(SYSDATE,'FRIDAY')-14
    AND TRUNC(COMPLETION_TIME) <= NEXT_DAY(SYSDATE,'FRIDAY')-7;
    spool off
    exit

    [\code]



    DELARCH.CMD
    ------------------
    [code]

    REM *=============================================================================+
    REM | Notes: This command script is used to call 'delarch.sql' program, which |
    REM | generates spool file 'delgen.cmd' with DOS DEL command in it to |
    REM | delete Archive log files from local machine's hard disk. |
    REM | This command script is scheduled to run on every monday at 5:00 PM.|
    REM | It can be scheduled with the 'Scheduled tasks' feature of Windows. |
    REM +=============================================================================+
    REM |
    REM | FILENAME
    REM | delarch.cmd
    REM |
    REM | DESCRIPTION
    REM | Script used to call 'delarch.sql' file.
    REM |
    REM | USAGE
    REM | This script is scheduled to run on every monday at 5:00 PM.
    REM +============================================================================+
    sqlplus system/@ @delarch.sql
    call delgen.cmd

    [\code]


    Then, schedule DELARCH.CMD with AT command of NT.

    Pl. comment.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Nov 2001
    Location
    SC
    Posts
    23
    Thanks all of you for replying. I really appreciate the help. I have one more question.
    Sam, I tried your script and it is doing what I want, but I have some really old archive logs that do not fall into the 7 day range and I will like those deleted first before I start scheduling the script to run weekly. Sould you help me fix it so that they also get deleted.
    :)

  8. #8
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Sam, I tried your script and it is doing what I want, but I have some really old archive logs that do not fall into the 7 day range and I will like those deleted first before I start scheduling the script to run weekly. Sould you help me fix it so that they also get deleted. [/B]
    Hi,

    Either delete 'em manually first or change the date range in my query.

    Also pl. note that in my query, I have hard coded 'FRIDAY' as we take cold backup on Friday night.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  9. #9
    Join Date
    Nov 2001
    Location
    SC
    Posts
    23
    Thanks Sam. I tried that and it worked.
    :)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width