To delete Archive Logs in RMAN backupset
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: To delete Archive Logs in RMAN backupset

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    I use the following script to backup archivelogs ...

    Code:
    replace script backup_al_all {
      execute script alloc_all_disks;
      backup
        filesperset 20
        format 'f:\frman\oracle\admin\rman\al_t%t_s%s_p%p'
        (archivelog all);
      execute script rel_all_disks;
    }
    Now, I want to delete the archive log backups dating one month back. How can I do this ?

    For datafile backup, I do the following ...

    Code:
    list all backupset of database;
    
    allocate channel for delete type disk;
    change backupset (number_from_list) delete;
    Now, how can I do a similar maintenance for archive log backupsets ?

    Thanks for your input.




  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    You can use "delete input" option with archive log all. It will delete all archive log you just backed up.

    Sanjay

  3. #3
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Solution one:

    delete backup of archivelog until time='sysdate-30';


    Solution two:

    configure retention policy to recovery window of 30 days;

    delete obsolete;
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  4. #4
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Originally posted by Calvin_Qiu


    configure retention policy to recovery window of 30 days;

    How can I do this ? Any docs online ??

    Thanks.

    BTW, I found out the following...for archivelog maintenance.

    Code:
    list backupset of archivelog all;
     
    allowcate channel for delete type disk;
    change backupset (id_from_list) delete;
    [Edited by ggnanaraj on 10-16-2002 at 04:42 AM]

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    retention policy is for 9i

  6. #6
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Originally posted by Calvin_Qiu


    delete backup of archivelog until time='sysdate-30';

    The above also does not work in my scenario. I must have said it in the beginning ... that the database is Oracle 8.0.3 EE.




  7. #7
    Join Date
    Nov 2001
    Posts
    335
    Take a look at the script below. It needs some ajustments for your specific environment. If you run in sqlplus it will generate RMAN script to delete backup for both:
    datafiles and archive logs.

    set echo off;
    set verify off;
    set feedback off;
    SET HEADING OFF;
    SET PAGESIZE 0;
    set linesize 132;
    Prompt
    Prompt
    ACCEPT id char PROMPT 'Enter the full or partial Database Identifier ex UP003: '
    ACCEPT days number PROMPT 'Enter the number of days you want to retain: '

    /* This program will create a script to delete old database and archive backupsets */
    /* saving one extra day of archived logs */
    /* Backups sets must be in the format DBID_db_* */
    /* Archive sets must be in the format DBID_ar_* */

    spool delete_backup.rmn;

    select 'allocate channel for maintenance type "sbt_tape" ;' from dual;
    select unique 'CHANGE BACKUPSET ' || bs_key || ' DELETE ;'
    , ' # '||to_char(completion_time, 'MM/DD/YYYY')|| ' Archive Logs'
    from bp
    where handle like '%'||upper('&id')||'_ar%' and
    completion_time < (sysdate - (&days )) ;
    select unique 'CHANGE BACKUPSET ' || bs_key || ' DELETE ;'
    ,' # '||to_char(completion_time, 'MM/DD/YYYY')||' Datafiles' from bp
    where handle like '%'||upper('&id')||'_db%' and
    completion_time < (sysdate - &days )
    order by 1;
    select 'release channel ;' from dual;
    spool off;
    set pagesize 60;
    set feedback on;
    set verify on;
    set echo on;
    exit;
    One, who thinks that the other one who thinks that know and does not know, does not know either!

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