Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 25 March 2012

RMAN Weekly and Daily Backup Scripts

Database Version : 9.2.0.7.0
OS platform : Window 2003 Server
Archive log : Enabled
---------------------------------------------------------------------------------------------------------------------
My backup procedure: Fri Sat Sun Mon Tue Wed Thu Fri
Incremental Level 0      L0  -    -      -      -      -      -   L0
Incremental Level 1      -    L1  L1   -      L1    L1  L1   -
Inc_level_cumulative -    L1  L1  C1    L1    L1  L1   -
--------------------------------------------------------------------------------------------------------------------
Weekly_BACKUP.RCV contents
--------------------------------------------------------------------------------------------------------------------
# Resync the catalogue
resync catalog;
allocate channel for delete type disk;

# validate backups older than 15 days. If they are no longer on disk then mark then for deletion.
crosscheck backup completed before 'SYSDATE-15';
delete expired backup;
# lists backups that not needed to recover the database to within 15 days and delete those backup.
REPORT OBSOLETE RECOVERY WINDOW OF 15 DAYS;
Delete obsolete;
# Backup Incremental Level 0 database with Archivelog all delete input
run {
allocate channel d1 type disk FORMAT "D:\BACKUP\RMAN\HOTBACKUP\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "D:\BACKUP\RMAN\HOTBACKUP\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d3 type disk FORMAT "D:\BACKUP\RMAN\HOTBACKUP\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d4 type disk FORMAT "D:\BACKUP\RMAN\HOTBACKUP\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d5 type disk FORMAT "D:\BACKUP\RMAN\HOTBACKUP\Weekly_%T_L0_%d-%s_%p.db";
sql 'alter system archive log current';
backup
incremental level 0
tag Weekly_Full_database
filesperset 10
format "D:\BACKUP\RMAN\HOTBACKUP\Weekly_%T_L0_%d-%s_%p.db"
database archivelog all delete all input
;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
}
# Reports which database files require backup because they have been affected by some NOLOGGING operation
REPORT UNRECOVERABLE;

# Reports which database files need to be backed up to meet a configured or specified retention policy
REPORT NEED BACKUP RECOVERY WINDOW OF 15 DAYS;
---------------------------------------------------------------------------------------------------------------------
Daily_BACKUP.RCV contents
---------------------------------------------------------------------------------------------------------------------
# Backup database Cumulative as well as differential incremental level 1 backup along with Archivelog.
run {
allocate channel d1 type disk FORMAT "D:\BACKUP\RMAN\HOTBACKUP\Daily_%T_L1_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "D:\BACKUP\RMAN\HOTBACKUP\Daily_%T_L1_%d-%s_%p.db";
allocate channel d3 type disk FORMAT "D:\BACKUP\RMAN\HOTBACKUP\Daily_%T_L1_%d-%s_%p.db";
allocate channel d4 type disk FORMAT "D:\BACKUP\RMAN\HOTBACKUP\Daily_%T_L1_%d-%s_%p.db";
allocate channel d5 type disk FORMAT "D:\BACKUP\RMAN\HOTBACKUP\Daily_%T_L1_%d-%s_%p.db";

backup
incremental level 1
cumulative
tag Daily_Cumulative_Backup
filesperset 5
format "D:\BACKUP\RMAN\HOTBACKUP\Daily_%T_CUMUL_%d-%s_%p.db"
Database PLUS ARCHIVELOG
;
sql 'alter system archive log current';
backup
incremental level 1
tag Daily_Differential_Backup
filesperset 5
format "D:\BACKUP\RMAN\HOTBACKUP\Daily_%T_INCR_%d-%s_%p.db"
Database PLUS ARCHIVELOG
;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
}
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I Scheduled Above backup procedure Every day 1.00 am according to backup procedure I created two  Seperate windows NT COMMAND script and two Separate RCV file. One for Weekly Backup and one for Daily Backup Procedure. Weekly Backup is scheduled on Friday only while Daily backup is scheduled rest of the Week days (Sat-Thurs).
My command scripts are: Weekly_backup.bat, Daily_backup.bat
C:\oracle1\bin\RMAN TARGET SYS/***@ORCL3 CATALOG RMAN/RMAN@SHAAN CMDFILE=D:\BACKUP\RMAN\Scripts\Weekly_BACKUP.RCV LOG=D:\BACKUP\RMAN\HOTBACKUP\Weekly_BACKUP.LOG
C:\oracle1\bin\RMAN TARGET SYS/***@ORCL3 CATALOG RMAN/RMAN@SHAAN CMDFILE=D:\BACKUP\RMAN\Scripts\Daily_BACKUP.RCV LOG=D:\BACKUP\RMAN\HOTBACKUP\Daily_BACKUP.LOG
Note: If you donot want to overwrite logfile check the last link of the below link. To generate unique loogfiles each day with date and time.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Important link related to Rman backup:

4 comments:

  1. It's very useful post and working fine......Thanks a lot.

    ReplyDelete
  2. Thanks much

    -Satya
    http://satya-dba.blogspot.com/2009/01/rman-was-first-introduced-in-oracle8.html

    ReplyDelete
    Replies
    1. I really appreciate your steps, i have seen some of your post, superb and useful for all of us.

      Delete
  3. It is very useful , working and usable

    ReplyDelete