When utilizing Kettle 5.4 with a JDBC interface, you might encounter exceptions specifically when targeting an Oracle 11g RAC environment, while other database connections function correctly. The error log typically resembles the following:

java.sql.SQLException: Issue creating database connection:
oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@90.12.xx.xx:1521:orcl
ORCL_CON
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
90.12.xx.xx:1521:orcl

Verification via sqlplus confirms the database is operational, suggesting the need to explore alternative connection methods within Kettle.

Switching to ODBC

A straightforward workaround is using the ODBC protocol:

1. Navigate to Administrative Tools > Data Sources (ODBC).
2. Select User DSN and click Add.
3. Choose the appropriate Oracle driver from the list.

Once the system DSN is configured, set up the corresponding ODBC connection in Kettle and run a test. This method typically resolves the immediate connection issue.

Implementing OCI Connections

Attempting to use the OCI (Oracle Call Interface) method might initially result in a libray path error:

org.pentaho.di.core.exception.KettleDatabaseException: 
Error occurred while trying to connect to the database

Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
no ocijdbc11 in java.library.path

Hostname: 
Port: 1521
Database name: orcl

To resolve the ocijdbc11 missing library error, configure the system environment variables to point to your Oracle client installation:

ORACLE_HOME=D:\app\gssjcj\product\11.2.0\dbhome_1
TNS_ADMIN=D:\app\gssjcj\product\11.2.0\dbhome_1\NETWORK\ADMIN
PATH=%ORACLE_HOME%\BIN;%PATH%

Ensure your tnsnames.ora file contains the correct service descriptor. For example:

J3_CX =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 90.xx.xx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Additionally, copy the ocijdbc11.dll file into the Kettle directory specific to your architecture, such as libswt\win64. After these steps, the OCI connection test in Kettle should pass successfully.

Driver Compatibility Notes

OCI relies on the local Oracle client. If you are using OCI with an Oracle Net8 client, the JDBC driver version in Kettle must align with your Oracle client version. For instance, PDI 2.5.0 shipped with version 10.1, while 3.0.0 shipped with 10.2.

To synchronize versions, replace the ojdbc14.jar and orai18n.jar files located in the libext/JDBC directory of your Kettle distribution with the drivers found in your server's $ORACLE_HOME/jdbc directory. For Oracle 11g, these are typically named ojdbc5.jar or ojdbc6.jar.

Handling RAC (Real Application Cluster)

For complex failover scenarios like RAC, define the connection using a native (JDBC) type but utilize a full connection descriptor in the "Database Name" field instead of a simple SID:

• Set connection type to Native (JDBC).
• Clear the hostname and port fields (or set port to -1).
• Insert the full TNS descriptor in the Database Name field.

Example descriptor for load balancing and failover:

(DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = host1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = host2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = db-service)
        (FAILOVER_MODE =
            (TYPE = SELECT)
            (METHOD = BASIC)
            (RETRIES = 180)
            (DELAY = 5)
        )
    )
)