Linux/unix calling Sql,rman script in shell script

Calling or executing SQL, RMAN, etc. in Linux / Unix shell scripts provides great convenience for automated jobs and multiple repeated executions, so Oracle / Linux shell
Related work is also one of the essential skills of DBA. This article gives examples for Linux / Unix shell scripts calling sql, rman scripts.

 

First, call sql, rman script by shell script

[python] view plain copy print?
1.shell script calls sql script
#First edit the sql file
[email protected]: ~> more dept.sql
connect scott / tiger
spool /tmp/dept.lst
set linesize 100 pagesize 80
select * from dept;
spool off;
exit;
  
#Edit the shell script file and call the SQL script within the shell script
[email protected]: ~> 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 #Note the method of executing the SQL script here -S means to execute in silent mode
exit
  
#Grant script execution permissions
[email protected]: ~> chmod 775 get_dept.sh
  
-> Execute shell script
[email protected]: ~> ./get_dept.sh
  
    DEPTNO DNAME LOC
---------- -------------- -------------
        10 ACCOUNTING NEW YORK
        20 RESEARCH DALLAS
        30 SALES CHICAGO
        40 OPERATIONS BOSTON
  
2, shell script calls rman script
#First edit RMAN script
[email protected]: ~> 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;
}
  
#Edit the shell script file and call the rman script within the shell script
[email protected]: ~> 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
  
#Grant script execution permissions
[email protected]: ~> chmod 775 rman_bak.sh
  
#Execute shell script
[email protected]: ~> ./rman_bak.sh

Second, embed SQL statements and rman into shell scripts

[python] view plain copy print?
1.Embed SQL statements directly into shell scripts
[email protected]: ~> 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 This means that when EOF is encountered during input, the entire SQL script is entered
connect scott / tiger
spool /tmp/dept.lst
set linesize 100 pagesize 80
select * from dept;
spool off;
exit; #Exit the sqlplus environment
EOF
exit #Launch shell script
  
#Grant script execution permissions
[email protected]: ~> chmod u + x get_dept_2.sh
  
#Execute shell script
[email protected]: ~> ./get_dept_2.sh
  
    DEPTNO DNAME LOC
---------- -------------- -------------
        10 ACCOUNTING NEW YORK
        20 RESEARCH DALLAS
        30 SALES CHICAGO
        40 OPERATIONS BOSTON
  
2. Embed the SQL statement directly into the shell script.
[email protected]: ~> 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
  
#Another implementation, output all sql statements to generate sql scripts before calling
[email protected]: ~> 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
  
Embed the rman script into a shell script
[email protected]: ~> 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
  
#Grant script execution permissions
[email protected]: ~> chmod u + x rman_bak_2.sh
  
#Execute shell script
[email protected]: ~> ./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> [email protected] : ~>
   Go: http://blog.csdn.net/leshami/article/details/7965916
Call SQL, RMAN script in Linux / Unix shell script