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.
- 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 ERROR: 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 ERROR: 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 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydbserver)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.publicsubnet.vcnfra.oraclevcn.com) ) ) ...
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 ERROR: 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 220.127.116.11 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__@18.104.22.168:1523/orclpdb Error: 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 ERROR: ORA-12543: TNS:destination host unreachable
Open the port on the database server machine:
[root@automation ~]# firewall-cmd --permanent --add-port=1521/tcp success [root@automation ~]# firewall-cmd --reload success
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 ERROR: ORA-12541: TNS:no listener
Check the listener on the database machine:
[grid@dbserver ~]$ lsnrctl status LSNRCTL for Linux: Version 22.214.171.124.0 - Production on 12-OCT-2021 11:08:18 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) 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 126.96.36.199.0 - Production on 12-OCT-2021 11:08:40 STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 188.8.131.52.0 - 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 ERROR: 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 "pdb1.publicsubnet.vcnfra.oraclevcn.com" has 1 instance(s). Instance "CDB01", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:3 refused:0 state:ready LOCAL SERVER "DEDICATED" established:4 refused:0 state:ready LOCAL SERVER ...
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; OPEN_MODE -------------------- READ WRITE 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 ERROR: 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'; VALUE ----- 250 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 ERROR: ORA-01017: invalid username/password; logon denied
If the user account is locked:
[user@client ~]$ sqlplus myuser/MyDBpasswd__11x@PDB1 ERROR: 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 ERROR: 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.
When all previous checks are passed:
[user@client ~]$ sqlplus myuser/MyDBpasswd__11@PDB1 Connected to: Oracle Database 19c Standard Edition 2 Release 184.108.40.206.0 - Production Version 220.127.116.11.0 SQL>
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.
- Connect SQL Developer to Oracle Cloud Databases in Private Subnets
- How to use OCI Bastion Service to connect to your Private Resources
- How to create a Serial Console Connection to your VM DB System