-
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.
-
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]
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|