4 Replies Latest reply: May 2, 2013 4:46 PM by Gary Graham-Oracle RSS

    Problems Creating Custom Connections In SQL Developer

    842271
      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-Oracle
          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 Database Discussions
          Database Installation

          Best Wishes,
          Gary
          • 2. Re: Problems Creating Custom Connections In SQL Developer
            842271
            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
              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-Oracle
                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