qileilove

blog已经转移至github,大家请访问 http://qaseven.github.io/

Linux/Unix shell 脚本中调用SQL,RMAN脚本

  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:~>


posted on 2013-06-19 10:54 顺其自然EVO 阅读(370) 评论(0)  编辑  收藏 所属分类: linux


只有注册用户登录后才能发表评论。


网站导航:
 
<2013年6月>
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456

导航

统计

常用链接

留言簿(55)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