Linux/unix calling Sql,rman script in shell script

Source: Internet
Author: User
Tags dname sqlplus
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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.