Oracle materialized view log is good for capture the DML change, however it's not flexible, for example, the table must have a physical primary key defined for asyncdata, and you have no wait to skip the change capture in case you want to do some massive DML both on source table and target table (Oracle 11g has this feature, to skip the materialized view log at session level).
So I will introduce using triggers in Oracle for the same features of materialized view log, first you need to create a sequence object (The source table name is ASYNCDATA).
CREATE SEQUENCE MLOG$_ASYNCDATA_SEQ START WITH 1 CACHE 20;
Then create a log table, the first two columns' name should be "SEQUENCE$$" and "DMLTYPE$$". These two columns' name are derived from oracle materialized view log table.
CREATE TABLE MLOG$_ASYNCDATA
(
SEQUENCE$$ NUMBER NOT NULL PRIMARY KEY, -- The DML sequence column
DMLTYPE$$ CHAR, -- The DML type column
COL1 NUMBER -- The primary key column
);
The last step is to create log capture trigger on source table. In this case, the DML will not be captured if you run it under ASYNCADMIN schema.
CREATE OR REPLACE TRIGGER ASYNCDATA_MLOG_TRG
BEFORE INSERT OR UPDATE OR DELETE
ON ASYNCDATA
FOR EACH ROW
WHEN (USER <> 'ASYNCADMIN')
BEGIN
IF INSERTING THEN
INSERT INTO MLOG$_ASYNCDATA (SEQUENCE$$, DMLTYPE$$, COL1) VALUES
(MLOG$_ASYNCDATA_SEQ.NEXTVAL, 'I', :NEW.COL1);
ELSIF UPDATING THEN
IF UPDATING('COL1') THEN
INSERT INTO MLOG$_ASYNCDATA (SEQUENCE$$, DMLTYPE$$, COL1) VALUES
(MLOG$_ASYNCDATA_SEQ.NEXTVAL, 'D', :OLD.COL1);
INSERT INTO MLOG$_ASYNCDATA (SEQUENCE$$, DMLTYPE$$, COL1) VALUES
(MLOG$_ASYNCDATA_SEQ.NEXTVAL, 'I', :NEW.COL1);
ELSE
INSERT INTO MLOG$_ASYNCDATA (SEQUENCE$$, DMLTYPE$$, COL1) VALUES
(MLOG$_ASYNCDATA_SEQ.NEXTVAL, 'U', :NEW.COL1);
END IF;
ELSE
INSERT INTO MLOG$_ASYNCDATA (SEQUENCE$$, DMLTYPE$$, COL1) VALUES
(MLOG$_ASYNCDATA_SEQ.NEXTVAL, 'D', :OLD.COL1);
END IF;
END;
/
If you can make sure that the data is isolated. We can build a master to master replication with asyncdata script.
Comments (1)
The same method as my script.
Posted by Thomas Zhang | September 12, 2007 10:54 AM