ora-12154/tns-03505

Continuing the discussion of “why can’t I connect to my database?”, I’d like to focus on one particular error – ora-12154. For this discussion and demonstration we will use Oracle’s sqlplus command processor as our client, but the principles apply to any client program that uses sqlnet to connect to the database.

There are actually several mechanisms for connecting to an Oracle database, but by far the most common is via the Oracle Call Interface (OCI), passing the connect request via Oracle’s Transparent Network Substrate (TNS), commonly referred to as “sqlnet”.

Hardly a day goes by that someone doesn’t post a problem on OTN, reporting they are getting an ora-12154 trying to connect to their database. And invariably they are sent on a wild goose chase, checking everything that can go wrong anywhere along the network stack. And yet, ora-12154 means one thing, and one thing only: The client side of sqlnet could not find the specified connect identifier. Period. And the official description of the error, returned by the Oracle utility “oerr” spells it out:

[oracle@vmlnx01 ~]$ oerr ora 12154
12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause:  A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.

Or more succinctly: "A connection ... was requested using a connect identifier (which) could not be resolved into a connect descriptor." So what is the connect identifier? Let's take the classic, textbook example of a simple client connection request:

c:> sqlplus scott/tiger@orcl

By the rules Oracle uses to parse the command line, the "@" symbol is used to mark the beginning of the connect identifier. So in this example, the connect identifier is "orcl", and an ora-12154 means an entry for "orcl" could not be resolved to a "connect descriptor" - the IP address, port number, and service name necessary to properly route the request across the network to the Oracle listener. There are several methods available to make this name resolution, but by far the most common is the use of "local naming"  - the tnsnames.ora file. This file serves no other purpose, so you should have it fixed firmly in your mind that it is used only by the client process. The only reason this file exists on the database server is because the server can also run client processes.

With this preliminary information out of the way, let's dig in and see how many ways we can create (and conversely, fix) an ora-12154. Let's first look at a good configuration, then we'll start taking it apart. My database is running on an Oracle Enterprise Linux server, while my client is running on my laptop, under Windows XP-Pro. My tnsnames.ora looks like this:

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\net
work\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
mytestdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = vlnxora1)
    )
  )

Make a connection to the database, just to show that everything is in order.

C:\>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:14:38 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Right out of the textbook! So what could go wrong, to generate an ora-12154?

Wrong naming method

The first thing sqlnet has to do is determine what naming method is being used - exactly how it is to resolve the connect identifier to an IP address, port, and service name. To do this, it looks in the file 'sqlnet.ora' (on the client machine, of course!) for the parameter NAMES.DIRECTORY_PATH. If we intend to use local naming, we must set this parameter appropriately:

NAMES.DIRECTORY_PATH= (TNSNAMES)

(Note: the string 'TNSNAMES' as a value for this parameter is not the name of the file.  It is the name of the method to be used for name resolution.  The name of the file used for this method is always tnsnames.ora.)

Let's set it to some other value (albeit a valid one) . . .

C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\netwo
rk\admin\sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (LDAP)

. . . and observe the behavior

C:\>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:27:46 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ora-12154: TNS:could not resolve the connect identifier specified

In this example, we told sqlnet to use LDAP naming services to resolve the connect identifier, but LDAP is not available in my test environment, thus it was not able to resolve "mytestdb".  Lesson: If you want to use local naming, you must specify such in the SQLNET.AUTHENTICATION_SERVICES parameter in the client's sqlnet.ora file. Let's fix the problem, then move on:

C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\netwo
rk\admin\sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)

C:\>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:52:33 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Can't locate tnsames.ora

The entire subject of locating tnsnames.ora was covered in "Help! I can't connect to my database (part duex)". For now, I will create the simplest means of not being able to locate the file - I'll rename it to something else . . .

C:\>cd C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>ren tnsnames.ora tnsnames.sav

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>dir tnsnames.ora
 Volume in drive C has no label.
 Volume Serial Number is 04C2-AD70

 Directory of C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

File Not Found

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>

. . . and observe the result:

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:57:14 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ora-12154: TNS:could not resolve the connect identifier specified

Of course, we fix it by providing a (valid) tnsnames.ora file:

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>ren tnsnames.sav tnsnames.ora

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:58:37 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Can't find connect descriptor in tnsnames.ora

We've told sqlnet to use tnsnames, and we've made sure sqlnet can find tnsnames.ora. But what if the connect identifier we provide cannot be found in the tnsnames.ora? And more importantly, what can cause it to not be found?

Of course, the simplest reason would be that we just gave it the wrong string. In our example file (shown above), we had a single entry, with the connect identifier of 'mytestdb'. If we provide a different connect identifier it will, of course fail - again with ora-12154:

