Wednesday, March 7, 2012

Script to backup Oracle database on tape and ftp server

I had a production Oracle database server which was configured to to backup the full export of database to a tape drive connected to the server. The vendor had configured it and it was working for some time. As it usually happens the server was working fine and we “thought” the backup was working fine. I recently checked the backup as part of the implementation of disaster recovery and found it not working for several months. Hence I wanted a script that is simple and takes backup in two different locations so that I am safe if any disaster happens. I Googled and found a lot of scripts from several guys especially Cyberciti.
I thank you guys for publishing the knowledge that you acquired for helping others. So I feel that I also should give back to the world what I acquired from you.
Below is the script that I have created from several websites. It is self explanatory, but please do tweak as per your needs. In case of any issues please do get in touch with me so that I can help you.
Assume that you will be having basic knowledge of Linux, Linux scripts, Oracle etc. I don’t in not compressing and complicating the backup which in future while restoring may cause decompressing issues. Moreover my database was more than 7.5GB.

================================Script=====================================
#!/bin/bash
######
######Script for full export backup of DB to Tape and FTP
######Created by Anil Kumar
######

########Variable declaration#######
USERNAME="ftpusername"
PASSWORD="ftppassword"
SERVER="ip.add.re.ss"
FOLDER="/path/to/tempworkingfolder"
export ORACLE_SID=oraclesid
export ORACLE_HOME=/path/to/oracle/home
export CURR_DATE=`/bin/date +%d%m%y_%H%M%S`
PATH=$PATH:$ORACLE_HOME/bin;export PATH

########Oracle Export#######
exp dbauser/dbsuserpass file=/path/to/tempworkingfolder/finename_${CURR_DATE}_department.dmp log=/path/to/tempworkingfolder/filename_${CURR_DATE}_department.log compress=y statistics=none grants=y full=y

########Status of the tape########
mt -f /dev/st0 status > /path/to/tempworkingfolder/tapestatus_${CURR_DATE}_department.log

########Rewinding the tape########
mt -f /dev/st0 rewind
cd $FOLDER

########Copying files to the tape########
tar -cpf /dev/st0 *.*

########List of files on tape to make sure that the backup works fine########
tar -tvf /dev/st0 > /path/to/tempworkingfolder/backup_${CURR_DATE}_department.log

########Eject the tape, this will indirectly make sure the script worked and may be backup worked########
mt -f /dev/st0 eject

#########FTP Upload#########
cd $FOLDER
ftp -n -i $SERVER <<EOF
user $USERNAME $PASSWORD
mput *
quit
EOF

#########Deleting files in tempworking forlder other than script#########
rm -rf filename* *.log

================================Script=====================================

0 comments: