1 2 Previous Next 18 Replies Latest reply: Apr 20, 2013 12:27 PM by 1004280 RSS

    Can't connect to Oracle server with SQL Developer

    1004280
      I just installed Oracle 11g XE on Oracle Linux 6. I just want to connect to this database from a Windows computer using Oracle SQL Developer. I've set up my Connection in SQL developer as follows:

      Username: system
      Password: <password>
      ConnectionType: Basic
      Role: default
      Hostname: 192.168.1.104 (the address of the Oracle Linux machine)
      Port: 1521
      SID: XE

      When I click "Test", the error comes up: "IO Error: The Network Adapter could not establish the connection. Vendor code 17002".
      Below I have supplied a bunch of relevant information about the Oracle XE server. Please help me to figure out what is wrong. Thanks in advance for any advice you can offer.

      -----

      lsnrctl service:
      LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 19-APR-2013 13:50:59

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
      Services Summary...
      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
      The command completed successfully

      -----

      lsnrctl status:
      LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 19-APR-2013 13:47:05

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
      STATUS of the LISTENER

      Alias LISTENER
      Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      Start Date 19-APR-2013 12:22:16
      Uptime 0 days 1 hr. 24 min. 48 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Default Service XE
      Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
      Listener Log File /u01/app/oracle/diag/tnslsnr/cdata4/listener/alert/log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cdata4.valmarc.com)(PORT=1521)))
      Services Summary...
      Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully

      -----

      listener.ora:
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = cdata4.valmarc.com)(PORT = 1521))
      )
      )

      DEFAULT_SERVICE_LISTENER = (XE)

      ------

      tnsnames.ora
      XE =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cdata4.valmarc.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)
      )
      )

      -----

      Edited by: 1001277 on Apr 19, 2013 6:57 PM

      Edited by: 1001277 on Apr 19, 2013 6:58 PM
        • 1. Re: Can't connect to Oracle server with SQL Developer
          sb92075
          Is SQL Developer installed on the same system as the DB Server?

          what you posted shows no evidence that the XE database is up & online.

          is the OS Service for the XE started & running now?

          can you start the XE database?

          post the last 100 or so lines from the alert_XE.log file.
          • 2. Re: Can't connect to Oracle server with SQL Developer
            1004280
            Thank you for your assistance. Sql Developer and the DB server are on different machines. DB server is on an Oracle Linux machine (no GUI), and SQL developer is on a Windows 7 machine. The two machines are on a local LAN together.

            If I log on to the DB server machine, the I can connect to the database by simply typing "sqlplus system". And it asks for my password and says I'm connected.

            The OS service seems to be running:
            [cdata4 ~]$ ps -ef | grep oracle
            oracle 1672 1 0 Apr19 ? 00:00:02 /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr LISTENER -inherit
            oracle 1742 1 0 Apr19 ? 00:00:06 xe_pmon_XE
            oracle 1744 1 0 Apr19 ? 00:00:08 xe_psp0_XE
            oracle 1746 1 0 Apr19 ? 00:00:09 xe_vktm_XE
            oracle 1750 1 0 Apr19 ? 00:00:01 xe_gen0_XE
            oracle 1752 1 0 Apr19 ? 00:00:01 xe_diag_XE
            oracle 1754 1 0 Apr19 ? 00:00:01 xe_dbrm_XE
            oracle 1756 1 0 Apr19 ? 00:00:16 xe_dia0_XE
            oracle 1758 1 0 Apr19 ? 00:00:01 xe_mman_XE
            oracle 1760 1 0 Apr19 ? 00:00:01 xe_dbw0_XE
            oracle 1762 1 0 Apr19 ? 00:00:01 xe_lgwr_XE
            oracle 1764 1 0 Apr19 ? 00:00:05 xe_ckpt_XE
            oracle 1766 1 0 Apr19 ? 00:00:02 xe_smon_XE
            oracle 1768 1 0 Apr19 ? 00:00:00 xe_reco_XE
            oracle 1770 1 0 Apr19 ? 00:00:07 xe_mmon_XE
            oracle 1772 1 0 Apr19 ? 00:00:07 xe_mmnl_XE
            oracle 1774 1 0 Apr19 ? 00:00:00 xe_d000_XE
            oracle 1776 1 0 Apr19 ? 00:00:00 xe_s000_XE
            oracle 1778 1 0 Apr19 ? 00:00:00 xe_s001_XE
            oracle 1780 1 0 Apr19 ? 00:00:00 xe_s002_XE
            oracle 1782 1 0 Apr19 ? 00:00:00 xe_s003_XE
            oracle 1888 1 0 Apr19 ? 00:01:27 xe_vkrm_XE
            oracle 1890 1 0 Apr19 ? 00:00:00 xe_qmnc_XE
            oracle 2038 1 0 Apr19 ? 00:00:06 xe_cjq0_XE
            oracle 2079 1 0 Apr19 ? 00:00:00 xe_q000_XE
            oracle 2081 1 0 Apr19 ? 00:00:00 xe_q001_XE
            oracle 2235 1 0 Apr19 ? 00:00:01 xe_smco_XE
            oracle 28790 1 0 01:05 ? 00:00:00 xe_w001_XE
            oracle 28940 1 0 01:51 ? 00:00:00 xe_w000_XE

            -----

            alertXE.log:_
            Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora
            System parameters with non-default values:
            sessions = 172
            memory_target = 1G
            control_files = "/u01/app/oracle/oradata/XE/control.dbf"
            compatible = "11.2.0.0.0"
            db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
            db_recovery_file_dest_size= 10G
            undo_management = "AUTO"
            undo_tablespace = "UNDOTBS1"
            remote_login_passwordfile= "EXCLUSIVE"
            dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
            shared_servers = 4
            job_queue_processes = 4
            audit_file_dest = "/u01/app/oracle/admin/XE/adump"
            db_name = "XE"
            open_cursors = 300
            diagnostic_dest = "/u01/app/oracle"
            Fri Apr 19 12:25:33 2013
            PMON started with pid=2, OS id=1742
            Fri Apr 19 12:25:33 2013
            PSP0 started with pid=3, OS id=1744
            Fri Apr 19 12:25:34 2013
            VKTM started with pid=4, OS id=1746
            VKTM running at (100ms) precision
            Fri Apr 19 12:25:34 2013
            GEN0 started with pid=5, OS id=1750
            Fri Apr 19 12:25:34 2013
            DIAG started with pid=6, OS id=1752
            Fri Apr 19 12:25:34 2013
            DBRM started with pid=7, OS id=1754
            Fri Apr 19 12:25:34 2013
            DIA0 started with pid=8, OS id=1756
            Fri Apr 19 12:25:34 2013
            MMAN started with pid=9, OS id=1758
            Fri Apr 19 12:25:34 2013
            DBW0 started with pid=10, OS id=1760
            Fri Apr 19 12:25:34 2013
            LGWR started with pid=11, OS id=1762
            Fri Apr 19 12:25:34 2013
            CKPT started with pid=12, OS id=1764
            Fri Apr 19 12:25:34 2013
            SMON started with pid=13, OS id=1766
            Fri Apr 19 12:25:34 2013
            RECO started with pid=14, OS id=1768
            Fri Apr 19 12:25:35 2013
            MMON started with pid=15, OS id=1770
            starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
            Fri Apr 19 12:25:35 2013
            MMNL started with pid=16, OS id=1772
            starting up 4 shared server(s) ...
            ORACLE_BASE from environment = /u01/app/oracle
            Fri Apr 19 12:25:35 2013
            Successful mount of redo thread 1, with mount id 2694264063
            Database mounted in Exclusive Mode
            Lost write protection disabled
            Completed: ALTER DATABASE MOUNT
            Fri Apr 19 12:25:40 2013
            ALTER DATABASE OPEN
            Beginning crash recovery of 1 threads
            Started redo scan
            Completed redo scan
            read 410 KB redo, 134 data blocks need recovery
            Started redo application at
            Thread 1: logseq 2, block 3648
            Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
            Mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_8q2vbz0c_.log
            Completed redo application of 0.29MB
            Completed crash recovery at
            Thread 1: logseq 2, block 4469, scn 375764
            134 data blocks read, 134 data blocks written, 410 redo k-bytes read
            Thread 1 advanced to log sequence 3 (thread open)
            Thread 1 opened at log sequence 3
            Current log# 1 seq# 3 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_8q2vbt67_.log
            Successful open of redo thread 1
            SMON: enabling cache recovery
            [1886] Successfully onlined Undo Tablespace 2.
            Undo initialization finished serial:0 start:4294310700 end:4294310950 diff:250 (2 seconds)
            Verifying file header compatibility for 11g tablespace encryption..
            Verifying 11g file header compatibility for tablespace encryption completed
            SMON: enabling tx recovery
            Database Characterset is AL32UTF8
            Opening with Resource Manager plan: INTERNAL_PLAN_XE
            Starting background process VKRM
            Fri Apr 19 12:25:43 2013
            VKRM started with pid=23, OS id=1888
            replication_dependency_tracking turned off (no async multimaster replication found)
            Starting background process QMNC
            Fri Apr 19 12:25:43 2013
            QMNC started with pid=24, OS id=1890
            Completed: ALTER DATABASE OPEN
            Fri Apr 19 12:25:47 2013
            db_recovery_file_dest_size of 10240 MB is 0.98% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup.
            Fri Apr 19 12:25:47 2013
            Starting background process CJQ0
            Fri Apr 19 12:25:47 2013
            CJQ0 started with pid=22, OS id=2038
            Fri Apr 19 12:30:44 2013
            Starting background process SMCO
            Fri Apr 19 12:30:44 2013
            SMCO started with pid=25, OS id=2235
            Fri Apr 19 13:11:12 2013
            XDB installed.
            XDB initialized.
            Fri Apr 19 22:00:00 2013
            Setting Resource Manager plan SCHEDULER[0x30FB]:DEFAULT_MAINTENANCE_PLAN via scheduler window

            -----
            • 3. Re: Can't connect to Oracle server with SQL Developer
              sb92075
              It appears that DB & listerener are OK, but you can't get out from Windows system.

              open Command Windows & issue OS command below

              ping 192.168.1.104
              • 4. Re: Can't connect to Oracle server with SQL Developer
                1004280
                I can definitely ping between the two machines. I also have SSHD setup on the Oracle Linux machine and I can SSH from the Windows machine into the Oracle Linux machine. In fact, that is how I've been accessing the Linux machine since there is no monitor attached to it right now.
                • 5. Re: Can't connect to Oracle server with SQL Developer
                  1004280
                  I can't seem to even connect using the network from within the Linux machine. For example if I log into the Linux machine and type the follow command:

                  [cdata4 ~]$ sqlplus system@192.168.1.104

                  SQL*Plus: Release 11.2.0.2.0 Production on Sat Apr 20 02:24:26 2013

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

                  Enter password:
                  ERROR:
                  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
                  • 6. Re: Can't connect to Oracle server with SQL Developer
                    sb92075
                    I don't think the connection request ever departs the Windows system.
                    every connection request that gets to the listener gets logged.
                    packet sniff using Ethereal or WireShark to determine if any packets ever get on the network cable.
                    If not, you have some basic OS/Networking issue on the Windows system.
                    • 7. Re: Can't connect to Oracle server with SQL Developer
                      1004280
                      But I can't connect (using the network) even from the Linux machine. I think maybe I should get that working first. Is my above command that I run from Linux OK?

                      E.g.

                      cdata4~$ sqlplus system@192.168.1.104/XE
                      -or-
                      cdata4~$ sqlplus system@localhost/XE

                      both of the above commands fail with the error "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor". But,

                      cdata4~$ sqplus system

                      works ok. It seems like it isn't really listening on the IP network.
                      • 8. Re: Can't connect to Oracle server with SQL Developer
                        sb92075
                        ORA-12514 ALWAYS only occurs due to a problem on DB Server system.
                        One cause of this problem is when the Oracle database is down & needs to be started.
                        A remote client send a request to the Listener asking to be connected to a specific service.
                        If/when the listener does not know anything about that service, the listener responds with ORA-12514

                        post results from the following two OS commands:
                        lsnrctl status
                        lsnrctl service

                        Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

                        This line contains valuable debugging details. So post this line & surrounding lines.

                        for additional debugging suggestions read the URL below:
                        http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

                        below is sample for correct way to connect via SQL*Net

                        sqlplus scott/tiger@XE
                        • 9. Re: Can't connect to Oracle server with SQL Developer
                          1004280
                          I posted the results from "lsnrctl status" and "lsnrctl service" in my first post, see there for the results.

                          I've taken the windows machine completely out of the equation. I'm now working only on the Linux machine and trying to connect from there (using the network).

                          The log file that you mentioned (oracle/diag/rdbms/xe/XE/trace/alert_XE.log) does not seem to be the right file for listener requests.
                          The correct file for listener logs seems to be: oracle/diag/tnslsnr/cdata4/listener/alert/log.txt

                          Watching this different log file, when I try to connect (from the Linux CLI) using "sqlplus system@localhost/XE", the following appears in the log file:

                          <msg time='2013-04-20T02:44:27.349-04:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='cdata4.valmarc.com' host_addr='68.233.165.53'>
                          <txt>20-APR-2013 02:44:27 * (CONNECT_DATA=(SERVICE_NAME=XE)(CID=(PROGRAM=sqlplus)(HOST=cdata4.valmarc.com)(USER=ncr))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=47306)) * establish * XE * 12514
                          </txt>
                          </msg>
                          <msg time='2013-04-20T02:44:27.349-04:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='cdata4.valmarc.com' host_addr='68.233.165.53'>
                          <txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
                          </txt>
                          </msg>
                          • 10. Re: Can't connect to Oracle server with SQL Developer
                            sb92075
                            deltamind106 wrote:
                            I posted the results from "lsnrctl status" and "lsnrctl service" in my first post, see there for the results.
                            The listener does NOT know about any database; including XE!
                            databases register themselves with listener every minute.
                            I've taken the windows machine completely out of the equation. I'm now working only on the Linux machine and trying to connect from there (using the network).

                            The log file that you mentioned (oracle/diag/rdbms/xe/XE/trace/alert_XE.log) does not seem to be the right file for listener requests.
                            The correct file for listener logs seems to be: oracle/diag/tnslsnr/cdata4/listener/alert/log.txt

                            Watching this different log file, when I try to connect (from the Linux CLI) using "sqlplus system@localhost/XE", the following appears in the log file:

                            <msg time='2013-04-20T02:44:27.349-04:00' org_id='oracle' comp_id='tnslsnr' type='UNKNOWN' level='16' host_id='cdata4.valmarc.com' host_addr='68.233.165.53'>
                            <txt>20-APR-2013 02:44:27 * (CONNECT_DATA=(SERVICE_NAME=XE)(CID=(PROGRAM=sqlplus)(HOST=cdata4.valmarc.com)(USER=ncr))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=47306)) * establish * XE * 12514
                            </txt>
                            </msg>
                            SNAFU!
                            Extreme network confusion. Look above to 68.233.165.53 & 127.0.0.1
                            PLUS you previously mentioned a 192.168 address.
                            127.0.0.1 can NEVER be accessed by any remote client.

                            rename, remove, delete, or otherwise eliminate any & all listener.ora files; then reboot the *NIX DB server                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                            • 11. Re: Can't connect to Oracle server with SQL Developer
                              1004280
                              Regarding 127.0.0.1 being unusable, I will just use 192.168.1.104 for now to avoid that.

                              It is true that if you run "nslookup cdata4.valmarc.com" (the FQDN) the Internet's DNS servers will report the 68.233.165.53 address. However, the computer is actually sitting inside a local subnet with an actual IP address of 192.168.1.104. Since our company firewall will not forward port 1521 traffic from the public address 68.233.165.53 to the internal sub-net 192.168.1.104, it is not possible to use 68.233.165.53 to access this computer. What is Oracle's recommendation for this common network scenario?
                              • 12. Re: Can't connect to Oracle server with SQL Developer
                                sb92075
                                deltamind106 wrote:
                                Regarding 127.0.0.1 being unusable, I will just use 192.168.1.104 for now to avoid that.

                                It is true that if you run "nslookup cdata4.valmarc.com" (the FQDN) the Internet's DNS servers will report the 68.233.165.53 address. However, the computer is actually sitting inside a local subnet with an actual IP address of 192.168.1.104. Since our company firewall will not forward port 1521 traffic from the public address 68.233.165.53 to the internal sub-net 192.168.1.104, it is not possible to use 68.233.165.53 to access this computer. What is Oracle's recommendation for this common network scenario?
                                Oracle has NO networking ability or requirements.
                                Oracle DB does not require any listener to exist.
                                Oracle depends upon OS system & network for any & all packet exchanges.
                                If/when hostnames are used, they must get "properly" resolved in order for SQL*Net to succeed.

                                I suggest that you start the listener when no listener.ora file exists; then see what gets reported from below

                                lsnrctl status
                                lsnrctl service
                                • 13. Re: Can't connect to Oracle server with SQL Developer
                                  1004280
                                  Well, for sure Oracle is somehow retrieving the FQDN of the computer from the OS, and is then trying to use DNS to resolve that FQDN to an IP address. That's the only way it could possibly find out about the 68.233.165.53 address, since that address is not known anywhere on the computer (/etc/hosts or elsewhere).

                                  Anyways, I did stop Oracle, removed the listener.ora file, and the re-started Oracle.
                                  It took over a minute to start, hanging for a minute on the message "Starting Oracle Net Listener".
                                  The command "lsnrctl service" hung, and after 5 minutes I eventually killed it.

                                  The command "lsnrctl status" also hung for a while, but eventually printed:
                                  [cdata4 mts]$ lsnrctl status

                                  LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-APR-2013 03:53:29

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

                                  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                                  TNS-12535: TNS:operation timed out
                                  TNS-12560: TNS:protocol adapter error
                                  TNS-00505: Operation timed out
                                  • 14. Re: Can't connect to Oracle server with SQL Developer
                                    sb92075
                                    OS/networking mis-configuration exists.

                                    issue OS commands below

                                    cat /etc/hosts
                                    uname -a
                                    hostname
                                    /sbin/ifconfig

                                    COPY the results from above, then PASTE all back here
                                    1 2 Previous Next