Shell Script to Connect to Oracle Database in Linux

By
admin
-
2352
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.

Also See:  Conditional Statement in Linux Shell Programming

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.

Also See:  Ubuntu

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.

Also See:  Bash Scripting and Shell Programming Linux Command Line

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.

RELATED ARTICLESMORE FROM AUTHOR