How do I create a new Connection in Oracle SQL Developer?

Question

I've just installed Oracle Database 11g Release 2 on windows 7.

I'm in Oracle SQL Developer and I'm trying to create a new connection, but when I click in "Connect", I got the message: "Status: Failure -Test failed: The Network Adapter could not establish the connection".

In the connection window, I've tried changing the role to SYSDBA, the OS Authentication, the port, and the SID, but even when changing those values I got the same message.

tnsnames.ora contents:

# tnsnames.ora Network Configuration File: F:\Oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.104)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.168.0.104)
    )
  )

listener.ora:

# listener.ora Network Configuration File: F:\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = F:\Oracle\product\11.2.0\dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.104)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = F:\Oracle

alert log:

<msg time='2015-06-02T19:28:12.428-05:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='KK-PC'
 host_addr='fe80::986b:9a38:5c9f:fc06%10' version='1'>
 <txt>02-JUN-2015 19:28:12 * (CONNECT_DATA=(SID=CLRExtProc)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=KK))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.104)(PORT=51332)) * establish * CLRExtProc * 0
 </txt>
</msg>

lsnrctl status:

Services Summary...
Service "orcl.168.0.104" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.168.0.104" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

How can I solve this?

thanks.

Did you set the host and port to match the listener for your database? Is the listener actually running, and on the address/port you think (lsnrctl status will tell you). Is there a firewall between the database and your PC, if it isn't the same box? — Alex Poole
– Alex Poole, Commented Jun 2, 2015 at 17:09
Not sure; that sounds like a firewall problem still. But check the alert log to see if something is breaking at the DB end - unlikely but you never know. Are you getting that testing/creating the connection, or is it established and you're trying to run statements now? — Alex Poole
– Alex Poole, Commented Jun 2, 2015 at 23:03
Why is the SID for the ORCL alias set to CLRExtProc in your tnsnames.ora? That should almost certainly be ORCL too - to match your actual database SID. Are you using a TNS connection from SQL Developer? It might be easier to use a Basic connection and supply that information directly; and use the service name (if you know it - that value in the tnsnames.ora look odd too). — Alex Poole
– Alex Poole, Commented Jun 3, 2015 at 17:52
The tnsnames.ora isn't relevant for a basic connection, but could be useful to check the connection settings you are using. I'm unclear how you're getting that alert log entry from a basic connection to ORCL. That's a very old version though - no idea why they still ship that! - and it's probably worth upgrading to the current version 4.1 anyway. — Alex Poole
– Alex Poole, Commented Jun 3, 2015 at 19:03
If it's successful with the service name, why are you using the SID? The SID would have to be explicitly referenced in the listener.ora to be usable. — Alex Poole
– Alex Poole, Commented Jun 3, 2015 at 19:31

pyram · Accepted Answer · 2015-07-12 17:58:22Z

0

So I'll answer my own question hehe.

What I did is to stop the listener service, then I removed the listener.ora from the ADMIN folder, and then I started the service again.

Then I did a lsnrctl status in command prompt and I got the service name and the SID. So now I could connect with the orcl SID and with the orcl.168.0.104 service name, everything works perfect. Thanks a lot.

answered Jul 12, 2015 at 17:58

pyram

9359 gold badges25 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I create a new Connection in Oracle SQL Developer?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related