Migrating From Oracle

Oracle JDBC Driver Information

To connect to an Oracle database, download the Oracle Thin Driver.

Supported Drivers

For Oracle 11 R2: Oracle Thin Driver (ojdbc6.jar)
For Oracle 12 R2 (12.2.0.1) Oracle Thin Driver (ojdbc8.jar)

Class Name

oracle.jdbc.driver.OracleDriver

JDBC URL

jdbc:oracle:thin:@//host:port/ServiceName
or
jdbc:oracle:thin:@host:port:SID
The Oracle Thin Driver is a platform-independent driver also known as the direct to database driver. This driver does not require any additional Oracle software on the client-side. For additional information, see Oracle JDBC.

Migration Commands

nuodb-migrator schema

This sample command generates a NuoDB DDL SQL script, /tmp/schema.sql, using the Oracle TEST1 schema.

$ nuodb-migrator schema                                               \
        --source.driver=oracle.jdbc.driver.OracleDriver               \
        --source.url=jdbc:oracle:thin:@//localhost:1521/ServiceName   \
        --source.username=user                                        \
        --source.password=pass                                        \
        --source.schema=TEST1                                         \
        --output.path=/tmp/schema.sql
The --source.schema parameter value must be uppercase.

Run the DDL SQL script, /tmp/schema.sql can be executed using the nuosql command to generate the test1 schema in a NuoDB database called db2.

$ nuosql db2@localhost --user cloud --password user --file /tmp/schema.sql

nuodb-migrator dump

The following nuodb-migrator dump is creating CSV dump files of the Oracle schema TEST1. These CSV files are written to the /tmp/dump directory, one file for each table object defined in the Oracle schema TEST1.

$ nuodb-migrator dump                                                 \
        --source.driver=oracle.jdbc.driver.OracleDriver               \
        --source.url=jdbc:oracle:thin:@//localhost:1521/ServiceName   \
        --source.username=user                                        \
        --source.password=pass                                        \
        --source.schema=TEST1                                         \
        --output.type=csv                                             \
        --output.path=/tmp/dump
The --source.schema parameter value must be uppercase.

nuodb-migrator load

Loading the Oracle dumped data from --input.path to a NuoDB database, with the same schema name as the source Oracle schema, is performed using nuodb-migrator load:

$ nuodb-migrator load                                  \
        --target.url=jdbc:com.nuodb://localhost/db2    \
        --target.username=cloud                        \
        --target.password=user                         \
        --input.path=/tmp/dump

For Oracle migrations, the V$PARAMETER value is assumed to be 300. For large Oracle databases, setting this parameter to less than 300 could abort nuodb-migrator when running nuodb-migrator load in parallel mode.

Mapping Oracle User Defined Types to NuoDB Types

nuodb-migrator schema maps Oracle user defined OBJECT, VARRAY and REF types to the NuoDB BLOB type with value NULL. nuodb-migrator schema preserves the Oracle source user defined column name.

For example, consider the following Oracle type definition:

CREATE OR REPLACE TYPE "ROOT"."SSN_T" AS OBJECT (ssn_type CHAR(11))

  • ROOT is the schema.

  • SSN_T is user defined type name.

Information about creating Oracle user defined types can be found here: https://docs.oracle.com/cd/A87860_01/doc/java.817/a83724/basic3.htm.

Using Oracle, suppose you create a table that uses the user defined type, for example:

CREATE TABLE SSN(PER_ID NUMBER(10), PER_SSN ROOT.SSN_T);
INSERT INTO ssn VALUES (1, ssn_t('123-45-6789'));
INSERT INTO ssn VALUES (1, ssn_t('123-45-7896'));

nuodb-migrator schema

The NuoDB schema command for converting that Oracle table to NuoDB would be something like this:

$ nuodb-migrator schema --source.driver=oracle.jdbc.driver.OracleDriver   \
       --source.url=jdbc:oracle:thin:@//localhost:1521/dev                \
       --source.username=root                                             \
       --source.password=root                                             \
       --source.schema=ROOT                                               \
       --output.path=/home/tmp/schema/schema.sql

nuodb-migrator converts the user defined type to a BLOB type. You can execute SQL statements such as the following:

DROP TABLE IF EXISTS "SSN" CASCADE;
CREATE TABLE "SSN" ("PER_ID" DECIMAL(10,0), "PER_SSN" BLOB);

nuodb-migrator dump

The command line for nuodb-migrator to dump the user defined type would be something like this:

$ nuodb-migrator dump --source.driver=oracle.jdbc.driver.OracleDriver   \
     --source.url=jdbc:oracle:thin:@//localhost:1521/dev                \
     --source.username=root                                             \
     --source.password=root                                             \
     --source.schema=ROOT                                               \
     --output.type=csv                                                  \
     --output.path=/home/tmp/dump

nuodb-migrator issues a warning message such as the following:

(WARN) - Unsupported type on table SSN column PER_SSN: type name SSN_T, type code 1111, length 0, precision 38, scale 0
 Backup.cat file
<catalog>
<schema name="ROOT">
<userDefined object_name="SSN_T" typecode="STRUCT"/>

nuodb-migrator load

The command line for nuodb-migrator to load the user defined column to a BLOB type with a NULL value would be something like this:

$ nuodb-migrator load --target.url=jdbc:com.nuodb://localhost/test  \
     --target.schema=TEST                                           \
     --target.username=test                                         \
     --target.password=test                                         \
     --input.path=/home/tmp/dump