Oracle SQL Developer: Failure - Test failed: The Network Adapter could not establish the connection?

16
votes

Problem

Please note that I changed details for security purposes. However, the problem remains intact.

I installed an Oracle 11g database on a server at location, say, herp-devDV.derp.edu. Now I have another Oracle 11g database on a server at location, say, derp-db.derp.edu.

I entered the connection name, username, password, hostname, and service name in Oracle SQL developer for both herp-devDV.derp.edu and derp-db.derp.edu. I can connect to schema in derp-db.derp.edu, but not herp-devDV.derp.edu. It gives me this message: 

Failure - Test failed: The Network Adapter could not establish the connection

Details

I have the following information:

  • Port 1521 is not open for either server when I telnet
  • My listener is up and running for both.
  • I can access derp-db.derp.edu on Oracle SQL Developer.
  • Oracle client is on my local machine + Oracle SQL Developer
  • I can remote desktop to both servers

What I have done

  • Googled
  • Stackoverflow
  • Ran stop and start lnrctl commands
  • On herp-devDB.derp.edu I ran lsnrctl status

I received the following output

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                03-JUN-2014 13:37:22
Uptime                    6 days 0 hr. 53 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         d:\oracle\diag\tnslsnr\HERP-DEVDB\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HERP-DEVDB.derp.edu)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "HERPDEVDBXDB" has 1 instance(s).
  Instance "herpdevdb", status READY, has 1 handler(s) for this service...
Service "herpdevdb" has 1 instance(s).
  Instance "herpdevdb", status READY, has 1 handler(s) for this service...
The command completed successfully

I then check out my listener.ora and find

  SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = HERP-DEVDB.derp.edu)(PORT = 1521))
    )
  )

At this point, I confess I am scratching my head as I don't see anything sticking out and telling me why this should not be working.

The only clue is when I check derp-db.derp.edu and run the command lsnrctl status. Please see excerpt below: 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DERP-DB.edu)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date                18-MAY-2014 02:19:01
Uptime                    22 days 12 hr. 23 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
---etc----

So I am still scratching my head. Why would derp-db be connecting to the DERP-DB.edu but herp-devDB is connecting to EXTPROC1521? How do I fix this? The listener.ora and other files between these two servers are almost identical except for the name of the instances. Hmmm.

  • EDIT1: I changed the listener.ora in herp. This didn't fix it.

  • EDIT2: I cannot telnet into derp-db.derp.edu on port 1521. But I can still connect to it with SQL Developer? Wth?

  • EDIT3 I cannot telnet into herp-devdb.derp.edu on port 1521 either.
  • EDIT4 I cannot ping IP addresses of either server.

Anyway assistance would be greatly appreciated. Thanks

Regards, Geeky

11
oracleoracle11gdatabase-connectionoracle-sqldeveloper
Can you telnet to herp-devDV.derp.edu on port 1521 from your client machine? Is the Oracle client installed on your client machine or just SQL Developer? - Justin Cave
@JustinCave I edited my question to address your questions. Thanks for your help. - GeekyOmega
Does HERP-DEVDB.derp.edu resolve to the same IP address from your client and from the server? Or to put it another way, if you do netstat -an | find "1521" on the server does it show that port on the IP address you expect? You mentioned changing your listener.ora; presumably you restarted the listener afterwards? (And where does HERP-DEVDB.users.campus fit in; is that just obscured differently, or does it resolve to the same address too?) - Alex Poole
The first thing I would try, is to add the database to the tnsnames.ora on your machine and then try to run tnsping. If that works, all the ports are open, if not you need to check the firewall settings on the db server and your machine. - Gisli
Then it really sounds like you have a firewall blocking port 1521, somewhere between your client and server, or on one of them. (The only odd thing is telnet not working to derp-db; that maybe suggests a fireall/antivirus on your client that's blocking at the application level. But then telnet would never work to any host or port). Running a traceroute to both servers might give a hint of where the block is, but you might need your network people to monitor a connection attempt to pin it down. - Alex Poole

11 Answers

17
votes

I am answering this for the benefit of future community users. There were multiple issues. If you encounter this problem, I suggest you look for the following:

  • Make sure your tnsnames.ora is complete and has the databases you wish to connect to
  • Make sure you can tnsping the server you wish to connect to
  • On the server, make sure it will be open on the port you desire with the specific application you are using.

Once I did these three things, I solved my problem.

11
votes

I had a similar issue where I also continuously got the same error. I tried many things like changing the listener port number, turning off the firewall etc. Finally I was able to resolve the issue by changing listener.ora file. I changed the following line:

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

to 

(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))

I also added an entry in the /etc/hosts file.

you can use Oracle net manager to change the above line in listener.ora file. See Oracle Net Services Administrator's Guide for more information on how to do it using net manager.

Also you can use the service name (database_name.domain_name) instead of SID while making the connnection.

I Hope it helps.

5
votes

I just had same issue when I installed the oracle 11g and then creating the database.

I don't even know that the listener has to create manually. Hence, I open Net Configuration Assistant and manually create the listener.

And I can connect the database that I created locally through sql developer.

3
votes

I solved just by: given correct host and port so:

  1. Open oracle net manager
  2. Local
  3. Listener

in Listener on address 2 then copy host to Oracle Developer

finally connect to oracle

2
votes

You can locate a file named listener.ora under the installation folder oraclexe\app\oracle\product\11.2.0\server\network\ADMIN It contains the following entries

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Codemaker-PC)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

You should verify the HOST (Here it is Codemaker-PC) should be the computer name. If it's not correct the change it as computer name.

then try the following command on the command prompt run as administrator,

lsnrctl start
2
votes

I solved this by writing the explicit IP address defined in the Listener.ora file as the hostname.

enter image description here

So, instead of "localhost", I wrote "192.168.1.2" as the "Hostname" in the SQL Developer field.

In the below picture I highlighted the input boxes that I've modified: enter image description here

1
votes

Curiously, I was able to solve the same issue by doing the exact opposite move to svc's ! I had to :

1) replace the FQDN hostname in my tnsnames.ora / listener.ora files with localhost, and restart the listener service, and

2) two, I had to use "SYS as SYSDBA" as the username in the SQL Developer input textbox

to finally be able to have SQL Developer hook to my local instance.

1
votes

only start listner then u can connect with database. command run on editor:

lsnrctl start

its work fine.

1
votes

This worked for me. may help some one. Turn off firewall. on RHEL 7

systemctl stop  firewalld
0
votes

I faced the same problem. I had to turn off my firewall, then it worked.

You could also open the port: http://windows.microsoft.com/en-in/windows/open-port-windows-firewall#1TC=windows-7

0
votes

For me, the HOST was set differently in tnsnames.ora and listener.ora. One was set to the full name of the computer and the other was set to IP address. I synchronized them to the full name of the computer and it worked. Don't forget to restart the oracle services.

I still don't understand exactly why this caused problem because I think IP address and computer name are ultimately same in my understanding.