Linux/Unix shell calls PL/SQL

In Linux/Unix, apart from calling SQL, calling PL/SQL is also a common situation for DBA. The following mainly uses some examples to show how to call PL/SQL under shell.

Other related references:
Linux/Unix shell scripts call SQL and RMAN scripts
Passing variables between Linux/Unix shell SQL statements

1. Enter the PL/SQL code line by line into the temporary file Robin @ szdb :~ /Dba_scripts/custom/bin> More shell_call_plsql.sh #/bin/bash # + examples ++ an example of calling PLSQL in Shell ++ # + usage: ++ # +. /shell_call_plsql.sh $ oracle_sid + # + Author: Robinson + # + region + # --------------------------------- # define variable and check Sid # else if [-f ~ /. Bash_profile]; then .~ /. Bash_profilefiif test $ #-LT 1 then Echo you must pass a Sid exit fioracle_sid = $1; export oracle_sid # kernel # Prepare PLSQL script # kernel echo "set serveroutput on size 1000000">/tmp/plsql_scr.sqlecho "Set feed off">/tmp/plsql_scr.sqlecho "declare">/ TMP/plsql_scr.sqlecho "cursor C1 (param1 varchar2) is ">/tmp/plsql_scr.sqlecho" selec T decode (substr (value, 1, 1 ),'? ', Param1 | substr (value, 2), value) dd ">/tmp/plsql_scr.sqlecho" from V \ $ parameter where name = 'background _ dump_dest '; ">/tmp/plsql_scr.sqlecho" v_value V \ $ parameter. value % type; ">/tmp/plsql_scr.sqlecho" begin open C1 ('$ ORACLE_HOME'); fetch C1 into v_value; close C1; ">>/tmp/plsql_scr.sqlecho" dbms_output.put_line (v_value); ">>/ tmp/plsql_scr.sqlecho" end; ">>/tmp/plsql_scr.sqlecho "/" >/Tmp/plsql_scr. SQL # Run # execute PLSQL script # -------------------------------- if [-S/tmp/plsql_scr. SQL]; Then ECHO-e "running SQL script to find out bdump directory... \ n "$ ORACLE_HOME/bin/sqlplus-s"/As sysdba ">/tmp/plsql_scr_result.log <EOF @/tmp/logs" Check the reslut "Echo" ---------------------- "cat/ TMP/plsql_scr_result.logexit # The code above is to query the dump path of the specified Oracle SID. # Add the code to a file in line by line to form PL/SQL code. # Note the use of escape characters. We have escaped the $ symbol of parameter. Robin @ szdb :~ /Dba_scripts/custom/bin>. /shell_call_plsql.sh cnbo1running SQL script to find out bdump directory... check the reslut ------------------------/u02/database/cnbo1/bdump2, one-time input of PL/SQL code to the temporary file Robin @ szdb :~ /Dba_scripts/custom/bin> More shell_call_plsql_2.sh #/bin/bash # + examples ++ an example of calling PLSQL in Shell ++ # + usage: ++ # +. /shell_call_plsql_2.sh $ oracle_sid + # + Author: Robinson + # + region + # ------------------------------- # define variable and check Sid # else if [-f ~ /. Bash_profile]; then .~ /. Bash_profilefiif test $ #-LT 1 then Echo you must pass a Sid exit fioracle_sid = $1; export oracle_sid # partition # Prepare PLSQL script # ------------------------------- echo "set serveroutput on size 1000000 Set feed offdeclare cursor C1 (param1 varchar2) is select decode (substr (value, 1, 1 ), '? ', Param1 | substr (value, 2), value) dd from v \ $ parameter where name = 'background _ dump_dest'; v_value V \ $ parameter. value % type; begin open C1 ('/users/Oracle/orahome10g'); fetch C1 into v_value; close C1; dbms_output.put_line (v_value); end; /exit ">/tmp/plsql_scr. SQL # Run # execute PLSQL script # ------------------------------ if [-S/tmp/plsql_scr. SQL]; Then ECHO-e" R Unning SQL script to find out bdump directory... \ n "$ ORACLE_HOME/bin/sqlplus-s"/As sysdba "@/tmp/plsql_scr. SQL>/tmp/plsql_scr_result.log fiecho" Check the reslut "Echo" -------------------- "cat/tmp/ plsql_scr_result.logexit # Author: robinson Cheng # blog: Workshop. Robin @ szdb :~ /Dba_scripts/custom/bin> chmod U + x shell_call_plsql_2.shrobin @ szdb :~ /Dba_scripts/custom/bin>. /shell_call_plsql_2.sh cnbo1running SQL script to find out bdump directory... check the reslut ------------------------/u02/database/cnbo1/bdump3, variant Scheme (replace PL/SQL with SQL) Robin @ szdb :~ /Dba_scripts/custom/bin> More shell_call_plsql_3.sh # ----------------------------- # Set environment here # -------------------------------- if [-f ~ /. Bash_profile]; then .~ /. Bash_profilefiexport mail_dir =/users/Robin/dba_scripts/sendEmail-v1.56export mail_list = 'robinson. chen@2GoTrade.com 'export mail_fm = 'oracle @ szdb.com '# ----------------------------------- # Find bdump directory for database # ------------------------------------- oracle_sid = $1; export oracle_siddump_dir = 'sqlplus-S'/As sysdba '<eofset pagesize 0 feedback off verify off heading off echo offselect valu E from V \ $ parameter where name = 'background _ dump_dest '; exiteof' If [-Z $ {dump_dir}]; then mail_sub = "The bdump directory was not found for $ {oracle_sid}" $ mail_dir/sendemail-U $ mail_sub-F $ mail_fm-T $ mail_list-M $ mail_sub exitelse echo $ {dump_dir} fiexit # note, the above does not call PL/SQL, but uses SQL to accomplish the same function. If SQL functions can be completed, we recommend that you use SQL first. # Note that the parameter here uses two escape characters. # Grant the returned results of SQL Execution directly to the shell variable Robin @ szdb :~ /Dba_scripts/custom/bin> chmod U + x shell_call_plsql_3.shrobin @ szdb :~ /Dba_scripts/custom/bin>./shell_call_plsql_3.sh cnbo1/u02/database/cnbo1/bdump

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

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
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
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

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)