Marco Pachaly-Mischke 7:45 am on September 28, 2023
Tags: , SQL*Net   

Connecting to an Oracle Database – what could possible go wrong

This is gonna be a very short blog post, isn’t it? Connecting to an Oracle Database is straight forward. Add and alias to your tnsnames.ora file with the propper connection description, save it, start SQL*Plus (or SQLcl or whatever) and type

SQL> connect myuser/mysecret@mytnsalias

And here we go, we have a connection. Or even simpler, use the EZConnect syntax for creating a connection which makes the tnsnames.ora obsolete.

SQL> connect myuser/mysecret@db-hostname:1521/db-servicename

But you guess it, I wouldn’t be writing this if it is always as easy as this. Errors during the process of establishing a connection to an Oracle Database are propably the most common issues that I need to troubleshoot and solve.

For this post, I asume that a tnsnames.ora file is being used rather than EZConnect or a central LDAP directory. I’ll walk through the connection process step by step by using “SQL*Plus” and “tnsping”.

TNS-03505: Failed to resolve name / ORA-12154: TNS:could not resolve the connect identifier specified

[opc@cmp-openvpn ~]$ sqlplus system/********@db23c

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 06:27:09 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

[oracle@db23ctest ~]$ tnsping db23c

TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 28-SEP-2023 06:29:45

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/23.0.0.0/dbhome_1/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

These two error messages are basically the same. It means, that we were unable to find the given alias in the tnsnames.ora file. Sounds like a simple thing, but may have different root causes:

  • The alias is not in the TNSNAMES.ORA file
  • A wrong TNSNAMES.ORA file was being used, the path for the SQLNET.ORA file might indicate the location. I wrote about the search order in another blog post.
  • The parameter NAMES.DEFAULT_DOMAIN is specified in SQLNET.ORA. The value of this parameter is automatically added to the connection alias before searching the TNSNAMES.ORA in case it contains no dots.

TNS-12545 / ORA-12545: Connect failed because target host or object does not exist

[opc@cmp-openvpn ~]$ sqlplus system/********@db23ctest

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 06:43:28 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

This error means, that the given hostname in the connect description could not be resolved by DNS. So we need to verify the hostname using “nslookup” or “ping” for instance.

TNS-12535: TNS:operation timed out / ORA-12170: TNS:Connect timeout occurred

[opc@cmp-openvpn ~]$ sqlplus system/********@db23ctest

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 06:56:23 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

ERROR:
ORA-12170: TNS:Connect timeout occurred

C:\Users\marco\>tnsping db23test

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 28-SEP-2023 08:54:44

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\client19\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.30.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB23C_db23test.snrsbctl.vcnrsbctl.oraclevcn.com)))
TNS-12535: TNS:operation timed out

When encountering this error, we were able to resolve the alias via TNSNAMES.ORA and were also able to identify the database hostname. But for some reason we were not able to connect to the listener port. Most propably this is due to a firewall rule preventing the connection.

ORA-12541: TNS:no listener

[opc@cmp-openvpn ~]$ sqlplus system/********@db23ctest

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 07:02:13 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener

[oracle@db23ctest ~]$ tnsping db23test

TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 28-SEP-2023 07:02:42

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/23.0.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.30.110)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB23C_db23test.snrsbctl.vcnrsbctl.oraclevcn.com)))
TNS-12541: Cannot connect. No listener at host 10.0.30.110 port 1522.
 TNS-12560: Database communication protocol error.
  TNS-00511: No listener
   Linux Error: 111: Connection refused

This error seems to be straight forward, but may occur for several reasons. At least we made it to the database server. Possible reasons are:

  • The listener is not running
  • The listener is running but listens to another port
  • The listener is running and listens to the given port, but is not registered with the specified IP address

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

[opc@cmp-openvpn ~]$ sqlplus system/********@db23ctest

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 07:15:34 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

At this point we made it to the listener, but as the error messages states, the listener does not know the service name we are trying to connect to. This error can only be seen using SQL*Plus (or similar), TNSPING will not reveal any issues at this point since it only checks the connection until it gets a response from the server at the given port. It does not verify the service name. The service name comes into play, when really we want to establish a database connection. 

[oracle@db23ctest ~]$ tnsping db23test

TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 28-SEP-2023 07:17:39

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/23.0.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.30.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DB23C_db23test.snrsbctl.vcnrsbctl.oraclevcn)))
OK (0 msec)

Again there are several possible reasons for encountering an ORA-12514:

  • We are using a wrong service name.
  • The service is not registered with the listener. Maybe the database is down or the service is not started.
  • The databases “listener*” parameters are not properly configured preventing the database from registering its services with the listener.

ORA-01017: invalid credential or not authorized; logon denied

[opc@cmp-openvpn ~]$ sqlplus system/Wr0ngPwd@db23ctest

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 28 07:30:15 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid credential or not authorized; logon denied

If we see this error message, we finally made it to the database. We reached the listener, the listener did know of our requested service name and handed the connection over to the database. The database now checks the logon credentials and returns this error in case this validation failed.

Summary

Establishing a connection to an Oracle Database is quite a complex thing. And it starts the be a “real” database issue when we encounter an ORA-1017 error. Anything else happens before even touching the database.