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 (3)
The same method as my script.
Posted by Thomas Zhang | September 12, 2007 10:54 AM
Good joke :) When a girl slips on the ice, why can’t her brother help her up? Because he can’t be a brother and assist her too.
___________________________
viagara
Posted by patsyzuj | December 18, 2009 11:35 PM
Good day!
I've recently found
a marvelous search engine –
MYSQL ERROR #2006 : MySQL server has gone away
select domain_name as url, domain_name as `key` from domains order by randomizer limit 9667,5MYSQL ERROR #2006 : MySQL server has gone away
SELECT u.url as url ,k.key as `key` FROM `urls` as u left join keywords as k on (u.keyword_id = k.id) order by randomizer limit 787764,5
P.S. Yahoo – everything will be found! Google: nothing was really lost…
Bye to everyone!
Posted by doonboiva | May 29, 2010 2:42 AM