BEQ connection Error ->>> ORA-12162: TNS:net service name is incorrectly specified

BEQ connection Error ->>> ORA-12162: TNS:net service name is incorrectly specified

Prognosis: Only occurs for oracle user

[SID@cdrserver ~]$ oerr ORA 12162
12162, 00000, "TNS:net service name is incorrectly specified"
// *Cause: The connect descriptor corresponding to the net service name in
// TNSNAMES.ORA or in the directory server (Oracle Internet Directory) is
// incorrectly specified.
// *Action: If using local naming make sure there are no syntax errors in
// the corresponding connect descriptor in the TNSNAMES.ORA file. If using
// directory naming check the information provided through the administration
// used for directory naming.
[SID@cdrserver ~]$

problem only accurs when connecting to oracle directly; i.e.

sqlplus username/password [results in ORA-12162 ERROR]

[oracle@cdrserver trace]$ sqlplus username/*********

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 4 09:50:06 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

sqlplus username/password@servicename [successful]

[oracle@cdrserver trace]$ sqlplus username/*********@SID

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 4 09:56:22 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


So I went into MOS and I must say, I feel sheepish!!

Solution [MOS Note: 312348.1]:

The user environment variable $ORACLE_SID is not set while running a bequeath (local) connection.
When this env var is omitted, then using SQL*Plus without the @SID connect string, may result in an ORA-12162 error.

Make sure to add the $ORACLE_SID environment variable to the .profile for the Oracle user.
This will give the BEQ adapter a route to connect to the instance.

ORACLE_SID environment variable

[oracle@cdrserver trace]$ echo $ORACLE_SID

[oracle@cdrserver trace]$

Oops! MOS was right after all.

Environment variable

[oracle@cdrserver ~]$ cat .bashrc
# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi

# User specific aliases and functions
export PATH=$PATH:/oradata01/app/oracle/product/11.1.0/db_1/bin:/usr/x11R6/bin
export ORACLE_SID= SID <-- Take note of space
export ORAENV_ASK=NO
. oraenv

[oracle@cdrserver ~]$

You will not believe how long it took me to notice that the [export ORACLE_SID= SID] statement had a space between the variable name and the value

[oracle@cdrserver ~]$ sqlplus username/**********

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 4 10:01:24 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>