14 Replies Latest reply: Nov 14, 2011 8:43 AM by 898823 RSS

    can't connect to Express Edition 11g by sql developer

    898823
      hello,

      i installed the 11g edition and try to connect to it by sql developer. it didn't work, then I noticed there was an older version (10g) on the laptop. So i removed both, and installed the 11g version, but still it doesn't work.

      My laptop runs on 32-bit windows 7 so I got the 32bit installer of 11g.

      I checked several other threads, but didn't found a solution.

      Some information:

      connecting by sqlplus
      SQL*Plus: Release 11.2.0.2.0 Production on Di Nov 8 09:34:25 2011

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

      Enter user-name: system
      Enter password:
      Connected to:
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

      tnsping
      TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 08-NOV-2011 09:32:43

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

      Used parameter files:
      C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

      Used HOSTNAME adapter to resolve the alias
      Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=fe80::7944:fff9:594b:a534%12)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=fe80::831:366f:f5fe:eab5%16)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.21.74)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=2001:0:5ef5:79fb:831:366f:f5fe:eab5)(PORT=1521)))
      OK (0 msec)

      lsnrctl status
      LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 08-NOV-2011 09:29:24

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
      Start Date 08-NOV-2011 08:51:17
      Uptime 0 days 0 hr. 38 min. 7 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Default Service XE
      Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
      Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\W7-008436\listener\alert\log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=W7-008436.iconos.be)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=W7-008436.iconos.be)(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" has 1 instance(s).
      Instance "xe", status READY, has 1 handler(s) for this service...
      The command completed successfully
        • 1. Re: can't connect to Express Edition 11g by sql developer
          19426
          I recommend you read the Getting Started Guide, it tells you (among other topics) how to create a valid SQL Developer connection:

          http://download.oracle.com/docs/cd/E17781_01/admin.112/e18585/toc.htm#BJFBHCHI

          Werner
          • 2. Re: can't connect to Express Edition 11g by sql developer
            898823
            I had read the guide and followed it. But when I tested the connection nothing happend, it kept trying till a time-out happend (several minutes later).
            • 3. Re: can't connect to Express Edition 11g by sql developer
              bop
              You have to explain how you are trying to connect to xe with sqldeveloper. Are you using a "Basic" connection (jdbc) or "TNS" when you define a connection?

              If you try a jdbc connection you can try localhost or your ip as "Hostname", normally Port 1521 and SID=xe, this should work locally. If you try a TNS connection you must check if you possibly have previous installations of Oracle clients and possible more than one tnsnames.ora, this can mess things up.
              • 4. Re: can't connect to Express Edition 11g by sql developer
                898823
                this is wat I filled in

                image sqldeveloper connection properties

                I also can't go to the "get started" page
                • 5. Re: can't connect to Express Edition 11g by sql developer
                  bop
                  Your problem may be related to a firewall blocking ports 1521 and 8080. I assume port 8080 which is normally used by Application express running the "get started" pages in XE. I you use only a standard Win7 firewall you can check and possibly change settings in Control Panel - System and Security - Windows Firewall or get some system admin to help you if you are on a work network.
                  • 6. Re: can't connect to Express Edition 11g by sql developer
                    898823
                    windows firewall was turned off, that was one of the things my colleague advised me to do.

                    It can't be the fault of the network, cause on the computer of my colleague eveythings works fine.
                    • 7. Re: can't connect to Express Edition 11g by sql developer
                      bop
                      Which version of sqldeveloper are you using. If relevant, download version 3.0 or 3.1 prerelease with the jre included and try again.

                      Concerning application express (apex) and the "get started" pages, connect to xe in sqlplus and check if apex is listening.
                      Verify port:
                      SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
                      Enable port:
                      EXEC DBMS_XDB.SETHTTPPORT(8080);

                      I assume that you have not installed a newer version of apex after installing Oracle XE, I believe this will break the "get started" in Oracle XE. If some other program is using port 8080, you can change 8080 to e.g. 7777 in the line above.
                      • 8. Re: can't connect to Express Edition 11g by sql developer
                        898823
                        bop wrote:
                        Which version of sqldeveloper are you using. If relevant, download version 3.0 or 3.1 prerelease with the jre included and try again.
                        I'm using version 3.0.04
                        bop wrote:
                        Concerning application express (apex) and the "get started" pages, connect to xe in sqlplus and check if apex is listening.
                        Verify port:
                        SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
                        get httpport = 8080
                        bop wrote:
                        Enable port:
                        EXEC DBMS_XDB.SETHTTPPORT(8080);
                        Nothing has changed
                        • 9. Re: can't connect to Express Edition 11g by sql developer
                          orafad
                          zero wrote:
                          i installed the 11g edition and try to connect to it by sql developer.
                          Is SQL Developer on the same machine?
                          it didn't work,
                          What's the actual error message?

                          What's the command and output from connecting with sqlplus using:

                          sqlplus user@'hostname:1521/xe'

                          Try hostname=localhost and hostname=<actual host name>.
                          • 10. Re: can't connect to Express Edition 11g by sql developer
                            898823
                            yes sql developer is on the same machine

                            error message:
                            Status:Failure - Test failed: IO-fout:Socket read timed out

                            when I try it with sql plus and hostname=localhost, it also fails, it keeps "hanging"
                            the same for hostname= actual hostname
                            • 11. Re: can't connect to Express Edition 11g by sql developer
                              bop
                              The error message indicates that Oracle XE is not running. You can check that in several ways, e.g. by entering Computer Management - select Services and check if OracleServiceXE and OracleXETNSListener are running.
                              You can also check log files (*.log,*.trc) in directory C:\oraclexe\app\oracle\diag\rdbms\xe\xe\trace and look for error messages.

                              I don't quite understand the mention of hostname related to sqlplus, do you have several Oracle installations? On a machine with Oracle XE you can normally select "Run Sql Command Line" and you are prompted like this:

                              SQL*Plus: Release 11.2.0.2.0 Production on To Nov 10 23:47:26 2011
                              Copyright (c) 1982, 2010, Oracle. All rights reserved.
                              SQL> connect system/pwd
                              Connected.
                              SQL>
                              • 12. Re: can't connect to Express Edition 11g by sql developer
                                898823
                                bop wrote:
                                The error message indicates that Oracle XE is not running. You can check that in several ways, e.g. by entering Computer Management - select Services and check if OracleServiceXE and OracleXETNSListener are running.
                                You can also check log files (*.log,*.trc) in directory C:\oraclexe\app\oracle\diag\rdbms\xe\xe\trace and look for error messages.
                                the services were started.

                                I found an error in the "alert_xe.log"
                                Errors in file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_j000_388.trc:
                                ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
                                ORA-06550: line 1, column 807:
                                PLS-00201: identifier 'DBSNMP.BSLN_INTERNAL' must be declared
                                ORA-06550: line 1, column 807:
                                file xe_j000_388.trc: Trace file C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_j000_388.trc
                                Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
                                Windows NT Version V6.1
                                CPU : 2 - type 586, 2 Physical Cores
                                Process Affinity : 0x0x00000000
                                Memory (Avail/Total): Ph:2120M/3063M, Ph+PgF:4631M/6124M, VA:1095M/2047M
                                Instance name: xe
                                Redo thread mounted by this instance: 1
                                Oracle process number: 27
                                Windows thread id: 388, image: ORACLE.EXE (J000)


                                *** 2011-11-14 08:35:58.999
                                *** SESSION ID:(102.5) 2011-11-14 08:35:58.999
                                *** CLIENT ID:() 2011-11-14 08:35:58.999
                                *** SERVICE NAME:(SYS$USERS) 2011-11-14 08:35:58.999
                                *** MODULE NAME:(DBMS_SCHEDULER) 2011-11-14 08:35:58.999
                                *** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2011-11-14 08:35:58.999

                                ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
                                ORA-06550: line 1, column 807:
                                PLS-00201: identifier 'DBSNMP.BSLN_INTERNAL' must be declared
                                ORA-06550: line 1, column 807:
                                PL/SQL: Statement ignored
                                so apparently somewhere the parameter DBSNMP.BSLN_INTERNAL is not declared, but how do i fix it?

                                bop wrote:
                                I don't quite understand the mention of hostname related to sqlplus, do you have several Oracle installations? On a machine with Oracle XE you can normally select "Run Sql Command Line" and you are prompted like this:

                                SQL*Plus: Release 11.2.0.2.0 Production on To Nov 10 23:47:26 2011
                                Copyright (c) 1982, 2010, Oracle. All rights reserved.
                                SQL> connect system/pwd
                                Connected.
                                SQL>
                                it was a reply to the post of orafad
                                • 13. Re: can't connect to Express Edition 11g by sql developer
                                  orafad
                                  zero wrote:
                                  error message:
                                  Status:Failure - Test failed: IO-fout:Socket read timed out

                                  when I try it with sql plus and hostname=localhost, it also fails, it keeps "hanging"
                                  the same for hostname= actual hostname
                                  Possibly some network configuration error or interference (like antivirus or firewall).

                                  What if you try IPv4 address (from ipconfig command) and 127.0.0.1 in Sql Developer connection?

                                  Does ping localhost and ping hostname work?

                                  What's the output from hostname command?
                                  • 14. Re: can't connect to Express Edition 11g by sql developer
                                    898823
                                    orafad wrote:
                                    Possibly some network configuration error or interference (like antivirus or firewall).

                                    What if you try IPv4 address (from ipconfig command) and 127.0.0.1 in Sql Developer connection?
                                    both: status: Failure - Test failed: IO-fout: Socket read timed out

                                    orafad wrote:
                                    Does ping localhost and ping hostname work?
                                    both works
                                    ping :
                                    Pinging W7-008436.iconos.be [fe80::7944:fff9:594b:a534%12] with 32 bytes of data:
                                    Reply from fe80::7944:fff9:594b:a534%12: time<1ms
                                    Reply from fe80::7944:fff9:594b:a534%12: time<1ms
                                    Reply from fe80::7944:fff9:594b:a534%12: time<1ms
                                    Reply from fe80::7944:fff9:594b:a534%12: time<1ms

                                    Ping statistics for fe80::7944:fff9:594b:a534%12:
                                    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
                                    Approximate round trip times in milli-seconds:
                                    Minimum = 0ms, Maximum = 0ms, Average = 0ms
                                    orafad wrote:
                                    What's the output from hostname command?
                                    an error: The procedure entry point s_perror could not be located in the dynamic link library MSWSOCK.dll.