C:\>sqlplus scott/tiger@fubar

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:05:48 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ora-12154: TNS:could not resolve the connect identifier specified

But there is another factor that comes into play at this point. There is an optional parameter that can be set in sqlnet.ora that we have not yet discussed. Before looking in tnsnames.ora, sqlnet will check to see if the parameter NAMES.DEFAULT_DOMAIN has been set. If it has, it's value will be appended to the connect descriptor supplied by the user - before searching tnsames.ora for the result. Let's add that parameter to our existing sqlnet.ora . . .

C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\netwo
rk\admin\sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)
NAMES.DEFAULT_DOMAIN=acme.com

. . . and observe the result

C:\>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:13:42 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ora-12154: TNS:could not resolve the connect identifier specified

There are two ways to fix this error. Obviously, we can revert back to our previous configuration by removing the NAMES.DEFAULT_DOMAIN parameter from sqlnet.ora. The other would be to add the domain name to the entry in tnsnames.ora.

C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\net
work\admin\tnsnames.ora
#===========================
mytestdb.acme.com =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = vlnxora1)
    )
  )

C:\>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:30:57 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

tns-03505

That pretty well covers every means of creating an ora-12154 when using local naming. What about the tns-03505 error? Actually, tns-3505 is exactly the same error as ora-12154, with all the same causes and resolutions. The only difference I've been able to discover is that tns-03505 is produced when using the 'tnsping' utility. Why Oracle chose to code tnsping this way I don't know, and I've been unable to find definitive documentation on this difference but my own testing bears it out.

Using sqlplus . . .

C:\>sqlplus scott/tiger@fubar

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:44:33 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ora-12154: TNS:could not resolve the connect identifier specified

Enter user-name:

And using tnsping . . .

C:\>tnsping fubar

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 26-FEB-2
011 16:44:42

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

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

tns-03505: Failed to resolve name

C:\>

(Thanks to Sybrand Bakker for pointing out this distinction).

I will discuss 'tnsping', it's use, what it does and does not do, in another post.

This'n'that

There is one often overlooked 'gotcha' in all this. Remember that when sqlnet is parsing out the connect string, it uses the '@' sign as a meta-character to mark the beginning of the connect descriptor. So if we have this:

c:>sqlplus scott/tiger@orcl

everything is just fine. But suppose there is an '@' sign someplace unexpected. What happens if someone constructs a complex password that includes it?

c:> sqlplus scott/P@ssW0rd@orcl

In this case, sqlnet will take the 'P' as the password, and take 'ssW0rd@orcl' as the connect descriptor.  By now, you can guess the result.

Conclusion

In this post I have attempted to demonstrate every possible cause of an ora-12154 error, as relates to use of tnsnames.ora, for address resolution of a connect descriptor. There is one overriding factor here that should be obvious from the very description of the error, regardless of the naming method used: ora-12154 is a client side problem. It has absolutely nothing to do with anything on the server. It has nothing to do with the listener; it has nothing to do with the database. If you are faced with a ora-12154, you can monkey around with your database and/or listener "until the cows come home" and you will not solve your problem.

Stay tuned for more discussion of sqlnet connection  problems.

About these ads

