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:\>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.
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.
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:
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
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
The interesting part is the bold value:
The second interesting part is in the tnsnames.ora:
(ADDRESS = (PROTOCOL = TCP)(HOST = NACS_SERVER)(PORT = 1910))
(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:
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.