oraenv or coraenv, ORACLE_HOME, ORACLE_SID & oratab

Oraenv

Oraenv and coraenv are Unix/ Linux command line utilities that sets the required environment variables (ORACLE_SID, ORACLE_HOME and PATH) to allow a user to connect to a given database instance. If these environment variables are not set, commands such as SQL*Plus, imp, exp, or any other utility for that matter, will not work (or not be found).

Use coraenv when using the C Shell and oraenv when using a Bourne, Korn or Bash shell.

Note the syntax: ". oraenv" or {period} {space} oraenv.

[edit]Examples

Interactive:

$ . oraenv
ORACLE_SID = [] ? orcl

Non-interactive (handy for scripting):

$ export ORACLE_SID=orcl
$ export ORAENV_ASK=NO
$ . oraenv

ORACLE HOME

ORACLE_HOME refers to either:

1. a directory where the Oracle software is installed; or
2. an environment variable pointing to the directory where the Oracle software is installed.

Windows

Check current value:

echo The current ORACLE_HOME is %ORACLE_HOME%

Set the ORACLE_HOME environment variable:

set ORACLE_HOME=C:\oracle\ora10.2

[edit]Unix & Linux

Check current value:

env | grep ORACLE_HOME

Change the ORACLE_HOME environment variable (valid for bash and ksh):

export ORACLE_HOME=/app/oracle/product/10.2.0/db_1

ORACLE SID

The Oracle System ID (SID) is used to uniquely identify a particular database on a system. For this reason, one cannot have more than one database with the same SID on a computer system.

When using RAC, all instances belonging to the same database must have unique SID's.

[edit]Switching between databases

Set the ORACLE_SID environment variable (or ORA_SID on VMS systems) to work on a particular database. Remember that the SID is case sensitive in Unix / Linux environments.

Windows:

set ORACLE_SID=orcl

Unix/ Linux:

export ORACLE_SID=orcl

Oratab

Oratab is a colon-delimited text file on Unix and Linux systems that associates ORACLE_SID and ORACLE_HOME values.

The last field contains a "Y" or "N" for database startup when the machine boots up. One can also use hashes (#) to start comment lines.

This file is either found in the /var/opt/oracle or the /etc directories.

Scripts like oraenv, dbhome, dbstart and dbshut use the oratab file.

[edit]Sample oratab file

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/opt/oracle/product/10.2.0/db_1:Y

The above oratab file describes an Oracle instance called orcl (don't confuse this with the database name!) that uses directory /opt/oracle/product/10.2.0/db_1 as its Oracle home. The Yindicates that this database should be started when the system boots up.