5.1.2. Oracle Replication Pre-Requisites

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 B.3, “Host Configuration”. For supported platforms, and environments, see Section B.1, “Requirements”.

5.1.2.1. Prerequisite Check Script

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

  1. Obtain the script here and copy to the Oracle host machine, or a host that has Oracle Client installed.

  2. Login to sqlplus as sys

    SQL> sqlplus / as sysdba

    or

    SQL> sqlplus sys as sysdba
  3. Install the package as follows

    SQL> @pre-req-checker.sql

The above package install step is one time only - once installed, you can run it as many times as required, or DROP it afterwards if you no longer need it.

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

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

Substituting variables as follows:

  • target - The flavour of DB that you are replicating to, one of 'ORACLE','MYSQL','BATCH'

  • schema - A comma separated list of schmeas that you will be replicating

  • path - The local OS path the pre-req-cheker script will log the results of the scan too

  • user - (Optional) The tungsten tracking schema user, if already created

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','/home/oracle/log');
>>Checks complete, results in /home/oracle/log/prereq-2018060121134.log
PL/SQL procedure successfully completed.

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

shell> cat /home/oracle/log/prereq-201709021134.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]

5.1.2.2. Prepare the Oracle OS System User

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

  • Account belongs to the same group as the Oracle software owner (Typically oinstall) so it can read Oracle files.

  • OS Account environment is set correctly. This can be configured by ensuring that the correct system variables are enabled within your .bash_profile, or your OS equivalent, for example:

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

    The values above are examples. The environment variables and assigned values should only be set if they are not already specified. 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.

  • Tungsten Replicator manages a number of tables in the source database for storing metadata regarding the configuration of the replicator and also for tracking extraction position. If you do not have a suitable tablespace you wish to use for this, you need to create one. This value will be used with the --oracle-redo-tablespace argument and the ALTER USER statement in the next step.

    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.

    ALTER DATABASE OPEN;
  • Next, you should enable archive logging and supplemental logging.

    Enabling Archive Logging requires a restart of the Oracle database, however supplemental logging can be enabled whilst the database is open.

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

    You can now confirm the status of archive logging. Checking the status again should show the archive log enabled:

    archive log list;

    Warning

    For RAC Deployments, archive logs must be enabled on all nodes within the cluster. The archive log destination needs to be on shared storage (cluster file system or ASM)

  • Next, create a replication user. The username and password will need to be used with the --replication-user and --replication-password arguments.

    Note

    For Heterogeneous Deployments like Oracle to Vertica or Oracle to Hadoop, or for Master-Master Deployments, 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 defined in your configuration

    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

    The example SQL below assumes a ServiceName of alpha

    CREATE USER tungsten_alpha IDENTIFIED BY "secret" DEFAULT TABLESPACE tungsten_ts;
    GRANT CONNECT TO tungsten_alpha;
    ALTER USER tungsten_alpha QUOTA UNLIMITED ON tungsten_ts;
    GRANT CREATE TABLE TO tungsten_alpha;
    GRANT CREATE VIEW TO tungsten_alpha;
    GRANT CREATE SEQUENCE TO tungsten_alpha;
    GRANT SELECT ANY TABLE TO tungsten_alpha;
    GRANT EXECUTE ON DBMS_FLASHBACK TO tungsten_alpha;

5.1.2.4. Prerequisites specific to Oracle Target Hosts

If the Target for replication is also Oracle, then the above user will also need creating on the target node, however in a target database, the tungsten user will be responsible for applying data changes from the source, therefore a higher level of privilege is required.

GRANT DBA TO tungsten_alpha;
GRANT UNLIMITED TABLESPACE TO tungsten_alpha;

Note

If you prefer not to grant the DBA role, then you will need to apply all the appropriate object privileges to allow this user to perform all DML against all target objects, and to be able to execute DDL in the target Schema if full schema level replication is configured.

5.1.2.5. Prerequisites specific to Offboard Installations

The following steps only need to be completed if you are installing the replicator in an offboard configuration. For more details on how to deploy this configuration, see Section 5.2.3, “Deploying an Offboard Configuration” or Section 5.2.4, “Deploying on Oracle RAC”

There are two offboard installation methods:

  1. FETCHER installed on Oracle host and MINE and Extractor processes installed on the offboard host.

  2. MINE and Extractor installed on the offboard host, with NFS mounts to the source Redo and Archive Logs.

5.1.2.5.1. Prepare the Oracle Client

The offboard host will require connectivity through to the Source Database, therefore you need to ensure that you have the appropriate Oracle Client software installed, that matches the release of your source database.

5.1.2.5.2. Export NFS Mounts

If you are choosing the offboard Installation method using NFS then you will need to configure NFS on the source to export the locations of both the Archive Logs and Redo Logs.

When mounting them on the offboard host, the path needs to match exactly, the path on the source. In this method of deployment, when we start the MINE process, it connects to the source via TNS and queries for the location of the Redo and Archive logs, it will then expect to find those logs in that location on the local host. This method of deployment effectively "spoofs" the replicator into thinking the database is actually local.

Example /etc/exports file on Source Host

/u01/app/oracle/oradata/ORCL    10.0.0.251(ro)
/u01/app/oracle/fast_recovery_area/ORCL/archivelog  10.0.0.251(ro)

Example /etc/fstab file on Offboard Host

10.0.0.24:/u01/app/oracle/oradata/ORCL  /u01/app/oracle/oradata/ORCL    nfs nofail  0   0
10.0.0.24:/u01/app/oracle/fast_recovery_area/ORCL/archivelog    »
  /u01/app/oracle/fast_recovery_area/ORCL/archivelog  nfs nofail  0   0

See Section 5.2.3.2.1, “Configure NFS Mounts” for a more detailed step-by-step guide

5.1.2.6. Prepare TNS Entries

If you do not already have TNS Entries configured, you need to ensure that you have them setup. If you are planning to install via the Off-Board method, TNS entries need to exist on both DB Nodes AND the Off-Board host and they should have the same TNS Alias.

If you are installing against RAC, the you should also use the same TNS Alias on each RAC Node, resolving to it's own local instance. The TNS Alias on the Off-Board host should have the same alias and use the correct RAC TAF syntax

Test the TNS Entries to ensure they work

shell> sqlplus tungsten_<servicename>/<password>@TNSENTRY