Oracle Shell Scripting
This report presents some fundamental methods for generating Windows batch files and UNIX/Linux shell scripts related to SQL*Plus, and RMAN.
Windows
Powershell
UNIX and Linux (Method 1)
UNIX and Linux (Method 2)
UNIX and Linux (Returning values in SQL)
Assessing the Database is Up
Windows
To conduct a SQL script with SQL*Plus, put the SQL alongside almost any SQL*Plus commands from a document and store it on your working system. As an instance, keep 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;
Then make a batch file named “C:\get_emp.bat” comprising the next command.
sqlplus /nolog @C:\emp.sql
The subsequent batch file could be run manually, either by double-clicking onto it or scheduled with the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks) or even the AT scheduler.
The way is quite similar when using Recovery Manager (RMAN). For example, set the next RMAN commands inside a file called “C:\cmdfile.txt”.
RUN { BACKUP DATABASE PLUS ARCHIVELOG; } EXIT;
Next, make a batch file named “C:\backup.bat” comprising the next command.
rman target=/ @cmdfile.txt
This control may incorporate a catalog = entrance when a recovery catalog is used. Yet more, the resulting batch file can be run manually or scheduled.
Powershell
Powershell enables file redirection, very similar to UNIX/Linux shell scripting. Therefore we could do something similar to the following for SQL*Plus.
@" CONNECT scott/tiger SPOOL /u01/emp.lst SET LINESIZE 100 SET PAGESIZE 50 SELECT * FROM emp; SPOOL OFF EXIT; "@ | sqlplus.exe /nolog
This example works for RMAN.
@" RUN { BACKUP DATABASE PLUS ARCHIVELOG; } EXIT; "@ | rman target=/
UNIX and Linux (Method 1)
The prior methods work similarly well in UNIX and Linux environments. By way of instance, store 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;
Then make a shell script named “/u01/get_emp.ksh” containing these lines.
#!/bin/ksh sqlplus /nolog @/u01/emp.sql
This command makes the file executable to the document owner.
chmod u+x /u01/get_emp.ksh
The subsequent shell script could be run manually in the command line or scheduled with CRON.
To get RMAN, set the next RMAN commands inside a file called “/u01/cmdfile.txt”.
RUN { BACKUP DATABASE PLUS ARCHIVELOG; } EXIT;
Next, make a batch file named “/u01/backup. Ksh” containing these lines.
#!/bin/ksh rman target=/ @/u01/cmdfile.txt
This control may incorporate a catalog = entrance when a recovery catalog is used. Yet more, the resultant shell script has to be created executable with the next command.
chmod u+x /u01/backup.ksh
The shell script is now prepared to operate.
UNIX and Linux (Method 2)
UNIX and Linux environments also permit the SQL*Plus, and RMAN controls to be piped straight from the command line. By way of instance, save the next orders 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 that the “<< EOF” along with “EOF” tags, signaling the beginning and finish of the command, has been piped in the SQL*Plus executable. The shell script was created executable with the next order.
chmod u+x /u01/get_emp.ksh
The shell script is prepared to be run manually in the command line or scheduled with CRON.
The next example demonstrates how RMAN may use exactly the identical method. Create a file called “/u01/backup. Ksh” with these contents.
#!/bin/ksh rman target=/ << EOF RUN { BACKUP DATABASE PLUS ARCHIVELOG; } EXIT; EOF
Yet more, the script could be created executable using the next command.
chmod u+x /u01/backup.ksh
The shell script is now prepared to operate.
UNIX and Linux (Returning values in SQL)
This code reveals a script to pull on the output of a question into a shell script factor.
#!/bin/bash RETVAL=`sqlplus -silent scott/tiger <<EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF SELECT * FROM emp; EXIT; EOF` if [ -z "$RETVAL" ]; then echo "No rows returned from database" exit 0 else echo $RETVAL fi
If you’re returning one value, this procedure works nicely. If you’re returning multiple rows of multiple columns, it will get a little cluttered and compels you to emphasize the yield value.
Checking the Database is Up
It’s occasionally essential to inspect the database remains up before doing a job. In the next example, we check that the database consists of up before beginning a Tomcat application server. In case the database is down, then the script waits for 5 minutes, then checks again.
The script demands an Oracle Client to create links into the database. This might be a complete client setup or an Oracle Instant Client setup.
Create a script known as “check_db. Sh” will the subsequent contents. Here is the script which will check whether the database is not.
# Environment variables necessary for Oracle Instant Client export LD_LIBRARY_PATH=/home/tomcat/scripts/instantclient_11_2 export PATH=$PATH:$LD_LIBRARY_PATH function check_db { CONNECTION=$1 RETVAL=`sqlplus -silent $CONNECTION <<EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF SELECT 'Alive' FROM dual; EXIT; EOF` if [ "$RETVAL" = "Alive" ]; then DB_OK=0 else DB_OK=1 fi }
Next, create a script called “tomcat_start_dev.sh” with the following contents.
scriptPath=${0%/*} source $scriptPath/check_db.sh CONNECTION="up_check_user/password@//hostname:1523/service" echo "Wait until DB is up" check_db $CONNECTION while [ $DB_OK = 1 ] do echo "DB not up yet. Sleeping for 5 mins (CTRL+C to exit)" sleep 300 check_db $CONNECTION done echo "Starting" echo "DEV: /u01/dev" /u01/dev/bin/tomcat start
This resources the “check_db.sh” script; therefore, it’s included as though it were a part of the writing. It might be united, but this permits the “check_db.sh” script has to be shared with multiple hands. The “tomcat_start_dev.sh” script pliers around, checking to determine whether the DB is upward. When it’s up, it drops to the Tomcat begins to control.