Unix FAQ
Oracle on Unix FAQ:
Contents
How do I get Oracle to automatically start when my server boots up?[edit]
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#?[edit]
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?[edit]
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?[edit]
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[edit]
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