Introduction
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
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)
)
)
...
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
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
130.71.127.2 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__@130.61.86.212: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 19.0.0.0.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 19.0.0.0.0 - Production on 12-OCT-2021 11:08:40
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.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.
Successful Connection
When all previous checks are passed:
[user@client ~]$ sqlplus myuser/MyDBpasswd__11@PDB1
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL>
Conclusion
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.
Database Heartbeat 