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 220.127.116.11, “Installing a Standalone Oracle Extractor for Heterogeneous Deployments” followed by the appropriate section listed below to configure the target environment.
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.
|CREATE TABLE / CREATE TABLE AS SELECT||See notes (1)(2)|
|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|
|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|
|CREATE MATERIALIZED VIEW||See Notes||See Section 18.104.22.168, “Handling Materialized Views”|
|ALTER INDEX||See notes (4)|
|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|
|GRANT... / REVOKE...||See notes||ONLY Object grants are replicated, SYSTEM grants are not.|
Notes from the above table:
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
during table create, or as separate statements after creating the
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));
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);
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)
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.
ALTER INDEX RENAME and
(IN-)VISIBLE statements are replicated, all others
variations are not.
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
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
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.