Linux下Oracle数据库自动备份

Oracle数据库备份是每个商业项目中DBA必须要执行的工作，备份数据库工作周期长，内容单调。

现在，通过运行挂载在crontab进程当中的backup.sh文件的方法，实现了定时自动备份数据库，大大缩减了DBA的工作量，同时保证了夜间闲时备份数据库。闲话少说，代码如下。注意，拷贝代码后一定要看后面的注意内容。

通过INI文件设置初始化信息。第一个文件：

#################################################################
#This is a system ini file :
# CopyRight By Davidhan 2008
# the '#' is ingnor :
#the format style is xxxx=value
#################################################################
#Oracle cionfig
[oracle]
username=system            //备份的帐户
password=manager         //密码
con_alias=testsid           //Oracle_Sid

#the main machine config
[mainhost]
myhost_ip=192.168.10.XX   //本地服务器IP
myhost_username=root         //本地登录帐户名
myhost_password=123456  //登录帐户密码
dump_dir=/home/oracle/oraclebackup   //备份文件存储路径  

#the backup machine config
[backuphost]
backhost_ip=192.168.10.XX      //备份FTP服务器IP
backhost_username=XXXXXX       //FTP服务器帐号
backhost_password=XXXXXX       //密码
backup_dir=/cncfile/oraclebackup  //存放备份文件的路径

#delete the dump file x days ago
[deletedumpfile]
delete_days=2                  //删除DB服务器几日前备份文件

#the Oracle database's back method
#如果start_tactic=Y 则采用增量备份，否则按每天完全导出
#如果采用增量备份，则必须给SYSTEM用户的口令
[backmethod]
start_tactic=N
system_password=manager

#the recode the system execute's log!
[systemlog]
sys_log=/home/oracle/backupLog/backup.log   //       执行Log
#end


通过backup.sh文件备份数据库。第二个文件：

