Posted by Just a normal start to the day today…I had my coffee
and then started working on some AskTOM questions. Naturally pretty much the first thing I needed to do is connect to my database, and then this happened:
C:\oracle\product\18\bin>sqlplus scott/tiger@//gtx:1518/pdb1
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 6 09:23:09 2019
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
I made the standard assumptions
- I had set ORACLE_HOME wrong….checked that, nope!
- I had my TNS_ADMIN set wrong….nope!
- Listener not configured corrrectly….nope!
- Database not started or not registered with listener….nope!
- Had my good friends at Microsoft restarted the PC with Windows Update without telling me?…nope!
so once I’d ticked off the normal culprits, I then tried with other software installations on the same machine
C:\oracle\instantclient>sqlplus scott/tiger@//gtx:1518/pdb1
SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 6 09:21:44 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Wed Mar 06 2019 09:13:31 +08:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
C:\oracle\product\xe\18\dbhomeXE\bin>sqlplus scott/tiger@//gtx:1518/pdb1
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 6 09:22:38 2019
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Wed Mar 06 2019 09:21:44 +08:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0
and voila! Obviously everything is OK from the listener and database perspective.
So back to the original installation, and I had the light bulb moment. I had been tinkering with sqlnet.ora to help out with a different question yesterday, and I had isolated the connection options down to only TNSNAMES
NAMES.DIRECTORY_PATH= (TNSNAMES)
In such a scenario, you need to explicitly allow for EZCONNECT method to allow the connection to work.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
and then everything was fine back in the original installation directory.
C:\oracle\product\18\bin>sqlplus scott/tiger@//gtx:1518/pdb1
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 6 09:50:52 2019
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Wed Mar 06 2019 09:45:06 +08:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0
If you have never touched your sqlnet.ora, it probably looks like this:
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
but there is no need to panic. Those default values allow EZCONNECT as well.
Learning is not a spectator sport 
Connor, I think you had a “light bulb” moment, though it’s still a lot of light.
Auto-generated correct eh? 😁
hee hee …. How do you know it wasn’t a reference to my gym workload :-).
Thanks, will correct.
I’m glad I’m not the only one with these kinds of moments.