Looking for a procedure to delete archive logs automatically for Oracle 8I
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Looking for a procedure to delete archive logs automatically for Oracle 8I

  1. #1
    Join Date
    Dec 2001
    Posts
    1
    Do you know a procedure to delete archive logs automatically on a Windows platform?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Could you explain in a little more detail about what you are trying to do?

    Include details such as:
    When do you want the archivelogs deleted?
    What is your backup plan?
    What is your Oracle version?
    What is your OS Version?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    flemingdg and I work for the same company. We install Oracle at multiple remote sites, we turn on Archive log and instruct the local admins to purge the archive log files on a regular basis. They don't always do that. We are trying to create some kind of procedure to automatically delete the archive logs that are older then 30 days. We would want to run some kind of pl/sql procedure on a regular interval which would look at the right table, delete the archive log and maybe even the record pointing to the file. I know that pl/sql is limited in file handleing so I thought about writing a dos command file that accepted a file name as an input and deleted it. But I'm not sure how to run an external command in pl/sql. Any thoughts or help on managing this process would be appreciated.

    Thanks.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    I should have mentioned the following Full cold backups, Oracle 8.1.6, and Windows NT.

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

    You have not mentioned about backing up them before deletion.

    I have two solutions to yr problem.

    1. If you have Veritas Backup Exec, there is a File Grooming feature which takes the backup of selected files and then deletes them from the server. You can define number of days and other options.

    2. I have got following script to delete Archive log files which are older than n number of days but it does not have any mechanism of backing them first.
    ---------------------------------------------------------------------
    Here's a way to delete your archive log files automatically.
    This script deletes archive logs that are more than 30 days old.
    It is very useful when you need to clear some disk space.
    Of course, make sure that you have valid backups or you risk deleting archives that you
    may need.

    First, the script gives a grant to objects owned by the user sys.
    Then, it creates a directory to use the bfilename function. Finally,
    it creates two procedures: One that extracts the name of the archive and another
    that finds old archive logs and deletes them.

    You can execute this procedure manually, or create a job that will execute it periodically.


    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;
    /

    declare
    l_job_nb number;
    l_inst_nb number;
    begin
    select sys.jobseq.nextval into l_job_nb from dual;
    dbms_job.submit(l_job_nb, 'proc_dele_arch_log;',
    to_date(sysdate,'DD-MON-YYYY HH24:MI'), 'sysdate + 7');
    commit;
    end;
    /


    Thanks,

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

  6. #6
    Join Date
    Jan 2002
    Posts
    474
    Sam,

    The procedure is working great munaully, Could you or any body in here tell me how to create the job to run this???

    Thanks

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

    Surprise to see you after a long time.

    For submitting the job in Oracle, foll. is the procedure.

    begin
    DBMS_JOB.ISUBMIT
    (job => 12 --> or any number,
    what => '',
    next_date => trunc(sysdate) + 17/24 + 00/1440,
    interval => 'sysdate + 1',
    no_parse => FALSE);
    commit;
    end;

    Above is just an example. Pl. replace the values in there appropriately.

    How you manage to back those archived logs up before deleting them?

    Thanks,


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

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by flemingdg
    Do you know a procedure to delete archive logs automatically on a Windows platform?
    Why don't you consider switching to RMAN? You can very easilly handle such sort of problems there. You will not have to write adiitional functions and procedure to accomplish so simple tasks.

  9. #9
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by julian
    Originally posted by flemingdg
    Do you know a procedure to delete archive logs automatically on a Windows platform?
    Why don't you consider switching to RMAN? You can very easilly handle such sort of problems there. You will not have to write adiitional functions and procedure to accomplish so simple tasks.
    How do I use RMAN to delete old archive logs.

    Please advice.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  10. #10
    Join Date
    Apr 2002
    Posts
    28
    Ronnie,

    Here is a script which does exactly what you are requesting - it does an RMAN backup of the archivelog files and puts the backup into a different directory (where you can copy it to tape etc).

    http://www.dotcomsolutionsinc.net/pr...in901_85_.html

    This script was created with Installgen which automates creating backup/recovery scripts along with creating the documentation for performing 15 different database recovery scenarios.

    You may download a demo version of Installgen so that you may conveniently look at or modify the scripts on your own computer:
    http://www.dotcomsolutionsinc.net/do...are/index.html

    Or you may choose to browse the individual output files via the web:
    http://www.dotcomsolutionsinc.net/pr...es_win901.html

    Here is a full-featured RMAN backup script I like to use for situations where I am not using a recovery catalog:
    http://www.dotcomsolutionsinc.net/pr...in901_29_.html

    RMAN is preferred by Oracle over the Hot Backup method because it generates less redo in the database and you can also throttle the amount of I/O used by RMAN. (I have implemented this functionality on my scripts.) This means that if necessary, you can perform the RMAN backup while users are actively using the server without consuming all of the I/O bandwidth of the server. You will need to adjust the default I/O rate to a value which is suitable for your server.

    David Simpson
    http://www.dotcomsolutionsinc.net
    David Simpson
    www.dotcomsolutionsinc.net

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