Ilmar Kerm

Oracle, databases, Linux and maybe more

Oracle Database has had the possibility to run Java code inside the database for a long time. It’s a very rare occasion when you need to use it but still. Here is one example I used to download content from HTTPS website that required user certificates for authentication. Please take the code below more as an example how to put simple Java code inside the database, not as a solution for user certificates authentication, because UTL_HTTP can do the same thing (although I wasn’t successful in implementing it under 11.2.0.2).

First, load the Java source into database. The code below shows:

  • How to return simple datatype (int) from Java function – makeConnection
  • How to return Oracle CLOB datatype from Java – makeConnectionClob
  • How to execute SQL from Java, in the same calling session

Note that method main is just added for testing from command line.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "HttpsHandler" as
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;

import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.SSLSocketFactory;
import javax.net.ssl.SSLSession;

import java.sql.Connection;
import java.sql.PreparedStatement;
import oracle.jdbc.driver.*;
import oracle.sql.CLOB;

public class HttpsHandler {
  
  
  public static CLOB makeConnectionClob(String keyStorePath, String keyStorePass, String trustStorePath, String httpsUrl, String proxyHost, String proxyPort) throws Exception {
    int i = makeConnection(keyStorePath, keyStorePass, trustStorePath, httpsUrl, proxyHost, proxyPort);
    String s = Integer.toString(s);
    OracleDriver driver = new OracleDriver();
    Connection dbconn = driver.defaultConnection();
    CLOB clob = CLOB.createTemporary(dbconn, false, CLOB.DURATION_CALL);
    clob.setString(1, s);
    return clob;
  }
  
  public static int makeConnection(String keyStorePath, String keyStorePass, String trustStorePath, String httpsUrl, String proxyHost, String proxyPort) throws Exception {
      //
      System.setProperty("javax.net.ssl.keyStore", keyStorePath);
      System.setProperty("javax.net.ssl.trustStore", trustStorePath);
      //System.setProperty("javax.net.debug", "ssl");
      System.setProperty("javax.net.ssl.keyStorePassword", keyStorePass);
      
      if (proxyHost != null && proxyPort != null) {
        System.setProperty("https.proxyHost", proxyHost);
        System.setProperty("https.proxyPort", proxyPort);
      }
      
      //
      SSLSocketFactory sslsocketfactory = (SSLSocketFactory) SSLSocketFactory.getDefault();
      URL url = new URL(httpsUrl);
      HttpsURLConnection conn = (HttpsURLConnection)url.openConnection();
      conn.setConnectTimeout(8000);
      conn.setSSLSocketFactory(sslsocketfactory);
      // Do not verify that hostname matches the certificate
/*      conn.setHostnameVerifier(new HostnameVerifier() {        
          public boolean verify(String hostname, SSLSession session)  {  
        return true;
          }
      });*/
      // Set request header
      conn.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
      InputStream inputstream = conn.getInputStream();
      InputStreamReader inputstreamreader = new InputStreamReader(inputstream);
      BufferedReader bufferedreader = new BufferedReader(inputstreamreader);
      
      OracleDriver driver = new OracleDriver();
      Connection dbconn = driver.defaultConnection();
      PreparedStatement dml_stmt = dbconn.prepareStatement("INSERT INTO https_output (num, line) VALUES (?,?)"); 
      
      String s = "";
      int linecount=0;
      while ((s = bufferedreader.readLine()) != null) {
        linecount++;
        dml_stmt.setInt(1, linecount);
        dml_stmt.setString(2, s);
        dml_stmt.executeUpdate();
      }
      dml_stmt.close();
      
      return linecount;
  }
  
  public static void main(String[] args) {
    try {
      int i = makeConnection("/path/to/keystore.jks", "keystore_pass", "/path/to/truststore.jks", "https://site.that.requires.user.cert/authentication/", null, null);
      System.out.println(Integer.toString(i));
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }

};

Then you need to create a wrapper package in database. This declares the PL/SQL wrapper function names and input/output parameters.

CREATE OR REPLACE package https_user_cert_wrapper as

