Create Connection

This section explains how to set up the connection between Oracle and Exasol.

Before you begin the migration procedure, you need to upload the Oracle JDBC driver or Oracle OCI driver to Exasol and configure a connection between the databases. 

JDBC driver

Download the JDBC Driver

Download the JDBC driver from the Oracle JDBC Driver download site. Make sure that you download the correct version of the JDBC driver for the Oracle version.

Install the Driver

In Exasol 8, drivers must be uploaded to BucketFS. By default, the database is configured to use the following path for all JDBC drivers:

/buckets/bfsdefault/default/drivers/jdbc/

The default bucket is available on port 2581 by default. Ensure that traffic on this port has been enabled in your network settings.

For more information about BucketFS, see the BucketFS section.

Create a Configuration File

The following examples contain placeholders that are styled as Bash variables, for example: $NODE_IP. Replace the placeholders with your own values.

Create a configuration file settings.cfg that specifies the configuration parameters for your driver, using the following format:

DRIVERNAME=$MY_DRIVERNAME
PREFIX=$PREFIX
FETCHSIZE=100000
INSERTSIZE=-1

  • Replace $MY_DRIVERNAME with a unique name of your choice. This name is used in the driver clause in an IMPORT or EXPORT statement.

  • Replace $PREFIX with the required URL prefix for the JDBC driver. The prefix can be found on the driver manufacturer's website.

  • Do not modify the FETCHSIZE or INSERTSIZE parameters unless instructed to by Exasol support.

  • Make sure that the last line ends with a line feed (LF) character, as there must be an empty line at the end of the file.

Security Manager

Some drivers require additional permissions to run correctly. In these cases, you may receive an error message pointing to permission problems in the IMPORT statement. To disable the security manager, add the line NOSECURITY=YES to the settings.cfg file. The security manager is enabled by default.

Disabling the security manager can reduce the security of your system. Only add NOSECURITY=YES if the driver cannot run with the security manager enabled.

Examples

The following example is a settings.cfg file for the Exasol JDBC driver:

DRIVERNAME=EXASOL_JDBC
PREFIX=jdbc:exa:
FETCHSIZE=100000
INSERTSIZE=-1

Upload the configuration file

Upload the settings.cfg file and the driver *.jar files to the specified bucket path on one of the database nodes.

The following examples use curl on a Linux terminal. You can also use other interfaces and languages to execute curl commands.

The option --insecure or -k tells curl to bypass the TLS certificate check. This option allows you to connect to a HTTPS server that does not have a valid certificate. Only use this option if certificate verification is not possible and you trust the server.

Examples:

curl -v --insecure -X PUT -T settings.cfg https://w:$WRITE_PW@$DATABASE_NODE_IP:2581/default/drivers/jdbc/exasol/settings.cfg
curl -v --insecure -X PUT -T exajdbc.jar https://w:$WRITE_PW@$DATABASE_NODE_IP:2581/default/drivers/jdbc/exasol/exajdbc.jar

Create and test the connection

  1. Open a SQL client and create the connection from Exasol to Oracle: 
    CREATE OR REPLACE CONNECTION oracle_jdbc    
    TO 'jdbc:oracle:thin:@192.168.56.106:1521/orcl' 
    USER 'stage' IDENTIFIED BY 'stage';
  2. Test the connection by performing a small dummy import: 
    IMPORT FROM JDBC AT oracle_jdbc 
    STATEMENT 'SELECT ''Connection works'' from dual';

Oracle OCI driver

  • Download Oracle Instant Client (instantclient-basic-linux.x64-12.1.0.2.0.zip) from the Oracle website.

    Version 12.1.0.2.0 is the only version of Oracle Instant Client that is supported in Exasol.

  • The default bucket is available on port 2581 by default. Ensure that traffic on this port has been enabled in your network settings.

  • For more information about BucketFS, see the BucketFS section.

Install Oracle Instant Client

The following examples use curl on a Linux terminal. You can also use other interfaces and languages to execute curl commands.

The option --insecure or -k tells curl to bypass the TLS certificate check. This option allows you to connect to a HTTPS server that does not have a valid certificate. Only use this option if certificate verification is not possible and you trust the server.

Upload the zip archive containing the client to the specified bucket path. For example:

curl -v --insecure -X PUT -T instantclient-basic-linux.x64-12.1.0.2.0.zip https://w:$WRITE_PW@$DATABASE_NODE_IP:2581/default/drivers/oracle/instantclient-basic-linux.x64-12.1.0.2.0.zip

To verify that the client is installed, open a SQL client and run an IMPORT statement using the IMPORT FROM ORA syntax. For example:

CREATE OR REPLACE CONNECTION OCI_ORACLE
    TO '192.168.99.103:1521/xe'
    USER 'system'
    IDENTIFIED BY 'oracle';
SELECT * FROM (
    IMPORT FROM ORA at OCI_ORACLE
    STATEMENT 'select ''Connection works'' from dual'
);