5.6.3. Best Practices: Oracle to Vertica Replication

Follow the guidelines in Section 2.2, “Best Practices”. Management and Monitoring of Oracle to Vertica Deployments

Monitoring a Vertica replication scenario requires checking the status of both the master - extracting data from Vertica - and the slave which retrieves the remote THL information and applies it to Vertica.

Check replication status on host1

shell-host1> . /opt/continuent/share/env.sh
shell-host1> trepctl status
shell-host1> vmrrd_oracle2vertica status

Check replication status on host2

shell-host2> . /opt/continuent/share/env.sh
shell-host2> trepctl status

The appliedLastSeqno should match as normal. Because of the batching of transactions the appliedLatency may be much higher than normal homogenous replication. Troubleshooting Vertica Installations

  • Remember that changes to the DDL within the source database are not automatically replicated to Vertica. Changes to the table definitions, additional tables, or additional databases, must all be updated manually within Vertica.

  • If you get errors similar to:

    stage_xxx_access_log does not exist

    When loading into Vertica, it means that the staging tables have not created correctly. Check the steps for creating the staging tables using ddlscan in Section 6.4.1, “Preparing Hosts for Vertica Deployments”.

  • Replication may fail if date types contain zero values, which are legal in MySQL. For example, the timestamp 0000-00-00 00:00:00 is valid in MySQL. An error reporting a mismatch in the values will be reported when applying the data into Vertica, for example:

    ERROR 2631:  Column "time" is of type timestamp but expression is of type int
    	HINT:  You will need to rewrite or cast the expression


    ERROR 2992:  Date/time field value out of range: "0"
    	HINT:  Perhaps you need a different "datestyle" setting

    To address this error, use the zerodate2null filter, which translates zero-value dates into a valid NULL value. This can be enabled by adding the zerodate2null filter to the applier stage when configuring the service using tpm:

    shell> ./tools/tpm update oracle2vertica --repl-svc-applier-filters=zerodate2null