Script to housekeeping Archived logfiles on Win2K
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Script to housekeeping Archived logfiles on Win2K

  1. #1
    Join Date
    Apr 2001
    Posts
    110

    Script to housekeeping Archived logfiles on Win2K

    Hi,

    Does anyone has a good script to purge Oracle archived logfiles on Win2k?

    I'm too comfortable with using UNIX to do the same job, but don't know the right commands on Win2k to do it.

    Just need a DOS script to purge archived logfiles older than 2 days.

    Hope someone can help.

    Cheers
    Peng Soon
    What's next after 10g?

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    1) If you use RMAN and set a recovery window it will delete old log files when they are outside the recovery window if you issue the following command:

    delete obsolete;

    2) install cygwin on your windows server and use the same UNIX script you normally would.

    3) Install ActiveState Perl and write a script to do it.

    4) Write a little program (Java, VB etc.) to do it for you.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    And what happens if he doesn't have RMAN?

    If you're not using RMAN let me know and I'll show you a nifty .bat script.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by OracleDoc
    And what happens if he doesn't have RMAN?

    If you're not using RMAN let me know and I'll show you a nifty .bat script.
    we're using RMAN but I am interested in your script...please share with the class
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    If I do...do I get extra credit or an extra 15 minutes at recess?

    basically what it does is move all the backed up'd stuff to another disk so you have a not only last nights stuff but the night previous. So you have basically 2 days worth of backups /exports. This is only about a 2 gig database so it takes all of maybe 15 minutes to do the whole copy and backup.

    Code:
    del /Q f:\oradata\istora\old_archive\*
    del /Q f:\oradata\istora\old_backup\*
    del /Q f:\oradata\istora\old_export\*
    move H:\oradata\istora\backup\*.* f:\oradata\istora\old_backup
    move H:\oradata\istora\backup\archive\*.* f:\oradata\istora\old_archive
    move h:\oradata\istora\backup\export\*.* f:\oradata\istora\old_export
    exp system/ file=h:\oradata\istora\backup\export\istora_full.dmp full=y direct=y log=h:\oradata\istora\backup\export\istora_full.log
    c:\oracle\ora92\bin\sqlplus system/ @c:\oracle\ora92\maint\hotbackup.sql
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    thats cool but what if you are in the middle or writing an archive when you "move H:\oradata\istora\backup\*.* " for example?
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by stmontgo
    thats cool but what if you are in the middle or writing an archive when you "move H:\oradata\istora\backup\*.* " for example?
    Under Winoze the move will fail (OS level locking on the file), under UN*X you need to be more careful.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by DaPi
    Under Winoze the move will fail (OS level locking on the file)
    This can't be true, can it?

    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."

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I hope I read what stmontgo intended rather than what he wrote! Windoze won't let you move a file while it's being written. (You can move a backup file while an archlog is being written )

  10. #10
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Originally posted by stmontgo
    thats cool but what if you are in the middle or writing an archive when you "move H:\oradata\istora\backup\*.* " for example?
    Yes, you are correct that's why I don't blow away the actual archive logs yet I do it in the actual hot backup like below.
    Code:
    set serveroutput on
    set feedback off
    host del h:\oradata\istora\backup\*.* /q
    spool c:\oracle\ora92\maint\hotbkup.sql
    declare 
    cursor tbspc is 
    select tablespace_name from 
    dba_tablespaces
    where
    contents <> 'TEMPORARY'
    order by tablespace_name;
    
    cursor datfil (tbspcname varchar2) is 
    select df.file_name from dba_data_files df, v$datafile d
    where
    df.tablespace_name = tbspcname
    and
    df.file_name = d.name
    and
    d.enabled like '%WRITE%'
    order by df.tablespace_name;
    
    tabcur tbspc%rowtype;
    datcur datfil%rowtype; 
    
    sql_string Varchar2(500);
    tsname1 varchar2(30);
    tsname2 varchar2(30);
    begin
    open tbspc; 
    loop
    fetch tbspc into tabcur;
    exit when tbspc%notfound;
    sql_string := 'ALTER TABLESPACE ' || tabcur.tablespace_name || ' BEGIN BACKUP;';
    dbms_output.put_line(sql_string);
    for datcur in datfil(tabcur.tablespace_name)
    loop
    sql_string := 'host copy ' || datcur.file_name || ' h:\oradata\istora\backup';
    dbms_output.put_line(sql_String);
    
    end loop;
    sql_string := 'ALTER TABLESPACE ' || tabcur.tablespace_name || ' END BACKUP;';
    dbms_output.put_line(sql_string);
    end loop;
    close tbspc;
    dbms_output.put_line('host del h:\oradata\istora\backup\control\*.* /q');
    dbms_output.put_line('alter database backup controlfile to trace;');
    dbms_output.put_line('alter database backup controlfile to ''h:\oradata\istora\backup\control\'|| to_char(sysdate,'ddmmyyhhmi') || '.ctl'';');
    dbms_output.put_line('alter system archive log all;');
    dbms_output.put_line('alter system archive log current;');
    dbms_output.put_line('host copy h:\oradata\istora\arch\*.* h:\oradata\istora\backup\archive');
    dbms_output.put_line('host del h:\oradata\istora\arch\*.* /q');
    dbms_output.put_line('exit;');
    dbms_output.put_line(' ');
    end;
    /
    
    spool off
    
    @c:\oracle\ora92\maint\hotbkup.sql
    
    exit
    Geessh do I have to give away all my secrets?
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

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