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

Question

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:oracleproduct11.2.0dbhome_1
etworkadminlistener.ora
Listener Log File         d:oraclediagnslsnrHERP-DEVDBlisteneralertlog.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))
  (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:oracleproduct11.2.0dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:oracleproduct11.2.0dbhome_1inoraclr11.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

Answer 1

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.