Example of 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

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

dump

The following dump command 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.

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 the load command:

$ 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 the migrator when running the load command in parallel mode.

Mapping Oracle User Defined Types to NuoDB Types

The NuoDB Migrator schema command maps Oracle user defined OBJECT, VARRAY and REF types to the NuoDB BLOB type with value NULL. The schema command 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'));

schema Command

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);

dump Command

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"/>

load Command

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