Prepare the Oracle environment as documented in section Section 5.1.2, “Oracle Replication Pre-Requisites”.
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:
vsql -Udbadmin -wsecret bigdataWelcome 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
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
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:
../../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 */ CREATE SCHEMA "DB1"; DROP TABLE "DB1"."EMPLOYEES"; CREATE TABLE "DB1"."EMPLOYEES" ( "EMP_NO" NUMBER , "BIRTH_DATE" DATE , "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:
../../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:
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:
../../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:
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