How to setup environment variables in oracle to successfully connect to a particular database/instance
Oracle environment variable is set so that your operating system (OS) can know the Oracle software installation directory and which oracle software/binaries should interact with which database/instance.
The most important environment variable are listed below
ORACLE_HOME : setting oracle home means which oracle product/software or
installation you will be using to associate with your databasehaving oracle
home environment variable makes easier for maintenance and management of oracle
software.As per Oracle optimal flexible architecture (OFA)
/u01/app/oracle/product/10.2.0.1/dbhome_1
ORACLE_SID : The oracle system identifier is a unique identifier which is set to interact with a particular instance. You may have multiple oracle database on a single machine by setting this variable we are information oracle software to connect to that particular instance.
we will learn various way to set this variable
firstly we will check oracle home and how many databases are installed on the machine
[oracle@db10g /]$ cat /etc/oratab
#
# 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.
#
#
ora10gdb01:/u01/app/oracle/product/10.2.0.1/db01:N
10gdb02:/u01/app/oracle/product/10.2.0.1/db01:N
In the above out put we can see we have two databases ora10gdb01 & 10gdb02
now let see if our environment variable is not set correctly what will happen
Login: oracle
Last login: Sat May 31 18:12:45 2014 from 192.168.1.1
[oracle@db10g ~]$ sqlplus
-bash: sqlplus: command not found
[oracle@db10g ~]$
the operating system doesn’t know what is sqlplus command
setting oracle home and sid for ora10gdb01 manually
set oracle_sid by giving below command
[oracle@db10g /]$ export ORACLE_SID=ora10gdb01
check if the environment variable is set correctly
[oracle@db10g /]$ echo $ORACLE_SID
set oracle_home by giving below command
[oracle@db10g /]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0.1/db01
check if the environment variable is set correctly
[oracle@db10g /]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0.1/db01
now you can connect to the database issuing below
[oracle@db10g /]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Sat May 31 20:07:18 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
using .oraenv script provided by oracle, installed during software installation
[oracle@db10g ~]$ . oraenv
ORACLE_SID = [ora10gdb01] ? ora10gdb01
[oracle@db10g ~]$ echo $ORACLE_SID
ora10gdb01
[oracle@db10g ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0.1/db01
let us again login as oracle user and check the environment variables
Login: oracle
[oracle@db10g ~]$ echo $ORACLE_HOME
[oracle@db10g ~]$ echo $ORACLE_SID
[oracle@db10g ~]$
as you can see, there is no output that means that our environment variables are not set
now we will again use .oraenv to set environment variable for our second database
[oracle@db10g ~]$ . oraenv
ORACLE_SID = [oracle] ? 10gdb02
[oracle@db10g ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0.1/db01
[oracle@db10g ~]$ echo $ORACLE_SID
10gdb02
[oracle@db10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Sat May 31 20:27:10 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
Earning the Oracle Database Foundations Certified Junior Associate
credential arms you with a fundamental level of programming and database
conceptual knowledge and skills that will demonstrate your capabilities to a
future employer.
This certification also shows your potential to progress into OCA level during your early stage of employment, and later to OCP, becoming an asset to any company.
This Junior Associate certification is focused on students who want to get into the career as a DBA and then work their way up as professionals.
The default location for a spfile or pfile is $ORACLE_HOME/dbs however if you are unsure as to where your spfile is located you can issue the following from SQLPLUS:
SQL> SHOW PARAMETER spfile;
NAME TYPE VALUE
-------- ------- ---------
spfile string
/app/oracle/product/10.2.0.4server/db_1/dbs/spfileictst3f.ora
Create pfile from spfile
If you wish to back up your spfile, you can create a pfile from the spfile which will save the current parameter configuration.
SQL> create pfile='<pfile location>' from spfile;
or
SQL> CREATE PFILE='<pfile location>' FROM SPFILE = '<spfile location>';
Create spfile from pfile
If you want to then revert back to a saved pfile, you can overwrite your current spfile with the following:
SQL> CREATE SPFILE FROM PFILE = '<pfile location>';
or
SQL> CREATE SPFILE='<spfile location>' FROM PFILE='<pfile location>';
