Apache Spark通过JDBC连接Oracle失败：ORA-17820错误排查求助

我正尝试使用Apache Spark与Scala连接公司的Oracle测试服务器，环境为JDK 8，已在Spark的jar目录安装对应JDBC驱动。以下是我在spark-shell中执行的语句：

val user = "<username>"
val password = "<password>"

val jdbc = spark.read.format("jdbc")
  .option("url","jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =<hostname>)(PORT = 1521))(LOAD_BALANCE = YES)(FAILOVER = YES)(SERVER = DEDICATED)(CONNECT_DATA = (SERVICE_NAME = <service name>)(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))")
  .option("driver", "oracle.jdbc.driver.OracleDriver")
  .option("query","select * from realmed.office_user_status_type")
  .option("user",username)
  .option("password",password)
  .load()

执行后等待约一分钟，返回如下错误：

java.sql.SQLRecoverableException: ORA-17820: The network adapter could not establish the connection. (CONNECTION_ID=QDew7TDmTtecLW0yGr39SA==)
  at oracle.jdbc.driver.T4CConnection.handleLogonNetException(T4CConnection.java:1382)
  at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:975)
  at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1126)
  at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:104)
  at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:825)
  at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:651)
  at org.apache.spark.sql.execution.datasources.jdbc.connection.BasicConnectionProvider.getConnection(BasicConnectionProvider.scala:49)
  at org.apache.spark.sql.execution.datasources.jdbc.connection.ConnectionProviderBase.create(ConnectionProvider.scala:102)
  at org.apache.spark.sql.jdbc.JdbcDialect.$anonfun$createConnectionFactory$1(JdbcDialects.scala:160)
  at org.apache.spark.sql.jdbc.JdbcDialect.$anonfun$createConnectionFactory$1$adapted(JdbcDialects.scala:156)
  at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.getQueryOutputSchema(JDBCRDD.scala:63)
  at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:58)
  at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.getSchema(JDBCRelation.scala:241)
  at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:37)
  at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:346)
  at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:229)
  at org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:211)
  at scala.Option.getOrElse(Option.scala:201)
  at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:211)
  at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:172)
  ... 42 elided
Caused by: oracle.net.ns.NetException: ORA-17820: The network adapter could not establish the connection. (CONNECTION_ID=QDew7TDmTtecLW0yGr39SA==)
  at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:769)
  at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:707)
  at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:938)
  at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:911)
  at oracle.net.ns.NSProtocolNIO.redirectConnection(NSProtocolNIO.java:525)
  at oracle.net.ns.NSProtocolNIO.handleConnectPacketResponse(NSProtocolNIO.java:447)
  at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:268)
  at oracle.net.ns.NSProtocol.connect(NSProtocol.java:331)
  at oracle.jdbc.driver.T4CConnection.connectNetworkSessionProtocol(T4CConnection.java:2947)
  at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:910)
  ... 60 more

现求助：

1. 我的URL或其他配置语法是否有误？
2. 该ORA-17820错误除了“网络适配器无法建立连接”外还有其他有用信息吗？

一、配置语法问题排查 #

1. 变量名笔误：代码中定义的变量是user，但在option("user",username)里误写为username，会导致变量未定义报错，需修正为option("user", user)。
2. URL格式检查：你的TNS风格URL语法本身符合Oracle Thin驱动规范，但需确认细节：
   • <hostname>、<service name>是否替换为实际有效值，注意区分服务名（SERVICE_NAME）和实例名（SID），不要混淆。
   • 可先简化URL测试基础连接：jdbc:oracle:thin:@<hostname>:1521/<service name>，确认能连接后再加回负载均衡、故障转移等复杂配置。
3. 驱动类优化：旧驱动类oracle.jdbc.driver.OracleDriver已过时，若使用新版本JDBC驱动，建议替换为oracle.jdbc.OracleDriver，兼容性更好。

二、ORA-17820错误的深层信息 #

这个错误本质是网络层面无法建立到Oracle服务器的连接，从堆栈日志可挖掘额外线索：

1. 连接策略执行失败：oracle.net.nt.ConnStrategy.execute说明驱动按配置的连接策略（如LOAD_BALANCE=YES）尝试寻找可用节点，但全部失败。
2. NIO连接协商失败：NSProtocolNIO.negotiateConnection表明驱动用NIO方式协商连接时出错，可能是防火墙限制、端口未开放，或Oracle监听配置异常。
3. CONNECTION_ID作用：错误中的CONNECTION_ID=QDew7TDmTtecLW0yGr39SA==可提供给Oracle DBA，他们能通过该ID在服务器监听日志中查找更具体的失败原因。

三、后续排查建议 #

• 在Spark所在机器用telnet <hostname> 1521或nc -zv <hostname> 1521测试端口连通性，确认网络链路正常。
• 用Oracle官方工具sqlplus在同一机器测试连接：sqlplus <username>/<password>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service name>)))，若sqlplus也无法连接，问题出在Oracle服务器或网络，与Spark无关。
• 检查JDBC驱动版本与Oracle服务器版本是否兼容，比如Oracle 19c服务器建议搭配19c及以上版本驱动。

内容的提问来源于stack exchange，提问作者user8675309