Linux/Unix shell scripts call SQL and RMAN scripts

In Linux/Unix shell scripts, calling or executing SQL and RMAN provide great convenience for automated jobs and repeated execution. Therefore, you can use Linux/Unix shell to Complete Oracle
It is also an essential skill for DBAs. This article provides examples of calling SQL using Linux/Unix shell scripts.

 

I. Use shell scripts to call SQL and RMAN scripts

1. The shell script calls the SQL script # First edit the SQL file Oracle @ szdb: ~> More Dept. sqlconnect Scott/tigerspool/tmp/Dept. lstset linesize 100 pagesize 80 select * from Dept; spool off; exit; # edit the shell script file and call the SQL script Oracle @ szdb in the shell script: ~> More get_dept.sh #! /Bin/bash # Set environment variableif [-f ~ /. Bashrc]; then .~ /. Bashrcfiexport oracle_sid = cnmmbosqlplus-S/nolog @/users/Oracle/Dept. SQL # note the method for executing the SQL script here-s indicates that exit is executed in silent mode # grant the script execution permission to Oracle @ szdb: ~> Chmod 775 get_dept.sh --> execute the shell script Oracle @ szdb: ~> ./Get_dept.sh deptno dname loc ---------- ------------ --------------- 10 Accounting New York 20 research Dallas 30 sales Chicago 40 operations boston2. The shell script calls the RMAN script # First edit the RMAN script Oracle @ szdb: ~> More RMAN. rcvrun {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 category type disk maxpiecesize = 4G; allocate channel CH2 type disk maxpiecesize = 4G; set limit channel ready readrate = 10240; set limit channel limit 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 commit; release channel CH2;} # edit shell script file, call the RMAN script in the shell script Oracle @ szdb: ~> More rman_bak.sh #! /Bin/bash # Set environment variableif [-f ~ /. Bashrc]; then .~ /. Bashrcfiexport oracle_sid = cnmmbo $ ORACLE_HOME/bin/RMAN target/partition file =/users/Oracle/RMAN. RCV log =/users/Oracle/bak/RMAN. logexit # grant the script execution permission to Oracle @ szdb: ~> Chmod 775 rman_bak.sh # execute the shell script Oracle @ szdb: ~> ./Rman_bak.sh

2. embed SQL statements and RMAN scripts into shell scripts

1. Directly embed the SQL statement into the shell script Oracle @ szdb: ~> More get_dept_2.sh #! /Bin/bash # Author: Robinson Cheng # blog: http://blog.csdn.net/robinson_0612# set environment variableif [-f ~ /. Bashrc]; then .~ /. Bashrcfiexport oracle_sid = cnmmbosqlplus-S/nolog <EOF # EOF indicates that after the EOF is encountered during the input process, the entire SQL script is input to connect Scott/tigerspool/tmp/Dept. lstset linesize 100 pagesize 80 select * from Dept; spool off; exit; # exit the sqlplus environment eofexit # Release the shell script # grant the script execution permission to Oracle @ szdb: ~> Chmod U + x get_dept_2.sh # execute the shell script Oracle @ szdb: ~>. /Get_dept_2.sh deptno dname loc ---------- ------------ --------------- 10 Accounting New York 20 research Dallas 30 sales Chicago 40 operations boston2. Directly embed SQL statements into shell scripts (method 2, use pipeline symbol> instead of spool to output logs) Oracle @ szdb: ~> More get_dept_3.sh #! /Bin/bash # Set environment variableif [-f ~ /. Bashrc]; then .~ /. Bashrcfiexport oracle_sid = cnmmbosqlplus-S/nolog 1>/users/Oracle/Dept. log 2> & 1 <eofconnect Scott/tigerset linesize 80 pagesize 80 select * from Dept; exit; eofcat/users/Oracle/Dept. logexit # Another method is to output all SQL statements to generate an SQL script and then call Oracle @ szdb: ~> More get_dept_4.sh #! /Bin/bash # Set environment variableif [-f ~ /. Bashrc]; then .~ /. Bashrcfiexport oracle_sid = cnmmboecho "conn Scott/tiger select * from Dept; exit; ">/users/Oracle/get_dept.sqlplus-silent/nolog @ get_dept. SQL 1>/users/Oracle/get_dept.log 2> & 1cat get_dept.logexit3. embed the RMAN script into shell script Oracle @ szdb: ~> More rman_bak_2.sh #! /Bin/bash # Set environment variableif [-f ~ /. Bashrc]; then .~ /. Bashrcfiexport oracle_sid = cnmmbo $ ORACLE_HOME/bin/RMAN log =/users/Oracle/bak/RMAN. log <eofconnect 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 category type disk maxpiecesize = 4G; Allocate channel CH2 type Disk maxpiecesize = 4G; set limit channel limit readrate = 10240; set limit channel limit 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; d Elete noprompt obsolete; release channel execution; release channel CH2;} exit; eofexit # grant the script execution permission to Oracle @ szdb: ~> Chmod U + x rman_bak_2.sh # execute shell script Oracle @ szdb: ~>. /Rman_bak_2.shrman> RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> RMAN> Oracle @ szdb: ~>

Iii. More references

For more information about user-managed backup and recovery, see

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management (describes media recovery and processing in detail)

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

Oracle backup control file recovery (unsing backup controlfile)

 

For information on RMAN backup recovery and management, see

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use RMAN catalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

RMAN backup path confusion (when using plus archivelog)

 

For the Oracle architecture, see

Oracle tablespace and data files

Oracle Password File

Oracle parameter file

Oracle online redo log file)

Oracle Control File)

Oracle archiving logs

Oracle rollback and undo)

Oracle database instance startup and Shutdown Process

Automated Management of Oracle 10g SGA

Oracle instances and Oracle databases (Oracle Architecture)