SQL Developer Connection Troubleshooting

SQL Developer is an Integrated Development Environment (IDE) for developing SQL in Oracle database. This is a free tool from Oracle which is used by both developer and database administrator. This article assumes that you have an Oracle database installed on a Linux server. You have installed SQL Developer to connect to the database. SQL Developer requires a minimum of  host name or IP, port, and a database SID and a database user account to make the connection to the database.


Sometime all the information required to make a connection to SQL Developer  are provided even then the connection failed with an error message. This is the most common issue I see with a new Oracle database set up or when something changes on server side. Let's find out all the breaking points, test them and fix the connection.

 See error message!



 status: failure - test failed: IO Error: The network Adapter could not establish the connection
We are now going to troubleshoot this connection issue. Some may know the answers but with this article, we will explore and verify everything that makes this connection. What are few things you need for this connections to succeed beside Oracle user account, host/port and SID?

Verify each listed below for successful connection.

1. Database host  is up
2. Oracle SID name
3. Oracle database is open
4. Oracle Listener is listening
5. listening port (1521) 
6. Oracle user account

Next, we are going to verify each of the above listed steps.

1. Ping host IP 192.168.1.120  from your pc cmd. This test if the host where the database is installed is up or not.  # verified,
2. Echo $ORACLE_SID  # verified the name of SID
3. On sqlplus run ( select status from v$instance;)  # verified the database is open and running
4. LSNRCTL status  # verified the listener is listening for the above SID
5. Verify port 1521 is the listening port # verified the listening port
6. Able to connect to database using the user credential via SQLPlus. 

The listener is up and listening at port 1521

oracle@oracldev etc]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-JUL-2015 13:25:12
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracldev.baniya.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                12-JUL-2015 13:15:00
Uptime                    0 days 0 hr. 10 min. 11 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracldev/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracldev.baniya.com)(PORT=1521)))
Services Summary...
Service "DEV" has 1 instance(s).
  Instance "DEV", status READY, has 1 handler(s) for this service...
Service "DEVXDB" has 1 instance(s).
  Instance "DEV", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracldev etc]$

The database is up and mounted

QL> select status from v$instance;
STATUS
------------
OPEN
SQL>

The host name and port on tnsname.ora

[oracle@oracldev admin]$ cat listener.ora
# listener.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora                                                   # Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracldev.baniya.com)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

Host Name and IP verified.

[oracle@oracldev ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.120 oracldev.baniya.com

Pinging to 192.168.1.120

Everything is verified, the host and oracle database is up but still cannot connect to database using SQL Developer.

1. Ping host IP 192.168.1.120  from your pc cmd. This test if the host where the database is installed is up or not.  # verified,
2. Echo $ORACLE_SID  # verified the name of SID
3. On sqlplus run ( select status from v$instance;)  # verified the database is open and running
4. LSNRCTL status  # verified the listener is listening for the above SID
5. Verify port 1521 is the listening port # verified the listening port
6. Able to connect to database using the user credential via SQLPlus. 

Why is the connection still hesitant? What did we miss to the above list?The only thing we didn't check was the firewall on the Linux host.  We will first try to disable the firewall if that works we know the problem. Connect to Linux host and disable firewall. You will need to have a root access to make this change.

1. service iptables stop 
2. service iptables status

Disabling firewall fixed the connection problem that we are having.  Note that disabling a firewall in a production environment isn't a good security practice, therefore you will need to open a port 1521 on your Linux server. Below, I will should you how you can open port 1521 on a Linux OS.

How to open port 1521 on linux server?

iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
service iptables save
open file /etc/sysconfig/iptables:
# vi /etc/sysconfig/iptables
and put the below line of code just above the RECJECT code
-A INPUT -p tcp -m state --state NEW -m tcp --dport 1521 -j ACCEPT
service iptables restart
Check if the rule of 1521 is active
iptables -L -n | grep 1521

The port 1521 is now open on the firewall. This resolved SQL Developer connection issue.

You should be connected by now if you are using a basic thin client connection. The FAT client connection may required some additional troubleshooting steps which we will discuss on next article.