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 位用户发现它有用
          • 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