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