This error is essentially telling you that you have messed up your database naming configuration, whether it be tnsnames, oracle names or OID. The fix is normally quite simple - just correct the naming.
This is usually quite easily done with netmgr, a tool that is fairly good at its job. The syntax for setting up a tnsname is a little convoluted, and I've fallen back on netmgr a number of times when I can't seem to get it just right by typing the entries in the file.
There's at least one other way to cause ORA-12154 to pop up and consume more time than you may like to admit. I won't tell how long I played with this...
The cause is actually due to security policy. While the characters !@#$%^&*()_-=+~` are not normally allowed in Oracle passwords, it is actually quite simple to include them. Simply enclose the password in double quotes.
alter user scott identified by "th!sat^st";
This was done on a number of accounts on our databases, all in the name of security.
These types of passwords have been used without issue for some time now.
Today was a notable exception.
After spending some time fiddling with tnsnames.ora files, I realized what the problem actually was.
Suppose you were to have a password with an '@' in it? Yep, that was the problem.
First let's see what it looks like from within sqlplus:
15:41:52 SQL> alter user scott identified by "what@mistake";
User altered.
15:42:03 SQL> connect scott/"what@mistake";
Connected.
scott SQL> select user from dual;
USER
------------------------------
SCOTT
SQL> connect scott/what@mistake
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
As long as the connect string was in double quotes, no problem.
Now let's try it from a linux command line:
Linux> sqlplus scott/'what@mistake'
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:42:20 2008
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Linux> sqlplus scott/'what\@mistake'
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:46:14 2008
ERROR:
ORA-01017: invalid username/password; logon denied
Linux> sqlplus scott/"what\@mistake"
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:46:21 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Linux> sqlplus 'scott/what\@mistake'
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:47:23 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Linux > sqlplus scott/what\@mistake
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:48:52 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
There doesn't seem to be any method to use a password with the @ character in it, at least not from sqlplus. The same password works fine when used on the command line with perl script that logs on to the same database and account:
Linux> DBI_template.pl -database dv11 -username scott -password 'what@mistake'
X
The 'X' is the correct output as this script simply selects * from dual.
Lesson learned, do not ever, under any circumstances, use the @ character in the password for an Oracle database account.
You might think that goes without saying, but it's actually pretty easy to get a password with '@' in it when the passwords are generated random characters.
FWIW, this problem was manifested in Windows as well
