14 Replies Latest reply: Aug 3, 2012 3:27 PM by EduardB RSS

    Remotely connect to Oracle Database 11gR2

    EduardB
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        [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
                          [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
                            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
                              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
                                [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
                                  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
                                    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 :)