DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Archive log

  1. #11
    Join Date
    May 2001
    Posts
    736
    Here is some example script

    (
    echo RUN {
    echo ALLOCATE CHANNEL "ch00" TYPE 'SBT_TAPE' %ALLOC2%;
    echo sql 'alter system archive log current';
    echo BACKUP
    echo FILESPERSET 150
    echo FORMAT 'arch2-s%%s-p%%p'
    echo ARCHIVELOG ALL delete input;
    echo RELEASE CHANNEL "ch00";
    echo }

  2. #12
    Join Date
    Jan 2003
    Posts
    141
    Hi akhadar,
    Thanks for the scripts! Where should i implement the piece of script in veritas?

    Many Thanks,

  3. #13
    Join Date
    May 2001
    Posts
    736
    If u have a support from VERITAS ask the guys to implement your ideas.If it is production system u need to be careful.Actually this piece of script will be followed by hot backup of your database.

  4. #14
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by akhadar
    Actually this piece of script will be followed by hot backup of your database.
    Is this a typo ?? Did you mean to say "This script will be followed by hotbackup" or "This script will follow the hotbackup."

    The archive backup and deletion script should run after the HOTBACKUP is complete and not before.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  5. #15
    Join Date
    May 2001
    Posts
    736
    Sorry .Its typing error.Time to have a cup of tea.

  6. #16
    Join Date
    Jan 2003
    Posts
    141
    Thanks a lot for all!

  7. #17
    Join Date
    Nov 2000
    Posts
    440
    I prefer using oracle to delete my archive log.
    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;
    /

  8. #18
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by steeve123
    I prefer using oracle to delete my archive log.
    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;
    /
    nice script, ever use the retention policy in 9i?

    steve
    I'm stmontgo and I approve of this message

  9. #19
    Join Date
    Nov 2000
    Posts
    440
    Dont use 9i yet.

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