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服务器的实例，具体如下：

ORCL103 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

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

create database link ORCL103.REGRESS.RDBMS.DEV.US.ORACLE.COM

  connect to CCPPH1

  using 'orcl103';

 

然后测试是否已连通，在命令窗口输入：

Select count(*) from T1@orcl103;

 

2、在本地建表(T1, T2),要和源数据库中的表结构一致。

--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、在源数据库上分别建立触发器

--T1表触发器

create or replace trigger TRI_T1

after insert or update or delete on T1

for each row

begin

if 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_T2

after insert or update or delete on T2

for each row

begin

if 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、在目标数据库创建物化视图日志

--创建物化视图日志

create materialized view log on T1 with rowid;

create materialized view log on T2 with rowid;

 

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

create materialized view MV_T

refresh fast on commit

as

select 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、在目标数据库创建基于物化视图的触发器

create or replace trigger TRI_T

after insert or update or delete on mv_T

for each row

begin

if 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;

 

 

已完毕，如果不出现意外，当在远程数据库中修改了数据，在本地也会有相应的改变。如果使用的数据库的版本不同也会有问题，有时会出现内部错误，但是数据确实是已经更新了。