Deleting Old Archive Log Files
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Deleting Old Archive Log Files

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    How do schedule a job to delete old archive log files. say for example all archive log files older than 3 days.

    I have a Windows 2000 server.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Nov 1999
    Posts
    226

    Script for deleting archive logs

    *******************************************************************************
    Rem This procedure creates a command file in C:\SCRIPTS called
    Rem 'arch_del.cmd' which can be scheduled to execute by using the
    Rem AT command in Windows NT. This procedure is submitted as
    Rem a batch job which is scheduled once a week and deletes old
    Rem archives.

    Rem This procedure is designed for Oracle version 7.x. For Oracle8,
    Rem check trunc(time) < trunc(sysdate)-10, where time is in the DATE
    Rem field and the table name is V$ARCHIVED_LOG. In version 7.x,
    Rem time is VARCHAR2. The procedure uses a pre-defined package
    Rem called UTL_FILE. The init.ora should be modified to include
    Rem a parameter called UTL_FILE_DIR=C:\SCRIPTS so that it can
    Rem write in that destination directory.

    *******************************************************************************
    CREATE OR REPLACE PROCEDURE sp_arch_delete AS

    arch_name UTL_FILE.FILE_TYPE;

    arch_file varchar2(100);

    BEGIN

    DECLARE CURSOR c1 IS SELECT 'del '||name name
    FROM V$ARCHIVED_LOG
    WHERE trunc(completion_time) < TRUNC(sysdate)-10;

    BEGIN

    arch_name := UTL_FILE.FOPEN('C:\SCRIPTS','arch_del.cmd','w');

    FOR rec IN c1

    LOOP

    arch_file := rec.name;

    UTL_FILE.PUT_LINE(arch_name,arch_file);

    END LOOP;

    UTL_FILE.FCLOSE(arch_name);

    END;

    END;

    /

    *********************************************************************************************
    Rem The following DBMS package procedure submits the batch job to execute
    Rem the above procedure once every week. The job is submitted by connecting
    Rem as internal through the Server Manager. The job executes the procedure
    Rem at 7:00 p.m. on the day you submit the job and 7:00 p.m. at the interval of
    Rem seven days.
    ********************************************************************************************
    SVRMGR> VARIABLE jobno number;

    SVRMGR> begin

    2> EXECUTE DBMS_JOB.SUBMIT(:jobno,

    3> 'sp_arch_del;',trunc(sysdate)+19/24,'(trunc(sysdate+19/24)+7');

    4> end;

    5> /
    *********************************************************************************************
    Rem The following AT command executes F:\SCRIPTS\arch_del.cmd every
    Rem Monday night at 8.00 p.m and creates a log file at F:\SCRIPTS\arch_del.log.
    *********************************************************************************************
    F:> at 20:00 /every:M "C:\SCRIPTS\arch_del.cmd 1>C:\SCRIPTS\arch_del.log 2>&1"



  3. #3
    Join Date
    Nov 1999
    Posts
    226

    This is modified for Oracle 8 i

    Thanks

  4. #4
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    There is no way to automate this thru Oracle.

    You must write a script to do this, and schedule it thru 'AT' - windows scheduler.

    I have a script that deletes the files after the cold backup.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Originally posted by khussain
    There is no way to automate this thru Oracle.

    You must write a script to do this, and schedule it thru 'AT' - windows scheduler.

    I have a script that deletes the files after the cold backup.
    there is way to do it in Oracle

    check
    http://www.dbasupport.com/forums/sho...threadid=18680

  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by pando
    Originally posted by khussain
    There is no way to automate this thru Oracle.

    You must write a script to do this, and schedule it thru 'AT' - windows scheduler.

    I have a script that deletes the files after the cold backup.
    there is way to do it in Oracle

    check
    http://www.dbasupport.com/forums/sho...threadid=18680
    Pando,

    Clicking on the Link takes me to dbasupport home page.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448

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