Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

EnterpriseDB: Create Publication Oracle Tips by Burleson

A publication is a replication job.  The replication job may be one or more tables.  This step creates a series of tables, triggers and a package when successfully executed.

The tables created include definitions about which tables are to be replicated and what replication sets exist.  The table structures and table names may change from release to release so I will not detail them here.  I don't think the specific tables are as important to understand as is understanding how the replication itself works.

For each table replicated, an identical table, with replication columns added, is created.  This is referred to as a shadow table.  The cleanup job needs to be scheduled to run and cleanup these shadow tables periodically.

Each table to be replicated also has a set of triggers created for it.  The replication server creates an Insert, Update and Delete trigger for each table.  These triggers are responsible for inserting a record in the appropriate shadow table.

Using the Employees table as an example, the replication server will create the following shadow table and the following triggers:

CREATE TABLE RRST_HR_LOCATIONS
(
  RREP_SYNC_ID          NUMBER                  NOT NULL,
  RREP_COMMON_ID        NUMBER,
  RREP_OPERATION_TYPE   CHAR(1),
  LOCATION_ID           NUMBER(4),
  STREET_ADDRESS        VARCHAR2(40),
  POSTAL_CODE           VARCHAR2(12),
  CITY                  VARCHAR2(30),
  STATE_PROVINCE        VARCHAR2(25),
  COUNTRY_ID            CHAR(2),
  RREP_OLD_LOCATION_ID  NUMBER(4),
  PRIMARY KEY
 (RREP_SYNC_ID)
); 

CREATE OR REPLACE TRIGGER RRPI_HR_EMPLOYEES
 AFTER INSERT ON HR.EMPLOYEES
--------------------------------------------------------
--  GENERATED BY EnterpriseDB Replicator
--  2006-12-30 18:35:56 EST
--  DON'T MAKE CHANGES IN THE CODE.
--------------------------------------------------------

 FOR EACH ROW

BEGIN
 INSERT INTO rrst_hr_employees
 (rrep_sync_id, rrep_operation_type, employee_id, first_name, last_name,
  email, phone_number, hire_date, job_id, salary, commission_pct,
  manager_id, department_id)
 VALUES (rrep_tx_seq.NEXTVAL, 'I', :NEW.EMPLOYEE_ID, :NEW.FIRST_NAME,
         :NEW.LAST_NAME, :NEW.EMAIL, :NEW.PHONE_NUMBER, :NEW.HIRE_DATE,
         :NEW.JOB_ID, :NEW.SALARY, :NEW.COMMISSION_PCT, :NEW.MANAGER_ID,
         :NEW.DEPARTMENT_ID);
END;
/

CREATE OR REPLACE TRIGGER RRPU_HR_EMPLOYEES
 AFTER UPDATE ON HR.EMPLOYEES
--------------------------------------------------------
--  GENERATED BY EnterpriseDB Replicator
--  2006-12-30 18:35:56 EST
--  DON'T MAKE CHANGES IN THE CODE.
--------------------------------------------------------

 FOR EACH ROW

BEGIN
 INSERT INTO rrst_hr_employees
 (rrep_sync_id, rrep_operation_type, employee_id, first_name, last_name,
  email, phone_number, hire_date, job_id, salary, commission_pct, manager_id,
  department_id, rrep_old_EMPLOYEE_ID)
 VALUES (rrep_tx_seq.NEXTVAL, 'U',:NEW.EMPLOYEE_ID, :NEW.FIRST_NAME,
         :NEW.LAST_NAME, :NEW.EMAIL, :NEW.PHONE_NUMBER, :NEW.HIRE_DATE,
         :NEW.JOB_ID, :NEW.SALARY, :NEW.COMMISSION_PCT, :NEW.MANAGER_ID,
         :NEW.DEPARTMENT_ID, :OLD.EMPLOYEE_ID);
END;
/

CREATE OR REPLACE TRIGGER RRPD_HR_EMPLOYEES
 AFTER DELETE ON HR.EMPLOYEES
--------------------------------------------------------
--  GENERATED BY EnterpriseDB Replicator
--  2006-12-30 18:35:56 EST
--  DON'T MAKE CHANGES IN THE CODE.
-------------------------------------------------------- 

 FOR EACH ROW

BEGIN
 INSERT INTO rrst_hr_employees
 (rrep_sync_id, rrep_operation_type, employee_id)
 VALUES (rrep_tx_seq.NEXTVAL, 'D', :OLD.EMPLOYEE_ID);
END;
/

Obviously, this code is subject to change.  DO NOT make changes to this code or you run the risk of breaking replication and/or not allowing yourself to upgrade.

The package that is created by the replication server contains procedures and functions that the replication server calls to replicate data and cleanup the tables.

There are performance considerations to think about before implementing any kind of replication.  Trigger based replication has a particularly high performance impact for two reasons: 

* SQL to PL/SQL context switch – Anytime pure SQL has to switch to procedural code and any time procedural code is switched to pure SQL, there is what is referred to as a context switch.  This is a measurable delay in processing.  It is minimal, but it is measurable. 

* Double IO – By taking each DML statement and inserting the data into another table, the trigger is doubling the apparent IO.  One insert is equal to two inserts, one update is equal to one update and one insert, and one delete is equal to one delete and one insert.

On large data loads, the additional overhead may be unacceptable. Alternatively, you may choose to unschedule replication during the load and then manually reschedule and resynchronize afterwards.

This is not a problem specific to the EnterpriseDB replication solution.  This is a general problem that must be addressed by any replication solution. 

The EnterpriseDB Replication Console has addressed this issue by enabling and disabling the triggers intelligently.  The triggers are turned on and off as needed to perform the replication.  Even though this is better than just leaving the triggers enabled all the time, there is still additional overhead with this type of replication method.

If the triggers are too much of a burden, you may want to use Oracle Streams to capture changes and propagate them to a less critical instance and then use EnterpriseDB replication to read from that instance.  This is just one possible solution.  The downside is that it would add additional Oracle license costs to create the additional instance if using Enterprise or Standard Editions.  Depending on size, the free Oracle 10g XE database may be a good candidate for this use.

In this example, I will be choosing to replicate all of the HR tables, except the history tables.  I could set up multiple publications to handle the replication but I am instead grouping all of these objects together into a single publication.  This makes sense as some of the tables have foreign key relationships to other tables.

To get to the Create Publication dialog (Figure 7.9), right click on the Publication Database that was created above and choose Create Publication.

Figure 7.9: Create Publisher Replication Objects

Enter a name for the publication.  This name can be anything that makes sense to you and helps you remember the objects being replicated.

The Snapshot-only replication checkbox is the equivalent of the Snapshot replication discussed in Chapter 5 in the section on Migrating from Oracle.

Check the boxes next to the tables to be replicated.  If there were a need to limit the number of rows being replicated, say only employees in the Accounting department were to be replicated to the Accounting workgroup server, the Filter Clause (Figure 7.10) could be set appropriately to do so.

For this example, I will not be limiting the data to be replicated.

Figure 7.10: Create Replication Data Filters

     

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.