The Journey of an Oracle Database Connection


Connecting to an Oracle Database is very straightforward. However, when you encounter an issue you might struggle with the root cause and how to solve it. For better troubleshooting, it is vital to understand the steps and checks being executed from the moment you hit the ENTER button.

In this blog post, we will have a look at 10 steps a connection runs through, what error messages do we get, what to check, and how to solve the issue. The list is not complete but covers the most common issues you might encounter while establishing a database connection.

The Environment

  • VM DB System running on Oracle Cloud.
  • SQL Developer and SQL*Plus clients running on my local machine.

We are going to use Local Naming (tnsnames.ora) for the connection. The same applies when using Easy Connect, except steps 1 and 2.

1. TNS_ADMIN Environment Variable

When we connect using a TNS Alias, we need to provide the path to the tnsnames.ora file via the TNS_ADMIN environment variable. If the path is not correct:

[user@client ~]$ export TNS_ADMIN=/wrong/path/to/tnsnames_ora_file/
[user@client ~]$ sqlplus myuser/MyDBpasswd__11@PDB1
ORA-12154: TNS:could not resolve the connect identifier specified

Provide the correct path to your tnsnames.ora:

[user@client ~]$ export TNS_ADMIN=/u01/app/oracle/client/network/admin/
[user@client ~]$ ls -l $TNS_ADMIN/tnsnames.ora
-rw-r----- 1 oracle oinstall 758 Oct  5 10:13 /u01/app/oracle/client/network/admin/tnsnames.ora

2. TNS Alias

After providing the correct path to the tnsnames.ora file, the string following the @ sign (the TNS Alias) needs to be found in the tnsnames.ora. If not, e.g. due to misspelling or wrong connection string syntax in tnsnames.ora, mostly missing parenthesis:

[user@client ~]$ sqlplus myuser/MyDBpasswd__11@PBD1
ORA-12154: TNS:could not resolve the connect identifier specified

Make sure you are using the correct TNS alias (here PDB1, not PBD1):

[user@client ~]$ cat $TNS_ADMIN/tnsnames.ora
PDB1 =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mydbserver)(PORT = 1521))

3. Hostname

When the TNS alias is found in the tnsnames.ora, then the hostname provided will be validated. If the hostname cannot be resolved via /etc/hosts or your DNS service:

[user@client ~]$ sqlplus myuser/MyDBpasswd__11@PDB1
ORA-12545: Connect failed because target host or object does not exist

An easy way to solve this is to add the IP address and the hostname to the /etc/hosts file. As user root:

[root@automation ~]# vi /etc/hosts mydbserver

Or even easier is just to use the IP address instead of the hostname in tnsnames.ora (not best practice). In a RAC environment, you’d use the SCAN as hostname instead.

4. Network Firewall Rules

Next step is to check the network connection using the IP address, port number, and network protocol provided. If the connection is not allowed via the firewall rules:

[user@client ~]$ sqlplus SYSTEM/NanaMina44__@
ORA-12170: TNS:Connect timeout occurred

This could be due to the IP address, the port number, or the network protocol used not being allowed in the firewall rules, egress from your client network, and/or ingress into the server network. Allow the traffic to flow from your client IP to the database host using the corresponding port and network protocol:

5. Port Number on Database Server

After getting through the network firewall, the port number on the database server will be checked. If the port is not open on the local database host machine:

[user@client ~]$ sqlplus myuser/MyDBpasswd__11@PDB1
ORA-12543: TNS:destination host unreachable

Open the port on the database server machine:

[root@automation ~]# firewall-cmd --permanent --add-port=1521/tcp
[root@automation ~]# firewall-cmd --reload

6. Database Listener

After being able to connect via the IP address, port number, and network protocol provided, the next check will be to look for the database listener running on that port. If the database listener is not running, or running on a different port:

[user@client ~]$ sqlplus myuser/MyDBpasswd__11@PDB1
ORA-12541: TNS:no listener

Check the listener on the database machine:

[grid@dbserver ~]$ lsnrctl status

LSNRCTL for Linux: Version - Production on 12-OCT-2021 11:08:18

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

TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory

If the listener is not running, then start it:

[grid@dbserver ~]$ lsnrctl start
LSNRCTL for Linux: Version - Production on 12-OCT-2021 11:08:40
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version - Production
Start Date                12-OCT-2021 11:08:40
Uptime                    0 days 0 hr. 0 min. 0 sec

When Grid Infrastructure (GI) is installed on your database server, then the listener is usually running from the GI owner, usually named grid.

Attention: be aware that in cases 4, 5, or 6, SQL Developer will show you the same following error message:

IO Error: The Network Adapter could not establish the connection

7. Database Service Name

When the listener is found, it will be asked about the database service name provided. If that database service name is not registered at the listener, e.g. due to misspelling or the database is down:

[user@client ~]$ sqlplus myuser/MyDBpasswd__11@PDB1
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Check the database services registered at the listener on the database host:

[grid@dbserver ~]$ lsnrctl services
Service "" has 1 instance(s).
  Instance "CDB01", status READY, has 2 handler(s) for this service...
      "DEDICATED" established:3 refused:0 state:ready
      "DEDICATED" established:4 refused:0 state:ready

If the service name is not shown, check the open mode of your database and try to register the service manually:

[oracle@dbserver ~]$ sqlplus / as sysdba

SQL> select open_mode from v$database;

SQL> alter system register;
System altered.

8. Background Processes

If the maximum number of background processes is reached, then no further background process for your connection can be started:

[user@client ~]$ sqlplus -s myuser/MyDBpasswd__11@PDB1
ORA-12537: TNS:connection closed

Check the PROCESSES parameter whether it should be increased:

[oracle@dbserver ~]$ sqlplus / as sysdba

SQL> select value from v$parameter where name='processes';

SQL> alter system set processes=300 scope=spfile;
System altered.

-- Restart of database instance is needed

The best practice is to use a connection pool to reuse existing connections.

9. Database User Account

Yes! Now we are finally getting in touch with the database, which will check the username and password provided. If the user does not exist or the password does not match:

[user@client ~]$ sqlplus myuserx/MyDBpasswd__11@PDB1
ORA-01017: invalid username/password; logon denied

If the user account is locked:

[user@client ~]$ sqlplus myuser/MyDBpasswd__11x@PDB1
ORA-28000: The account is locked.

Unlock the account:

[oracle@dbserver ~]$ sqlplus / as sysdba

SQL> alter user myuser account unlock;

User altered.

10. CREATE SESSION Privilege

If the user does not have the CREATE SESSION privilege:

[user@client ~]$ sqlplus myuser/MyDBpasswd__11@PDB1
ORA-01045: user MYUSER lacks CREATE SESSION privilege; logon denied

Grant the CREATE SESSION privilege to the user:

[oracle@dbserver ~]$ sqlplus / as sysdba

SQL> grant CREATE SESSION to myuser;

Grant succeeded.

Successful Connection

When all previous checks are passed:

[user@client ~]$ sqlplus myuser/MyDBpasswd__11@PDB1
Connected to:
Oracle Database 19c Standard Edition 2 Release - Production


Understanding how a database connection is getting established is necessary to know where to look and what to exactly check when encountering an issue.

You need a bit of zoom-in to read the content of the following summary.

Further Reading

Would you like to get notified when the next post is published?