Chapter 11. Day-to-day monitoring on Windows 2000 185
11.4 Automatic deletion of old backups
Automating deletion of TDP for Oracle for Windows should be planned and
tested carefully before implementation in a production environment. There is
no simple command that can be run to easily deactivate backups based on
redundancy or time. One way to get around this is to use command files for
automation. Another way is to use tags as part of your backup process and
use the same tags for deletion.
11.4.1 Command files to automate backup deletion
For this example to work, you must create a directory c:\oracle\admin\scripts.
In this directory you need to create two files: autodelete.cmd and delete.sql,
using a text editor. If you modify this directory path, you must modify the
contents of both files. We needed to modify every occurrence of BEACH to
match our database SID. You will need to modify
manager in the occurrences
of
internal/manager to be the correct password for your database. And you
must modify
rman/rman@rio to match the username, password, and database
of your recovery catalog.
The command file autodelete.cmd does three things, sets ORACLE_SID,
calls
sqlplus to execute the delete.sql file, and calls RMAN using the
delete_backup.rman file created by
sqlplus.
Figure 56. c:\oracle\admin\scripts\autodelete.cmd
The delete.sql creates a file c:\oracle\admin\scripts\delete_backup.rman
which logs on to the Oracle database and queries the v$backup_piece for all
backup pieces that are not deleted(deleted=NO) and were backed up 3 days
ago (completion_time < SYSDATE - 3). You can modify the
where statement
to match any backup pieces that you want.
set ORACLE_SID=BEACH
sqlplus internal/manager@beach @c:\oracle\admin\scripts\delete
rman target internal/manager@beach rcvcat rman/rman@rio cmdfile=
c:\oracle\admin\scripts\delete_backup.rman
186 Backing Up Oracle using Tivoli Storage Management
Figure 57. c:\oracle\admin\scripts\delete.sql
11.4.2 Using tags to help with deletion
Tags can be used to help automate the deletion of backups. In this example
we are doing weekly backups on Sunday, and daily backups during the week.
For the daily backups, we set the tag to be
Beach Daily.
col handle for a30
set echo off
set verify off
set feedback off
set heading off
set pagesize 0
spool c:\oracle\admin\scripts\delete_backup.rman;
select 'allocate channel for delete type ''SBT_TAPE'' ;' from dual;
select 'change backuppiece '''||handle||''' delete;'
from v$backup_piece wh
ere completion_time < SYSDATE - 3 and deleted = 'NO' ;
select 'release channel;' from dual;
spool off;
exit;
run {
allocate channel t1 type 'sbt_tape' parms
'ENV=( DSMO_AVG_SIZE=256)';
allocate channel t2 type 'sbt_tape' parms
'ENV=( DSMO_AVG_SIZE=256)';
set maxcorrupt for datafile 1 to 0 ;
backup
full
filesperset 3
format '%d/%t/%s/%p'
tag 'Beach Daily'
(database);
release channel t1 ;
release channel t2 ;
}
Chapter 11. Day-to-day monitoring on Windows 2000 187
We did a couple of backups using that tag; now we can list backups using
that tag qualifier:
Now we can use the tag qualifier to delete all backup pieces with that tag.
We created a text file called delete_tag.rman that contained these entries.
Then we can call this RMAN script using a Windows command file that we
created, delete_tag.cmd, which sets the ORACLE_SID and then executes the
RMAN command. That way, you can schedule the delete_tag.cmd using
Windows or TSM scheduling.
RMAN> list backup tag = 'Beach Daily';
RMAN-03022: compiling command: list
List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
3375 46 426020121 0 426020065 51 02-APR-01
List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- -----------------------
3378 1 1 AVAILABLE 02-APR-01 BEACH/426020065/51/1
List of Datafiles Included
File Name LV Type Ckp SCN Ckp Time
---- ------------------------------------- -- ---- ---------- ------------
3 C:\ORACLE\ORADATA\BEACH\USERS01.DBF 0 Full 653580 02-APR-01
6 C:\ORACLE\ORADATA\BEACH\INDX01.DBF 0 Full 653580 02-APR-01
8 C:\ORACLE\ORADATA\BEACH\OEM_REPOSITORY.ORA 0 Full 653580 02-APR-
allocate channel for delete type 'sbt_tape';
change backuppiece tag = 'Beach Daily' delete;
release channel;
set ORACLE_SID=BEACH
rman target internal/manager@beach rcvcat rman/rman@rio
cmdfile=c:\oracle\admin\scripts\delete_tag.rman
188 Backing Up Oracle using Tivoli Storage Management

Get Backing Up Oracle Using Tivoli Storage Management now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.