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