oci_connect() ORA-12170: TNS:Connect timeout occurred

Question

I need to connect to remote oracle database using oci_connect(). After waiting for several minutes I get this error.

$db =
  '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = XXXX))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = citi)
    )
  )';

$conn = oci_connect('username', 'password', $db);
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$stid = oci_parse($conn, 'SELECT * FROM Tmt');
oci_execute($stid);

I get this following error

PHP Warning:  oci_connect(): ORA-12170: TNS:Connect timeout occurred in /var/www/curiouslabx.com/public_html/wordpress/projects/test.php on line 14
Array
(
    [code] => 12170
    [message] => ORA-12170: TNS:Connect timeout occurred
    [offset] => 0
    [sqltext] => 
)

Answer 1

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.

• Reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT,SQLNET.OUTBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT,TCP.CONNECT_TIMEOUT in sqlnet.ora to larger values.

  • SQLNET.RECV_TIMEOUT - For environments in which clients shut down on occasion or abnormally, setting this parameter is recommended. If a client does not send any data in time specified, then the database server logs ORA-12535: TNS:operation timed out and ORA-12609: TNS: Receive timeout occurred messages to the sqlnet.log file. Without this parameter, the database server may continue to wait for data from clients that may be down or are experiencing difficulties.
  • SQLNET.SEND_TIMEOUT - If the database server cannot complete a send operation in the time specified, then it logs ORA-12535: TNS:operation timed out and ORA-12608: TNS: Send timeout occurred messages to the sqlnet.log file. Without this parameter, the database server may continue to send responses to clients that are unable to receive data due to a downed computer or a busy state.
  • SQLNET.INBOUND_CONNECT_TIMEOUT - If an Oracle Net connection is not established in the time specified, then the connect attempt is terminated. The client receives an ORA-12170: TNS:Connect timeout occurred error. The outbound connect timeout interval is a superset of the TCP connect timeout interval, which specifies a limit on the time taken to establish a TCP connection. Additionally, the outbound connect timeout interval includes the time taken to be connected to an Oracle instance providing the requested service. Without this parameter, a client connection request to the database server may block for the default TCP connect timeout duration (60 seconds) when the database server host system is unreachable. The outbound connect timeout interval is only applicable for TCP, TCP with SSL, and IPC transport connections.
  • SQLNET.INBOUND_CONNECT_TIMEOUT - If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection. In addition, the database server logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to the sqlnet.log file. The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message.
  • TCP.CONNECT_TIMEOUT - To specify the time, in seconds, for a client to establish a TCP connection (PROTOCOL=tcp in the TNS connect address) to the database server. If a TCP connection to the database host is not established in the time specified, then the connection attempt is terminated. The client receives an ORA-12170: TNS:Connect timeout occurred error. The timeout applies to each IP address to which a host name resolves. For example, if a host name resolves to an IPv6 and an IPv4 address, and if the host is not reachable through the network, then the connection request times out twice the TCP.CONNECT_TIMEOUT setting because there are two IP addresses. In this example, the default timeout setting of 60 would cause a timeout in 120 seconds.

• 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).

• Verify that your /etc/hosts file has a DNS entry: example XXX.XXX.XXX.XXX myhost

The sqlnet.ora file typically resides in $ORACLE_HOME/network/admin on UNIX platforms and %ORACLE_HOME%\NETWORK\ADMIN on Windows operating systems.

In Windows OS, your hosts file is located at %SystemRoot%\System32\drivers\etc\hosts and on Unix, Unix-like, POSIX, at /etc/hosts.

Credits: ORA-12170: TNS: Connect timeout occurred tips,Parameters for the sqlnet.ora File