Not able to connect to On-Prem Oracle from Databricks cluster

[Anonymous]

Hi Everyone,

I was trying to connect to Oracle Instance from Databricks cluster and it is giving below error:

java.sql.SQLTimeoutException: ORA-12170: Cannot connect. TCP connect timeout of 30000ms for host xx.x.x.*** port 1521. (CONNECTION_ID=CgM7V7UBQDSzltL/SWk9LA==)

I have tried checking the port 1521 is listening mode or not using below:

netstat -aon

and State is Listening.

Also I have ojdbc8.jar in the libraries of the running cluster.

Below is the code template I am using:

jdbcHostname = "Your Host Name"

jdbcPort = 1521

jdbcDatabase = "DB Service Name"

jdbcUsername = "hr"

jdbcPassword = "hr"

jdbcUrl = "jdbc:oracle:thin:@//" + jdbcHostname + ":" + str(jdbcPort) + "/" + jdbcDatabase

# Define connection properties

connectionProperties = {

  "user": jdbcUsername,

  "password": jdbcPassword,

  "driver": "oracle.jdbc.driver.OracleDriver"

}

# Load a table from the Oracle database

df = spark.read.jdbc(url=jdbcUrl, table="employees", properties=connectionProperties)

Please help me with your expertise to solve this.

Thanks and Regards,

Satya

[Avinash_94]

The problem is that the Oracle service is running on a IP address, and the host is configured with another IP address.

To see the IP address of the Oracle service, issue an 

lsnrctl status command and check the address reported

Try a static IP

[Anonymous]

Thanks Avinash for the reply.

​I have checked the IP by using lsnrctl status and it is matching with the given IP and Port number and still getting the same issue.

[Avinash_94]

are you able to ping your on prem IP from Databricks using telnet command from notebook if no there is an outbound inbound rule need to be added to you firewall

[Anonymous]

No @Avinash Kumar​ . I tried with below:

%sh telnet xx.x.x.*** 1521

and getting "telnet: Unable to connect to remote host: Connection timed out"

Please help me in telling where to add for outbound inbound rule to my firewall.

[Avinash_94]

Hi you need to get in touch with the networking team and ask them to add an inbound rule to external databricks ip address allowing access to the said IP and port. the rules would be specific to you organization policy.

[-werners-]

with on-prem connections you have to make sure databricks can reach your oracle environment networkwise, so you might have to change firewall settings or use private endpoints, vnet injection etc.

[Anonymous]

Thanks Werners for your reply.

Could you please let me know where to check for changing the firewall settings ..

[Anonymous]

@Satya89:

The error message you received indicates that the TCP connection to the Oracle database timed out. This could be caused by a number of factors such as network issues, firewall restrictions, or the database being overloaded.

Here are a few steps you can try to troubleshoot and resolve the issue:

1. Verify that the host name and port number you are using to connect to the Oracle database are correct. You can try connecting to the database using the same host name and port number from a different client such as SQL*Plus or SQL Developer to confirm that the connection works.
2. Check if there are any firewall restrictions in place that may be preventing the Databricks cluster from accessing the Oracle database. You can work with your network or security team to check if there are any network restrictions that need to be updated.
3. Verify that the ojdbc8.jar file is in the correct location and that it is accessible to the Databricks cluster. You can check the driver configuration by navigating to the cluster UI and clicking on the "JDBC/ODBC" tab to see if the driver is listed there.
4. Check the Oracle database logs to see if there are any errors or warnings that may indicate why the connection is failing. You can also check the database performance metrics to see if the database is overloaded or running out of resources.
5. Try increasing the timeout value for the connection by adding the following property to the connection properties:"oracle.net.CONNECT_TIMEOUT" : "5000" This will increase the timeout value to 5 seconds, which may help if the connection is being dropped due to a brief network interruption.
6. If all else fails, you may need to reach out to Oracle support or your IT department for assistance in troubleshooting the issue.

I hope these suggestions help you to resolve the issue and connect to the Oracle database from Databricks.