Shell Script to Connect to Oracle Database in Linux

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.