28 thoughts on “ora-12154/tns-03505

  1. Great post. You’ve given a great understanding of the error rather than the myriad of possible solutions found over the net.
    I ran into most of those issues, and one more – having two Oracle clients installed on one machine, which somehow messed up (and didn’t report) which tnsnames.ora file was used.

  2. Seth – Glad it helped. I’m a bit puzzled by your comment that you were unable to report which tnsames.ora was being used. This should have been shown with tnsping. Of course, that would only reflect the environment of the command line processor where you executed tnsping – not necessarily the environment of some app.

    Also, the various options were discussed in “Help! I can’t connect to my database (part duex)”. Did you check that out?

  3. Thank you for wonderfull article, but i would like add some more reference.

    Do not make the password contain oracle reserve word for example G@ssPr4t.

    Oracle will check this @ character as reserve word which defined as connection string.

    After i change the password, then i can login successfully. :-)

    • Yep. Exactly what I pointed out under “This’n’That”

      Just to ‘pick a nit’, I wouldn’t consider that a ‘reserved word’. Simply a character that Oracle uses as a delimiting tag.

    • I haven’t tested specifically for that, but I’ve heard lots of reports of Oracle not playing well with Windows directory names with embedded spaces. I’ve seen reports of that in dealing with installations try to place ORACLE_HOME in a Windows directory with spaces. So I’d guess it has more to do with the spaces than the (x86). Maybe I’ll do some testing with that in the next couple of days.

  4. One more note:
    if you are using TNS_ADMIN with GI installation (RAC or Oracle Restart environment)
    don’t foget to set srvctl setenv db -d YouDB -t TNS_ADMIN=path_to_tnsnames.ora_folder
    for all DBs that start via srvctl start db.
    to check: srvctl getenv db -d YouDB

  5. when i try to connect using:
    $ORACLE_SID=mydb
    $sqlplus sys/sys as sysdba@mydb
    It connect successfully.
    But when i use:
    $sqlplus sys/sys@mydb
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor.
    Listener is runnig, but says ‘not processing any service’
    I have checked sqlnet.ora and tnsname.ora, both files are perfect.
    Please help

    • Offhand, I can’t think of anything. Something like this would be better discussed on the OTN forum (https://forums.oracle.com/forums/forum.jspa?forumID=61). I’d suggest you post your question there. I’m pretty active there, and you can get participation from several other people as well. Never hurts to have several sets of eyes looking at a problem. When you post there, please be sure to state your version of Oracle, to 4 decimals, your OS, and copy and paste an entire command line session demonstrating the problem. Much better to show actual inputs and responses as opposed to your own description/interpretation of them.

  6. Pingback: Error while connect database by usnig EXP.exe utility - dBforums

  7. I’ve gone through from above post and did suggested changes but still I’m unable to resolve this error,

    Here is my lsnrctl service output:

    C:\Windows\system32>lsnrctl service

    LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 – Production on 01-JUN-2013 13:43:25

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    Services Summary…
    Service “CLRExtProc” has 1 instance(s).
    Instance “CLRExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:0 refused:0
    LOCAL SERVER
    Service “PLSExtProc” has 1 instance(s).
    Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
    “DEDICATED” established:0 refused:0
    LOCAL SERVER
    The command completed successfully

    listener.ora
    —————–

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\atg\oraclexe\app\oracle\product\11.2.0\server)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\atg\oraclexe\app\oracle\product\11.2.0\server)
    (PROGRAM = extproc)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = Srinivas)(PORT = 1521))
    )
    )

    DEFAULT_SERVICE_LISTENER = (XE)

    tnsnames.ora:
    ——————

    XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Srinivas)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
    )
    )

    What might be the problem?..

    • It’s impossible to say without seeing your connection string. Whatever you specify after the “@” in your connection string should be in your tnsnames.ora file. Your tnsnames has the following entries:

      XE
      EXTPROC_CONNECTION_DATA
      ORACLR_CONNECTION_DATA

      I suspect the XE is what you want to connect to, so you should be connecting (with sqplus) with the following syntax:

      sqlplus scott/tiger@XE

  8. Hello,

    Back in November we started deploying W7 x64 PC’s in to our production environment. This required installing Oracle Instant Client 11.2.0.3. Our TNSNAMES.ORA file is on a Novell mapped drive (supposedly for ease of keeping the file up to date). TNS_ADMIN user variable is pointing to the mapped drive (Q:\oradev6\net80\admin). Reports started coming in the past couple of weeks that ODBC connectivity was failing on client PC’s. We double checked all environment variables

    Variable name: ORACLE_HOME
    Variable value: c:\oracleic64\instantclient_11_2

    Variable name: TNS_ADMIN
    Variable value: Q:\oradev6\net80\admin

    APPEND to the existing path statement:
    c:\oracleic64\instantclient_11_2;c:\oracleic32\instantclient_11_2

    The novell drive was mapped and the local Windows user is a member of administrators.

    Going into System DSN tab of the ODBC Data Source Administrator we click on one of the data sources and select test connection, enter credentials and get, “Unable to connect. SQLState=08004. ORA-12154: TNS:could not resolve the connect identifier specified.” (this use to work)

    Running a pcap in the background shows that the client PC is trying to us DNS and NBNS to resolve the TNS Service Name. Also, another clue that the TNSNAMES.ORA file is not being referenced is that when a connection is being created none of the Service Names are listed in the drop down list. (this use to work).

    With all that said- logging in to windows as Administrator (and creating the user variables defined above) everything works. (Yes the local user is a member of Administrators). We are not in a domain.

    The problem crept up when a new “version” of our desktop image was updated. There are a lot of chefs in this kitchen so it is hard to ascertain what changes were made, but I took the older image (where everything works) and patched the OS to the latest version, but couldn’t get it to break. On the newer image I removed the Novell client and mapped Q: drive using a microsoft share that used the same directory structure, same problems (fails for user, works for administrator). We have even removed our antivirus program and the problem still persists.

    Drilling down further we tried process monitor on the working and failing configs.

    On the working config;

    Process Name PID Operation Path Result Detail
    odbcad32.exe 3416 CreateFile \\ADRVNFS1\BANNER\oradev6\NET80\ADMIN\tnsnames.ora SUCCESS Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a, OpenResult: Opened
    odbcad32.exe 3416 QueryDeviceInformationVolume \\ADRVNFS1\BANNER\oradev6\NET80\ADMIN\tnsnames.ora SUCCESS DeviceType: Disk, Characteristics: Mounted, Remote
    odbcad32.exe 3416 ReadFile \\ADRVNFS1\BANNER\oradev6\NET80\ADMIN\tnsnames.ora SUCCESS Offset: 0, Length: 4,096, Priority: Normal
    odbcad32.exe 3416 ReadFile \\ADRVNFS1\BANNER\oradev6\NET80\ADMIN\tnsnames.ora SUCCESS Offset: 4,096, Length: 2,955, Priority: Normal
    odbcad32.exe 3416 ReadFile \\ADRVNFS1\BANNER\oradev6\NET80\ADMIN\tnsnames.ora SUCCESS Offset: 7,051, Length: 4,096, Priority: Normal
    odbcad32.exe 3416 CloseFile \\ADRVNFS1\BANNER\oradev6\NET80\ADMIN\tnsnames.ora SUCCESS

    On the failed config;

    Process Name PID Operation Path Result Detail
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2 SUCCESS Desired Access: None, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened
    odbcad32.exe 1036 QueryDirectory C:\oracleic64\instantclient_11_2\oracle.key NO SUCH FILE Filter: oracle.key
    odbcad32.exe 1036 CloseFile C:\oracleic64\instantclient_11_2 SUCCESS
    odbcad32.exe 1036 CreateFile C:\oracleic64 SUCCESS Desired Access: None, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened
    odbcad32.exe 1036 QueryDirectory C:\oracleic64\instantclient_11_2 BUFFER OVERFLOW Filter: instantclient_11_2, 1: inst̻
    odbcad32.exe 1036 CloseFile C:\oracleic64 SUCCESS
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2\oracle.key NAME NOT FOUND Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2\bin NAME NOT FOUND Desired Access: None, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2 SUCCESS Desired Access: None, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened
    odbcad32.exe 1036 QueryDirectory C:\oracleic64\instantclient_11_2\bin NO SUCH FILE Filter: bin
    odbcad32.exe 1036 CloseFile C:\oracleic64\instantclient_11_2 SUCCESS
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2\bin\oracle.key PATH NOT FOUND Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2\network\admin PATH NOT FOUND Desired Access: None, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2\network NAME NOT FOUND Desired Access: None, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2\network\admin\tnsnames.ora PATH NOT FOUND Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2\net80\admin PATH NOT FOUND Desired Access: None, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2\net80 NAME NOT FOUND Desired Access: None, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a
    odbcad32.exe 1036 CreateFile C:\oracleic64\instantclient_11_2\net80\admin\tnsnames.ora PATH NOT FOUND Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a
    odbcad32.exe 1036 CreateFile C:\Windows\System32 SUCCESS Desired Access: None, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened
    odbcad32.exe 1036 QueryDirectory C:\Windows\System32\tnsnames.ora NO SUCH FILE Filter: tnsnames.ora
    odbcad32.exe 1036 CloseFile C:\Windows\System32 SUCCESS
    odbcad32.exe 1036 CreateFile C:\Windows SUCCESS Desired Access: None, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened
    odbcad32.exe 1036 QueryDirectory C:\Windows\system32 BUFFER OVERFLOW Filter: system32, 1: Syst̻
    odbcad32.exe 1036 CloseFile C:\Windows SUCCESS
    odbcad32.exe 1036 CreateFile C:\Windows\System32\tnsnames.ora NAME NOT FOUND Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: n/a

    To reiterate, this works when user is logged into novell and windows as user on an older “version” of our W7x64 image.
    User is member of administrators.

    This stopped working on our newer “version” of our W7 x64 image which I am told has OS and driver updates. But does work when logged in as administrator.

    When it doesn’t work, process monitor shows attempts to locate TNSNAMES.ORA in the C:\oracleic64\instantclient_11_2\network\admin\ instead of referencing the Q: drive as defined by the user variable TNS_ADMIN = Q:\oradev6\net80\admin. Pcaps shows attempts to resolve service names using DNS and NBNS.

    Help?

    Thank you!

    • I’ve been watching your posting of this issue on OTN and I’m afraid I don’t have anything else to add at this time. It looks like Mark Powell’s response there is getting close to the issue.

  9. Yes, I’m using XE as my connection string but still I’m unable to connect or start database

    Here is my output:

    C:\Windows\system32>sqlplus scott/tiger@XE

    SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 3 21:24:21 2013

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

    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor

    ————————————————–
    Meanwhile I can see in logs these messages appending:

    03-JUN-2013 21:25:32 * service_register * xe * 0

    03-JUN-2013 21:25:33 * service_update * xe * 0

    03-JUN-2013 21:25:39 * service_died * xe * 12537

    ———————————————————————

    When I’m trying to ping my tnsping XE, everything looks perfect

    C:\Windows\system32>tnsping XE

    TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 – Production on 03-JUN-2013 21:23:22

    Copyright (c) 1997, 2010, Oracle. All rights reserved.

    Used parameter files:
    C:\atgtraining\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Srinivas)(PORT = 1521)) (CONNECT_DATA = (SERVER
    OK (0 msec)

    • I see you are running 32-bit Oracle. Is this on a 32-bit or 64-bit Windows? When I googled “service_died tns-12537″ I saw some discussions about something like that, and I do know that Oracle doesn’t certify their 32-bit windows products on 64-bit Windows. Given that you can’t even *buy* 32-bit hardware any more I’d be really surprised if your OS is 32-bit.

      • Hey Stevens thank you so much for taking out sometime trying to resolve this issue !!
        Not sure but everything was working fine later suddenly it got stopped.. Yes, I m using 64 bit Windows.. When I’m trying to trace out I didn’t find any clue where exactly it causing the problem.. It’s seems like I’ve only option to re install.

  10. Thanks for an excellent article.

    I had a strange problem this week on a new oracle installation on a linux server. I found that sqlplus user/pswd@mydatabase worked fine from the oracle login account, but when I created a new unix account for a user they could not get sqlplus to work. They were seeing ORA-12154 (of course)

    After working through your notes I spotted that the unix file permissions on tnsnames.ora were “-rw-r—–” rather than “-rw-r–r–” Adding the world read permission to tnsnames.ora fixed the problem. Simple when I finally spotted it!

  11. Hi Ed,
    First thanks for your comprehensive explanation. Very insightfull.

    I only beg to differ on the ‘ora-12154 always being a clientside issue’.

    I had a problem in my dataguard config. It worked fine except when i issued an switchover, the broker couldnt start the standby after it was being shutdown.
    I got an ora-12154.
    I’ve found my error in the listener.ora.
    For Dataguard you have to add a service _DGMGRL.
    I made a mistake with copy/pasting and i had added this entry in the listener.ora:
    (SID_DESC =
    (GLOBAL_NAME = CANONST_DGMGRL.gemalm.intern)
    (ORACLE_HOME = c:\oracle11g\product\11.2.0\dbst)
    (SID_NAME = canonst)
    )
    The entry should be:
    (SID_DESC =
    (GLOBAL_DBNAME = CANONST_DGMGRL.gemalm.intern)
    (ORACLE_HOME = c:\oracle11g\product\11.2.0\dbst)
    (SID_NAME = canonst)
    )
    Notice the difference GLOBAL_NAME vs GLOBAL_DBNAME
    The listener starts normally with both entries. So no issues there.
    Except when you look at the status of the listener, with the first entry there is no CANONST_DGMGRL service.
    When you look at the dataguard broker property: StaticConnectIdentifier you see:
    StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.100.5)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=canon_DGMGRL.gemalm.intern)(INSTANCE_NAME=canon)(SERVER=DEDICATED)))’

    This connectidentifier is automatically created during the create configuration part of the dataguard-config. (Dont know for sure if you can edit it?)
    But anyway, the solution was editing the listener.ora to resolve this ora-12154 error.

    Theoretically you can say this is also an clientside issue, Because the client (=dataguardbroker) had the ‘wrong’ name for the service and i just modified the listener to to comply with the client.

    But the point is: a missing service in the listener.ora also results in a ora-12154 and that should be addressed in the listener on the server.

    With kind regards,
    Ojee

    • Ojee,
      I’ll have to think on that one a while. I don’t have a lot of experience with DG. I put up one configuration a few years ago, and haven’t touched it since. Perhaps someone else with more insight can comment. Are you sure you aren’t getting a bit dyslexic, and confusing ora-12154 with ora-12514? I’ve seen that mistake a lot on OTN. ;-)

      • Haha, You are right. I’m dyslexic. It’s a ora-12514. LoL. Sorry to bother you.
        But at least I’ve learned something.
        Cya.
        Ojee

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s