Linux/Unix shell脚本中调用或执行SQL,RMAN 等为自动化作业以及多次反复执行提供了极大的便利,因此通过Linux/Unix shell来完成Oracle的相关工作,也是DBA必不可少的技能之一。本文针对Linux/Unix shell脚本调用sql, rman 脚本给出了相关示例。
一、由shell脚本调用sql,rman脚本
1、shell脚本调用sql脚本
#首先编辑sql文件 oracle@SZDB:~> more dept.sql connect scott/tiger spool /tmp/dept.lst set linesize 100 pagesize 80 select * from dept; spool off; exit; #编辑shell脚本文件,在shell脚本内调用sql脚本 oracle@SZDB:~> more get_dept.sh #!/bin/bash # set environment variable if [ -f ~/.bashrc ]; then . ~/.bashrc fi export ORACLE_SID=CNMMBO sqlplus -S /nolog @/users/oracle/dept.sql #注意此处执行sql脚本的方法 -S 表示以静默方式执行 exit #授予脚本执行权限 oracle@SZDB:~> chmod 775 get_dept.sh -->执行shell脚本 oracle@SZDB:~> ./get_dept.sh DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON |
2、shell脚本调用rman脚本
#首先编辑RMAN脚本 oracle@SZDB:~> more rman.rcv 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; } #编辑shell脚本文件,在shell脚本内调用rman脚本 oracle@SZDB:~> more rman_bak.sh #!/bin/bash # set environment variable if [ -f ~/.bashrc ]; then . ~/.bashrc fi export ORACLE_SID=CNMMBO $ORACLE_HOME/bin/rman target / cmdfile=/users/oracle/rman.rcv log=/users/oracle/bak/rman.log exit #授予脚本执行权限 oracle@SZDB:~> chmod 775 rman_bak.sh #执行shell脚本 oracle@SZDB:~> ./rman_bak.sh |
二、嵌入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:~> |