Oracle DB for system administrators, part8 (shell scripts)

In this part I will discuss some shell scripts you can create/use for work automation in Oracle database.

36. One very important, but very dangerous think is to create start and stop script for oracle.
Its important because if you want to stop and start the machine (and database) graceful you should execute serie of commands and always exist possibility to forget one of them. Because of this its good to have some script to stop and start Oracle DB.
36.1. Very important is to edit /etc/oratab file (here and below i will use locations of the files as they are in RHEL) and to have your instance(s) on this way:
[root@rh-or ~]# grep -v ^# /etc/oratab
orcl:/home/oracle/11g2:Y

First value is name of the instance i.e. SID, second is the value of ORACLE_HOME and third one is Y - to start this instance and N do not start it. Lines, starting with # are comments

36.2. Next I will create sample start/stop script
[root@rh-or ~]# more /etc/init.d/oracle
#!/bin/bash
#
# oracle        Startup script for start oracle database server
#
ORACLE_HOME=/home/oracle/11g2
ORACLE_SID=orcl
PATH=$PATH:/$ORACLE_HOME/bin
ORACLE=oracle # user to run oracle DB
export ORACLE_HOME ORACLE_SID PATH ORACLE

#
start() {
        echo -n $"Starting oracle"
        su - -c "dbstart" $ORACLE
        su - -c "lsnrctl start" $ORACLE
}

stop() {
        echo -n $"Stopping oracle"
        su - -c "lsnrctl stop" oracle
        su - -c "dbshut" oracle
}

# See how we were called.
case "$1" in
  start)
        start
        ;;
  stop)
        stop
        ;;
  restart)
        stop
        start
        ;;
  *)
        echo $"Usage: $prog {start|stop|restart}"
        exit 1
esac

I put my script in /etc/init.d because this is the place for such scripts in Linux, but do not create softlinks in rc.d directories to avoid auto start/restart because this is not a good idea. If You want You can do it
Of course I can use only dbstart and dbshut, but for this i should change those scripts and I personalty prefer do not touch them. And this give me better control over the precedence of start listener and database itself. Actually the only change is to define variable ORACLE_HOME_LISTNER (which in my case is equiv to ORACLE_HOME).

37. the next point is to automate execution of sql scripts from command line w/o starting sqlplus in interactive mode
I prepare one sample script:
[oracle@rh-or ~]$ cat date.sql
set head off
select sysdate from dual;
exit

and I will execute it on this way:
[oracle@rh-or ~]$ sqlplus -S romeo/pass1234@orcl @date.sql

24-MAR-10

[oracle@rh-or ~]$

As you see execution is not different from any shell script. Here I mention in command line explicitly the name of the SID, because this is very helpful in situation you execute script from server with installed oracle client against some oracle server

38. If Your sql script need some parameters to be entered you can enter them as parameters of command line. For script:
[oracle@rh-or ~]$ cat date.sql
set head off
set verify off
select '&1', sysdate from dual;
exit
I need to enter one parameter (usually by hand), but I will set it from command line
[oracle@rh-or ~]$  sqlplus -S romeo/pass1234@orcl @date.sql "Today is "

Today is  24-MAR-10

[oracle@rh-or ~]$