#!/bin/sh ############################################################################# # #Get the all ini file config #CopyRight By Davidhan 2008-2009? #copyright (C) 2007-2008 Davidhan #All Rights Reserved. #program date 2008-6-22 #usage: main #This script is used to start oracle dump database . #It should ONLY be executed as part of the crontabs. #function content is: #1: test connectiong status of oracle #2: from ini.txt get information of shell start paramater #3: auto export oracle dump everyday #4: auto delete X days ago dump files that setted in config file #5: auto realese and send backup file to FTP server #6: log info #7: optical backup ############################################################################## mypwd='pwd' inifile=/home/backupOracle/init.txt oratab=/etc/oratab TEMP=temp.$$ DUMP_FILE=`date +%Y%m%d`
initme () { #read from the config file 'init.txt'
#oracle config USERNAME=`cat ${inifile:-"init.txt"} | grep -i '^username' | awk -F = '{print $2}'` PASSWORD=`cat ${inifile:-"init.txt"} | grep -i '^password' | awk -F = '{print $2}'` MY_ALIAS=`cat ${inifile:-"init.txt"} | grep -i '^con_alias' | awk -F = '{print $2}'`
#the main host config MYHOST_IP=`cat ${inifile:-"init.txt"} | grep -i '^myhost_ip' | awk -F = '{print $2}'` MYHOST_USERNAME=`cat ${inifile:-"init.txt"} | grep -i '^myhost_username' | awk -F = '{print $2}'` MYHOST_PASSWORD=`cat ${inifile:-"init.txt"} | grep -i '^myhost_password' | awk -F = '{print $2}'` DUMP_DIR=`cat ${inifile:-"init.txt"} | grep -i '^dump_dir' | awk -F = '{print $2}'`
#the backup host config BACKHOST_IP=`cat ${inifile:-"init.txt"} | grep -i '^backhost_ip' | awk -F = '{print $2}'` BACKHOST_USERNAME=`cat ${inifile:-"init.txt"} | grep -i '^backhost_username' | awk -F = '{print $2}'` BACKHOST_PASSWORD=`cat ${inifile:-"init.txt"} | grep -i '^backhost_password' | awk -F = '{print $2}'` BACKUP_DIR=`cat ${inifile:-"init.txt"} | grep -i '^backup_dir' | awk -F = '{print $2}'`
#the define's delete dump file days! DAYS=`cat ${inifile:-"init.txt"} | grep -i '^delete_days' | awk -F = '{print $2}'` #to get the system log LOGS=`cat ${inifile:-"init.txt"} | grep -i '^sys_log' | awk -F = '{print $2}'` TACTIC=`cat ${inifile:-"init.txt"} | grep -i '^start_tactic' | awk -F = '{print $2}'` SYSTEMPASS=`cat ${inifile:-"init.txt"} | grep -i '^system_password' | awk -F = '{print $2}'`
#read the config file end. }
#run the init parameter test ! -e $inifile && echo "not find the init file:"$inifile"!" && exit 0
initme
echo "=========$LOGS=========" echo "=========$USERNAME=========" echo "=========$PASSWORD=========" echo "=========$MY_ALIAS=========" echo "=========$MYHOST_IP=========" echo "=========$MYHOST_USERNAME=========" echo "=========$MYHOST_PASSWORD========="
if [ -z $LOGS ]; then echo "<<can not to find the define of system log...!!!>>" fi
#LOG = ${LOGS:-"x.log"} LOGME="tee -a ${LOGS:-"x.log"}"
if [ -e $LOGS -a -f $LOGS ]; then if test ! -w $LOGS; then echo "<<the log file:"$LOGS" can not to write!....................>>" exit fi fi
echo " " | $LOGME echo "-----------------------------------------------------------------" | $LOGME echo "<<system now is initing ..............!>>" | $LOGME
#check the parameter value if [ -z $USERNAME ]; then echo "<<the oracle database's username is null..............!>>" | $LOGME exit 1 fi
if [ -z $PASSWORD ]; then echo "<<the oracle database's password is null..............!>>" | $LOGME exit 2 fi
if [ -z $MY_ALIAS ]; then echo "<the oracle connect alias is null!..............>>" | $LOGME exit 0 fi
if [ -z $MYHOST_IP ]; then echo "<<the oracle's host ip is null..............!>>" | $LOGME exit 3 fi
if [ ! -d $DUMP_DIR ]; then echo "<the oracle's dump dir is not exist..............!>>" | $LOGME exit 0 fi
if [ -z $MYHOST_USERNAME ]; then echo "<<then oracle host username is null..............!>>" | $LOGME exit 4 fi
if [ -z $MYHOST_PASSWORD ]; then echo "<<the oracle host password is null..............!>>" | $LOGME exit 5 fi
if [ -z $BACKHOST_IP ]; then echo "<<the backup host's ip is null..............!>>" | $LOGME exit 6 fi
if [ -z $BACKHOST_USERNAME ]; then echo "<<the backup host's uesername is null..............!>>" | $LOGME exit 7 fi
if [ -z $BACKHOST_PASSWORD ]; then echo "<<the backup host's password is null..............!>>" | $LOGME exit 8 fi
if [ -z $BACKUP_DIR ]; then echo "<<the backup host's backup dir is null..............!>>" | $LOGME exit 9 fi elif [ ! -e $BACKUP_DIR -o ! -d $BACKUP_DIR ]; then echo "<<the backup dir "$BACKUP_DIR" is not exist or is not directory..............!>>" | $LOGME exit 0 fi
if [ ! -O $BACKUP_DIR ]; then echo "<<the backup host's backup dir is not owner to oracle..............!>>" | $LOGME exit 0 fi
if [ -z $DAYS ]; then echo "<<the delete file define days is null so not to delete...............!>>" | $LOGME fi
if test $TACTIC = "Y" ; then if test -z $SYSTEMPASS ; then echo "<<if you use TACTIC back,then you must give the oracle system password!...............!>>" | $LOGME exit 0 fi fi
if test $? -eq 0 ; then echo "<<system init is OK............................!>>" | $LOGME fi
if [ ! -f $oratab -o ! -e $oratab ]; then echo "<<the oracle define's oratab file is not normal file or is not exist..............!>>" | $LOGME exit 0 fi
ORACLE_SID=`cat $oratab | grep -v "^#" | grep "Y$" | awk -F : '{print $1}'` if [ -z $ORACLE_SID ]; then echo "<<the ORACLE_SID is not find ..............!>>" | $LOGME exit -1 fi export ORACLE_SID ORACLE_HOME=`cat $oratab | grep -v "^#" | grep "Y$" | awk -F : '{print $2}'` if [ ! -d $ORACLE_HOME ]; then echo "the oracle's home directory "$ORACLE_HOME" is not exist..............!>>" | $LOGME exit -1 fi export ORACLE_HOME ORACLE_BASE=/home/oracle/oracle export ORACLE_BASE export ORACLE_SID export ORACLE_HOME PATH=$PATH:$ORACLE_HOME/bin export PATH LIBPATH=$LIBPATH:$ORACLE_HOME/lib export LIBPATH ORA_NLS33=$ORACLE_HOME/nls/data export ORA_NLS33 export PATH ############################################# ## begin to dump the database #############################################
#trying to connect oracle ..... if [ -e $TEMP ]; then rm -f $TEMP if [ ! $? -eq 0 ]; then echo "<<delete the file: "$TEMP" is fail..............!!!!>>" | $LOGME exit -1 fi fi
if [ -x $ORACLE_HOME/bin/tnsping ]; then #$ORACLE_HOME/bin/tnsping $MY_ALIAS > $TEMP tnsping $MY_ALIAS > $TEMP ME=`cat $TEMP | grep "OK" | awk '{print $1}'` if [ "$ME" != "OK" ]; then echo "<<can not to connect oracle by tnsping:"$MY_ALIAS"..............!>>" | $LOGME rm -f $TEMP exit $? else echo "<<connect oracle is ok!...............................!>>" | $LOGME fi fi
rm -f $TEMP
if [ -x $ORACLE_HOME/bin/exp ]; then trap "" 1 2 3 15
if [ "$TACTIC" != "Y" -o "$TACTIC" != "y" ]; then echo "<<system will run at the way of user!..............................>>" | $LOGME $ORACLE_HOME/bin/exp $USERNAME/$PASSWORD@$MY_ALIAS buffer=40960 file=$DUMP_DIR/$DUMP_FILE.dmp full=y Log=$DUMP_DIR/$DUMP_FILE.log
#optical backup
else echo "<<system will run at the way of optical user!..............................>>" | $LOGME MYWEEK='date | awk '{print $1}'' case $MYWEEK in #1 Mon)exp system/$SYSTEMPASS@$MY_ALIAS inctype=complete buffer=40960 file=$DUMP_DIR/$DUMP_FILE.dmp grants=Y rows=Y compress=Y indexes=Y log=$DUMP_DIR/$DUMP_FILE.log echo "<<Mon is complete dump the db,file:"$DUMP_FILE.dmp"...............!>>" | $LOGME ;; #2 Tue)exp system/$SYSTEMPASS@$MY_ALIAS inctype=incremental buffer=40960 file=$DUMP_DIR/$DUMP_FILE.dmp grants=Y rows=Y compress=Y indexes=Y log=$DUMP_DIR/$DUMP_FILE.log echo "<<Tue is incremental dump the db,file:"$DUMP_FILE.dmp"...............!>>" | $LOGME ;; #3 Wed)exp system/$SYSTEMPASS@$MY_ALIAS inctype=incremental buffer=40960 file=$DUMP_DIR/$DUMP_FILE.dmp grants=Y rows=Y compress=Y indexes=Y log=$DUMP_DIR/$DUMP_FILE.log echo "<<Wed is incremental dump the db,file:"$DUMP_FILE.dmp"...............!>>" | $LOGME ;; #4 Thu)exp system/$SYSTEMPASS@$MY_ALIAS inctype=incremental buffer=40960 file=$DUMP_DIR/$DUMP_FILE.dmp grants=Y rows=Y compress=Y indexes=Y log=$DUMP_DIR/$DUMP_FILE.log echo "<<Thu is incremental dump the db,file:"$DUMP_FILE.dmp"...............!>>" | $LOGME ;; #5 Fri)exp system/$SYSTEMPASS@$MY_ALIAS inctype=cumulative buffer=40960 file=$DUMP_DIR/$DUMP_FILE.dmp grants=Y rows=Y compress=Y indexes=Y log=$DUMP_DIR/$DUMP_FILE.log echo "<<Fri is cumulative dump the db,file:"$DUMP_FILE.dmp"...............!>>" | $LOGME ;; #6 Sat)exp system/$SYSTEMPASS@$MY_ALIAS inctype=incremental buffer=40960 file=$DUMP_DIR/$DUMP_FILE.dmp grants=Y rows=Y compress=Y indexes=Y log=$DUMP_DIR/$DUMP_FILE.log echo "<<Sat is incremental dump the db,file:"$DUMP_FILE.dmp"...............!>>" | $LOGME ;; #7 Sun)exp system/$SYSTEMPASS@$MY_ALIAS inctype=incremental buffer=40960 file=$DUMP_DIR/$DUMP_FILE.dmp grants=Y rows=Y compress=Y indexes=Y log=$DUMP_DIR/$DUMP_FILE.log echo "<<Sun is incremental dump the db,file:"$DUMP_FILE.dmp"...............!>>" | $LOGME ;; *) ;; esac fi
if test $? -eq 0 -a -s $DUMP_DIR/$DUMP_FILE.dmp then echo "<<dump the oracle database success at: "$DUMP_FILE"..............!>>" | $LOGME else echo "<<dump the oracle database fail at: "$DUMP_FILE"..............!>>" | $LOGME exit $? fi else echo "<<sorry!,the exp not to find or not execute,please check it..............!>>" | $LOGME exit $? fi
#if define the days then to execute delete the dump file $DAYS ago! if [ -n $DAYS -a $DAYS -gt 0 ]; then echo "<<system will to remove the dump file $DAYS days ago..............!>>" | $LOGME if [ -x $ORACLE_HOME/bin/sqlplus ]; then sqlplus $USERNAME/$PASSWORD@$MY_ALIAS<<EOF spool $TEMP select to_char(sysdate-$DAYS,'yyyymmdd') mydate from dual; spool off exit EOF if test $? -eq 0 -a -f $TEMP -a -s $TEMP ; then echo "<<get the $DAYS days ago's date is ok..............!>>" | $LOGME else echo "<<to get $DAYS days ago's date is fail(connect oracle is fail---------)..............!>>" | $LOGME #exit $? fi else echo "<<not find the oracle's sqlplus program or cant not execute and get the days is fail..............!>>" | $LOGME exit $? fi
##################### ## ## Begin to remove the dump file ## #####################
TEMPDATE=`cat $TEMP | sed '1d' | sed '$d' | sed '/^--*/d'` echo "<<date:"$TEMPDATE"..............!>>" | $LOGME rm -f $TEMP if test ! $? -eq 0 ; then echo "<<can not to remove the temp file:"$TEMP"..............!>>" | $LOGME fi #echo "${DUMP_DIR}/${TEMPDATE}.dmp" if test -e $DUMP_DIR/$TEMPDATE.dmp -a -f $DUMP_DIR/$TEMPDATE.dmp -a -n $TEMPDATE; then rm -f $DUMP_DIR/$TEMPDATE.dmp rm -f $DUMP_DIR/$TEMPDATE.log rm -f $DUMP_DIR/$TEMPDATE.tar.Z if test $? -eq 0 ; then echo "<<remove the "$DUMP_DIR/$TEMPDATE.dmp" is ok..............!>>" | $LOGME else echo "<<remove the "$DUMP_DIR/$TEMPDATE.dmp" is fail..............!>>" | $LOGME #exit $? fi else echo "<<can not to find the file: "$DUMP_DIR/$TEMPDATE.dmp" ..............!>>" | $LOGME #exit $? fi fi
#################################################### # #to transfer the dump file to other's host user ftp # ####################################################
if [ -e $DUMP_DIR/$DUMP_FILE.dmp -a -e $DUMP_DIR/$DUMP_FILE.log ]; then tar -cvf $DUMP_DIR/$DUMP_FILE.tar $DUMP_DIR/$DUMP_FILE.dmp $DUMP_DIR/$DUMP_FILE.log && compress $DUMP_DIR/$DUMP_FILE.tar | $LOGME if test $? -eq 0 -a -e $DUMP_DIR/$DUMP_FILE.tar.Z ; then echo "<<the dump file "$DUMP_DIR/$DUMP_FILE.tar.Z" is archive ok.........!>>" | $LOGME rm -f $DUMP_DIR/$DUMP_FILE.tar rm -f $DUMP_DIR/$DUMP_FILE.log rm -f $DUMP_DIR/$DUMP_FILE.dmp if test ! $? -eq 0 ; then echo "<<delete the dump file is fail!.........................!>>" | $LOGME else echo "<<delete the dump file is ok!.........................!>>" | $LOGME fi else echo "<<the dump file "$DUMP_FILE.tar.Z" is archive fail..........!>>" | $LOGME exit $? fi fi
echo "<<system now is transfering file to ${BACKHOST_IP}..............!>>" | $LOGME
ftp -n $BACKHOST_IP <<! | $LOGME user $BACKHOST_USERNAME $BACKHOST_PASSWORD bin prompt put $DUMP_DIR/$DUMP_FILE.tar.Z $BACKUP_DIR/$DUMP_FILE.tar.Z close bye !
if test $? -eq 0 ; then echo "<<Transfer the file:"$DUMP_DIR/$DUMP_FILE.tar.Z" to "$BACKHOST_IP"$BACKUP_DIR is OK.................! >>" | $LOGME else echo "<<Transfer the file:"$DUMP_DIR/$DUMP_FILE.tar.Z" to "$BACKHOST_IP"$BACKUP_DIR is fail..................! >>" | $LOGME exit $? fi
if test $? -eq 0 ; then echo "<<the script is execute finish!,please check it!.......................................!>>" | $LOGME echo "-----------------------------------------------------------------" | $LOGME exit 0 fi
代码部分结束。
可按照init文件中的内容设定工作目录和工作的文件内容。
现在是操作手顺：
1.        在将backup.sh和init.txt文件拷贝到 /home/backupOracle文件夹内。

说明：/home/backupOracle目录不存在可运行 #mkdir /home/backupOracle命令创建。

2.        创建工作目录。 工作日志文件存放目录：#mkdir /home/oracle/backupLog

生成备份文件存放目录：#mkdir /home/oracle/oraclebackup

3.        创建定时工作表。

l        #vi confile

l        进入定时工作表编辑器

l        点击键盘insert按键，调整编辑器状态至插入

l        输入：10 4 * * * /home/backupOracle/backup.sh（意为备份任务执行的时间为每天4点10分）

l        点击键盘ESC按键推出编辑状态

l        Shift +z+z（键盘输入两个大写Z）保存退出编辑器

l        #crontab confile(将任务注入到crontab进程)。此时可在 /var/spool/cron文件夹中生成名为root的工作表

l        #crontab -l 可查看到工作表的内容



通过以上步骤执行，我们为系统定制了每天4点10分执行的备份任务。工具采用全局备份，导出除ORDSYS,MDSYS,CTXSYS,ORDPLUGINS,LBACSYS 这些系统用户之外的所有用户的数据。在oraclebackup文件夹下生成命名为当日时间（格式为YYYYDDMM）的dmp文件的tar压缩文件，并在指定远程ftp备份主机中保留备份文件，工具可在执行时检测备份本地备份文件夹，自动删除2日前的备份的文件。这些都在init.txt文件中设定。



     还原数据库操作手顺

      

l        #cd /home/oracle/oraclebackup

      

l        #tar zxvf YYYYDDMM.tar.z



l        #imp user/password@oracle_sid file=/home/oracle/oraclebackup/home/ oracle/oraclebackup/YYYYDDMM.dmp full=y ignore=y



以上是备份的全部内容，实现了备份数据库，定时，ftp远程备份。

注意：没有linux基础的同学不太了解linux下的命令组成

提醒一点，不要把“`”看成“'”，一个是键盘上ESC对应那个符号。这个在linux中指命令域。