4 Replies Latest reply on Jul 26, 2017 3:02 PM by mxallen-Oracle

    Oracle database connectivity from a remote session

    888081

      Hi,

      I have a 12.1.0.2 single instance database running on Linux server.  Port - 1521

      I am not able to connect to the database from my local system.

       

      sqlnet.ora contents

      NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

      SQLNET.EXPIRE_TIME=5

       

      tnsnames.ora contents (on both the server and remote machine)

      SIT =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = SIT)

          )

        )

       

      listener.ora contents

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01)(PORT = 1521))

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

        )

       

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (GLOBAL_DBNAME = SIT)

            (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)

            (SID_NAME = SIT)

          )

        )

       

       

      LISTENER status:

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver01)(PORT=1521)))

      STATUS of the LISTENER

      ------------------------

      Alias                     listener

      Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production

      Start Date                26-JUL-2017 23:46:09

      Uptime                    0 days 0 hr. 18 min. 22 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Parameter File   /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora

      Listener Log File         /u01/app/oracle/diag/tnslsnr/dbserver01/listener/alert/log.xml

      Listening Endpoints Summary...

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

        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

      Services Summary...

      Service "SIT" has 2 instance(s).

        Instance "SIT", status UNKNOWN, has 1 handler(s) for this service...

        Instance "SIT", status READY, has 1 handler(s) for this service...

      Service "SITXDB" has 1 instance(s).

        Instance "SIT", status READY, has 1 handler(s) for this service...

      The command completed successfully

       

       

      When I do a ping from the local server using the IP address, it works.  But when I do a ping using the server name, I get "Ping request could not find host dbserver01. Please check the name and try again".

      I tried changing the hostname to the IP address in the listener.ora and tnsnames.ora file, still no success.

       

      Using IP address:

      C:> sqlplus system/xxxx@SIT

      TNS-12535: TNS:operation timed out

       

      Using hostname:

      C:> sqlplus system/xxxx@SIT

      ORA-12545: Connect failed because target host or object does not exist

        • 1. Re: Oracle database connectivity from a remote session
          888081

          When  I do a telnet from my local system for port 1521, it does not work.  Being the default port, should'nt 1521 be open already ?

          Could not open connection to the host, on port 1521: Connect failed

          • 2. Re: Oracle database connectivity from a remote session
            Gaz in Oz

            -->Being the default port, should'nt 1521 be open already ?

            No. It can depend on your OS, version... how you installed it, what options you chose, didn't choose.

             

            You will have to open up port 1521 on your server. There are various ways to do this.

            iptables is one way.

            Take a look at the output from

            iptables -S

            Anything above the line

            -A INPUT -j REJECT --reject-with icmp-host-prohibited

            with "-j ACCEPT" is open.

             

            Here's how to allow hosts with ip range 192.168.1.0 to 192.168.1.255, using iptables:

            # iptables -I INPUT -s 192.168.1.0/24 -p tcp --dport 1521 -j ACCEPT
            1 person found this helpful
            • 3. Re: Oracle database connectivity from a remote session
              EdStevens

              888081 wrote:

               

               

              <snip>

               

               

              When I do a ping from the local server using the IP address, it works. But when I do a ping using the server name, I get "Ping request could not find host dbserver01. Please check the name and try again".

              If you had actually shown us those ping commands and their results, it would have provided very useful information for helping you.  You could obfuscate the actual ip address and/or server name if you feel the need to.

              Don't tell us what you think you did. Show us what you actually did.  Copy and paste are your friends.

               

               

              I tried changing the hostname to the IP address in the listener.ora and tnsnames.ora file, still no success.

               

              Using IP address:

              C:> sqlplus system/xxxx@SIT

              TNS-12535: TNS:operation timed out

               

              Using hostname:

              C:> sqlplus system/xxxx@SIT

              ORA-12545: Connect failed because target host or object does not exist

              Sounds like a firewall issue to me.  And possibly a name resolution issue.  If you had actually shown us your 'ping' commands we'd know more about that.  Also 'tnsping sit'  would reveal a lot of diagnostic info.

              • 4. Re: Oracle database connectivity from a remote session
                mxallen-Oracle

                This thread is actually in the incorrect community. This issue is Oracle Net related.

                I do agree with Ed, this does indeed sound like a probable firewall related issue.  Ed is also correct stating that if you show us what you did, it makes it easier to give

                a meaningful answer.

                Finally, what happens when you tnsping SIT? ( as Ed asked)  I didn't see if you have tried this.

                 

                Matt