Oracle Connect timeout occurred

[Ynr0225]

Hi all,

While creating gateway connection for oracle data source i am getting the below error message. Could you please any one help me on how to troubleshoot the issue.

Oracle: ORA-12170: TNS:Connect timeout occurred

Thanks in advance

[v-luwang-msft · Accepted solution]

Hi @Ynr0225 ,

There are several possible causes for error ORA-12170. They include: problems with the firewall, when the database is down, when the listener is down, if the sqlnet.ora parm is invalid, among other possible causes.

To fix the error, the user should consider all possible causes and their corresponding solutions on how to get rid of the error. The solution to error ORA-12170 will emerge through process of elimination.

Check to make sure there is no firewall in place between the client and server. If there is, disable the firewall and try again

If the error occurred because there was a delay in the system or network, turn on tracing to determine exactly where clients are timing out.

It may be the case that the Oracle service is running on an IP address while the host is configured to another IP address. To determine the IP address of the Oracle service, issue an lsnrctl status command. Check the address that is reported. To determine the IP address of the host, issue an ipconfig or ifconfig. This is dependent on your operation system; use ipconfig for Windows OS and ifconfig for Linux. To avoid seeing error ORA-12170, use a static IP address rather than a DHCP for assigning an IP address of the host.

In one case, the server may have shut down because the connection establishment or communication with a client did not complete in an allotted time interval. This is probably due to delays within the system or the network, or it may possibly be a client that is trying to maliciously attack by causing a Denial of Service attack on the server.

If the error is occurring because of a slow network or system, you may fix the error by reconfiguring the following parameters in sqlnet.ora to larger values:

SQLNET.INBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT

If the error is occurring due to a malicious attack via a Denial of Service attack, you may use the sqlnet.log to identify the source of attack and restrict the attacker’s access. However, users should be aware that logged addresses might not be entirely reliable. To find your sqlnet.ora file, go to your $ORACLE_HOME/network/admin directory. If the timeout occurs before the IP address can be retrieved by the database server, enable listener tracing to determine the client who made the request.

Similar error refer:

https://stackoverflow.com/questions/23970158/ora-12170-tnsconnect-timeout-occurred 

https://www.tekstream.com/resource-center/ora-12170-tns-connect-timeout-occurred/ 

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-manage-oracle 

Did I answered your question?If so ,pls mark my answer.

Best Regards

Lucien

[Anonymous]

could you please verify your password?

[Ynr0225]

I checked with Database team password is correct.