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.***

Monday, 3 September 2012

Automate Rman Backup on Windows Environment

Tested: Oracle 9i on Windows 2003 Env.
Overview: This post is related to manage automation of RMAN backup with physical deletion of backup from target as well as backup server. Consider the scenario where backup is taken by using Cross storage technique and we have limited space on each production server (for only one set of backup). As we have designed new backup server (specially for rman) with sufficient storage space our aim is to store one set of backup on each target server and 4 set of backup on backup server in order to maintain whole month backup. The script is scheduled using windows task scheduler check the related post ‘How to schedule script”. Off course before scheduling test the script in order to estimate the backup size and time. Finally at the end after completion of all the target DB backup scheduled the catalog backup. Here our focus is only for rman hot backup you can also maintain RMAN cold backup on monthly basis.
Fig. Cross Backup Storage Technique
Managing the RMAN backup using the Cross Backup Storage Technique will saves the disk space specially when there is no more extra space on your Production system to store more than one set of backup and also It is useful in case of disaster recovery to restore and recover both the server target as well as RMAN. The details how to manage catalog backup and production server backup I already posted check the below script for backup “How to manage catalog backup” only the addition is after each target server backup weekly and daily backup transfer the backup files from target server to RMAN server and vice versa in order to maintain an extra copy of backup in both the server.
Steps:
1.      Set the Recovery Window of 30 days and assume other parameter is already configure for all target databases (Sadhan/Issco/Mujaz)
2.      Run Daily Script (INCR+COMUL backup) with Archive log
3.      Run Script to copy only newly backup from Source to destination server.
4.      Run Weekly Script (Full backup) by deleting the old backup first from live server
5.      Same above script is continue to copy newly backup from source to destination server
6.      Run Monthly Script (Last Friday of Month) with all archivelog by deleting physical archive input.
7.      Run Script to Copy Backup from source to destination server by first deleting all the files from destination server.
To run and schedule the below rcv file create a simple .cmd file and set the rman target. For Example to run Daily_Backup.rcv create Create Daily_Backup.cmd as
Daily_Backup.cmd:
D:\oracle\ora92\bin\RMAN TARGET SYS/****@sadhan.world CATALOG catalog/****@rman CMDFILE=D:\DBserver_Rman_Backup\Daily_BACKUP.RCV LOG=D:\DBserver_Rman_Backup\Logs\Daily_BACKUP.LOG
For deletion of backup you can fix seperate script or with the .cmd file just before the rman target of Every Weekly backup.
set path=c:\WINDOWS\system32; 
cd \\192.168.14.17\h$\oraback
del \\192.168.14.17\h$\oraback\*.* /q
CAUSION: Do not fix crosscheck or obsolete or resync catalog command in Daily or Weekly backup as your backup is not available on the original location.
Setup the backup deletion on seperate drive location. 
Daily_BACKUP.rcv
# Backup Cumulative as well as incrmental database along with archivelog
run {
allocate channel d1 type disk FORMAT "H:\oraback\Daily_%T_L1_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\oraback\Daily_%T_L1_%d-%s_%p.db";
allocate channel d3 type disk FORMAT "H:\oraback\Daily_%T_L1_%d-%s_%p.db";
allocate channel d4 type disk FORMAT "H:\oraback\Daily_%T_L1_%d-%s_%p.db";
allocate channel d5 type disk FORMAT "H:\oraback\Daily_%T_L1_%d-%s_%p.db";
backup 
incremental level 1
cumulative
tag Cumulative_L1_datafile
filesperset 5 
format "H:\oraback\Daily_%T_CUMUL_%d-%s_%p.db"
database 
;
backup
incremental level 1
tag Differential_L1_datafile
filesperset 5 
format "H:\oraback\Daily_%T_INCR_%d-%s_%p.db"
database 
;
sql 'alter system archive log current';
backup
archivelog all
tag Sadhan_Archive_Bkp
filesperset 5
format "H:\oraback\Daily_%T_FULL_%d-%s_%p.arc"
;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
}

Weekly_BACKUP.rcv
# Backup Full database with Archivelog all by maintaining deleting old backup from live server
run {
allocate channel d1 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d3 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d4 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
allocate channel d5 type disk FORMAT "H:\oraback\Weekly_%T_L0_%d-%s_%p.db";
backup 
incremental level 0
tag Sadhan_Full_DBbackup
filesperset 8
FORMAT "H:\oraback\Weekly_%T_FULL_%d-%s_%p.db" 
DATABASE
;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup
archivelog all
tag Sadhan_Full_Archiveback
filesperset 8
format "H:\oraback\Weekly_%T_FULL_%d-%s_%p.arch"
;
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;

DBserver_Daily_Backup_Copy.bat:
set path=c:\WINDOWS\system32; 
echo Copying started at %date% %time%>>Copy_date_.txt 
xcopy \\192.168.14.17\h\oraback \\192.168.14.126\i$\DBserver_backup_copy /Y /a /d
echo Copying finished at %date% %time%>>Copy_date_.txt 
echo Completed Successfully at %date% %time%>>Copy_date_.txt 
echo --------------------------------------------------------------------------- >>Copy_date_.txt 
@rem The script will run after the Daily (Mon- Thur) or Weekly (Each Friday). It will copy all the
newly files from target server to backup server (if any file already will overwrite).

Monthly_BACKUP.RCV:
# Backup  Full database with Archivelog all by deleting Physical (Archive_input, obsolete_backup, Expired_backup) from the live server 
allocate channel for delete type disk;
# lists and delete that backups, is not able to recover the database and release that space.
Report Obsolete;
Delete Obsolete;
# Crosscheck and examine the backups with the Controlfile or Catalog. If they are no longer on disk then delete that backup and release the space.
Crosscheck backup;
Crosscheck archivelog all;
delete expired backup;
delete expired archivelog all;

run {
allocate channel d1 type disk FORMAT "H:\oraback\Monthly_%T_L0_%d-%s_%p.db";
allocate channel d2 type disk FORMAT "H:\oraback\Monthly_%T_L0_%d-%s_%p.db";
allocate channel d3 type disk FORMAT "H:\oraback\Monthly_%T_L0_%d-%s_%p.db";
allocate channel d4 type disk FORMAT "H:\oraback\Monthly_%T_L0_%d-%s_%p.db";
allocate channel d5 type disk FORMAT "H:\oraback\Monthly_%T_L0_%d-%s_%p.db";
backup 
incremental level 0
tag Sadhan_Full_DBbackup
filesperset 8
FORMAT "H:\oraback\Monthly_%T_FULL_%d-%s_%p.db" 
DATABASE
;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup
archivelog all delete all input
tag Sadhan_Full_Archiveback
filesperset 8
format "H:\oraback\Monthly_%T_FULL_%d-%s_%p.arch"
;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
}
# To perform a full resynchronization of metadata in a recovery catalog schema with ‎metadata in a target database control file.
resync catalog;
# 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;

DBserver_Monthly_Backup_Copy.bat:
set path=c:\WINDOWS\system32; 
cd \\192.168.14.126\i$\DBserver_backup_copy
del \\192.168.14.126\i$\DBserver_backup_copy\*.* /q
cd D:\DBSERVER_RMAN_BACKUP\Logs
echo Copying started at %date% %time%>>Copy_date_.txt 
xcopy \\192.168.14.17\h\oraback \\192.168.14.126\i$\DBserver_backup_copy /Y /a /d
echo Copying finished at %date% %time%>>Copy_date_.txt 
echo Completed Successfully at %date% %time%>>Copy_date_.txt 
echo --------------------------------------------------------------------------- >>Copy_date_.txt 
@rem The script will run after the monthly backup once in a month (last Friday of the month).
This script will first delete all the files from the Rmanserver folder "DBserver_backup_copy" 
then fresh copy all files from source DBserver folder "oraback". The script will also record the copy time.

Summary: Daily_Backup
The script will take INCREMENTAL (all datafiles changed since last full backup) as well as COMULATIVE (accumulating the last incremental with the current one) backup will backup all database (changed since last full backup) along with archivelog and current control file backup.
The script will run automatically from Monday to Thursday.
Summary: Weekly_Backup
The script will take FULL (all database files since the backup time) along with all the archive log and current control file every Friday of the week. The script will delete first all the files from target folder (oraback) then will perform the backup so able to manage storage space on the target server as it has limited space.
Summary: DBserver_Daily_Backup_Copy
The script will run immediate after the Daily and Weekly backup. It will copy all the files from target server to Rman backup server in order to manage extra copy of backup. If the files is already available on the Rmansever then overwrite it as we have sufficient space on the backup server will manage whole month backup (4 set of weekly backup). It will also able to manage Copy start time and end time into the separate log file each time the script will run.
Summary: Monthly_Backup/Monthly_Backup_Copy
The script will run only on the last Friday of the month
        By taking full backup but not deleting same day weekly backup from the target server
        By deleting all the obsolete files from the target server as per the current configured recovery window.
        By deleting all the expired files by crosschecking the backup from the target server
        Re-synchronizing the physical available backup with RMAN catalog.
        Transferring these backup from target server to backup server by deleting the previous available backup from backup server in order to manage space on the backup server.
        Finally on the Saturday Morning we are able to Manage Monthly/Weekly/Daily on the target as well as backup server.

0 comments:

Post a Comment