9 Replies Latest reply on Apr 10, 2012 9:47 PM by orafad

    Oracle XE 11g on windows server 2008

    920466
      Hi All,

      I have installed Oracle express edition 11g (11.2.0.2.0) on windows server 2008.
      I am able to connect to the database from the server, but unable to connect from a client machine.

      I am using SQL developer by specifying the hostname or IP address and service name (not using tnsnames.ora file configuration in sql developer)

      Firewall has been modified for port 1521. Still there is some connectivity problem.

      I just read a post on asktom. Does the solution mentioned in the post, applies only to Oracle 8i DB or is applicable to 11g XE as well.
      http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:264415410962


      Please help me to resolve this connectivity problem.
        • 1. Re: Oracle XE 11g on windows server 2008
          orafad
          Sandeep Shenoy wrote:
          I am able to connect to the database from the server, but unable to connect from a client machine.
          I am using SQL developer by specifying the hostname or IP address and service name (not using tnsnames.ora file configuration in sql developer)
          Unable to connect how, exactly?

          What error message is reported?

          Please post lsnrctl stat output from XE.

          Forum for XE, {forum:id=251}, at least for future reference.
          • 2. Re: Oracle XE 11g on windows server 2008
            920466
            Unable to connect how, exactly?
            As mentioned, i am able to connect to the database from the server machine whereas unable to connect to the database from a client machine over the network.

            Required firewall modification is done for port 1521.


            What error message is reported?
            When tried using SQL developer from a client machine its giving a error message. Status: Failure - Test Failed: Io exception: The Network Adapter could not


            Listener is up and running fine. In fact restarted the listener couple of times :) Request packet is reaching the server over the network at port 1521 but not getting any response from the server machine.

            How does Oracle server serves the client request? The reply is sent via port 1521 or any other port is used?
            • 3. Re: Oracle XE 11g on windows server 2008
              920466
              Unable to connect how, exactly?
              As mentioned, i am able to connect to the database from the server machine whereas unable to connect to the database from a client machine over the network.

              Required firewall modification is done for port 1521.


              What error message is reported?
              When tried using SQL developer from a client machine its giving a error message. Status: Failure - Test Failed: Io exception: The Network Adapter could not


              Listener is up and running fine. In fact restarted the listener couple of times :) Request packet is reaching the server over the network at port 1521 but not getting any response from the server machine.

              How does Oracle server serves the client request? The reply is sent via port 1521 or any other port is used?
              • 4. Re: Oracle XE 11g on windows server 2008
                Udo
                Hi Sandeep,
                Required firewall modification is done for port 1521.
                Did you also make sure that the database listener is actually listening to the network interface your client is connecting from?
                orafad asked you to
                Please post lsnrctl stat output from XE.
                That's really necessary. Also, please verify that any hostname you see in that output is actually reachable from your remote client.

                -Udo
                1 person found this helpful
                • 5. Re: Oracle XE 11g on windows server 2008
                  orafad
                  Sandeep Shenoy wrote:
                  When tried using SQL developer from a client machine its giving a error message. Status: Failure - Test Failed: Io exception: The Network Adapter could not
                  That's a lot better than just "unable to connect". Unfortunately, Oracle SQL Developer does not report back completely in above situation.

                  In addition to lsnrctl output, could you post result from connection attempt with sqlplus from the client machine, such as:
                  C:\>sqlplus a/b@'serverhostname:1521/xe'
                  
                  SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 9 23:26:54 2012
                  
                  Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                  
                  ERROR:
                  ORA-12541: TNS:no listener
                  >
                  Listener is up and running fine.
                  We do not know that yet.

                  Post output from:

                  lsnrctl stat
                  • 6. Re: Oracle XE 11g on windows server 2008
                    920466
                    Given Below is the error, when i am trying to connect to the database from a hostmachine using SQL*Plus

                    +SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 10 10:48:02 2012

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

                    ERROR:
                    ORA-12170: TNS:Connect timeout occurred+


                    The lsnrctl stat ouput is as follows.

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

                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domainname.com)(PORT=1521)))
                    STATUS of the LISTENER
                    ------------------------
                    Alias LISTENER
                    Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
                    Start Date 22-MAR-2012 06:29:12
                    Uptime 18 days 15 hr. 49 min. 38 sec
                    Trace Level off
                    Security ON: Local OS Authentication
                    SNMP OFF
                    Default Service XE
                    Listener Parameter File C:\TEST\app\oracle\product\11.2.0\server\network\admin\listener.ora
                    Listener Log File C:\TEST\app\oracle\diag\tnslsnr\TEST\listener\alert\log.xml
                    Listening Endpoints Summary...
                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.domainname.com))(PORT=1521)))
                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.domainname.com))(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+
                    • 7. Re: Oracle XE 11g on windows server 2008
                      920466
                      Following is the error, when i am trying to connect to the database from a hostmachine using SQL*Plus

                      +SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 10 10:48:02 2012

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

                      ERROR:
                      ORA-12170: TNS:Connect timeout occurred+

                      The lsnrctl stat ouput is as follows.

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

                      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domainname.com)(PORT=1521)))
                      STATUS of the LISTENER
                      Alias LISTENER
                      Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
                      Start Date 22-MAR-2012 06:29:12
                      Uptime 18 days 15 hr. 49 min. 38 sec
                      Trace Level off
                      Security ON: Local OS Authentication
                      SNMP OFF
                      Default Service XE
                      Listener Parameter File C:\TEST\app\oracle\product\11.2.0\server\network\admin\listener.ora
                      Listener Log File C:\TEST\app\oracle\diag\tnslsnr\TEST\listener\alert\log.xml
                      Listening Endpoints Summary...
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.domainname.com))(PORT=1521)))
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.domainname.com))(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+
                      • 8. Re: Oracle XE 11g on windows server 2008
                        Udo
                        Uptime 18 days 15 hr. 49 min. 38 sec
                        In fact restarted the listener couple of times
                        Really? When did you make that firewall change?
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.domainname.com))(PORT=1521)))
                        You didn't answer if your hostname.domainname.com is actually reachable from your client.
                        Also, please check whether this FQDN is actually resolved equally on the database server itself (i.e., you get the same IP for this name locally and remotely).
                        You could try to change that configuration to just use the hostname. Often FQDNs do not work as exepcted...
                        The place to change would be in Listener Parameter File C:\TEST\app\oracle\product\11.2.0\server\network\admin\listener.ora
                        ORA-12170: TNS:Connect timeout occurred+
                        What's the output of
                        tnsping <tnsname>
                        from the client you get that error from? Is your XE instance configured in TNS on that client at all?

                        -Udo
                        1 person found this helpful
                        • 9. Re: Oracle XE 11g on windows server 2008
                          orafad
                          Sandeep Shenoy wrote:
                          ERROR:
                          ORA-12170: TNS:Connect timeout occurred
                          This may indicate a network config/firewall issue.

                          As Udo suggests above, check "hostname", etc. Make sure listener is listening on all IPs (use netstat).
                          1 person found this helpful