1 2 3 4 Previous Next 52 Replies Latest reply: Feb 2, 2012 12:27 PM by 898236 RSS

    how to remotely access to oracle database XE ?

    898236
      hello

      I have oracle database XE 10.2.0.1.2 installed on a centos 6.2 (~redhat).

      I would like to connect to it from a java program, and at the moment from dbschema, a database manager, graphical.

      I hava the port used for the TCP access by the server : 1521
      the url is : jdbc:orac:e:thin@<the address of my computer with dns-admin>:1521:XE .

      I can ping the address of my computer so this is good for this part.
      I disabled the firewall of the OS, and opened a port in my livebox : port 1521, protocol TCP.

      but when I try to connect from dbschema, I have this error :

      --------------------------------------------------------------------------------------------------------------------------------
      Error
      java.net.ConnectException: Connexion refusée oracle.net.ns.NetException: The Network Adapter could not establish the connection java.sql.SQLRecoverableException: Erreur d'E/S: The Network Adapter could not establish the connection
      Advice
      Please disable the Firewall on server if any. Do 'ping <hostname>' from command prompt to check the hostname. Make sure the Port is correct and the database is running.
      ---------------------------------------------------------------------------------------------------------------------------------

      I don't know what to do, and I would like to connect to my database tomorrow.

      can you help me?

      olivier

      Edited by: lolveley on 31 janv. 2012 13:46
        • 1. Re: (URGENT) how to remotely access to oracle database XE ?
          clcarter
          Double check the hostname/IP setup for the client.

          The firewall does not need to be disabled on the database host, but TCP port 1521 does need to be added to the exceptions list.

          The firewall on the client machine is not relevant.

          For accessing a 10g apex, remote connections are disabled by default, and port 8080 (or which ever port the http endpoint is running on) also needs to be added to the firewall exceptions list.

          Apex is about the only "database manager, graphical" available with XE, and most of the management items are removed if apex is upgraded. See "Enabling Remote HTTP Connection to the Database" at http://docs.oracle.com/cd/E17781_01/server.112/e18804/network.htm#ADMQS156

          After changing the connection type, the instance may need to reregister with the listener so that the change is enforced.

          Also note the HOST= endpoints in `lsnrctl status` as previously mentioned. One may know they are right, and read the items as correct when in fact they are not.

          From the database server, at the command line try a `tnsping xe` also a sqlnet connection via the tns alias that should already be configured.
          $ tnsping xe
          ... attempting to contact ... <connect string *host=* ...>
          OK (N msec)
          $ sqlplus /nolog
          connect system
          -- make it register
          alter system register;
          connect <user>@xe -- using tnsalias xe for network test
          password  ... 
           ... connected.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          • 2. Re: (URGENT) how to remotely access to oracle database XE ?
            898236
            well look at this :

            -----------------------------------------------------------------------------------------------------
            [olivier@new-host-3 bin]$ ./lsnrctl status

            LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 31-JAN-2012 21:58:08

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

            Message 1053 not found; No message file for product=network, facility=TNSMessage 1020 not found; No message file for product=network, facility=TNSMessage 1021 not found; No message file for product=network, facility=TNSMessage 1022 not found; No message file for product=network, facility=TNSMessage 1023 not found; No message file for product=network, facility=TNSMessage 1026 not found; No message file for product=network, facility=TNSMessage 1034 not found; No message file for product=network, facility=TNSMessage 1024 not found; No message file for product=network, facility=TNSMessage 1025 not found; No message file for product=network, facility=TNSMessage 1040 not found; No message file for product=network, facility=TNSMessage 1422 not found; No message file for product=network, facility=TNSMessage 1033 not found; No message file for product=network, facility=TNSMessage 1028 not found; No message file for product=network, facility=TNSMessage 1415 not found; No message file for product=network, facility=TNS Message 1050 not found; No message file for product=network, facility=TNS
            Message 1050 not found; No message file for product=network, facility=TNS
            Message 1050 not found; No message file for product=network, facility=TNS
            Message 1029 not found; No message file for product=network, facility=TNSMessage 1411 not found; No message file for product=network, facility=TNS
            Message 1408 not found; No message file for product=network, facility=TNS
            Message 1411 not found; No message file for product=network, facility=TNS
            Message 1408 not found; No message file for product=network, facility=TNS
            Message 1411 not found; No message file for product=network, facility=TNS
            Message 1408 not found; No message file for product=network, facility=TNS
            Message 1411 not found; No message file for product=network, facility=TNS
            Message 1408 not found; No message file for product=network, facility=TNS
            Message 1052 not found; No message file for product=network, facility=TNS[olivier@new-host-3 bin]$ ^C
            [olivier@new-host-3 bin]$ ./tnsping xe

            TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 31-JAN-2012 21:59:21

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

            Message 3511 not found; No message file for product=network, facility=TNSTNS-03505: Message 3505 not found; No message file for product=network, facility=TNS
            [olivier@new-host-3 bin]$
            -------------------------------------------------------------------------------------------------------------------
            I don't understand.
            apex works fine.

            could you tell me what to do with it?
            • 3. Re: (URGENT) how to remotely access to oracle database XE ?
              898236
              here is the listener.ora file :

              ---------------------------------------------------------------------------------------------------------# listener.ora Network Configuration File:

              SID_LIST_LISTENER =
              (SID_LIST =
              (SID_DESC =
              (SID_NAME = PLSExtProc)
              (ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
              (PROGRAM = extproc)
              )
              )

              LISTENER =
              (DESCRIPTION_LIST =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
              (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
              )
              )

              DEFAULT_SERVICE_LISTENER = (XE)
              • 4. Re: how to remotely access to oracle database XE ?
                orafad
                Please remove the "URGENT" part of the subject line/thread, as this is not appropriate in a forum of volunteers and there is no fire to put out.
                lolveley wrote:
                here is the listener.ora file :
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                I was just going through your other thread - {thread:id=2339708} - and found:

                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

                This is confirmed by the listener.ora config above.

                Localhost means, in general, the loopback interface which is on the local machine only. I.e. it does not listen for connections coming in remotely.

                Simply try renaming the listener.ora (listener.ora_moved) and bounce the listener. Now the hostname should be picked up instead, and a binding to "all interfaces" be made.
                Try, after a minute, lsnrctl stat again to see that the line with HOST parameter has changed.

                If it didn't help, move listener.ora back and change the config manually, replacing 'localhost' with hostname (e.g. see output of command hostname).

                Also as clcarter stated, you probably need to open for remote connections to XDB listener. See XE documentation about this, as steps should be clearly laid out, so no point in repeating here. Doc link posted is for 11.2 XE, but it should suffice.

                Edited by: orafad on Jan 31, 2012 10:45 PM
                • 5. Re: how to remotely access to oracle database XE ?
                  898236
                  hello

                  thank you for helping me.
                  I removed the URGENT from my first message

                  You sais to change the name of the listener.ora file; I know how to do this, but I'm not sure about how to remove the listener.
                  is it automatic when I change the name of the file?

                  olivier

                  ps : I'm new in oracle database management, maybe my question is basic... don't be severe!
                  • 6. Re: how to remotely access to oracle database XE ?
                    orafad
                    lolveley wrote:
                    could you tell me what to do with it?
                    Try:

                    $ export ORACLE_HOME=<Oracle Home's directory path>

                    It seems your path in this case is: /usr/lib/oracle/xe/app/oracle/product/10.2.0/server

                    Then run lsnrctl, tnsping, etc. commands.

                    Edited by: orafad on Jan 31, 2012 10:55 PM

                    Edited by: orafad on Jan 31, 2012 10:56 PM
                    • 7. Re: how to remotely access to oracle database XE ?
                      orafad
                      lolveley wrote:
                      I'm not sure about how to remove the listener.
                      Not remove, but restart. Try:

                      $ lsnrctl stop ; lsnrctl start
                      • 8. Re: how to remotely access to oracle database XE ?
                        898236
                        here is the result of the commands :

                        ---------------------------------------------------------------------------------------------------------------
                        [olivier@new-host-3 ~]$ cd $ORACLE_HOME/bin
                        [olivier@new-host-3 bin]$ ./lsnrctl status

                        LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 31-JAN-2012 22:50:50

                        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 Linux: Version 10.2.0.1.0 - Production
                        Start Date 31-JAN-2012 21:51:50
                        Uptime 0 days 0 hr. 59 min. 0 sec
                        Trace Level off
                        Security ON: Local OS Authentication
                        SNMP OFF
                        Default Service XE
                        Listener Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
                        Listener Log File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
                        Listening Endpoints Summary...
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1234))(Presentation=HTTP)(Session=RAW))
                        Services Summary...
                        Service "PLSExtProc" has 1 instance(s).
                        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                        Service "XE" has 1 instance(s).
                        Instance "XE", status READY, 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...
                        The command completed successfully
                        [olivier@new-host-3 bin]$ tnsping xe
                        bash: tnsping : commande introuvable
                        [olivier@new-host-3 bin]$ ./tnsping e

                        TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 31-JAN-2012 22:51:31

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

                        Used parameter files:

                        TNS-03505: Failed to resolve name
                        • 9. Re: how to remotely access to oracle database XE ?
                          orafad
                          Ok (in reverse order):

                          1 - the No message file / ORACLE_HOME problem seem to be fixed.

                          2 - the hostname got picked up - good; hostname configuration may be incorrect - not good.


                          Please post /etc/hosts.
                          • 10. Re: how to remotely access to oracle database XE ?
                            898236
                            /etc/hosts :

                            ------------------------
                            127.0.0.1          localhost.localdomain localhost new-host-3
                            ::1          localhost6.localdomain6 localhost6
                            -------------------------

                            and here is the result of some commands, in a session with my username,

                            -----------------------
                            [olivier@new-host-3 bin]$ ./lsnrctl stop

                            LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 31-JAN-2012 22:57:32

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

                            Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
                            TNS-12541: TNS:no listener
                            TNS-12560: TNS:protocol adapter error
                            TNS-00511: No listener
                            Linux Error: 111: Connection refused
                            [olivier@new-host-3 bin]$ ./lsnrctl start

                            LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 31-JAN-2012 22:58:29

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

                            Starting /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/tnslsnr: please wait...

                            TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                            NL-00280: error creating log stream /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
                            NL-00278: cannot open log file
                            SNL-00016: snlfohd: error opening file
                            Linux Error: 13: Permission denied

                            Listener failed to start. See the error message(s) above...

                            ---------------------------
                            I tried to change in oracle user, or root user, but in vain.
                            • 11. Re: how to remotely access to oracle database XE ?
                              orafad
                              lolveley wrote:
                              /etc/hosts :

                              ------------------------
                              127.0.0.1          localhost.localdomain localhost new-host-3
                              ::1          localhost6.localdomain6 localhost6
                              -------------------------
                              I believe, for remote connectivity, first line should read:
                              <routable IP of your machine> new-host-3

                              Also remove new-host-3 as alias from the loopback/127.0.0.1 line.
                              and here is the result of some commands, in a session with my username,
                              Run as "oracle" user i.e. the user you choose as Oracle software owner for the installation. (See owner of oracle executable.)
                              TNSLSNR for Linux: Version 10.2.0.1.0 - Production
                              NL-00280: error creating log stream /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
                              NL-00278: cannot open log file
                              SNL-00016: snlfohd: error opening file
                              Linux Error: 13: Permission denied
                              Permissions on the listener.log file and log directory?

                              What error, if any, do you get from running lsnrctl command as "oracle" user?
                              • 12. Re: how to remotely access to oracle database XE ?
                                898236
                                I made the changes in /etc/hosts.
                                it looks like this:
                                -----------------------------
                                lolveley.<name of a site at dyndns>.org new-host-3
                                127.0.0.1          localhost.localdomain localhost
                                ::1          localhost6.localdomain6 localhost6
                                ---------------------------
                                Have I to reboot?


                                here is the result of the command as oracle user (oracle is the owner of the "oracle" command in $ORACLE_HOME/bin directory) :

                                -------------------
                                [olivier@new-host-3 admin]$ su - oracle
                                Mot de passe :
                                -bash-4.1$ ls
                                app Documents Modèles oradata Téléchargements
                                Bureau Images Musique Public Vidéos
                                -bash-4.1$ cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
                                -bash-4.1$ cd bin/
                                -bash-4.1$ ./lsnrctl stop

                                LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 31-JAN-2012 23:23:21

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

                                Message 1053 not found; No message file for product=network, facility=TNSTNS-12541: Message 12541 not found; No message file for product=network, facility=TNS
                                TNS-12560: Message 12560 not found; No message file for product=network, facility=TNS
                                TNS-00511: Message 511 not found; No message file for product=network, facility=TNS
                                Linux Error: 111: Connection refused
                                ----------------------------

                                Edited by: lolveley on 31 janv. 2012 14:34
                                • 13. Re: how to remotely access to oracle database XE ?
                                  clcarter
                                  /etc/hosts ... looks like this:
                                  lolveley.<name of a site at dyndns>.org new-host-3
                                  And the IP for your host? You've got an FQDN and (short) hostname "new-host-3" there also needs to be an IP address. A hosts entry needs an IP and the name(s) for the addrress.

                                  The IP should show in the IPv4 of an ifconfig -a (?) if that works for your linux host, and verify name resolution for all names and the IP as well.
                                  $ ifconfig -a
                                  ... en0 ... or eth0 ...
                                  ... inet addr:N.N.N.N bcast:N.N.N.255 ...
                                  $ nslookup -sil new-host-3
                                  ...
                                  $ nslookup -sil lolveley.<name of a site at dyndns>.org
                                  ...
                                  $ nslookup -sil N.N.N.N
                                  ...
                                  Or something similar. Might also need to add /sbin/ to use the full path to ifconfig.
                                  • 14. Re: how to remotely access to oracle database XE ?
                                    orafad
                                    lolveley wrote:
                                    lolveley.<name of a site at dyndns>.org new-host-3
                                    First argument should be the IP address, not name.

                                    Second should be a fqdn, so probably the dyndns name. (I gave as example alias new-host-3 from your previous posts, but you may want to remove it, you'll have to decide.)

                                    This thread, {thread:id=2335934}, discusses both fqdn and profile settings (see below), so it could be worth a read.

                                    However, If the IP is dynamically assigned, I don't see how you would successfully host a server. Try for now, but know that it may work today but not tomorrow. A static IP is most likely a better choice, if not required.
                                    bash-4.1$ ./lsnrctl stop
                                    Message 1053 not found; No message file for product=network, facility=TNSTNS-12541: Message 12541 not found; No message file for product=network, facility=TNS
                                    TNS-12560: Message 12560 not found; No message file for product=network, facility=TNS
                                    TNS-00511: Message 511 not found; No message file for product=network, facility=TNS
                                    Linux Error: 111: Connection refused
                                    ----------------------------
                                    ORACLE_HOME need to be set for every login. Putting it in a profile setting would be a good idea.

                                    Listener is not started so no need stopping it.
                                    Connection refused may indicate a firewall issue, but maybe we can save that for later.


                                    Edit:
                                    Sorry, clcarter already explained the ip/name config above but I saw it only after I've posted my reply.

                                    Edited by: orafad on Jan 31, 2012 11:53 PM
                                    1 2 3 4 Previous Next