Oracle用expdp方法备份脚本;

需求:备份oracle数据库并将数据备份的文件上传到ftp地址服务
ftp服务器IP地址:192.168.6.9
ftp服务器账户/暗码:test/test2019

1、oracle用户创立备份脚本途径:

su - oracle

$ mkdir /u0/app/backup/bin
$ cd /u01/app/backup/bin

2、创立备份脚本:

$ vi /u01/app/backup/bin/bak.sh

!/bin/sh

自己指定环境变量(适合多实例的数据库)

export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH:/sbin/
expot.UTF-8
export NLS_LANG='AMERICAN_AMERICA.AL32UTF8'

或运用oracle原先.bash_profile文件里环境变量

source /home/oracle/.bash_profile

上面环境变量二选一即可

timedate=$(date +%Y%m%d%H%M%S)
bakdbname=hb
bakdbpasswd=h1234
dumpbame=hd
bakdbhome=/u01/app/backup
expdp $bakdbname/$bakdbpasswd directory=backDir dumpfile=$dumpbame-$timedate.dmp logfile=$bakdbname-$timedate.log

方法一、紧缩dmp文件,会联接bakdbhome变量的目录及文件一同紧缩

zip -r $bakdbhome/$dumpbame-$timedate.zip $bakdbhome/$dumpbame-$timedate.dmp $bakdbhome/$bakdbname-$timedate.log #紧缩dmp文件及日志文件

或用方法二

若是不加项目途径目录名就切换到寄存dmp文件的目录进行紧缩即可

cd $bakdbhome
zip -r $dumpbame-$timedate.zip $dumpbame-$timedate.dmp $bakdbname-$timedate.log

find $bakdbhome/*.log -mtime +10 -exec rm -rf {} ;
find $bakdbhome/*.zip -mtime +10 -exec rm -rf {} ;
find $bakdbhome/*.dmp -exec rm -rf {} ; #前面紧缩后,删去原dmp文件

cd $bakdbhome #切换到备份文件目录下(这儿假如用的方法二的就不在需要在切换目录的了)
ftp -v -n 192.168.6.9 << EOF #实施ftp指令

ftp -v -n 192.168.6.9 >>${bakdbname}_${timedate}.log << EOF
将ftp上传日志信息一同追加到备份日志里边

user test test2018 #用户名和暗码
binary #切换传输方法为二进制方法,以字节传输(除文字文件外皆用此方法)
hash #切换#字号的呈现,每一个#字号标明传送了1024/8192BYTES
put $dumpbame-$timedate.zip $dumpbame-$timedate.zip
bye
EOF

将日志文件的GB2312转为UTF-8编码,这样脚本实施的log日志文件中文不会乱码

iconv -f gb2312 -t utf8 $bakdbname-$timedate.log -o $bakdbname-$timedate.log

3、实例脚本,备份好库并异地上传

!/bin/sh

source /home/oracle/.bash_profile
timedate=$(date +%Y%m%d%H%M%S)
bakdbname=re
bakdbpasswd=123
dumpdbname=re
bakdbhome=/u01/databack
expdp $bakdbname/$bakdbpasswd directory=backDir dumpfile=$dumpdbname'_'$timedate.dmp logfile=$dumpdbname'_'$timedate.log
cd /u01
zip -r databack/$dumpdbname'_'$timedate.zip databack/$dumpdbname'_'$timedate.dmp databack/$dumpdbname'_'$timedate.log
find $bakdbhome/*.log -mtime +10 -exec rm -rf {} ;
find $bakdbhome/*.zip -mtime +10 -exec rm -rf {} ;
find $bakdbhome/*.dmp -exec rm -rf {} ;

cd $bakdbhome
ftp -v -n 192.168.6.9 >>${dumpdbname}_${timedate}.log << EOF
user test test2018
binary
hash
put ${dumpdbname}_${timedate}.zip ${dumpdbname}_${timedate}.zip
bye
EOF

iconv -f gb2312 -t utf8 ${bakdbname}_${timedate}.log -o ${bakdbname}_${timedate}.log

FTP的指令行格局为:
ftp -v -d -i -n -g [主机名] ,其间
-v 闪现长途服务器的悉数照顾信息;
-n 捆绑ftp的主动登录,即不运用;
hash:每传输1024字节,闪现一个hash符号(#);
.n etrc文件;
-d 运用调试方法;
-g 撤消大局文件名;
bye:退出ftp会话进程;
get下传文件;
mget批量下传文件,需协作万用字元,例如:MGET*.GZ;
put上传文件;
mput批量上传文件,需协作万用字元;
recv相当于GET(RECV为RECEIVE的简写):
send相当于PUT。

给bak.sh脚本实施权限,做计划任务即可

$ crontab -l
10 0 * /bin/bash /u01/app/backup/bin/bak.sh

示例:

/bin/bash

echo "=======expdp-oracle-backup========"

source /home/oracle/.bash_profile

timedate=$(date +%Y%m%d%H%M%S)

bakdbname=system

bakdbpasswd=oracle

dumpbame=oracle_bak

bakdbhome=/opt/backup

expdp $bakdbname/$bakdbpasswd@10.32.10.30/orcl directory=ORACLE_BACKUP dumpfile=$dumpbame-$timedate.dmp logfile=$bakdbname-$timedate.log full=y

紧缩dmp文件,会联接bakdbhome变量的目录及文件一同紧缩;

zip -r $bakdbhome/$dumpbame-$timedate.zip $bakdbhome/$dumpbame-$timedate.dmp $bakdbhome/$bakdbname-$timedate.log #紧缩dmp文件及日志文件;

cd $bakdbhome

zip -r $dumpbame-$timedate.zip $dumpbame-$timedate.dmp $bakdbname-$timedate.log

find $bakdbhome/*.log -mtime +10 -exec rm -rf {} ;

find $bakdbhome/*.zip -mtime +10 -exec rm -rf {} ;

find $bakdbhome/*.dmp -exec rm -rf {} ; #前面紧缩后,删去原dmp文件;