linux shell实现oracle定时备份表数据脚本

上一篇 / 下一篇  2019-12-12 14:38:30 / 个人分类:oracle

一、备份脚本
#!/bin/bash
#如未安装则需要安装
#yum -y install expect
#源oracle数据库ip
begin_ip=10.204.125.238
#备份oracle数据库ip
bak_ip=10.204.125.215
#获取今天日期
date_today=`date -d '0 day ago' +%Y%m%d%H%M%S`
bak_dmp=ztsinfo_${begin_ip}_${date_today}.dmp
#获取7天前日期
date_7ago=`date -d '7 day ago' +%Y%m%d`
bak_date_7ago_dmp=ztsinfo_${begin_ip}_${date_7ago}**.dmp
#echo ${bak_date_7ago_dmp}
#echo $bak_dmp
#定义oracle表空间名
tablespace_name=DATA_TEST5
#定义oracle用户名
user_name=test5
#linux机器用户
user=root
#需要修改时间机器密码
begin_password=rtck12345
bak_password=rtck215
/usr/bin/expect << EOF
spawn ssh root@$begin_ip
expect {
"yes/no" { send "yes\r";exp_continue }
"password:" { send "${begin_password}\r" }
}
expect "]#"
send "su - oracle\r"
expect "]#"
send "rm -rf /home/oracle/imp/${bak_date_7ago_dmp}\r"
expect "]#"
send "exp TEST5/TEST5@ORCL file=/home/oracle/imp/${bak_dmp}\r"
expect "]#"
send "exit\r"
expect "]#"
send "exit\r"
expect eof
EOF

/usr/bin/expect << EOF
spawn scp -r root@$begin_ip:/home/oracle/imp/${bak_dmp}  /home/oracle/imp/
expect {
#"yes/no" { send "yes\r";exp_continue }
"password:" { send "${begin_password}\r" }
}
expect eof
EOF
sleep 10
chmod 777 -R /home/oracle/imp/${bak_dmp}
#su - oracle
echo "开始导入了"
#su - oracle
/usr/bin/expect << EOF
spawn su - oracle
expect "]#"
send "sqlplus / as sysdba\r"
expect "L>"
send "drop user ${user_name} cascade;\r"
expect "L>"
send "drop tablespace ${tablespace_name} including contents and datafiles;\r"
expect "L>"
send "create tablespace ${tablespace_name} datafile '/java/oracle/DATE3/${tablespace_name}.dbf' size 360M autoextend on;\r"
expect "L>"
send "create user ${user_name} identified by ${user_name} default tablespace ${tablespace_name} temporary tablespace TEMP profile DEFAULT;\r"
expect "L>"
send "grant connect to ${user_name};\r"
expect "L>"
send "grant dba to ${user_name};\r"
expect "L>"
send "grant create any table to ${user_name};\r"
expect "L>"
send "grant create any procedure to ${user_name}; \r"
expect "L>"
send "grant execute any procedure to ${user_name};\r"
expect "L>"
send "grant create job to ${user_name};\r"
expect "L>"
send "grant manage scheduler to ${user_name};\r"
expect "L>"
send "exit\r"
expect "]#"
send "imp test5/test5@$bak_ip/ORCL  full=y  file=/home/oracle/imp/${bak_dmp}\r"
expect "]#"
send "exit\r"
expect eof
EOF
rm -rf /home/oracle/imp/${bak_date_7ago_dmp}
echo "导入完成"

二、启动linux定时任务
[root@iskytest01 uptime]# crontab -e
配置每周五23点30分执行
30 23 * * 5  sh /home/oracle/imp/exp_imp.sh  >/home/oracle/imp/exp_imp_log.txt

查看已生效任务
[root@iskytest01 uptime]# crontab -l
30 23 * * 5  sh /home/oracle/imp/exp_imp.sh  >/home/oracle/imp/exp_imp_log.txt


TAG:

 

评分:0

我来说两句

Open Toolbar