This discussion is archived
14 Replies Latest reply: Aug 3, 2012 1:27 PM by EduardB RSS

Remotely connect to Oracle Database 11gR2

EduardB Newbie
Currently Being Moderated
Hi everyone,

I am a beginner aiming at becoming a DBA. Having no prior experience in Database administration, I suspect some of my questions will be pretty dumb, but once I learn, it's learned for good, so please be gentle :)
After installing Oracle Linux 6 Release 3 and Oracle Database 11gR2 on a dedicated machine, I have downloaded Navicat for Oracle (trial) and attempted to remotely connect to the database. So far I have been unsuccessful.

The Listener is UP and I can connect to https://localhost:1158/em just fine. Listener Net Address is configured as follows:
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
I have a gut feeling that "localhost" might not be enough to allow remote connections but whatever documentation I have read was a bit confusing. I also tried adding another listener which would use "HOST" entry as the full machine DNS name but that seemed to mess up EM completely.
On to the error message: every time I try to connect, I am getting "ORA-12541: TNS:no listener". But, as I was saying, the listener is UP.
  • 1. Re: Remotely connect to Oracle Database 11gR2
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl identify exact version of database you installed. Only 11.2.0.3 is certified/supported on Linux 6.x - previous versions may or may not work as advertised.

    11g instance creation database connection testing error with ORA-01882

    HTH
    Srini
  • 2. Re: Remotely connect to Oracle Database 11gR2
    sb92075 Guru
    Currently Being Moderated
    EduardB wrote:
    Hi everyone,

    I am a beginner aiming at becoming a DBA. Having no prior experience in Database administration, I suspect some of my questions will be pretty dumb, but once I learn, it's learned for good, so please be gentle :)
    After installing Oracle Linux 6 Release 3 and Oracle Database 11gR2 on a dedicated machine, I have downloaded Navicat for Oracle (trial) and attempted to remotely connect to the database. So far I have been unsuccessful.

    The Listener is UP and I can connect to https://localhost:1158/em just fine.
    above accesses the DB via webserver & browser.
    Listener Net Address is configured as follows:
    (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
    post results from following 2 OS commands

    lsnrctl status
    lsnrctl service

    COPY the results then PASTE all back here
    I have a gut feeling that "localhost" might not be enough to allow remote connections but whatever documentation I have read was a bit confusing. I also tried adding another listener which would use "HOST" entry as the full machine DNS name but that seemed to mess up EM completely.
    localhost allows & supports network connection only from DB Server;
    which is how I have my laptop configured.
  • 3. Re: Remotely connect to Oracle Database 11gR2
    sybrand_b Guru
    Currently Being Moderated
    localhost maps to 127.0.0.1, which is the local loopback address.
    This address is always on the local machine and you can not have any other machine connect to a different machine using 'localhost'

    TCP 101.

    So if you configured Oracle on 'localhost' other systems can not communicate with this database.

    ---------
    Sybrand Bakker
    Senior Oracle DBA
  • 4. Re: Remotely connect to Oracle Database 11gR2
    EduardB Newbie
    Currently Being Moderated
    Thank you for the VERY fast answers.
    srini: yes, 11.2.0.3 it is.
    sb92075: Your request opens a whole new discussion which I am interested in having: no matter what user do I try out (even root), I get this:
    [root@eburlacu-server bin]# ./lsnrctl status

    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-AUG-2012 22:04:33

    Copyright (c) 1991, 2009, 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
    I would be interested in finding out why this happens.

    The root cause was much simpler and much more basic, it was the Linux firewall, which was configured to accept no connections from anywhere. I have added the appropriate rules to iptables and then edited listener again, now it works perfectly. I can connect remotely just fine.
    >

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = eburlacu-server.ro.oracle.com)(PORT = 1522))
    )
    )
    )
  • 5. Re: Remotely connect to Oracle Database 11gR2
    sb92075 Guru
    Currently Being Moderated
    EduardB wrote:
    Thank you for the VERY fast answers.
    srini: yes, 11.2.0.3 it is.
    sb92075: Your request opens a whole new discussion which I am interested in having: no matter what user do I try out (even root), I get this:
    root user should NEVER be used against any Oracle software.
    ORACLE_HOME needs to be properly set.

    what is the SID for your DB?
  • 6. Re: Remotely connect to Oracle Database 11gR2
    EduardB Newbie
    Currently Being Moderated
    sb92075 wrote:
    EduardB wrote:
    Thank you for the VERY fast answers.
    srini: yes, 11.2.0.3 it is.
    sb92075: Your request opens a whole new discussion which I am interested in having: no matter what user do I try out (even root), I get this:
    root user should NEVER be used against any Oracle software.
    ORACLE_HOME needs to be properly set.

    what is the SID for your DB?
    the SID is eburlacu.
    I tried running lsnrctl from user "oracle" which owns the folder structure with the same result.
    ORACLE_HOME is set to /oracledb/product/11.2.0/dbhome_1/
  • 7. Re: Remotely connect to Oracle Database 11gR2
    sb92075 Guru
    Currently Being Moderated
    EduardB wrote:
    sb92075 wrote:
    EduardB wrote:
    Thank you for the VERY fast answers.
    srini: yes, 11.2.0.3 it is.
    sb92075: Your request opens a whole new discussion which I am interested in having: no matter what user do I try out (even root), I get this:
    root user should NEVER be used against any Oracle software.
    ORACLE_HOME needs to be properly set.

    what is the SID for your DB?
    the SID is eburlacu.
    I tried running lsnrctl from user "oracle" which owns the folder structure with the same result.
    ORACLE_HOME is set to /oracledb/product/11.2.0/dbhome_1/
    ORACLE_HOME=/oracledb/product/11.2.0/dbhome_1
    export ORACLE_HOME
    # ORACLE _HOME contains no trailing slash character!


    issue following OS commands

    ls -ltr $ORACLE_HOME/dbs
    id
    env | sort

    COPY the results then PASTE all back here
  • 8. Re: Remotely connect to Oracle Database 11gR2
    EduardB Newbie
    Currently Being Moderated
    [oracle@eburlacu-server ~]$ ls -ltr $ORACLE_HOME/dbs
    total 20
    -rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
    -rw-r-----. 1 oracle oinstall 24 Aug 3 18:39 lkEBURLACU
    -rw-rw----. 1 oracle oinstall 1544 Aug 3 18:45 hc_eburlacu.dat
    -rw-r-----. 1 oracle oinstall 1536 Aug 3 19:47 orapweburlacu
    -rw-r-----. 1 oracle oinstall 2560 Aug 3 22:11 spfileeburlacu.ora


    [oracle@eburlacu-server ~]$ id
    uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

    When I paste the "env | sort" output, I get "Content not allowed" from Oracle Forums.
  • 9. Re: Remotely connect to Oracle Database 11gR2
    EduardB Newbie
    Currently Being Moderated
    [oracle@eburlacu-server ~]$ env | sort
    _=/bin/env
    CVS_RSH=ssh
    G_BROKEN_FILENAMES=1
    HISTCONTROL=ignoredups
    HISTSIZE=1000
    HOME=/home/oracle
    HOSTNAME=eburlacu-server.ro.oracle.com
    KDEDIRS=/usr
    KDE_IS_PRELINKED=1
    LANG=en_US.UTF-8
    LESSOPEN=|/usr/bin/lesspipe.sh %s
    LOGNAME=oracle


    MAIL=/var/spool/mail/oracle
    ORACLE_HOME=/oracledb/product/11.2.0/dbhome_1
    PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
    PWD=/home/oracle
    QTDIR=/usr/lib64/qt-3.3
    QTINC=/usr/lib64/qt-3.3/include
    QTLIB=/usr/lib64/qt-3.3/lib
    SELINUX_LEVEL_REQUESTED=
    SELINUX_ROLE_REQUESTED=
    SELINUX_USE_CURRENT_RANGE=
    SHELL=/bin/bash
    SHLVL=1
    SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
    SSH_CLIENT=10.171.84.190 57486 22
    SSH_CONNECTION=10.171.84.190 57486 10.171.80.239 22
    SSH_TTY=/dev/pts/2
    TERM=xterm
    USER=oracle

    (LS_COLORS apparently contains disallowed content)

    Edited by: EduardB on Aug 3, 2012 1:06 PM
  • 10. Re: Remotely connect to Oracle Database 11gR2
    sb92075 Guru
    Currently Being Moderated
    EduardB wrote:
    [oracle@eburlacu-server ~]$ env | sort
    _=/bin/env
    CVS_RSH=ssh
    G_BROKEN_FILENAMES=1
    HISTCONTROL=ignoredups
    HISTSIZE=1000
    HOME=/home/oracle
    HOSTNAME=eburlacu-server.ro.oracle.com
    KDEDIRS=/usr
    KDE_IS_PRELINKED=1
    LANG=en_US.UTF-8
    LESSOPEN=|/usr/bin/lesspipe.sh %s
    LOGNAME=oracle


    MAIL=/var/spool/mail/oracle
    ORACLE_HOME=/oracledb/product/11.2.0/dbhome_1
    PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
    PWD=/home/oracle
    QTDIR=/usr/lib64/qt-3.3
    QTINC=/usr/lib64/qt-3.3/include
    QTLIB=/usr/lib64/qt-3.3/lib
    SELINUX_LEVEL_REQUESTED=
    SELINUX_ROLE_REQUESTED=
    SELINUX_USE_CURRENT_RANGE=
    SHELL=/bin/bash
    SHLVL=1
    SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
    SSH_CLIENT=10.171.84.190 57486 22
    SSH_CONNECTION=10.171.84.190 57486 10.171.80.239 22
    SSH_TTY=/dev/pts/2
    TERM=xterm
    USER=oracle

    (LS_COLORS apparently contains disallowed content)

    Edited by: EduardB on Aug 3, 2012 1:06 PM
    issue OS commands below

    export ORACLE_SID=eburlacu
    export ORACLE_BASE=/oracledb
    export PATH=$ORACLE_HOME/bin:$PATH
    lsnrctl status
    lsnrctl service

    COPY commands & results then PASTE all back here
  • 11. Re: Remotely connect to Oracle Database 11gR2
    EdStevens Guru
    Currently Being Moderated
    EduardB wrote:
    [oracle@eburlacu-server ~]$ env | sort
    _=/bin/env
    CVS_RSH=ssh
    G_BROKEN_FILENAMES=1
    HISTCONTROL=ignoredups
    HISTSIZE=1000
    HOME=/home/oracle
    HOSTNAME=eburlacu-server.ro.oracle.com
    KDEDIRS=/usr
    KDE_IS_PRELINKED=1
    LANG=en_US.UTF-8
    LESSOPEN=|/usr/bin/lesspipe.sh %s
    LOGNAME=oracle


    MAIL=/var/spool/mail/oracle
    ORACLE_HOME=/oracledb/product/11.2.0/dbhome_1
    PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
    PWD=/home/oracle
    QTDIR=/usr/lib64/qt-3.3
    QTINC=/usr/lib64/qt-3.3/include
    QTLIB=/usr/lib64/qt-3.3/lib
    SELINUX_LEVEL_REQUESTED=
    SELINUX_ROLE_REQUESTED=
    SELINUX_USE_CURRENT_RANGE=
    SHELL=/bin/bash
    SHLVL=1
    SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
    SSH_CLIENT=10.171.84.190 57486 22
    SSH_CONNECTION=10.171.84.190 57486 10.171.80.239 22
    SSH_TTY=/dev/pts/2
    TERM=xterm
    USER=oracle

    (LS_COLORS apparently contains disallowed content)

    Edited by: EduardB on Aug 3, 2012 1:06 PM
    That's ok, we don't care about those. Really, all you need for the necessary info is that related directly to oracle. This will get it:
    whoami
    id
    env | grep ORA | sort
    env | grep PATH
    It's extremely rare to need to see anything else in the environment.

    And sometimes the content filter just goes off on a tangent. Last week I couldn't even get a one word reply of "Yes" to go through.
  • 12. Re: Remotely connect to Oracle Database 11gR2
    EduardB Newbie
    Currently Being Moderated
    [oracle@eburlacu-server ~]$ lsnrctl status

    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-AUG-2012 23:12:38

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date 03-AUG-2012 20:54:51
    Uptime 0 days 2 hr. 17 min. 47 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /oracledb/product/11.2.0/dbhome_1/network/admin/listener.ora
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=eburlacu-server.ro.oracle.com)(PORT=1522)))
    Services Summary...
    Service "eburlacu.ro.oracle.com" has 1 instance(s).
    Instance "eburlacu", status READY, has 1 handler(s) for this service...
    Service "eburlacuXDB.ro.oracle.com" has 1 instance(s).
    Instance "eburlacu", status READY, has 1 handler(s) for this service...
    The command completed successfully


    [oracle@eburlacu-server ~]$ lsnrctl service

    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-AUG-2012 23:13:08

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    Services Summary...
    Service "eburlacu.ro.oracle.com" has 1 instance(s).
    Instance "eburlacu", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:164 refused:0 state:ready
    LOCAL SERVER
    Service "eburlacuXDB.ro.oracle.com" has 1 instance(s).
    Instance "eburlacu", status READY, has 1 handler(s) for this service...
    Handler(s):
    "D000" established:0 refused:0 current:0 max:1022 state:ready
    DISPATCHER <machine: eburlacu-server.ro.oracle.com, pid: 24497>
    (ADDRESS=(PROTOCOL=tcp)(HOST=eburlacu-server.ro.oracle.com)(PORT=56734))
    The command completed successfully

    Hey, this worked! Thank you very much!

    One last question: About exporting environment variables: how do I make them permanent and available to all users? If I add an environment variable from a user, it seems to not become available to other users.
  • 13. Re: Remotely connect to Oracle Database 11gR2
    sb92075 Guru
    Currently Being Moderated
    EduardB wrote:

    One last question: About exporting environment variables: how do I make them permanent and available to all users? If I add an environment variable from a user, it seems to not become available to other users.
    I create a file for each DB; in your case it would be "eburlacu.env"
    The content of this file would properly establish the environmental variables for its instance.
    See the environmental variable values change based upon ORACLE_SID & ORACLE_HOME values.
  • 14. Re: Remotely connect to Oracle Database 11gR2
    EduardB Newbie
    Currently Being Moderated
    Thank you again, I'll do that.

    You were all extremely helpful and I appreciate the help. It is very nice to see such a responsive community around. Hopefully I will become less of a questions guy and more of an answers guy in time :)

Legend

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