Unix FAQ

Oracle on Unix FAQ:

How do I get Oracle to automatically start when my server boots up?

Make sure the entry for your database SID in the ORATAB file ends in a capital "Y". Eg:

#   $ORACLE_SID:$ORACLE_HOME:[N|Y]
#
ORCL:/u01/app/oracle/product/8.0.5:Y
#

The scripts for starting and stopping databases are: $ORACLE_HOME/bin/dbstart and dbshut. SQL*Net (or Net8) is started with the lsnrctl command. Add the following entries to your /etc/rc2.d/S99dbstart (or equivalent - for example HP-UX uses /sbin/rc2.d/*) file:

su - oracle -c "/path/to/$ORACLE_HOME/bin/dbstart"         # Start DB's
su - oracle -c "/path/to/$ORACLE_HOME/bin/lsnrctl start"   # Start listener
su - oracle -c "/path/tp/$ORACLE_HOME/bin/namesctl start"  # Start OraNames (optional)

Can I trace a Unix process id to a SID and SERIAL#?

Yes, v_$process.spid contains the Unix process id. See example script at http://www.orafaq.com/scripts/performance/unixusr.txt

How does one SELECT information into a vi-file?

When using vi to edit SQL*Plus or Pro*C code, sometimes one need to insert a table definition or data values into the file. You can simply open a new line, put 'desc EMP' (or a SELECT statement) into it and type:

:.,.!sqlplus -s /

... automatically output from your statement is put in the vi buffer for cutting and pasting. One can even pipe the output through grep, awk, sed or perl before inserting into the file.

How does one SELECT a value from a table into a Unix variable?

One can select a value from a database column directly into a Unix environment variable. Look at the following shell script examples:

#!/bin/sh
VALUE=`sqlplus -silent user/password@instance <<END
set pagesize 0 feedback off verify off heading off echo off
select max(c1) from t1;
exit;
END`
if [ -z "$VALUE" ]; then
  echo "No rows returned from database"
  exit 0
else
  echo $VALUE
fi

Second example, using the SQL*Plus EXIT status code (can only return integer values smaller than 256):

#!/bin/ksh
sqlplus -s >junk1 /nolog <<EOF
connect user/password@instance
column num_rows new_value num_rows format 9999
select count(*) num_rows
  from table_name;
exit num_rows
EOF
echo "Number of rows are: $?"

Yet another example, only this time we will read multiple values from SQL*Plus into shell variables.

#!/bin/ksh
sqlplus  -s /nolog |&     # Open a pipe to SQL*Plus

print -p -- 'connect user/password@instance'
print -p -- 'set feed off pause off pages 0 head off veri off line 500'
print -p -- 'set term off time off'
print -p -- "set sqlprompt "

print -p -- "select sysdate from dual;"
read  -p SYSDATE

print -p -- "select user from dual;"
read  -p USER

print -p -- "select global_name from global_name;"
read  -p GLOBAL_NAME

print -p -- exit

echo SYSDATE:     $SYSDATE
echo USER:        $USER
echo GLOBAL_NAME: $GLOBAL_NAME

Note: In all these examples we use the -s or -silent option to suppress SQL*Plus banners. The /nolog option indicates that the script will login to the database. This prevents Unix from displaying your userid and password in the Unix process list (ps -ef).

How to start Enterprise Manager

When you can't access https://your-hostname:1158/em then the enterprise manager (EM) is down and needs to be started:

For sh, bash, ksh:

ORACLE_SID=YOURDBSID export ORACLE_SID
$ORACLE_HOME/bin/emctl start dbconsole

For csh, tcsh:

setenv ORACLE_SID YOURDBSID
$ORACLE_HOME/bin/emctl start dbconsole