DBA Hub

📋Steps in this guide1/6

Oracle Shell Scripting

This article presents some some basic techniques for creating Windows batch files and UNIX/Linux shell scripts that connect to SQL*Plus and RMAN.

oracle miscconfigurationintermediate
by OracleDba
15 views
1

Windows

To run a 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". Next, create a batch file called "C:\get_emp.bat" containing the following command. 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". Next create a batch file called "C:\backup.bat" containing the following command. This command can include a entry if a recovery catalog is used. Once again, resulting batch file can be run manually or scheduled.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CONNECT scott/tiger
SPOOL C:\emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;

sqlplus /nolog @C:\emp.sql

RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
}
EXIT;

rman target=/ @cmdfile.txt
2

Powershell

Powershell allows file redirection similar to UNIX/Linux shell scripting, so we can do something like the following for SQL*Plus. The following example works for RMAN.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@"
  CONNECT scott/tiger
  SPOOL /u01/emp.lst
  SET LINESIZE 100
  SET PAGESIZE 50
  SELECT *
  FROM emp;
  SPOOL OFF
  EXIT;
"@ | sqlplus.exe /nolog

@"
RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
}
EXIT;
"@ | rman target=/
3

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". Next, create a shell script called "/u01/get_emp.ksh" containing the following lines. The following command makes the file executable for the file owner. 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". Next create a batch file called "/u01/backup.ksh" containing the following lines. This command can include a entry if a recovery catalog is used. Once again, resulting shell script must be made executable using the following command. The shell script is now ready to run.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CONNECT scott/tiger
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;

#!/bin/ksh
sqlplus /nolog @/u01/emp.sql

chmod u+x /u01/get_emp.ksh

RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
}
EXIT;

#!/bin/ksh
rman target=/ @/u01/cmdfile.txt

chmod u+x /u01/backup.ksh
4

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". 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. 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. Once again, the script can be made executable using the following command. The shell script is now ready to run.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#!/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

chmod u+x /u01/get_emp.ksh

#!/bin/ksh
rman target=/ << EOF
RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
}
EXIT;
EOF

chmod u+x /u01/backup.ksh
5

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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
#!/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
6

Checking the Database is Up

It is sometimes necessary to check the database is up before performing a task. In the following example, we check the database is up before starting a Tomcat application server. If the database is down, the script sleeps for 5 minutes, then checks again. The script requires an Oracle Client to make connections to the database. This could be a full client installation, or an Oracle Instant Client installation. Create a script called "check_db.sh" will the following contents. This is the script that will check if the database is up or not. Next, create a script called "tomcat_start_dev.sh" with the following contents. This sources the "check_db.sh" script, so it is included as if it were part of this script. It could be combined, but this allows the "check_db.sh" script to be shared by multiple scripts. The "tomcat_start_dev.sh" script loops round, checking to see if the DB is up. Once it is up, it falls through to the Tomcat start command. For more information see: - SQL*Plus User's Guide and Reference - Oracle Database Backup and Recovery Reference - UNIX Commands for DBAs Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 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
}

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!