This discussion is archived
1 2 3 4 Previous Next 52 Replies Latest reply: Feb 2, 2012 10:27 AM by 898236 RSS

how to remotely access to oracle database XE ?

898236 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    /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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    /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 Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points