Oracle Simple Replication 筆記

本篇為簡單的 Oracle 資料表同步設定，自己做個紀錄以免日後又忘記，讓資料庫專家們見笑了。目標是將 Source 資料庫中的 ABC 資料表同步到 Destination 資料庫中，並給予一個資料表名稱 TPE_ABC。隨後建立 Trigger 當 TPE_ABC 資料有異動時，將資料寫入另外一個總資料表 ALL_ABC。

DROP SNAPSHOT LOG ON SOURCE.ABC;

丟棄來源資料庫的 SNAPSHOT LOG

DROP MATERIALIZED VIEW DESTINATION.TPE_ABC;

丟棄目的端資料庫的 DROP MATERIALIZED VIEW

CREATE MATERIALIZED VIEW LOG ON SOURCE.ABC
TABLESPACE SOURCE_MLOG
WITH PRIMARY KEY;

在來源資料庫建立 MATERIALIZED VIEW LOG

CREATE MATERIALIZED VIEW DESTINATION.TPE_ABC
TABLESPACE TPE
NOLOGGING
BUILD DEFERRED
USING INDEX TABLESPACE TPE_I
REFRESH FAST
NEXT SYSDATE + 1/2
WITH PRIMARY KEY
AS SELECT
A, B, C, D,
ROWID AS SOURCE_ROWID
FROM SOURCE.ABC@SOURCE_DATABASE;

由來源資料庫建立 MATERIALIZED VIEW

CREATE TRIGGER "DESTINATION"."TR_TPE_ABC"
AFTER INSERT OR UPDATE OR DELETE OF
"A", "B", "C", "D"
ON "DESTINATION"."TPE_ABC"
FOR EACH ROW
BEGIN

IF ( DELETING OR UPDATING ) THEN
DELETE ABC WHERE SOURCE_DB = 'SOURCE_DATABASE'
AND A = :OLD.A;
END IF;

IF INSERTING OR UPDATING THEN
INSERT INTO ALL_ABC
( A, B, C, D, SOURCE_DB, SOURCE_ROWID, REC_SEQ_NO)
VALUES
( :NEW.A, :NEW.B, :NEW.C, :NEW.D, 'SOURCE_DATABASE', :NEW.SOURCE_ROWID, SEQ_SOURCE_DATABASE.NEXTVAL);
END IF;

建立 Trigger 當資料有異動時寫入