Linux/Unix shell脚本中调用或执行SQL,RMAN 等为自动化作业以及多次反复执行提供了极大的便利,因此通过Linux/Unix shell来完成Oracle
的相关工作,也是DBA必不可少的技能之一。本文针对Linux/Unix shell脚本调用sql, rman 脚本给出了相关示例。
一、由shell脚本调用sql,rman脚本
- 1、shell脚本调用sql脚本
- #首先编辑sql文件
- oracle@SZDB:~>moredept.sql
- connectscott/tiger
- spool/tmp/dept.lst
- setlinesize100pagesize80
- select*fromdept;
- spooloff;
- exit;
- #编辑shell脚本文件,在shell脚本内调用sql脚本
- oracle@SZDB:~>moreget_dept.sh
- #!/bin/bash
- #setenvironmentvariable
- if[-f~/.bashrc];then
- .~/.bashrc
- fi
- exportORACLE_SID=CNMMBO
- sqlplus-S/nolog@/users/oracle/dept.sql#注意此处执行sql脚本的方法-S表示以静默方式执行
- exit
- #授予脚本执行权限
- oracle@SZDB:~>chmod775get_dept.sh
- -->执行shell脚本
- oracle@SZDB:~>./get_dept.sh
- DEPTNODNAMELOC
- -------------------------------------
- 10ACCOUNTINGNEWYORK
- 20RESEARCHDALLAS
- 30SALESCHICAGO
- 40OPERATIONSBOSTON
- 2、shell脚本调用rman脚本
- #首先编辑RMAN脚本
- oracle@SZDB:~>morerman.rcv
- RUN{
- CONFIGURERETENTIONPOLICYTORECOVERYWINDOWOF7DAYS;
- CONFIGUREBACKUPOPTIMIZATIONON;
- CONFIGURECONTROLFILEAUTOBACKUPON;
- CONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO'/users/oracle/bak/%d_%F';
- ALLOCATECHANNELCH1TYPEDISKMAXPIECESIZE=4G;
- ALLOCATECHANNELCH2TYPEDISKMAXPIECESIZE=4G;
- SETLIMITCHANNELCH1READRATE=10240;
- SETLIMITCHANNELCH1KBYTES=4096000;
- SETLIMITCHANNELCH2READRATE=10240;
- SETLIMITCHANNELCH2KBYTES=4096000;
- CROSSCHECKARCHIVELOGALL;
- DELETENOPROMPTEXPIREDARCHIVELOGALL;
- BACKUP
- DATABASEFORMAT'/users/oracle/bak/%d_FULL__%U';
- SQL'ALTERSYSTEMARCHIVELOGCURRENT';
- BACKUPARCHIVELOGALLFORMAT'/users/oracle/bak/%d_LF_%U'DELETEINPUT;
- DELETENOPROMPTOBSOLETE;
- RELEASECHANNELCH1;
- RELEASECHANNELCH2;
- }
- #编辑shell脚本文件,在shell脚本内调用rman脚本
- oracle@SZDB:~>morerman_bak.sh
- #!/bin/bash
- #setenvironmentvariable
- if[-f~/.bashrc];then
- .~/.bashrc
- fi
- exportORACLE_SID=CNMMBO
- $ORACLE_HOME/bin/rmantarget/cmdfile=/users/oracle/rman.rcvlog=/users/oracle/bak/rman.log
- exit
- #授予脚本执行权限
- oracle@SZDB:~>chmod775rman_bak.sh
- #执行shell脚本
- oracle@SZDB:~>./rman_bak.sh
二、嵌入sql语句及rman到shell脚本
- 1、直接将sql语句嵌入到shell脚本
- oracle@SZDB:~>moreget_dept_2.sh
- #!/bin/bash
- #Author:RobinsonCheng
- #Blog:http://blog.csdn.net/robinson_0612
- #setenvironmentvariable
- if[-f~/.bashrc];then
- .~/.bashrc
- fi
- exportORACLE_SID=CNMMBO
- sqlplus-S/nolog<<EOF#EOF在此表示当输入过程中碰到EOF后,整个sql脚本输入完毕
- connectscott/tiger
- spool/tmp/dept.lst
- setlinesize100pagesize80
- select*fromdept;
- spooloff;
- exit;#退出sqlplus环境
- EOF
- exit#推出shell脚本
- #授予脚本执行权限
- oracle@SZDB:~>chmodu+xget_dept_2.sh
- #执行shell脚本
- oracle@SZDB:~>./get_dept_2.sh
- DEPTNODNAMELOC
- -------------------------------------
- 10ACCOUNTINGNEWYORK
- 20RESEARCHDALLAS
- 30SALESCHICAGO
- 40OPERATIONSBOSTON
- 2、直接将sql语句嵌入到shell脚本(方式二,使用管道符号>代替spool来输出日志)
- oracle@SZDB:~>moreget_dept_3.sh
- #!/bin/bash
- #setenvironmentvariable
- if[-f~/.bashrc];then
- .~/.bashrc
- fi
- exportORACLE_SID=CNMMBO
- sqlplus-S/nolog1>/users/oracle/dept.log2>&1<<EOF
- connectscott/tiger
- setlinesize80pagesize80
- select*fromdept;
- exit;
- EOF
- cat/users/oracle/dept.log
- exit
- #另一种实现方式,将所有的sql语句输出来生成sql脚本后再调用
- oracle@SZDB:~>moreget_dept_4.sh
- #!/bin/bash
- #setenvironmentvariable
- if[-f~/.bashrc];then
- .~/.bashrc
- fi
- exportORACLE_SID=CNMMBO
- echo"connscott/tiger
- select*fromdept;
- exit;">/users/oracle/get_dept.sql
- sqlplus-silent/nolog@get_dept.sql1>/users/oracle/get_dept.log2>&1
- catget_dept.log
- exit
- 3、将rman脚本嵌入到shell脚本
- oracle@SZDB:~>morerman_bak_2.sh
- #!/bin/bash
- #setenvironmentvariable
- if[-f~/.bashrc];then
- .~/.bashrc
- fi
- exportORACLE_SID=CNMMBO
- $ORACLE_HOME/bin/rmanlog=/users/oracle/bak/rman.log<<EOF
- connecttarget/
- RUN{
- CONFIGURERETENTIONPOLICYTORECOVERYWINDOWOF7DAYS;
- CONFIGUREBACKUPOPTIMIZATIONON;
- CONFIGURECONTROLFILEAUTOBACKUPON;
- CONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO'/users/oracle/bak/%d_%F';
- ALLOCATECHANNELCH1TYPEDISKMAXPIECESIZE=4G;
- ALLOCATECHANNELCH2TYPEDISKMAXPIECESIZE=4G;
- SETLIMITCHANNELCH1READRATE=10240;
- SETLIMITCHANNELCH1KBYTES=4096000;
- SETLIMITCHANNELCH2READRATE=10240;
- SETLIMITCHANNELCH2KBYTES=4096000;
- CROSSCHECKARCHIVELOGALL;
- DELETENOPROMPTEXPIREDARCHIVELOGALL;
- BACKUP
- DATABASEFORMAT'/users/oracle/bak/%d_FULL__%U';
- SQL'ALTERSYSTEMARCHIVELOGCURRENT';
- BACKUPARCHIVELOGALLFORMAT'/users/oracle/bak/%d_LF_%U'DELETEINPUT;
- DELETENOPROMPTOBSOLETE;
- RELEASECHANNELCH1;
- RELEASECHANNELCH2;
- }
- EXIT;
- EOF
- exit
- #授予脚本执行权限
- oracle@SZDB:~>chmodu+xrman_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:~>
三、更多参考
有关基于用户管理的备份和备份恢复的概念请参考
Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)
Oracle 基于备份控制文件的恢复(unsing backup controlfile)
有关RMAN的备份恢复与管理请参考
RMAN备份路径困惑(使用plus archivelog时)
有关ORACLE体系结构请参考