How does one prepare the Oracle environment?

Дата канвертавання26.04.2016
Памер27.8 Kb.


How does one prepare the Oracle environment?

An oraenv script is provided with every Oracle installation. Run it as part of your current environment to set the necessary environment variables like ORACLE_HOME, ORACLE_SID, etc. Never set the Oracle Home directory explicitely in a script.

The following example shows how to use oraenv (non-interactively) from a script:



. oraenv

NOTE: the ". " in front of "oraenv" is required to run the script as part of the current shell.

 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:





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) 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 one export directly to a tape drive?

One can use a tape device name instead of a normal file name. When using a device, one also needs to specify the VOLSIZE= parameter. Look at this example:

exp userid/password file=/dev/rmt0 table=emp volsize=1.2G

Note: The volume size is a number ending with a "m", "k", or "b" (M, K, or B). The default is bytes.

 I don't have enough space to export my database! Any suggestions?

If one cannot afford to buy extra disk space one can run the export and compress utilities simultaneously. This will prevent the need to get enough space for both the export file AND the compressed export file. Eg:

# Make a pipe

mknod expdat.dmp p # or mkfifo pipe

# Start compress sucking on the pipe in background

compress < expdat.dmp > expdat.dmp.Z &

# Wait a second or two before kicking off the export

sleep 5

# Start the export

exp scott/tiger file=expdat.dmp

Or export accross the network directly into the target database:

Host A:

mknod FIFO.dmp p

exp u/p FILE=FIFO.dmp rest_of_parameters...
Host B:

mknod FIFO2.dmp p

rsh host1 dd if=FIFO.dmp > FIFO2.dmp &

imp u/p FILE=FIFO2.dmp rest_of_parameters...

Note: It is important that one verify that the named pipe is ready on each side before you start the process.

Back to top of file

How does one overcome the Unix 2 Gig file limit?

This example uses the Unix split command to create multiple files, each smaller than the Unix (and imp/exp) 2 Gigabyte file size limit. This method can typically be used for import, export and SQL*Loader operations.

cd /tmp/data

rm exp.dmp

mknod exp.dmp p # mkfifo on certain Unix flavours

split -b2047m

exp scott/tiger file=/tmp/data/exp.dmp record=n tables=tableX

cd /tmp/data

rm exp.dmp

mknod exp.dmp p

cat xaa xab xac xad >/tmp/data/exp.dmp &

imp scott/tiger file=/tmp/data/exp.dmp commit=y tables=tableX

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:


VALUE=`sqlplus -silent user/password@instance <

set pagesize 0 feedback off verify off heading off echo off

select max(c1) from t1;



if [ -z "$VALUE" ]; then

echo "No rows returned from database"

exit 0


echo $VALUE


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


sqlplus -s >junk1 /nolog <

connect user/password@instance

column num_rows new_value num_rows format 9999

select count(*) num_rows

from table_name;

exit num_rows


echo "Number of rows are: $?"

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

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;"


print -p -- exit


echo USER: $USER


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

 Can one see what patches are applied to Oracle?

With the Unix "what" command one can see what patches are applied to an executable. The Oracle implementation of what is owhat.

The 'what' command delves into a file and extracts SCCS version control information from that file, if any exists. For more details, look up 'man what'. Look at this example:

$ cd $ORACLE_HOME/bin

$ owhat oracle

Oracle patches in /app/oracle/product/7.3.4/bin/oracle:

sf.c VLFS patch 08/1/96

sksa.c VLFS patch 08/1/96

sp.c VLFS patch 08/1/96

ssf.c VLFS patch 08/1/96

How does one monitor and trace Unix processes?

To trace what a Unix process is doing enter:

truss -rall -wall -p

truss -p $ lsnrctl dbsnmp_start

NOTE: The "truss" command works on SUN and Sequent. Use "tusc" on HP-UX, "strace" on Linux, "trace" on SCO Unix or call your system administrator to find the equivalent command on your system.

Monitor your Unix system:

Unix message files record all system problems like disk errors, swap errors, NFS problems, etc. Monitor the following files on your system to detect system problems:

tail -f /var/adm/SYSLOG

tail -f /var/adm/messages

tail -f /var/log/syslog

What should one do with those core files?

Make sure the complete CORE file was written out. System administrators usually limit core size to avoid dangerous core files filling file systems. Core file size can be limited with the "ulimit" or "limit" commands. Look at these examples:

$ ulimit -a # Display limits for your session under sh or ksh

$ limit # Display limits for your session under csh or tcsh
$ ulimit -c SIZE_IN_BLOCKS # Limit core size under sh or ksh

$ limit coredumpsize SIZE_IN_KB # Limit core size under csh or tcsh

If you see a core file lying around, just type "file core" to get some details about it. Example:

$ file core

core:ELF-64 core file - PA-RISC 2.0 from 'sqlplus' - received SIGABRT

Run the Unix process debugger to obtain more information about where and why the process abended. This information is normally requested by Oracle Support for in-depth analysis of the problem. Some example:


$ gdb $ORACLE_HOME/bin/sqlplus core

bt # backtrace of all stack frames

HP-UX, Solaris, etc:

$ adb $ORACLE_HOME/bin/sqlplus core




$ debug -c core $ORACLE_HOME/bin/sqlplus

debug> stack

debug> quit

Note: Some Unix operating systems use debugger commands like sdb, xdb, dbx, etc. The same principles apply; do a man on the command and go for it!

База данных защищена авторским правом © 2016
звярнуцца да адміністрацыі

    Галоўная старонка