SetDb.env - Oracle Database Key environment Settings.

 You need the following environment setting set in your linux environment so that you can start working with the Oracle DB.

ORACLE_SID=<NAME of your DB>

export ORACLE_SID

ORACLE_HOME=<complete path of your oracle home>

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

you can either have them typed every time you log in to the linux environment or create them as an  environment file which you can execute every time you log in to your Linux Environment.

Steps as follows:

==============

1. vi <Filename>.env or <Sid_ServerName>.env

2. copy paste the below:

ORACLE_SID=<NAME of your DB>

export ORACLE_SID

ORACLE_HOME=<complete path of your oracle home>

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

3. Replace the parameters <Values> with your Database values.

4. Save the file.

5. Make sure that you have provided execute permissions for the file.

6. Now execute the file (Source the file) as shown below:

.<Space>./<Filename>.env.

Now all your environment variables are set.

If you would like to verify the environment Variables try the following:

$ echo $ORACLE_HOME or $ORACL_SID

You should see the values as set earlier.

Note: When the above environment variables are not set most of the Database utilities will fail to work.

For example when you run sqlplus will return an error such as:

message file spl<lang>.msb not found

SP2-0750 need to set ORACLE_HOME to your ORACLE software directory 

Reference Source: sqlplus not initializing in linux (Oracle Forum)

How to Trace or Debug Database for issues / How to setup Tracing in database.

often during integration testing we encounter ORA Data base errors,  In this case we are debugging ORA- 02074 error.

here is how we can get more info about the tracing.

You need to set the command as below to you DB:

alter system set events '2074 trace name errorstack level 3';

If you would like to know more about the alter system set events command please

Oracle event trace types - by Burleson Consulting

When you set the above command and reproduce the issue, what ever that might be, look into the Alert_xxx.log file for statements such as below

Mon Apr 04 10:31:34 2011
Errors in file <Path>/trace/<InstanceName>_ora_20926.trc:
ORA-02074: cannot SET NLS in a distributed transaction
Mon Apr 04 10:31:36 2011
Errors in file <Path>/trace/<InstanceName>_ora_20950.trc:
ORA-02074: cannot SET NLS in a distributed transaction

This file gives the trace file name which contains the session dumps. In order to read the trace file, I believe you need extensive DBA skills. But do give it a shot.