Oracle Shell Scripting
This article presents some basic techniques for creating Windows batch files and UNIX/Linux shell scripts that connect to SQL*Plus and RMAN.
Windows
To run an SQL script. using SQL*Plus, place the SQL along with any SQL*Plus commands in a file and save it on your operating system. For example, save the following script. in a file called "C:\emp.sql".
CONNECT scott/tiger SPOOL C:\emp.lst SET LINESIZE 100 SET PAGESIZE 50 SELECT * FROM emp; SPOOL OFF EXIT;
Next, create a batch file called "C:\get_emp.bat" containing the following command.
sqlplus /nolog @C:\emp.sql
The resulting batch file can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks) or the AT scheduler.
The method is very similar when using Recovery Manager (RMAN). As an example, place the following RMAN commands in a file called "C:\cmdfile.txt".
RUN { ALLOCATE CHANNEL ch1 TYPE DISK FORMAT 'C:\oracle\backup\DB10G%d_DB_%u_%s_%p'; BACKUP DATABASE PLUS ARCHIVELOG; RELEASE CHANNEL ch1; } EXIT;
Next create a batch file called "C:\backup.bat" containing the following command.
rman target=/ @cmdfile.txt
This command can include a catalog= entry if a recovery catalog is used. Once again, resulting batch file can be run manually or scheduled.
UNIX and Linux (Method 1)
The previous methods works equally well in UNIX and Linux environments. For example, save the following script. in a file called "/u01/emp.sql".
CONNECT scott/tiger SPOOL /u01/emp.lst SET LINESIZE 100 SET PAGESIZE 50 SELECT * FROM emp; SPOOL OFF EXIT;
Next, create a shell script. called "/u01/get_emp.ksh" containing the following lines.
#!/bin/ksh sqlplus /nolog @/u01/emp.sql
The following command makes the file executable for the file owner.
chmod u+x /u01/get_emp.ksh
The resulting shell script. can be run manually from the command line, or scheduled using CRON.
For RMAN, place the following RMAN commands in a file called "/u01/cmdfile.txt".
RUN { ALLOCATE CHANNEL ch1 TYPE DISK FORMAT '/u01/backup/DB10G/%d_DB_%u_%s_%p'; BACKUP DATABASE PLUS ARCHIVELOG; RELEASE CHANNEL ch1; } EXIT;
Next create a batch file called "/u01/backup.ksh" containing the following lines.
#!/bin/ksh rman target=/ @/u01/cmdfile.txt
This command can include a catalog= entry if a recovery catalog is used. Once again, resulting shell script. must be made executable using the following command.
chmod u+x /u01/backup.ksh
The shell script. is now ready to run.
UNIX and Linux (Method 2)
UNIX and Linux environments also allow the SQL*Plus and RMAN commands to be piped directly from the command line. For example, save the following commands in a file called "/u01/get_emp.ksh".
#!/bin/ksh sqlplus /nolog << EOF CONNECT scott/tiger SPOOL /u01/emp.lst SET LINESIZE 100 SET PAGESIZE 50 SELECT * FROM emp; SPOOL OFF EXIT; EOF
Notice the "<< EOF" and "EOF" tags, indicating the start and end of the command being piped into the SQL*Plus executable. The shell script. is made executable using the following command.
chmod u+x /u01/get_emp.ksh
The shell script. is ready to be run manually from the command line or scheduled using CRON.
The following example shows how RMAN can use the same method. Create a file called "/u01/backup.ksh" with the following contents.
#!/bin/ksh rman target=/ << EOF RUN { ALLOCATE CHANNEL ch1 TYPE DISK FORMAT '/u01/backup/DB10G/%d_DB_%u_%s_%p'; BACKUP DATABASE PLUS ARCHIVELOG; RELEASE CHANNEL ch1; } EXIT; EOF
Once again, the script. can be made executable using the following command.
chmod u+x /u01/backup.ksh
The shell script. is now ready to run.
UNIX and Linux (Returning values from SQL)
The following code show a script. to pull the output of a query into a shell script. variable.
#!/bin/bash RETVAL=`sqlplus -silent scott/tiger <
If you are returning a single value, this method works well. If you are returning multiple rows of multiple columns it gets a bit messy and forces you to parse the return value.
For more information see:
Hope this helps. Regards Tim...
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/90618/viewspace-709487/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 12 Bash For Loop Examples for Your Linux Shell ScriptingOOPLinux
- Scripting on the Java platformJavaPlatform
- Batch Scripting TutorialBAT
- VBScript Scripting Engine初探
- 走近 WSH(Windows Scripting Host)Windows
- XDS: Cross-Device Scripting AttacksROSdev
- shell oracle互動Oracle
- How to Prevent Cross-Site Scripting AttacksROS
- linux-oracle-shellLinuxOracle
- 評估 Redis 最近的 Cross Protocol Scripting 漏洞RedisROSProtocol
- 深入Scripting Runtime Library 之一 (轉)
- Oracle-SHELL程式設計Oracle程式設計
- oracle安裝shell(未驗證)Oracle
- 巧用Proxyman Scripting 進行資料分類檢測
- shell oracle 建立使用者指令碼Oracle指令碼
- ORACLE自動備份shell指令碼Oracle指令碼
- oracle會話監控shell指令碼Oracle會話指令碼
- [Shell] monitor oracle database listener & instance statusOracleDatabase
- [Shell] monitor oracle alert.log file and sendmailOracleAI
- Internet Explorer漏洞分析(三)[上]——VBScript Scripting Engine初探
- 透過shell指令碼監控oracle session指令碼OracleSession
- 通過shell指令碼監控oracle session指令碼OracleSession
- shell 指令碼訪問oracle 寫法 (ZT)指令碼Oracle
- 【SHELL】Linux系統 Oracle例項監控、重啟 簡易Shell指令碼LinuxOracle指令碼
- crontab呼叫oracle的shell指令碼注意事項Oracle指令碼
- 使用shell自動傳送Oracle AWR報告Oracle
- shell 指令碼讀多個oracle_sid指令碼Oracle
- MoveIt! 學習筆記2- MoveIt! Commander Scripting(命令列控制)筆記命令列
- 求助:如何解決Scripting.fileSystem列印中文顯示問題?急!!!!!!!!!!
- Oracle SQLPLUS中DEFINE在SHELL中的呼叫OracleSQL
- [Shell] Monitor other host oracle instance alert.log and mailOracleAI
- oracle資料庫執行狀態監控SHELLOracle資料庫
- 監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- SHELL指令碼實現Oracle自啟動與關閉指令碼Oracle
- 獲取兩天內的告警日誌(bash|shell|oracle)Oracle
- 如何把shell變數傳入oracle的sqlplus變數OracleSQL
- shell 指令碼常用 oracle 環境變數set 設定指令碼Oracle變數
- 解決linux oracle shell上下箭呼叫歷史命令LinuxOracle