ORACLE＿远程数据库同步

在本地建立两张表(T1,T2)，这两张表和远程的表结构一样，通过触发器实现数据的同步，然后对本地的两张表进行物化，再在物化视图上建立触发器，实时的修改T表(并没有考虑大字段的情况)。
该方案是测试成功的，源数据库是oracle10.2.0.3.0,目标数据库是oracle10.2.0.3.0,并且做到了数据的实时更新。
1、在源数据库创建database link,确保两台服务器可以连通。
首先在oracle 的E:\oracle\product\10.2.0\db_1\network\ADMIN\tnsnames.ora配置文件里添加远程oracle服务器的实例，具体如下：

SQL code

ORCL103 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )

然后执行创建database link的语句：

SQL code

create database link ORCL103.REGRESS.RDBMS.DEV.US.ORACLE.COM  connect to CCPPH1  using 'orcl103';

然后测试是否已连通，在命令窗口输入：
Select count(*) from T1@orcl103;
2、在本地建表(T1, T2),要和源数据库中的表结构一致。

SQL code

--T1表Create table T1 as select * from T1@orcl103;alter table T1 add constraint PK_ID primary key (ID);--T2表Create table T2 as select * from T2@orcl103;alter table T2 add constraint PK_ID primary key (ID);

3、在源数据库上分别建立触发器

SQL code

--T1表触发器create or replace trigger TRI_T1after insert or update or delete on T1for each rowbeginif deleting then      dbms_output.put_line('删除');      delete from T1@orcl101 where id=:old.id;end if;if inserting then      dbms_output.put_line('插入');      insert into T1@orcl101(id,name)      values(:new.id,:new.name);end if;if updating then     dbms_output.put_line('修改');     update T1@orcl101 set id=:new.id,name=:new.name where id=:old.id;end if;end TRI_T1;--T2表触发器create or replace trigger TRI_T2after insert or update or delete on T2for each rowbeginif deleting then      dbms_output.put_line('删除');      delete from T2@orcl101 where id=:old.id;end if;if inserting then      dbms_output.put_line('插入');      insert into T2@orcl101(id,name)      values(:new.id,:new.name);end if;if updating then     dbms_output.put_line('修改');     update T2@orcl101 set id=:new.id,name=:new.name where id=:old.id;end if;end TRI_T2;

4、在目标数据库创建物化视图日志

SQL code

--创建物化视图日志create materialized view log on T1 with rowid;create materialized view log on T2 with rowid;

5、在目标数据库创建物化视图

SQL code

create materialized view MV_Trefresh fast on commitasselect t1.rowid as t1rowid,t2.rowid as t2rowid,t1.id,t1.name,t2.id as t2id,t2.name as t2name from T1 t1,T2 t2 where t1.id = t2.id;

6、在目标数据库创建基于物化视图的触发器

SQL code

create or replace trigger TRI_Tafter insert or update or delete on mv_Tfor each rowbeginif deleting then      dbms_output.put_line('删除');      delete from T where ID=:old.ID;end if;if inserting then      dbms_output.put_line('插入');      insert into T(id,name)      values(:new.id,:new.name);end if;if updating then      dbms_output.put_line('修改');     update T set id=:new.id,name=:new.name where id=:old.id;end if;end TRI_T;

已完毕，如果数据库的版本不同，有时在执行更新的时候会报内部错误，但是数据确实是已经更新过来了。