Linux/Unix shell 脚本中调用SQL,RMAN脚本
作者:网络转载 发布时间:[ 2013/6/18 9:51:20 ] 推荐标签:
二、嵌入sql语句及rman到shell脚本
1、直接将sql语句嵌入到shell脚本
oracle@SZDB:~> more get_dept_2.sh
#!/bin/bash
# Author : Robinson Cheng
# Blog : http://blog.csdn.net/robinson_0612
# set environment variable
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
export ORACLE_SID=CNMMBO
sqlplus -S /nolog <<EOF #EOF在此表示当输入过程中碰到EOF后,整个sql脚本输入完毕
connect scott/tiger
spool /tmp/dept.lst
set linesize 100 pagesize 80
select * from dept;
spool off;
exit; #退出sqlplus 环境
EOF
exit #推出shell脚本
#授予脚本执行权限
oracle@SZDB:~> chmod u+x get_dept_2.sh
#执行shell脚本
oracle@SZDB:~> ./get_dept_2.sh
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2、直接将sql语句嵌入到shell脚本(方式二,使用管道符号>代替spool来输出日志)
oracle@SZDB:~> more get_dept_3.sh
#!/bin/bash
# set environment variable
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
export ORACLE_SID=CNMMBO
sqlplus -S /nolog 1>/users/oracle/dept.log 2>&1 <<EOF
connect scott/tiger
set linesize 80 pagesize 80
select * from dept;
exit;
EOF
cat /users/oracle/dept.log
exit
#另一种实现方式,将所有的sql语句输出来生成sql脚本后再调用
oracle@SZDB:~> more get_dept_4.sh
#!/bin/bash
# set environment variable
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
export ORACLE_SID=CNMMBO
echo "conn scott/tiger
select * from dept;
exit;" >/users/oracle/get_dept.sql
sqlplus -silent /nolog @get_dept.sql 1>/users/oracle/get_dept.log 2>&1
cat get_dept.log
exit
3、将rman脚本嵌入到shell脚本
oracle@SZDB:~> more rman_bak_2.sh
#!/bin/bash
# set environment variable
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
export ORACLE_SID=CNMMBO
$ORACLE_HOME/bin/rman log=/users/oracle/bak/rman.log <<EOF
connect target /
RUN {
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/users/oracle/bak/%d_%F';
ALLOCATE CHANNEL CH1 TYPE DISK MAXPIECESIZE=4G;
ALLOCATE CHANNEL CH2 TYPE DISK MAXPIECESIZE=4G;
SET LIMIT CHANNEL CH1 READRATE=10240;
SET LIMIT CHANNEL CH1 KBYTES=4096000;
SET LIMIT CHANNEL CH2 READRATE=10240;
SET LIMIT CHANNEL CH2 KBYTES=4096000;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
BACKUP
DATABASE FORMAT '/users/oracle/bak/%d_FULL__%U';
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL FORMAT '/users/oracle/bak/%d_LF_%U' DELETE INPUT;
DELETE NOPROMPT OBSOLETE;
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
}
EXIT;
EOF
exit
#授予脚本执行权限
oracle@SZDB:~> chmod u+x rman_bak_2.sh
#执行shell脚本
oracle@SZDB:~> ./rman_bak_2.sh
RMAN> RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> RMAN> oracle@SZDB:~>
相关推荐
更新发布
功能测试和接口测试的区别
2023/3/23 14:23:39如何写好测试用例文档
2023/3/22 16:17:39常用的选择回归测试的方式有哪些?
2022/6/14 16:14:27测试流程中需要重点把关几个过程?
2021/10/18 15:37:44性能测试的七种方法
2021/9/17 15:19:29全链路压测优化思路
2021/9/14 15:42:25性能测试流程浅谈
2021/5/28 17:25:47常见的APP性能测试指标
2021/5/8 17:01:11