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
9 comments:
Jared, i don't have 10.2.0.3 to test with, but sqlplus scott/\"@ora102\"@ora102 works for me on both linux and windows with 10.2.0.4 client.
Best regards
Maxim
I hadn't tried that particular syntax. Even if you can make it work, I think leaving the @ out of passwords is a good idea. One less thing to cause confusion.
Well, then equally bad thing should be the character "/" as part of password, because it has special meaning for sqlplus as well. Maybe, the list can be continued. In my opinion, however, it is easier to memorize, that the command arguments are first evaluated by the shell ( don't matter, command.com or bash or whatever) and then are passed to particular program. To preserve the double quotes, they should be escaped - very simply, isn't it? But from the other side, it can become very fast an obscure syntax ( consider, in sqlplus the alter user scott identified by "'$a'"; will lead to the bash expression like sqlplus scott/\"\'\$a\'\" - so, maybe it is indeed not so bad idea to stay with letters and numbers.
Best regards
Maxim
I prefer lengthening the passwords over using characters that may be interpreted by the shell as having special meaning.
Another area where this is more of a problem is when working with large systems such as SAP and Oracle eBiz Apps.
If there are special characters in the passwords it is often necessary to simplify the password during maintenance, else the app just won't handle it properly.
After having to deal with that a few times I just made the passwords longer.
I fully agree, it makes sense and not only from the maintainance point of view. The number of possible passwords equals to number of used characters power password length. So, obviously, increasing the password length increases complexity stronger than expansion of character set used (like 26^^10 is bigger than 27^^9). In practice probably alphanumeric passwords of length 10 and more can be considered as secure enough...
Best regards
Maxim
For us on various versions of the database from 9i to 10g the user/schema with a password with @ in it was not able to log on to SQLPlus and was only able to logon through TOAD. Needless to say, after a bit of reasearch with the tns, etc, we changed the password so I could troubleshoot the problem, and wound up solving the problem at the same time. Needless to say, this user/schema is happy to change password, leaving out the @ It is not the first thing you ask during a connection problem, "what is your password?" But wish we had.
sqlplus scott/\"@ora102\"@ora102
Thank you!
Excellent post.
I tried Maxims one and worked!!
Thanks.
Thanks for posting this! I'm not usually an Oracle user, but I have one system that I'm required to connect to every once in a blue moon. I passed this article up to the admins so that they can reset my password or at least unlock my account.
Post a Comment