5.5.1. Prepare: Oracle to Vertica Replication

Oracle Preparation

Prepare the Oracle environment as documented in section Section 5.1.2, “Oracle Replication Pre-Requisites”.

Vertica Preparation

On the Vertica host, you need to perform some preparation of the destination database, first creating the database, and then creating the tables that are to be replicated.

  • Create a database (if you want to use a different one than those already configured), and a schema that will contain the Tungsten data about the current replication position:

    shell> vsql -Udbadmin -wsecret bigdata
    Welcome to vsql, the Vertica Analytic Database v5.1.1-0 interactive terminal.
    Type:  \h for help with SQL commands
           \? for help with vsql commands
           \g or terminate with semicolon to execute query
           \q to quit
    bigdata=> create schema tungsten_oracle2vertica;

    The schema will be used only by Tungsten Replication to store metadata about the replication process.

  • Locate the Vertica JDBC driver. This can be downloaded separately from the Vertica website. The driver will need to be copied into the Tungsten Replication lib directory.

    shell> cp vertica-jdbc-7.1.2-0.jar tungsten-replicator-6.0.4-27/tungsten-replicator/lib/
  • You need to create tables within Vertica according to the databases and tables that need to be replicated; the tables are not automatically created for you. From a Tungsten Replication deployment directory, the ddlscan command can be used to identify the existing tables, and create table definitions for use within Vertica.

    Please be sure to identify the name and location of the Oracle driver file ojdbc6.jar or ojdbc7.jar and define the CLASSPATH environment variable to point to that file.

    To use ddlscan, the template for Vertica must be specified, along with the user/password information to connect to the source database to collect the schema definitions. The tool should be run from the templates directory.

    The tool will need to be executed twice, the first time generates the live table definitions:

    shell> export CP=/path/to/ojdbc6.jar
    shell> cd tungsten-replicator-6.0.4-27
    shell> cd tungsten-replicator/support/ddlscan/
    shell> ../../bin/ddlscan -user tungsten -url 'jdbc:oracle:thin://host1:1521/ORCL' \
        -pass secret -template ddl-oracle-vertica.vm -db DB1
    SQL generated on Fri Dec 04 07:26:48 EST 2015 by ./ddlscan utility of Tungsten
    url = jdbc:oracle:thin:@//localhost:1521/ORCL
    user = tungsten
    dbName = DB1
      "EMP_NO" NUMBER ,
      "FIRST_NAME" VARCHAR(14) ,
      "LAST_NAME" VARCHAR(16) ,
      "GENDER" CHAR(1) ,
      "HIRE_DATE" DATE  ) 
      ORDER BY "EMP_NO" 

    The output should be redirected to a file and then used to create tables within Vertica:

    shell> ../../bin/ddlscan -user tungsten -url 'jdbc:oracle:thin://host1:1521/ORCL' -pass secret \
        -template ddl-oracle-vertica.vm -db DB1 > db1.ddl

    The output of the command should be checked to ensure that the table definitions are correct.

    The file can then be applied to Vertica:

    shell> cat db1.ddl | vsql -Udbadmin -wsecret bigdata

    This generates the table definitions for live data. The process should be repeated to create the table definitions for the staging data by using te staging template:

    shell> ../../bin/ddlscan -user tungsten -url 'jdbc:oracle:thin://host1:1521/ORCL' -pass secret \
        -template ddl-oracle-vertica-staging.vm -db DB1 > db1-staging.ddl

    Then applied to Vertica:

    shell> cat db1-staging.ddl | vsql -Udbadmin -wsecret bigdata


    The process should be repeated for each database that will be replicated.

Once the preparation of the MySQL and Vertica databases are ready, you can proceed to installing Tungsten Replication