10 Replies Latest reply on Apr 21, 2012 5:01 AM by 130179

    ORA-12170: TNS:Connect timeout occurred

    ivwvi
      Hi Experts,

      I am getting ORA-12170 while connecting to remote database.

      Please help
      SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      With the Partitioning and Automatic Storage Management options
      =======================================================================
      host_a:/home/siebel > 
      host_a:/home/siebel > sqlplus scott/tiger@dbtwo
      SQL*Plus: Release 11.2.0.1.0 Production on Fri May 21 08:45:41 2010
      Copyright (c) 1982, 2009, Oracle.  All rights reserved.
      ERROR:
      ORA-12170: TNS:Connect timeout occurred
      
      Enter user-name:
        • 1. Re: ORA-12170: TNS:Connect timeout occurred
          Gurjas
          This error is mainly associated with Firewall setting or slow Networks.
          Other possible reason for same is the timeout setting in listener.ora file (INBOUND_CONNECT_TIMEOUT_listener).
          1 person found this helpful
          • 2. Re: ORA-12170: TNS:Connect timeout occurred
            NavneetU
            The ORA-12170 error can be for many reasons, firewall issue, database down, listener down, bad sqlnet.ora parm, network trouble, etc.  The docs note:
            
                ORA-12170: TNS:Connect timeout occurred
            
                Cause: The server shut down because connection establishment or communication with a client failed to complete within the allotted time interval. This may be a result of network or system delays; or this may indicate that a malicious client is trying to cause a Denial of Service attack on the server.
            
                Action: If the error occurred because of a slow network or system, reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values. If a malicious client is suspected, use the address in sqlnet.log to identify the source and restrict access. Note that logged addresses may not be reliable as they can be forged (e.g. in TCP/IP).
            
            Your sqlnet.ora file can be found in your $ORACLE_HOME/network/admin directory.
            source: - http://www.dba-oracle.com/t_ora_12170_tns_connect_timeout.htm

            Regards
            1 person found this helpful
            • 3. Re: ORA-12170: TNS:Connect timeout occurred
              ivwvi
              alert log says

              I have 11.2.0.1 listener trace level set to support .
              I found listrener trace file but how to read it?
              3085  ***********************************************************************
                3086
                3087  Fatal NI connect error 12170.
                3088
                3089    VERSION INFORMATION:
                3090  TNS for HPUX: Version 11.2.0.1.0 - Production
                3091  Oracle Bequeath NT Protocol Adapter for HPUX: Version 11.2.0.1.0 - Produ
              ction
                3092  TCP/IP NT Protocol Adapter for HPUX: Version 11.2.0.1.0 - Production
                3093    Time: 13-MAY-2010 13:56:29
                3094    Tracing not turned on.
                3095    Tns error struct:
                3096      ns main err code: 12535
                3097
                3098  TNS-12535: TNS:operation timed out
              3384      ns secondary err code: 12560
                3385      nt main err code: 505
                3386
                3387  TNS-00505: Operation timed out
                3388      nt secondary err code: 238
                3389      nt OS err code: 0
                3390    Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.222.77.111)(PORT=35921
              ))
                3391  2010-05-13 20:18:29.002000 +02:00
                3392
                3393
                3394  ***********************************************************************
              • 4. Re: ORA-12170: TNS:Connect timeout occurred
                NavneetU
                Check if any firewall on the server is blocking the port. Also check your sqlnet.ora file for any entry specified in the above post.

                Regards
                1 person found this helpful
                • 5. Re: ORA-12170: TNS:Connect timeout occurred
                  Chinar
                  Hi,Some times this error occur because on server(Oracle server installed) running firewall,turn off firewall(or open listener port) and try again
                  1 person found this helpful
                  • 6. Re: ORA-12170: TNS:Connect timeout occurred
                    ivwvi
                    i have get firewall checked , there is no firewall issue.


                    It may be due to Dynamic Registraion Issue

                    LISTENER_EAI =
                      (DESCRIPTION_LIST =
                        (DESCRIPTION =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = primus)(PORT = 1527))
                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1527))
                        )
                      )
                    
                    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_EAI = ON
                    Even ther is no service in listener .ora


                    but listnere with service is running fine

                    oracle@primus:/app/oracle/diag/rdbms/eai/EAI/trace # lsnrctl status LISTENER_EAI
                    
                    LSNRCTL for HPUX: Version 11.2.0.1.0 - Production on 21-MAY-2010 13:05:20
                    
                    Copyright (c) 1991, 2009, Oracle.  All rights reserved.
                    
                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primus)(PORT=1527)))
                    STATUS of the LISTENER
                    ------------------------
                    Alias                     LISTENER_EAI
                    Version                   TNSLSNR for HPUX: Version 11.2.0.1.0 - Production
                    Start Date                13-MAY-2010 18:11:43
                    Uptime                    7 days 18 hr. 53 min. 37 sec
                    Trace Level               support
                    Security                  ON: Local OS Authentication
                    SNMP                      OFF
                    Listener Parameter File   /app/oracle/product/11.2.0/network/admin/listener.ora
                    Listener Log File         /app/oracle/diag/tnslsnr/primus/listener_eai/alert/log.xml
                    Listener Trace File       /app/oracle/diag/tnslsnr/primus/listener_eai/trace/ora_29694_1.trc
                    Listening Endpoints Summary...
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primus)(PORT=1527)))
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1527)))
                    Services Summary...
                    Service "EAI" has 1 instance(s).
                      Instance "EAI", status READY, has 1 handler(s) for this service...
                    Service "EAIXDB" has 1 instance(s).
                      Instance "EAI", status READY, has 1 handler(s) for this service...
                    The command completed successfully
                    • 7. Re: ORA-12170: TNS:Connect timeout occurred
                      NavneetU
                      have you checked sqlnet.ora file?

                      Regards
                      1 person found this helpful
                      • 8. Re: ORA-12170: TNS:Connect timeout occurred
                        ivwvi
                        content are :
                        # sqlnet.ora Network Configuration File: /app/oracle/product/11.2.0/network/admin/sqlnet.ora
                        # Generated by Oracle configuration tools.
                        
                        NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
                        
                        ADR_BASE = /app/oracle
                        • 9. Re: ORA-12170: TNS:Connect timeout occurred
                          Chinar
                          logged on server as root and "/etc/init.d/./iptables stop" then run from client "sqlplus scott/tiger@dbtwo" and post result there.
                          • 10. Re: ORA-12170: TNS:Connect timeout occurred
                            130179
                            Flushing the iptables has resolved my tnsping issue