This discussion is archived
4 Replies Latest reply: May 2, 2013 2:46 PM by Gary Graham RSS

Problems Creating Custom Connections In SQL Developer

842271 Newbie
Currently Being Moderated
I have the Auto-Generated Local Connections available in SQL Developer 3.2.20.09 Build MAIN-09.87, but cannot create my own connections. I've searched the forums, but haven't had an "AHA" moment.

I installed the 32-Bit Oracle 11gR2 and SQL Developer with JRE on my Windows x64 system because installing the x64 version of Oracle 11gR2 failed. This is for database classes I'm taking at the local community college.

Trying to create a connection with OS Authentication:
Connection Name: PaulK
Connection type: Basic
Role: default
Hostname: localhost
Port: 1521
SID: orcl

and clicking Test returns Status: Failure - Test failed:  The Network Adapter could  not establish the connection

When I run lsnrctl services, I get

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\PaulK>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 26-APR-2013 13:36:35

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER

listener.ora contains

# listener.ora Network Configuration File: E:\app\PaulK\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 = E:\app\PaulK\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app\PaulK\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)

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

ADR_BASE_LISTENER = E:\app\PaulK

tnsnames.ora contains

# tnsnames.ora Network Configuration File: E:\app\PaulK\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 = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

I assume something is missing or incorrect, but I don't know what.

Thanks in advance for your help.
  • 1. Re: Problems Creating Custom Connections In SQL Developer
    Gary Graham Expert
    Currently Being Moderated
    Hi Paul,

    It looks like the listener does not know about your ORCL service. If it did, the "lsnrctl services" should include something like this...
    Service "orcl" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:4830 refused:0 state:ready
             LOCAL SERVER
    Also, it is not a best practice to have "localhost" in your listener.ora and tnsnames.ora files. It should be the actual host name, preferably the fully qualified name, as in myhost.mycompany.com.

    If you can't get this worked out easily yourself, then you will get a better response by posting your questions in one of these forums:
    General Questions
    Installation

    Best Wishes,
    Gary
  • 2. Re: Problems Creating Custom Connections In SQL Developer
    842271 Newbie
    Currently Being Moderated
    This is an Oracle installation on my personal PC. How do I qualify that, other than "PaulK-PC", which is the name of my PC?

    Should I just clone the sections of listener.ora and tnsnames.ora that refer to CLRExtProc and replace CLRExtProc with orcl?

    I will check out the other fora you listed.


    Thanks.
  • 3. Re: Problems Creating Custom Connections In SQL Developer
    842271 Newbie
    Currently Being Moderated
    I reinstalled Oracle 11gR2 x86 and ran lsnrctl:

    Microsoft Windows [Version 6.1.7601]
    Copyright (c) 2009 Microsoft Corporation. All rights reserved.

    C:\Users\PaulK>lsnrctl status

    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 02-MAY-2013 15:51:56

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
    Start Date 02-MAY-2013 14:57:07
    Uptime 0 days 0 hr. 54 min. 53 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File E:\app\PaulK\product\11.2.0\dbhome_1\network\admin\listener.ora
    Listener Log File e:\app\paulk\diag\tnslsnr\PaulK-PC\listener\alert\log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "orcl" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclXDB" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    The command completed successfully

    C:\Users\PaulK>lsnrctl services

    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 02-MAY-2013 15:53:19

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    Service "orcl" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:4 refused:0 state:ready
    LOCAL SERVER
    Service "orclXDB" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
    "D000" established:0 refused:0 current:0 max:1022 state:ready
    DISPATCHER <machine: PAULK-PC, pid: 8880>
    (ADDRESS=(PROTOCOL=tcp)(HOST=PaulK-PC)(PORT=51451))
    The command completed successfully

    As you can see, my Service "orcl" matches your example, but I am still unable to create a new connection in SQL Developer.

    This is my tnsnames.ora file:

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

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


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

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

    and my listener.ora file:

    # listener.ora Network Configuration File: E:\app\PaulK\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 = E:\app\PaulK\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:E:\app\PaulK\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    )

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

    ADR_BASE_LISTENER = E:\app\PaulK

    These files are just as the install process created them, but are there any sore thumbs sticking out?

    Thanks.
  • 4. Re: Problems Creating Custom Connections In SQL Developer
    Gary Graham Expert
    Currently Being Moderated
    Hi Paul,

    First off, I make no claim to being a listener expert. Comparing the information you provide with my own tnsnames / listener files (on both my work machine with Oracle 11.2.0.1 and a non-work machine with 11g XE installed), the main differences are:

    1. None of the host references are localhost. I think PaulK-PC would have been a better option for your installation.
    2. The non-work 11gXE machine's tnsnames.ora does not include the LISTENER_<SID> = bit, but the work machine does, so probably OK.
    3. The work machine's tnsnames.ora uses SID = rather than SERVICE_NAME = in some of the TNS alias entries. Whatever works!
    4. The work machine's listener.ora SID_LIST_LISTENER = section has a SID_DESC entry for the database instance.
    5. To expand on (4), the SID_DESC entry also includes a GLOBAL_NAMES entry that fully qualifies where the database lives...
        (SID_DESC =
          (GLOBAL_DBNAME = orcl.<myuser-dns-domain>)
          (ORACLE_HOME = C:\app\<myuser-name>\product\11.2.0\dbhome_1)
          (SID_NAME = ORCL)
        )                
    Another thing you might consider is whether the prior failed installed did not get cleaned up properly. Maybe it is interfering with the operation of you current installation. Also, try checking for errors in E:\app\PaulK\diag\tnslsnr\PaulK-PC\listener\alert\log*.xml.

    That's all I can really add. As previously mentioned, there are better forums for this sort of question.

    Best Wishes,
    Gary

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points