ORA-12154 tns: could not resolve service name

Toad for Oracle
1

I have seen plenty of these threads all over the place but none of the proposed solutions has worked for my issue. We are moving to Oracle Apps R12 and part of the move involves renaming the current database. Our tnsnames entry is below which has always worked fine for our old version 8 TOAD install.

DERP, DERP.world =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL = tcp)
(HOST=derpdb)
(PORT=1543)
)
)
(CONNECT_DATA=(SID=DERP))
)

The database was renamed to ODERP so we modified our tnsnames file accordingly:

ODERP, ODERP.world =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL = tcp)
(HOST=oderpdb)
(PORT=1543)
)
)
(CONNECT_DATA=(SID=ODERP))
)

I am now able to connect to ODERP with sqlplus and sqldeveloper but Toad produces the error above. I have attempted the solution that many suggested of a TNS_ADMIN environment variable but that also fails.

2

try changing SID to SERVICE_NAME in your tns entry.

3

Thanks for your reply. Unfortunately it had no change, I receive the same error when attempting to connect to ODERP database.

4

Hi

  1. Which version of toad you have?

  2. try this:
    ODERP, ODERP.world =
    (DESCRIPTION=
    (ADDRESS_LIST =
    (ADDRESS=
    (PROTOCOL = tcp)
    (HOST=oderpdb)
    (PORT=1543)
    )
    )
    (CONNECT_DATA=(SID=DERP))
    )
    Same sid as before … this should work!

From command prompt, where your sqlplus is working please get us result:
“set”
command (whole output)

show how do you connect to your database in sqlplus (exactly).

  1. Then from the same command prompt run toad … i .e
    “C:\Program Files\Quest Software\Toad for Oracle xx\Toad.exe”"

And then try to connect.

There are many issues, but let us start with this answers …
Damir

5

Thanks for the posts, with your help I was able to track it down to a problem with the administrative account being used to run TOAD(don’t ask, corporate policy). Still strange that I could see the database name in the dropdown, so it must have been reading the tnsnames file, but still unable to connect with that user. Start toad with my standard user name and it works fine.

6

Still strange that I could see the database name in the dropdown, so it must have been reading the tnsnames file,

Correct. tnsnames entry is somehow present, but to be able to connect to that database is not sure ... listener may be up but database (SID or SERVICE_NAME) may be different or database may be down (or not opened).

Would be nice if you place correct answer if that helped you ..

.-)

7

Now one thing come to mine mind … about “spooky” TNS entries.

If your tnsnames.ora contain line like:

IFILE = …

then this file is also read as valid tnsnames entry … and this file can be placed on some server

IFILE = \server\tns_2.ora

and if is in the end, IT OVERRIDE any previous tnsnames value entry with SAME NAME.

:slight_smile: