Monday, January 10, 2011

oraenv or coraenv, ORACLE_HOME, ORACLE_SID & oratab


Oraenv and coraenv are UnixLinux command line utilities that sets the required environment variables (ORACLE_SIDORACLE_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.


$ . oraenv
ORACLE_SID = [] ? orcl
Non-interactive (handy for scripting):
$ export ORACLE_SID=orcl
$ export ORAENV_ASK=NO
$ . oraenv


ORACLE_HOME refers to either:
  1. directory where the Oracle software is installed; or
  2. an environment variable pointing to the directory where the Oracle software is installed.

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


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.
set ORACLE_SID=orcl
Unix/ Linux:

export ORACLE_SID=orcl


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 oraenvdbhomedbstart 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:
# 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.
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.

No comments:

Post a Comment