  FUNCTION make_request(keyStorePath IN varchar2, keyStorePass IN varchar2, trustStorePath IN varchar2, httpsUrl IN varchar2, proxyHost IN varchar2, proxyPort IN varchar2)
  RETURN number AS LANGUAGE JAVA 
  NAME 'HttpsHandler.makeConnection(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return java.lang.int';

  FUNCTION make_request_clob(keyStorePath IN varchar2, keyStorePass IN varchar2, trustStorePath IN varchar2, httpsUrl IN varchar2, proxyHost IN varchar2, proxyPort IN varchar2)
  RETURN clob AS LANGUAGE JAVA 
  NAME 'HttpsHandler.makeConnectionClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return oracle.sql.CLOB';

end;
/

Download the source: java_source.java and PL/SQL wrapper.sql.

When you first execute the code, you will most likely get some privilege errors, but the error message will tell you how to grant the needed privileges. For example, for this code the following grants were needed:

exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStore', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.trustStore', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStorePassword', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', 'site.that.requires.user.cert', 'resolve' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', '1.2.3.4:443', 'connect,resolve' );

Oracle Wallet Manager and orapki do not let you extract the private key associated with user certificate located in Oracle Wallet. If you need it for some reason, for example testing with external tools like wget, then its possible to extract the private key using openssl, since Orale Wallet (ewallet.p12 file) is just PKCS#12 file.

[oracle@jfadboc1n01 wallet]$ openssl pkcs12 -in /home/oracle/wallet/ewallet.p12 -nocerts -out private_key.pem
Enter Import Password:
MAC verified OK
Warning unsupported bag type: secretBag
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
[oracle@jfadboc1n01 wallet]$ ls -l
total 16
-rw-r--r-- 1 oracle oinstall 11629 May 15 11:38 ewallet.p12
-rw-r--r-- 1 oracle oinstall  1879 May 17 08:53 private_key.pem

Here private_key.pem contains the private key extracted from Oracle Wallet.

The first password that openssl asks (Enter Import Password) is the wallet password, the other password (Enter PEM pass phrase) is used to protect the exported key.

When instance fails in Oracle RAC, the services that were using this instance as a preferred instance are automatically relocated to instances marked as available for this service. But after the failed instance recovers and starts up again, the relocated services are not moved back and need manual srvctl relocate service command from administrator to move them back.

Here is a little Bash script to automate this process. Oracle Clusterware (Grid Infrastructure) can execute user callout scripts on FAN events, like INSTANCE up/down. Place this script under $GRID_HOME/racg/usrco/ and set the execute bits on the file. Then clusterware will execute that script for all FAN events, but the script will start processing only for instance up event.

Why is it needed? We just switched over to 4-node RAC consisting of many different applications, almost each of them connecting to its own schema. We created each application its own service restricting it to 1 (or max 2) nodes (1 or 2 nodes as preferred, all other nodes listed as available). After the first rolling patching, I noticed that the connection count and load on each node was very unbalanced, vast majority of the connections were connected to node1 and the last patched node had almost none and it did not get better over a few hours. This was because most of the services ended up on node1 and I had to manually look over each service and relocate it back where it belongs. This script attempts to automate this process.

Please use the download link for downloading, seems that blogger escapes some characters in the code below.

#!/bin/bash
#
# GI callout script to catch INSTANCE up event from clusterware and relocate services to preferred instance
# Copy or symlink this script to $GRID_HOME/racg/usrco
# Tested on Oracle Linux 5.8 with 11.2.0.3 Oracle Grid Infrastructure and 11.2.0.2 & 11.2.0.3 Oracle Database Enterprise Edition
# 2012 Ilmar Kerm ilmar.kerm@gmail.com
#

LOGFILE=/u02/app/oracle/grid_callout/log.txt
SCRIPTDIR=`dirname $0`

# Determine grid home
if [[ "${SCRIPTDIR:(-11)}" == "/racg/usrco" ]]; then
  CRS_HOME=""${SCRIPTDIR:0:$(( ${#SCRIPTDIR} - 11 ))}""
  export CRS_HOME
fi

# Only execute script for INSTANCE events
if [ "$1" != "INSTANCE" ]; then
  exit 0
fi

STATUS=""
DATABASE=""
INSTANCE=""

# Parse input arguments
args=("$@")
for arg in ${args[@]}; do
  if [[ "$arg" == *=* ]]; then
    KEY=${arg%=*}
    VALUE=${arg#*=}
    
    case "$KEY" in
      status)
        STATUS="$VALUE"
        ;;
      database)
        DATABASE="$VALUE"
        ;;
      instance)
        INSTANCE="$VALUE"
        ;;
    esac
    
  fi
done

# If database, status and instance values are not set, then exit
# status must be up
if [[ -z "$DATABASE" || -z "$INSTANCE" || "$STATUS" != "up" ]]; then
  exit 0
fi

echo "`date`" >> "$LOGFILE"
echo "[$DATABASE][`hostname`] Instance $INSTANCE up" >> "$LOGFILE"

#
# Read database software home directory from clusterware
#
DBCONFIG=`$CRS_HOME/bin/crsctl status res ora.$DATABASE.db -f | grep "ORACLE_HOME="`
if [ -z "$DBCONFIG" ]; then
  exit 0
fi
declare -r "$DBCONFIG"
echo "ORACLE_HOME=$ORACLE_HOME" >> "$LOGFILE"

# Array function
in_array() {
    local hay needle=$1
    shift
    for hay; do
        [[ $hay == $needle ]] && return 0
    done
    return 1
}

#
# Read information about services
#
for service in `$CRS_HOME/bin/crsctl status res | grep -E "ora.$DATABASE.(.+).svc" | sed -rne "s/NAME=ora.$DATABASE.(.+).svc/1/gip"`; do
  SERVICECONFIG=`$ORACLE_HOME/bin/srvctl config service -d $DATABASE -s $service`
  echo "Service $service" >> "$LOGFILE"
  if [[ `echo "$SERVICECONFIG" | grep "Service is enabled" | wc -l` -eq 1 ]]; then
    echo " enabled" >> "$LOGFILE"
    PREFERRED=( `echo "$SERVICECONFIG" | grep "Preferred instances:" | sed -rne "s/.*: ([a-zA-Z0-9]+)/1/p" | tr "," "n"` )
    #
    # Check if current instance is preferred for this service
    #
    if in_array "$INSTANCE" "${PREFERRED[@]}" ; then
      echo " preferred" >> "$LOGFILE"
      #
      # Check if service is already running on current instance
      #
      SRVSTATUS=`$ORACLE_HOME/bin/srvctl status service -d $DATABASE -s $service`
      if [[ "$SRVSTATUS" == *"is not running"* ]]; then
          #
          # if service is not running, then start it
          #
        echo " service stopped, starting" >> "$LOGFILE"
        $ORACLE_HOME/bin/srvctl start service -d "$DATABASE" -s "$service" >> "$LOGFILE"
      else
        #
        # Service is running, but is it running on preferred instance?
        #
        RUNNING=( `echo "$SRVSTATUS" | sed -rne "s/.* ([a-zA-Z0-9]+)/1/p" | tr "," "n"` )
        echo "${RUNNING[@]} = ${PREFERRED[@]}"
        if ! in_array "$INSTANCE" "${RUNNING[@]}" ; then
          echo " not running on preferred $INSTANCE" >> "$LOGFILE"
          #
          # Find the first non-preferred running instance
          #
          CURRENT=""
          for inst in "${RUNNING[@]}"; do
            if ! in_array "$inst" "${PREFERRED[@]}" ; then
              CURRENT="$inst"
              break
            fi
          done
          #
          # Relocate
          #
          if [[ -n "$CURRENT" ]]; then
            echo " relocate $CURRENT -> $INSTANCE" >> "$LOGFILE"
            $ORACLE_HOME/bin/srvctl relocate service -d "$DATABASE" -s "$service" -i "$CURRENT" -t "$INSTANCE" >> "$LOGFILE"
          fi
        else
          #
          # Service is already running on preferred instance, no need to do anything
          #
          echo " running on preferred $INSTANCE" >> "$LOGFILE"
        fi
      fi
    fi
  fi
done

Download: relocate_services_callout.sh

Tested on Oracle Linux 5.8 with Oracle Grid Infrastructure 11.2.0.3 and Oracle Database 11.2.0.2 and 11.2.0.3.

Categories