Oracle initscript and usability improvements
Oracle can be installed on regular Linux systems nowadays, but there are a few things that we felt could be improved in terms of integration and usability. We've made the initscript's behaviour more in-line with normal expectations, and provide a few shell shortcuts to make life easier.
Initscript diff
This diff works for us at the time of writing. I wouldn't expect it to suddenly change substantially, but it'd be easy to update if Oracle makes some modifications themselves.
1 --- oracle-xe.orig 2009-07-31 13:45:43.000000000 +1000
2 +++ oracle-xe 2009-07-31 13:47:35.000000000 +1000
3 @@ -20,6 +20,7 @@
4 # changing the password for anonymous user logic is also
5 # removed
6 # svaggu 12/12/05 - Password validation is modified
7 +# anchor 2009-07-16 - Various improvements, gives a sure-kill on stop()
8 #
9
10 # Source fuction library
11 @@ -532,26 +533,43 @@
12 '/etc/init.d/oracle-xe configure' as the root user to configure the database."
13 exit 0
14 fi
15 +
16 + # Start the listener
17 status=`ps -ef | grep tns | grep oracle`
18 if [ "$status" == "" ]
19 then
20 if [ -f $ORACLE_HOME/bin/tnslsnr ]
21 then
22 - echo "Starting Oracle Net Listener."
23 + echo -n "Starting Oracle Net Listener... "
24 $SU -s /bin/bash $ORACLE_OWNER -c "$LSNR start" > /dev/null 2>&1
25 - fi
26 - fi
27 - echo "Starting Oracle Database 10g Express Edition Instance."
28 - $SU -s /bin/bash $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql" > /dev/null 2>&1
29 RETVAL=$?
30 if [ $RETVAL -eq 0 ]
31 then
32 - echo
33 + echo "Okay."
34 + else
35 + echo "Failed but continuing."
36 + fi
37 + fi
38 + fi
39 +
40 + # Start the DB component
41 + echo -n "Starting Oracle Database 10g Express Edition Instance... "
42 + LASTLINE=`$SU -s /bin/bash $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql" | tail -1`
43 + if [ x"$LASTLINE" == x"Database opened." ]
44 + then
45 + RETVAL=0
46 + echo "Okay."
47 + elif [ x"$LASTLINE" == x"ORA-01081: cannot start already-running ORACLE - shut it down first" ]
48 + then
49 + RETVAL=1
50 + echo "$LASTLINE"
51 else
52 - echo Failed to start Oracle Net Listener using $ORACLE_HOME/bin/tnslsnr\
53 - and Oracle Express Database using $ORACLE_HOME/bin/sqlplus.
54 RETVAL=1
55 + echo "Unknown error - $LASTLINE"
56 + echo "Failed to start Oracle Express Database using $ORACLE_HOME/bin/sqlplus."
57 fi
58 +
59 + # We're done
60 return $RETVAL
61 }
62
63 @@ -563,21 +581,48 @@
64 exit 0
65 fi
66
67 - # Stop Oracle 10g Express Edition Database and Listener
68 - echo Shutting down Oracle Database 10g Express Edition Instance.
69 - $SU -s /bin/bash $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/stopdb.sql" > /dev/null 2>&1
70 - echo Stopping Oracle Net Listener.
71 + # Stop the DB component
72 + echo -n "Shutting down Oracle Database 10g Express Edition Instance... "
73 + LASTLINE=`$SU -s /bin/bash $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/stopdb.sql" | tail -1`
74 + if [ x"$LASTLINE" == x"ORACLE instance shut down." ]
75 + then
76 + RETVAL=0
77 + echo "Okay."
78 + elif [ x"$LASTLINE" == x"Linux Error: 2: No such file or directory" ]
79 + then
80 + RETVAL=1
81 + echo "Failed, Oracle is not running"
82 + else
83 + RETVAL=1
84 + echo "Unknown error, $LASTLINE"
85 + echo "Failed to stop Oracle Express Database using $ORACLE_HOME/bin/sqlplus."
86 + fi
87 +
88 + # Stop the listener
89 + echo -n "Stopping Oracle Net Listener... "
90 $SU -s /bin/bash $ORACLE_OWNER -c "$LSNR stop" > /dev/null 2>&1
91 - RETVAL=$?
92 - echo
93 - if [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$LSNR
94 + LISTENRETVAL=$?
95 + if [ $LISTENRETVAL -eq 0 ]
96 then
97 - return $RETVAL
98 + echo "Okay."
99 + rm -f /var/lock/subsys/$LSNR
100 + else
101 + echo "Failed."
102 + RETVAL=1
103 + rm -f /var/lock/subsys/$LSNR
104 fi
105 +
106 + # Nuke any remaining processes from orbit; it's the only way to be sure.
107 + /bin/sleep 2
108 + /usr/bin/skill -9 -u oracle
109 + /bin/sleep 1
110 +
111 + # We're done
112 + return $RETVAL
113 }
114
115 dostatus() {
116 - if test ! -f "$CONFIGURATION"
117 + if [ ! -f "$CONFIGURATION" ]
118 then
119 echo "Oracle Database 10g Express Edition is not configured. You must run
120 '/etc/init.d/oracle-xe configure' as the root user to configure the database."
121 @@ -589,16 +634,43 @@
122 exit 0
123 fi
124
125 - $SU -s /bin/bash $ORACLE_OWNER -c "$LSNR status"
126 - RETVAL=$?
127 + # Check the TNS Listener
128 + $SU -s /bin/bash $ORACLE_OWNER -c "$LSNR status" >/dev/null
129 + LISTENRETVAL=$?
130 +
131 + # Check the DB itself. Assume that we can't check the DB if the listener isn't running
132 + if [ $LISTENRETVAL -eq 0 ]
133 + then
134 + # Show the user
135 + $SU -s /bin/bash $ORACLE_OWNER -c "$LSNR status" | egrep '^ Instance "[A-Za-z_]+", status (UNKNOWN|READY), has 1 handler\(s\) for this service...'
136 + # "PLSExtProc" belongs to the TNS Listener, so we don't want to count it
137 + NUMPROCS=`$SU -s /bin/bash $ORACLE_OWNER -c "$LSNR status" | grep -v '^ Instance "PLSExtProc"' | egrep '^ Instance "[A-Za-z_]+", status (UNKNOWN|READY), has 1 handler\(s\) for this service...' | wc --lines`
138 + fi
139 +
140 + # Sort out the error codes
141 + RETVAL=0
142 + if [ $LISTENRETVAL -ne 0 ]
143 + then
144 + echo "TNS Listener is not running, and cannot check the DB"
145 + RETVAL=1
146 + else
147 + if [ $NUMPROCS -lt 1 ]
148 + then
149 + echo "Expecting at least one 'XE' process running, database does not appear to be running"
150 + RETVAL=1
151 + fi
152 + fi
153 +
154 + # We're done
155 + return $RETVAL
156 }
157
158 # See how we were called
159 case "$1" in
160 start)
161 - if test -f "$CONFIGURATION"
162 + if [ -f "$CONFIGURATION" ]
163 then
164 - if test "$ORACLE_DBENABLED" != "true"
165 + if [ "$ORACLE_DBENABLED" != "true" ]
166 then
167 exit 0
168 fi
169 @@ -613,9 +685,9 @@
170 configure
171 ;;
172 stop)
173 - if test -f "$CONFIGURATION"
174 + if [ -f "$CONFIGURATION" ]
175 then
176 - if test "$ORACLE_DBENABLED" != "true"
177 + if [ "$ORACLE_DBENABLED" != "true" ]
178 then
179 exit 0
180 fi
Improving usability
We've applied these mods for Oracle XE (the free edition). They should be similarly applicable to Real Oracle, but we haven't tested it. We assume you use a bash-alike shell.
Improve the root account's .bashrc/.bash_profile (we routinely login as root when doing maintenance)
1 # Append this to the file, it makes getting to oracle much faster 2 alias sql='echo Changing to oracle account ; su -c "sqlplus / as sysdba" - oracle'
Improve the oracle user's .bashrc/.bash_profile - the oracle user needs its environment setup to get proper access. Oracle comes with a source-able file to do this for you, upon which this is based.
1 # This is from oracle's supplied environment setup file 2 export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server 3 export ORACLE_SID=XE 4 export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh` 5 export PATH=$ORACLE_HOME/bin:$PATH 6 if [ $?LD_LIBRARY_PATH ] 7 then 8 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH 9 else 10 export LD_LIBRARY_PATH=$ORACLE_HOME/lib 11 fi 12 13 14 # For your convenience 15 export PS1='oracle $ ' 16 alias sql='sqlplus / as sysdba' # 'sqlplus' is a lot to type; the best sysadmins are lazy