12 Replies Latest reply: May 14, 2014 6:54 PM by user12167361 RSS

    Not able to connect to database from SQL Developer.

    user12167361

      Hi,

       

      I installed Oracle Database 10g Express Edition and SQL Developer on my laptop. My laptop name is learn.com

       

      The following services are running while I am trying to connect Database from SQL Developer,

       

      OracleJobSchedulerXEManualLocal System
      OracleMTSRecoveryServiceManualLocal System
      OracleServiceXEStartedAutomaticLocal System
      OracleXEClrAgentManualLocal System
      OracleXETNSListenerStartedAutomaticLocal System

       

      Content of file - tnsnames.ora

       

      XE =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = learn.com)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = XE)

          )

        )

       

      EXTPROC_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

          )

          (CONNECT_DATA =

            (SID = PLSExtProc)

            (PRESENTATION = RO)

          )

        )

       

      ORACLR_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

          )

          (CONNECT_DATA =

            (SID = CLRExtProc)

            (PRESENTATION = RO)

          )

        )

       

      Content of file - listener.ora

       

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (SID_NAME = PLSExtProc)

            (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)

            (PROGRAM = extproc)

          )

          (SID_DESC =

            (SID_NAME = CLRExtProc)

            (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)

            (PROGRAM = extproc)

          )

        )

       

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

            (ADDRESS = (PROTOCOL = TCP)(HOST = learn.com)(PORT = 1521))

          )

        )

       

      DEFAULT_SERVICE_LISTENER = (XE)

       

      Output of command - lsnrctl status

       

      LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 05-MAY-2014 17:05:15

       

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

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

      STATUS of the LISTENER

      ------------------------

      Alias                     LISTENER

      Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production

      Start Date                05-MAY-2014 09:12:18

      Uptime                    0 days 7 hr. 52 min. 56 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Default Service           XE

      Listener Parameter File   C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora

      Listener Log File         C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log

      Listening Endpoints Summary...

        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=learn.com)(PORT=1521)))

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))(Presentation=HTTP)(Session=RAW))

      Services Summary...

      Service "CLRExtProc" has 1 instance(s).

        Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

      Service "PLSExtProc" has 1 instance(s).

        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

      Service "XEXDB" has 1 instance(s).

        Instance "xe", status READY, has 1 handler(s) for this service...

      Service "XE_XPT" has 1 instance(s).

        Instance "xe", status READY, has 1 handler(s) for this service...

      Service "xe" has 1 instance(s).

        Instance "xe", status READY, has 1 handler(s) for this service...

      The command completed successfully

       

      Output of command - lsnrctl services

       

      LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 05-MAY-2014 17:05:23

       

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

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

      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 "PLSExtProc" has 1 instance(s).

        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

          Handler(s):

            "DEDICATED" established:0 refused:0

               LOCAL SERVER

      Service "XEXDB" has 1 instance(s).

        Instance "xe", status READY, has 1 handler(s) for this service...

          Handler(s):

            "D000" established:0 refused:0 current:0 max:1002 state:ready

               DISPATCHER <machine: IDR360987, pid: 3964>

               (ADDRESS=(PROTOCOL=tcp)(HOST=learn.com)(PORT=49245))

      Service "XE_XPT" has 1 instance(s).

        Instance "xe", status READY, has 1 handler(s) for this service...

          Handler(s):

            "DEDICATED" established:3 refused:0 state:ready

               LOCAL SERVER

      Service "xe" has 1 instance(s).

        Instance "xe", status READY, has 1 handler(s) for this service...

          Handler(s):

            "DEDICATED" established:3 refused:0 state:ready

               LOCAL SERVER

      The command completed successfully

       

      Output of commend - tnsping xe

       

      TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 05-MAY-2014 17:06:29

       

      Copyright (c) 1997, 2005, Oracle.  All rights reserved.

       

      Used parameter files:

      C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora

       

       

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = learn.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))

      OK (0 msec)

       

      I am able to connect Database through SQL Plus by using sys ans system user,

       

      sqlplus / as sysdba

       

      SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 5 17:07:55 2014

       

      Copyright (c) 1982, 2005, Oracle.  All rights reserved.

       

      Connected to:

      Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

       

      SQL> startup

      ORA-01081: cannot start already-running ORACLE - shut it down first

       

      SQL> select name from v$database;

       

      NAME

      ---------

      XE

       

      sqlplus system/learnoracle

       

      SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 5 17:09:08 2014

       

      Copyright (c) 1982, 2005, Oracle.  All rights reserved.

       

      Connected to:

      Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

       

      SQL> select name from v$database;

       

      NAME

      ---------

      XE

       

      I am able to access some remote database through SQL Developer. Please let me know if you require any other information.

       

      Thanks,

      Prashant

        • 1. Re: Not able to connect to database.
          user12167361

          While connecting, I am getting below error,

           

          io exception: the network adaptor could not establish the connection

          Vendor code 17002

           

          Thanks,

          Prashant

          • 2. Re: Not able to connect to database.
            RobbR

            What happens when you attempt to create a Basic connection in SQL Developer with the following information?

             

            Hostname: localhost

            Port: 1521

            SID: xe

            • 3. Re: Not able to connect to database.
              spiderwoman

              "

                              

              Not able to connect to database. "

               

              But it seems you are connected....and trying to startup a database (XE) that is already running?  You will need to shutdown the database first to start it up again. 

              • 4. Re: Not able to connect to database.
                rp0428
                While connecting, I am getting below error,

                 

                io exception: the network adaptor could not establish the connection

                Vendor code 17002

                Ok - can you post the actual values you are entering into the connection dialog?

                1. Connection Type

                2. Role

                3. Hostname

                4. Port

                5. SID

                6. Service name

                • 5. Re: Not able to connect to database.
                  user12167361

                  I tried below combinations to connect Database through SQL Developer,

                   

                  Username - system

                  Password - learnoracle

                  Connection Type - Basic

                  Hostname - learn.com

                  Port - 1521

                  Service name - xe

                   

                  Username - system

                  Password - learnoracle

                  Connection Type - Basic

                  Hostname - 192.168.1.8

                  Port - 1521

                  Service name - xe

                   

                  Username - system

                  Password - learnoracle

                  Connection Type - Basic

                  Hostname - localhost

                  Port - 1521

                  Service name - xe

                   

                  Username - system

                  Password - learnoracle

                  Connection Type - Basic

                  Hostname - 127.0.0.1

                  Port - 1521

                  Service name - xe

                   

                  Username - system

                  Password - learnoracle

                  Connection Type - Basic

                  Hostname - learn.com

                  Port - 1521

                  SID - xe

                   

                  Username - system

                  Password - learnoracle

                  Connection Type - Basic

                  Hostname - 192.168.1.8

                  Port - 1521

                  SID - xe

                   

                  Username - system

                  Password - learnoracle

                  Connection Type - Basic

                  Hostname - localhost

                  Port - 1521

                  SID - xe

                   

                  Username - system

                  Password - learnoracle

                  Connection Type - Basic

                  Hostname - 127.0.0.1

                  Port - 1521

                  SID - xe

                   

                  Exact error is socket time out.

                  • 6. Re: Not able to connect to database.
                    user12167361

                    I corrected the subject. Sorry about that.

                    • 7. Re: Not able to connect to database.
                      user12167361

                      Role is default.

                      • 8. Re: Not able to connect to database.
                        Jim Smith

                        This may be a firewall issue.

                         

                        sqlplus system/learnoracle@xe should provoke a similar error.

                         

                        try

                         

                        telnet localhost 1521 (and variations on the hostname)

                         

                        That should also fail.

                         

                        Check your windows firewall settings and open the 1521 port.

                        • 9. Re: Not able to connect to database.
                          RobbR

                          Jim Smith wrote:

                           

                          This may be a firewall issue.

                           

                          sqlplus system/learnoracle@xe should provoke a similar error.

                           

                          try

                           

                          telnet localhost 1521 (and variations on the hostname)

                           

                          That should also fail.

                           

                          Check your windows firewall settings and open the 1521 port.

                          Since you weren't able to connect using localhost, 127.0.0.1, or your IP address, I'm lead to believe it isn't a DNS issue.  I agree with Jim Smith, this appears to be a firewall issue and would follow his advice.

                          • 10. Re: Not able to connect to database.
                            Gary Graham-Oracle

                            Hi Prashant,

                             

                            In addition to Jim Smith's comment about firewall settings, you may wish to try connecting with your laptop disconnected from the internet altogether.  I say this because I tried accessing learn.com from my web browser and got the following message:

                             

                            Web Site Not Found

                            Sorry, we could not find any content for this web address. Please check the URL.

                            If you are an Acquia Cloud customer and expect to see your site at this address, you'll need to add this domain name to your site via the Acquia Network management console.

                            Acquia Inc.

                             

                            I have no idea if using learn.com for your laptop name is technically OK or not, it just struck me as a bit odd ... I'm accustomed to specifying the hostname in the form <userid>.<somewhere-in>.<something>.<com>.

                             

                            Best Wishes,

                            Gary

                            SQL Developer Team

                            • 11. Re: Not able to connect to database.
                              user12167361

                              Hi Jim,

                               

                              Thanks for the direction.

                               

                              Below things I tried,

                               

                              sqlplus system/learnoracle@xe

                              SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 14 13:56:22 2014

                              Copyright (c) 1982, 2005, Oracle.  All rights reserved.

                              ^C

                              (Stuck)

                               

                              sqlplus system/learnoracle

                              SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 14 13:57:58 2014

                              Copyright (c) 1982, 2005, Oracle.  All rights reserved.

                              Connected to:

                              Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

                              SQL>

                              (Confused. Why this one is working?)

                               

                              telnet learn.com 1234567890

                              Connecting To learn.com...Could not open connection to the host, on port 1234567890: Connect failed

                              (Expected)

                               

                              telnet learn.com 1521

                              Connecting To learn.com...

                              (Blank screen opened. Not sure telnet worked or not because I expected some promt like DOS)

                               

                              Thanks,

                              Prashant

                              • 12. Re: Not able to connect to database.
                                user12167361

                                Hi Gary,

                                 

                                I will try it and post the results.

                                 

                                Thanks,

                                Prashant