CODEWITHSUNDEEP
oracle-databaseapache-sparkhdfshadoop-yarnojdbc
Steven Black
Steven Black

Reputation: 2232

Oracle Database JDBC driver cannot read wallet file from Spark

Objective

I'm trying to write to Oracle's ADWC (basically oracle database) from a Spark application running on Yarn. The only way to connect to this database is by using an Oracle Wallet file, which is basically a Java keystore.

Problem

The problem arises when the JDBC driver tries to read the wallet from HDFS. If I include the hdfs:// prefix the parser in the JDBC driver throws an error and if I don't then it cannot find the file.

Previous Attempts

  1. including the directory in the connect string (prefixed and non) jdbc:oracle:thin:@luigi_low?TNS_ADMIN=/user/spark/wallet_LUIGI
  2. including the directory as an spark.driver.extraJavaOptions with -Doracle.net.tns_admin and -Doracle.net.wallet_location

All the code is on GitHub, and specifically, the error messages are here https://github.com/sblack4/kafka-scala-jdbc/blob/master/ERROR.md

I've got a working example of the same connection here https://github.com/sblack4/scala-jdbc-adwc

help me StackOverflow. you are my only hope

If you need any more clarification don't hesitate :)

update (SparkFiles attempt)

the code is on a separate branch of the same repository, https://github.com/sblack4/kafka-scala-jdbc/tree/sparkfiles

This error message mystifies me as it seems my JDBC library has stopped trying to read the wallet files. It may be unrelated to the previous problem 

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: "host:port:sid"

I've deleted the other JDBC libraries from my classpath through Ambari as this error could be related to spark picking up an older version of my JDBC library

Upvotes: 2

Views: 2748

Answers (2)

Nirmala
Nirmala

Reputation: 1338

Are you using 18.3 JDBC drivers? Passing TNS_ADMIN as part of the connection URL requires 18.3 JDBC driver. Also, are you attempting to connect within the corporate network. In that case, you will need to pass HTTPS_PROXY and HTTPS_PROXY_PORT in the connection URL. Let us know. Happy to help with the problem.

Upvotes: 1

Kris Rice
Kris Rice

Reputation: 3410

Here's some code that will help diagnose what the issues is. It checks and configures everything required to connect.

  • JDBC Driver version
  • JCE Installed
  • Classpath dependencies

Configures

  • tns_admin
  • ssl settings
  • trust/key stores

This is a slimmed down version of what's in sqldev/sqlcl

import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Properties;

import javax.crypto.Cipher;

import oracle.jdbc.OracleConnection;

public class JDBCTest {

  public static void fail(String msg){
    System.err.println(String.join("", Collections.nCopies(20, "*")));
    System.err.println(msg);
    System.err.println(String.join("", Collections.nCopies(20, "*")));
    System.exit(1);
  }
  public static void main(String[] args) throws SQLException {
    System.out.println("JDBC Driver Version:" + oracle.jdbc.OracleDriver.getDriverVersion());

    // Check JDBC Driver Version
    if (!oracle.jdbc.OracleDriver.getDriverVersion().startsWith("18.")) {
      fail(" DRIVER TOOO OLD!!!");
    }

    // Check JCE Installed
    int maxKeySize = 0;
    try {
      maxKeySize = Cipher.getMaxAllowedKeyLength("AES");
    } catch (NoSuchAlgorithmException e) {
    }
    if (maxKeySize < 129 ) {
      fail(" JCE Policy not unlimited!!!");      
    }

    // Check Classpath

    String cp = System.getProperty("java.class.path");

    String[] cpFiles = {"ojdbc8.jar","oraclepki.jar","osdt_cert.jar","osdt_core.jar"};

    for (String file:cpFiles){
      if ( cp.indexOf(file) == -1 ){
        fail("CLASSPATH Missing:" + file);

      }
    }
    // Wallet unziped location
    String unzippedWalletLocation = "/Users/klrice/workspace/12.2JDBC/wallet";



    String conString = "jdbc:oracle:thin:@sqldev_medium";


    Properties props = new Properties();
    props.setProperty("oracle.net.wallet_location",unzippedWalletLocation);

    props.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CONNECT_TIMEOUT, "2000");

    // unzipped includes a tnsnames.ora
    props.setProperty("oracle.net.tns_admin",unzippedWalletLocation);
    props.setProperty("javax.net.ssl.trustStore","truststore.jks");
    props.setProperty("javax.net.ssl.trustStorePassword","<password>");
    props.setProperty("javax.net.ssl.keyStore","keystore.jks");
    props.setProperty("javax.net.ssl.keyStorePassword","<password>");
    props.setProperty("oracle.net.ssl_server_dn_match","true");    
    props.setProperty("oracle.net.ssl_version","1.2");


    props.setProperty("user", "ADMIN");
    props.setProperty("password", "<password>");

    try { 
      // now Connect 
      Connection conn = DriverManager.getConnection(conString,props);
    } catch (Exception e){
      e.printStackTrace();
      fail(e.getLocalizedMessage());
    }

    System.out.println("SUCCESS!!");


  }

}

Upvotes: 4

Related Questions