5.1.2. Preparing the Oracle Environment for Replication

There are a few steps that you must take before the replicator is installed, including configuring the Oracle database and environment.

Ensure you have followed the general notes within the Section 2.3, “Prepare Hosts”. For supported platforms, and environments, see Section B.1, “Requirements”.

For your reference, below is the syntax for connecting to Oracle with a full username and password. Substitute your own values for all items in capital letters.

shell> sqlplus "USER/PASSWORD@HOSTNAME:1521/SERVICE"

5.1.2.1. Prepare the Oracle Driver

You must have the Oracle JDBC driver available. Verify that it exists in the Oracle software tree.

The file will be named ojdbc6.jar, ojdbc7.jar, or ojdbc8.jar located in the $ORACLE_HOME/jdbc/lib/ directory.

You should copy this file into into the appropriate extracted Continuent software staging directory (vmware-continuent-replication-oracle-source-5.2.1-263/tungsten-replicator/lib/) prior to installation, and the jar file will be copied along with the rest of the Continuent release files.

5.1.2.2. Prepare the Oracle System User

If using a system account for replication other than oracle (e.g., tungsten), ensure that the following are met:

  • Account belongs to oinstall group so it can read Oracle files.

  • Account has Oracle environment set correctly. This can be configured by ensuring that the correct system variables are enabled within your .profile, .bash_profile, or .bashrc:

    export ORACLE_BASE=/app/oracle/local
    export ORACLE_HOME=/app/oracle/local/product/11/db_1
    export PATH=$PATH:$ORACLE_HOME/bin
    export ORACLE_SID=ORCL

    The values above are examples. The environment variables and assigned values should only be set if they are not specified yet. Please ensure that ORACLE_HOME is set to the location where Oracle is installed and that ORACLE_BASE is set to the base directory of the OFA installation. ORACLE_SID can have any value which identifies this particular database in the system.

5.1.2.3. Prepare the Oracle DBMS Servers

All of the Oracle servers need to be prepared with the following procedure. There are optional steps at the end, based on the role of the server.

If it does not already exist, create a tablespace that contains all the data, structures and components that will be replicated. This value will be used with the --oracle-redo-tablespace argument and the alter user statement in the next step.

SQL> create tablespace tungsten_ts datafile '/app/oracle/data/tungsten_ts.dbf'
    size 1000M extent management local autoallocate segment space management auto;

If you get an error like ORA-01109: database not open, try using the ALTER DATABASE command to prepare the database.

SQL> ALTER DATABASE OPEN;

If you get an error reporting that archive loggin has not been enabled, you should enable archive logging and supplemental logging. Doing so requires shutting down the Oracle database temporarily to enable logging mode.


SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database add supplemental log data (ALL) columns;
SQL> alter database open;

You can confirm the status of archive logging:

Checking the status again should show the archive log enabled:


SQL> archive log list;
SQL> archive log list;

Create a replication user. The username and password will need to be used with the --replication-user and --replication-password arguments.

Important

For Heterogeneous Deployments like Oracle to Vertica or Oracle to Hadoop, please note the following critical step when creating the replication user in Oracle (and the replication service name):

The tracking schema in Oracle is created with the replication user name. This differs from other datasources where the schema of the tracking tables are created with the pattern tungsten_{ServiceNameHere}.

To make this work, replication user should be called tungsten_ServiceNameHere, where ServiceNameHere is the name of the replication service.

For example, if the desired replication service name is "oracle2vertica", then create an Oracle replication user called "tungsten_oracle2vertica", which automatically creates a tracking schema named tungsten_oracle2vertica.

SQL> create user tungsten identified by "secret" default tablespace tungsten_ts;
SQL> grant connect to tungsten;
SQL> alter user tungsten quota unlimited on tungsten_ts;
SQL> grant dba to tungsten;

For all servers except Migration Secondary targets, perform the following:

SQL> grant execute on dbms_flashback to tungsten;

The above GRANT is required for the VMware Redo Reader to extract properly. If the server ever becomes a Primary, this GRANT will be needed.

5.1.2.4. Prerequisite Checker Script

The pre-req-checker.sql script will install a PL/SQL Package in the DB instance, which you can then run to check for any unsupported features, data types etc. The SQL package should be installed and executed as follows:

  1. Copy the tungsten-replicator/support/oracle-pre-req-checker/pre-req-checker.sql script to the Oracle host machine.

  2. Login to sqlplus as sys:

    SQL> sqlplus / as sysdba

    or

    SQL> sqlplus sys as sysdba
  3. Install the package as follow:

    SQL> @pre-req-checker.sql

The above package install step is one time only - once that package has been installed, you can run it as many times as you require.

To Execute the package and run the checks, type the following:

SQL> set serveroutput on
SQL> exec ContinuentPreReqCheck.run(target,schema,user,path);

Substituting variables as follows:

  • target — The flavour of DB that you are replicating to, one of 'ORACLE','MYSQL55','MYSQL56'

  • schema — A comma separated list of schemas that you will be replicating

  • user — The replicator DB user on the local source instance (Typically tungsten)

  • path — The local OS path for the results of the checker where the report will be logged

After executing, you will see a message that output has been written to a log file, the name and path will be given, and will look something similar to the following:

SQL> set serveroutput on
SQL> exec ContinuentPreReqCheck.run('ORACLE','LAB','TUNGSTEN','/home/oracle/log');
>> Checks complete, results in /home/oracle/log/prereq-201511020730.log
PL/SQL procedure successfully completed.
SQL>

You can then review the log file for the results, from the above example the log looks like the following:

shell> cat /home/oracle/log/prereq-201511020730.log
INFO >>Major Version : 11 Minor Version : 2
PASS >>Source Version Check [1]
ERROR>>Table [LAB.DEMOTABLE] : Column [COL_ERR] is of unsupported datatype [ROWID]
FAIL >>Data Type Check [0]
ERROR>>Table [LAB.DEMOTABLE] does not have a Primary Key
ERROR>>Table [LAB.LABTAB1] does not have a Primary Key
FAIL >>Primary Key Check [0]
PASS >>Unsupported Features Check [1]
INFO >>User [TUNGSTEN] exists
INFO >>[TUNGSTEN] user has DBA role
PASS >>User Check [1]