Automatically delete obsolete RMAN backups
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Automatically delete obsolete RMAN backups

Hybrid View

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

    Does anybody have a script which can automatically delete all
    obsolete RMAN backups including obsolete archive log backup sets from the catalog.

    I am using 8.1.7.0.0 RMAN

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Can you clarify what you mean by obsolete?

    We use the following scripts to delete backups and archive logs over 2 weeks old:

    --------------------------------
    export scriptnode=/home/oracle/rman_scripts

    ${ORACLE_HOME}/bin/sqlplus rman/rman@RCAT @${scriptnode}/CreateDelBackupsets.sql PROD

    export ORACLE_SID=PROD
    ${ORACLE_HOME}/bin/rman cmdfile=${scriptnode}/DelBackupsets.txt

    -------------------------------
    CreateDelBackupsets.sql:
    SET linesize 200 pagesize 0
    SET heading OFF feedback OFF echo OFF
    SET verify off
    SET recsep off

    SPOOL /home/oracle/rman_scripts/DelBackupsets.txt
    SELECT 'connect target sys/${SYS_PASS}' FROM dual
    /
    SELECT 'connect catalog rman/rman@RCAT' FROM dual
    /
    SELECT 'allocate channel for delete type ''SBT_TAPE''' FROM dual
    /
    SELECT 'change backupset '||bs_key||' delete;'
    FROM rc_backup_set, rc_database
    WHERE completion_time < SYSDATE - 14
    AND status = 'A'
    AND rc_database.db_key=rc_backup_set.db_key
    AND rc_database.name = '&1'
    /
    SELECT 'release channel;' FROM dual
    /
    SPOOL off
    SET feedback ON heading ON echo ON
    SET pagesize 100 linesize 120
    EXIT

    -----------------------------------------------
    DelBackupsets.txt looks something like this:

    connect target sys/${SYS_PASS}
    connect catalog rman/rman@RCAT
    allocate channel for delete type 'SBT_TAPE'
    change backupset 104950 delete;
    change backupset 104990 delete;
    change backupset 105011 delete;
    release channel;

    Good Luck!
    Jodie


    [Edited by jodie on 06-19-2002 at 04:44 PM]

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by jodie
    Can you clarify what you mean by obsolete?
    [Edited by jodie on 06-19-2002 at 04:44 PM]
    Thanks for the script Jodie,

    By obsolete I meant all databases prior to the last one., but I like your idea better.

    This is what my setup is

    I backup whole database on disk every day. My sysadmin picks up the backup from the disk onto a tape every night.

    At any given point of time i have last 2 days of whole database backup on the disk and rest are on the tape. I delete the backups older than 2 days from the disk using O/S command based on the files time stamp (this somewhat scares me )and leaving their entry intact in the catalog.

    Now after a week or maybe 2 , I would clean up the catalog using your script.

    Can somebody please confirm whether my setup is Ok or not .

    And Jodie wil your script work for what I am trying to do. I am using RMAN 8.1.7.0.0

    Please suggest.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  4. #4
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Ronnie,
    I'm really not sure what will happen if you use the script I posted. That script will try to delete it from disk/tape (Our script uses type tape... you would use type disk). I'm not really sure what would happen if the file wasn't actually on disk. My hunch is that it would work, just ignore that it wasn't physically on disk... but you should obviously test this..

    You may also want to look into CROSSCHECK. One warning on crosscheck though... especially for anyone whose backups are on tape. We ran a crosscheck on our backups once, and RMAN could not detect whether the files were there on tape using the Media Management Layer. So what happened is it deleted all of our backupsets from the catalog, but they WERE on the tapes. Ouch! (We had to immediatly do an incr0 since all the backups were essentially gone). You shouldn't have problems with it if you're using disk.. but again.. make sure to test it.

    Can you let us know what happens with the delete from disk? We may have to do this at some point in the future, so it would be good to know if it works!

    Oh - we are also using RMAN 8.1.7.3 catalog with 8.1.6.3 database and executable... so these scripts should work fine for you.

    Good Luck!
    Jodie


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

    I tried your script and it works only if the backup set exists on the disk. In my case it wont be because I would delete it from the disk.

    The script fails and to overcome the problem i am using crosscheck and then deleting expired backups.

    I have updated your script to

    connect rman/rman@reccatdb

    SET linesize 200 pagesize 0
    SET heading OFF
    set feedback OFF
    set echo OFF
    SET verify off
    SET recsep off

    SPOOL C:\RMANBACKUPS\DelBackupsets.txt

    SELECT 'connect target internal/eh458lb@brutweb' FROM dual ;

    SELECT 'connect catalog rman/rman@Reccatdb' FROM dual ;

    SELECT 'allocate channel for maintenance type disk;' FROM dual ;

    SELECT 'change backupset '||bs_key||' crosscheck;'
    FROM rc_backup_set, rc_database
    WHERE trunc(completion_time) <= trunc(SYSDATE) - 14
    AND status = 'A'
    AND rc_database.db_key=rc_backup_set.db_key
    AND rc_database.name = 'SID9' ;

    select 'delete expired backup;' from dual;

    SELECT 'release channel;' FROM dual ;
    SELECT 'exit' FROM dual ;

    SPOOL off

    exit


    Thanks for your help.


    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

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