The ORA-12154 error is fairly common.

There is a lot of reason why this could happen. Very often, it’s due to multiple oracle_homes on the server conflicting each others.

I will try to address the issues of multiple homes first and after that, explain a bit the use of service_name and SID in tnsnames.ora and how this affects the connectivity.

Configuration for a multiple home environment

The situation is as follow:

We have installed oracle 10g on a server and created a database on it. Everything connects fine. We can use the following commands:

C:\>set oracle_sid=SID
C:\>sqlplus / as sysdba

We can also use tns methods to connect to the database.

Then, we install an application using oracle forms and therefore install oracle forms.

Weird stuff starts happening.

When running an sqlplus command we can see that sqlplus version is 8.1.7. Same for tnsping.
Clearly, when typing the command, we are directed to the wrong oracle home. I would prefer my latest version 10.2.0.3.

This problem involves a wrong configuration of the PATH environment variable.
Go to control panel>system>advanced>environment variable.

Locate the PATH variable.

Normally, c:\[oracle_home]\bin should be at the very beginning of the PATH variable.
On my environment it’s c:\oracle\product\102\bin
However, what I get is c:\orant\bin;

The solution is simple:

Move the string c:\oracle\product\102\bin to be at the very beginning of the PATH environment variable.

Run sqlplus:

C:\>sqlplus
SQL*Plus: Release 10.2.0.3.0

Great. Now it’s using oracle 10g again.

However, when I try to connect to my database using tnsnames.ora regardless of the location (client or server), I get an ORA-12154 error.

The problem is that due to too many oracle forms, network information are conflicting.
There are:

c:\oracle\product\102\network\admin
c:\orant\network\admin
c:\orant\net80\network\admin

So, what we are going to do is create one to rule them all!

Create a new folder outside of the oracle binaries. We are going to make it independant of the binaries.
My folder looks like this:

c:\oracle\network\admin

Go back to the environment variable screen in control panel>system>advanced

Create a new environment variable called TNS_ADMIN:
in the ‘value’ field, add the new folder: c:\oracle\network\admin

Copy tnsnames.ora, sqlnet.ora and listener.ora from c:\oracle\product\102\network\admin
Paste into c:\oracle\network\admin

Stop/start listener

C:\>lsnrctl stop
C:\>lsnrctl start

From this point, you should be able to call your database using tnsnames.ora without conflict.

However, the path to a proper use is long.

My sqlnet.ora looks like this:

NAMES.DEFAULT_DOMAIN = world

sqlnet.authentication_services = (NTS)

SQLNET.EXPIRE_TIME = 0

SQLNET.ENCRYPTION_SERVER = requested

SQLNET.ENCRYPTION_CLIENT = requested

NAMES.DIRECTORY_PATH= (TNSNAMES)

The interesting part is the bold value:
NAMES.DEFAULT_DOMAIN

The second interesting part is in the tnsnames.ora:

MYDB.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NACS_SERVER)(PORT = 1910))
)
(CONNECT_DATA =
(SERVICE_NAME = MYDB.WORLD)
)
)

When the domain is specified in the sqlnet, and if you are using a service_name, then it is required to add the domain name following your DB name. A Service_name is different from the SID parameter.

When using a SID, you have to call the actual DB SID (MYDB) without the .world.
But service_name is not really a SID. It’s usually used when multiple applications run on the same DB to make them think they got their own database (where in fact they are sharing it).

When SERVICE_NAME is used, make sure to use .world following your db name.
Make sure to add .world to your tns name entry (the bold areas above).

Finally, when calling sqlplus make sure to use the following syntax:

sqlplus user/password@mydb.world

If the .world is not specified, the listener will consider that the tns entry does not exist thus generating an ORA-12154.

I hope this guide to connectivity with tnsnames.ora and its sometimes murky areas has been cleared with this post.

Powered by ScribeFire.

---

Possibly related posts: (automatically generated)

• The Oracle patching best practice
• Oracle Database 10g Utilities
• How to switch between SPFile and PFile
• Oracle BI EE 10.1.3.3/2 – Identifying Oracle Homes for OCI Drivers