I have SQL*Plus 12.1 installed on Fedora 19 trying to connect to an Oracle 11g database. I installed the instantclient RPM packages (basic, devel, sqlplus) from here. I can successfully connect to other Oracle databases using SQL*Plus, so I know I have a working installation of the software. However, when I try to connect to this particular database, I get this error:
ERROR:
ORA-01017: invalid username/password; logon denied
Here's my tnsnames.ora file (with the host and port obfuscated out):
PSPRODDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = #HOST-ADDR)(PORT = #PORT-NUM))
)
(CONNECT_DATA =
(SERVICE_NAME = PSPRODDB)
)
)
My TNS_ADMIN environment variable is set to the path of my tnsnames.ora file.
The command I'm running to connect:
sqlplus username/password@PSPRODDB
After pressing enter, it hangs on the version and copyright info for about 2-3 seconds before giving me the ORA-01017 error.
I know I have the username and password correctly typed in because I copied and pasted it from another application that successfully connects to the database.
Edit
I've looked at the log.xml file (in C:\oracle\product\11.2.0\diag\tnslsnr\test\listener\alert\log.xml), and found that there a few entries that show I'm talking to the correct listener. Here's an example of the log entry, but obfuscated for possibly sensitive info:
<msg time='2013-11-25T09:54:08.530-07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='PSTEST100-50'
host_addr='*my address*'>
<txt>25-NOV-2013 09:54:08 * (CONNECT_DATA=(SERVICE_NAME=PSPRODDB)(CID=(PROGRAM=sqlplus)(HOST=*localhost*)(USER=njones))) * (ADDRESS=(PROTOCOL=tcp)(HOST=*addr*)(PORT=38906)) * establish * PSPRODDB * 0
</txt>
</msg>
I've also since tried changing the *SERVICE_NAME* element in my tnsnames.ora file to SID, with no difference.
Surrounding my password with quotes didn't fix the problem either.
Could there be a version problem? I'm using instantclient and sqlplus version 12.1, but the database is version 11.2.
Edit 2
Well, it's official. I'm an idiot, and that's what caused the error. I was typing in the wrong password, and I guess whatever I was copy-pasting was wrong too.
Couple ideas, in order of likelihood of causing this problem:
1) If your password starts with a non-alphabetic character, surround password with quotes: user/"password"@service (note, GUI apps e.g. TOAD and SQLDeveloper don't require quotes).
2) Run
> tnsping service
And confirm your output matches the tnsnames.ora entry you think you are using
3) On server, run (or ask dba to run)
> lsnrctl status
Confirm that the service listed in your tnsnames.ora is being directed to the proper database.
EDIT: Saw Nathan's question, thought, "hmm - weird, I use tnsping all the time to validate client installs, why the heck wouldn't it be included in instantclients???" Asked Google, and lo-and-behold, it turns out TNSPING is pretty much useless. The ONLY think it checks is that host is reachable and that a tnslistener is running on the specified port (which you could easily check with telnet). H/T to "BillyVerreynne" on Oracle forums: https://forums.oracle.com/message/10561771
Yay, I learned something today! :-) On that note, I'll personally be switching to SQLPlus for deep-dive checking of TNS specifications, and recommend everyone reading this do the same. As Nathan already posted above, issues with SQLPlus connection attempts can be reviewed in $ORACLE_BASE/diag/tnslsnr/test/listener/alert/log.xml.