5.1.1. Oracle Redo Reader Replication Operation

Tungsten Replicator using the Oracle Redo Reader extracts data from Oracle using a specific sequence of operations, outlined as follows:

  • Oracle Redo Reader (orarr) reads the Oracle redo, supplemental and archive logs directly, extracting information about individual transactions and storing them within a series of files called PLOGS. This process is typically referred to as the MINE process.

  • In an Offboard installation, an additional process called the FETCHER would run on the Oracle Database Host. This process will pass the Redo Logs and Archive Logs to the MINE process on the offboard server, where the extraction of transactions will then continue.

  • Tungsten Replicator reads the PLOGS generated by the MINE process and converts the content into Transaction History Logs (THL).

  • Downstream Replicators process the THL appropriately for the configured target.

For installation, follow the steps outlined in Section 5.1.3.1, “Installing a Standalone Oracle Extractor for Heterogeneous Deployments” followed by the appropriate section listed below to configure the target environment.

5.1.1.1. DDL Operations

At present, NO DDL is replicated in a Heterogeous Deployment.

The following table clarifies which basic DDL statements will be replicated between Oracle and Oracle. Any DDL not mentioned below, can be assumed to NOT be replicated.

DDL Oracle Notes
CREATE TABLE / CREATE TABLE AS SELECT See notes (1)(2)  
DROP TABLE Replicated  
ALTER TABLE..ADD COLUMN Replicated  
ALTER TABLE..DROP COLUMN Replicated  
ALTER TABLE MODIFY COLUMN Replicated  
ALTER TABLE..ADD CONSTRAINT..PRIMARY KEY See notes (1)  
ALTER TABLE..ADD CONSTRAINT..FOREIGN KEY See notes (1)  
ALTER TABLE..ADD CONSTRAINT..UNIQUE KEY See notes (1)  
ALTER TABLE..DROP CONSTRAINT.. See notes (1)  
ALTER TABLE ENABLE|DISABLE CONSTRAINTS See notes (1) Providing recommendation in Note 1 followed to explicitly name constraints during creation
TRUNCATE TABLE Replicated  
CREATE SEQUENCE Not replicated  
CREATE (OR REPLACE) TRIGGER Not replicated (3)  
DROP TRIGGER See Notes (3)  
CREATE VIEW Replicated All tables that are part of the view must also be replicated
DROP VIEW Replicated  
CREATE MATERIALIZED VIEW See Notes See Section 5.1.1.2, “Handling Materialized Views”
CREATE INDEX Replicated  
ALTER INDEX See notes (4)  
DROP INDEX Replicated  
CREATE (OR REPLACE) PACKAGE Not replicated  
DROP PACKAGE Not replicated  
CREATE (OR REPLACE) PACKAGE BODY Not replicated  
DROP PACKAGE BODY Not replicated  
CREATE (OR REPLACE) PROCEDURE Not replicated  
DROP PROCEDURE Not replicated  
CREATE (OR REPLACE) FUNCTION Not replicated  
DROP FUNCTION Not replicated  
CREATE CLUSTER Replicated  
DROP CLUSTER Replicated  
ALTER CLUSTER Replicated  
GRANT... / REVOKE... See notes ONLY Object grants are replicated, SYSTEM grants are not.

Notes from the above table:

  • Note 1

    When issuing a CREATE TABLE and defining the Primary, Unique or Foreign Key columns, If you do not specify a name for the keys, Oracle, by default will generate a system defined name, something like SYS_C123245. Whilst the DDL is replicated OK and the key is created on the target, the name of the key on the target will differ as the statement is replicated as it was entered on the source, therefore further operations to specifically disable/enable the primary key on the source, will fail on the target as the primary key won't be found.

    Solution: Always explicitly specify the name for your keys using the CONSTRAINT clause during table create, or as separate statements after creating the table, examples:

    CREATE TABLE tab1 (
    iCol NUMBER,
    aCol VARCHAR2(5),
    CONSTRAINT pktab1 PRIMARY KEY (iCol));
    
    CREATE TABLE tab2 (
    nCol NUMBER,
    iCol NUMBER,
    vCol VARCHAR2(5),
    CONSTRAINT
    pktab2 PRIMARY KEY (nCol),
    CONSTRAINT fktab1 FOREIGN
    KEY (iCol) REFERENCES tab1 (iCol));

    or

    CREATE TABLE tab3 (
    iCol NUMBER,
    aCol VARCHAR2(5)
    );
    
    ALTER TABLE tab3
    ADD CONSTRAINT pktab3
    PRIMARY KEY (iCol);
    
    CREATE TABLE tab4 (
    nCol NUMBER,
    iCol NUMBER,
    vCol VARCHAR2(5)
    );
    
    ALTER TABLE tab4
    ADD CONSTRAINT pktab4
    PRIMARY KEY (nCol);
    
    ALTER TABLE tab4
    ADD CONSTRAINT
      fktab4
      FOREIGN KEY (iCol) REFERENCES tab3 (iCol);
  • Note 2

    All ALTER TABLE PARTITION table DDL is supported and replicated but by default this is disabled at install, the following needs adding to the tungsten_MINE.ddc file to enable this. (See Section 5.1.5 "Oracle Redo Reader Tuning" for details on how to apply these changes)

    set _DDL_REPLICATE_PARTITIONS=YES
  • Note 3

    Triggers are not replicated, however if the target Oracle instance was created and provisioned using a point in time export, then the triggers will exist. Also, in some cases, you may choose to maintain structural integrity between Source and Target and choose to create the triggers manually. In both instances, whenever triggers exist in the target schema, they MUST be disabled or carefully coded. If the resulting trigger updates data within the same target schema, then you would end up with data drift and unexpected results.

  • Note 4

    Only ALTER INDEX RENAME and ALTER INDEX (IN-)VISIBLE statements are replicated, all others variations are not.

5.1.1.2. Handling Materialized Views

Materialized views need to be understood and handled with care.

A Materialized view is similar to a standard view, however rather than executing the View query when the view is called, a Materialized view stores the results of the view in an underlying table. Various options during creation time allow the user to control the frequency of when the data in the view is refreshed to reflect the actual referenced tables.

When using &replicator_name; with Redo Reader, a CREATE MATERIALIZED view is represented in Oracles Redo Logs as a CREATE TABLE, and therefore when replicated, a table will be created in the target - regardless of whether the Target is Oracle or not. In addition, during creation, there will be individual INSERT statement generated for every row to populate the table.

When the Materialized view is refreshed on the source, this will generate a series of DELETES followed by a series of INSERTS for EVERY row in the view.

It is important to understand this process and make a decision during configuration on how to handle Materialized Views.

Within an Oracle to Oracle Topology

If you are replicating between Oracle source and target, it is recommended that you manually create the Materialized view on the Target with the same REFRESH option as per the source, then exclude the views from replication thus allowing the Target database to handle the refresh. Excluding the view can be done by using the ignore filter within the replicator or by unpreparing the views within the redo reader, both options are detailed here:

Within a Heterogenous Topology

When replicating from an Oracle source to a non-Oracle target, you should decide whether or not you need the Materialized View in your target environment, if so, then providing your target can handle the CREATE TABLE statement and the subsequent DELETES and INSERTS then you do not need to change anything, otherwise, follow the recommendations above for excluding the views from replication

If you choose to NOT exclude Materialize Views from replication, you must remember that a refresh of the view will generate a DELETE for every row, followed by an INSERT for every row. If your view has 1000's of rows or more, this will generate a significantly large amount of replication traffic.