巧用dblink结合oracle快照实现两台服务器的数据同步

ORACLE版本：ORACLE 9I

ORACLE 9I实际上以物化视图代替了ORACLE 8.5的快照，但快照在ORACLE 9I中同样能用。

--名词说明：源——被同步的数据库
                      目的——数据要同步到的数据库

一、创建dblink

--1、在目的数据库上，创建dblink

drop public database link dblink_orc92_183;
Create public DATABASE LINK dblink_orc92_183 CONNECT TO bst114 IDENTIFIED BY password USING 'orc92_183';

--dblink_orc92_183  数据链名称
--bst114     源数据库的用户名
--password   源数据库的密码
--orc92_183  ORACLE SID实例名

二、创建快照：

--1、在源和目的数据库上分别创建要被同步的表（注：表结构可以不同，这里为使演示简单两个数据库的表结构设为相同）
drop table user;
create table user(id number(10) primary key,name varchar2(30),age number(10));

--2、在目的数据库上，测试dblink
select * from  user@dblink_orc92_182;
select * from user;

--3、在源数据库上，创建要同步表的快照日志
Create snapshot log on user;

--4、在目标数据库上创建快照(被同步（源）数据库服务必须启动)
Create snapshot sn_user as select * from  user@dblink_183;

--5、设置快照刷新时间
Alter snapshot sn_user refresh fast Start with sysdate next sysdate+30/24*60*60;
--oracle自动在当前时间立即进行第一次快速刷新，以后每隔30秒快速刷新一次

三、创建用于同步本地表与远程表(源数据库)数据的触发器

由于创建快照后，快照在目标数据库表现为会创建一个数据表sn_user，此表根据上一步骤设置的快照刷新时间自动与源数据库的表

user同步，要想实现两个数据库的真正同步，只需针对sn_user表创建一个触发器即可解决，即通过触发器实现往目的数据库的指定

数据表中插入数据，实现本地数据与目的数据库表的数据同步。触发器代码如下：

CREATE OR REPLACE TRIGGER BST114.TRI_USER_AFR
AFTER DELETE OR INSERT OR UPDATE
ON BST114.SN_user 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
    tmp_id number(10):=-1;
    flag number(3):=0;
begin

  dbms_output.put_line('begin');

  if inserting then         
      for p in(select id from user where id=:new.id)
      loop
        tmp_id:=p.id;
      end loop;
      
      dbms_output.put_line(tmp_id||'===------------');

      if (tmp_id=-1) then
          insert into user(id,name,age)
          values(:new.id,:new.name,:new.age);
      end if;
  end if;
  
  if updating then

     dbms_output.put_line('updated');

     for p in(select name,age from user where id=:old.id)
     loop
         if (p.name!=:new.name) or (p.age!=:new.age) then
              update user set name=:new.name,age=:new.age where id=:old.id;
         end if;
     end loop;
  end if;
  
  if deleting then

      dbms_output.put_line('deleted');

      delete from user where id=:old.id;
  end if;

  dbms_output.put_line('end');

end TRI_USER_AFR;

四、测试

   针对源数据库的表USER进行增删改操作，观察目的数据库的表sn_user表与USER表的变化。如果目的数据库的表sn_user与表USER

都随源数据库的USER表变化而变化，恭喜你，两个数据库之间的单向同步就大功告成了！要想实现两个数据库的双向同步，只需将源

数据库与目标数据库的身份互换一下，重复以上操作即可！

五、关于快照的刷新方式

--1、FAST
Alter snapshot sn_test_182 refresh fast Start with sysdate next 30/(24*60*60) with primary key;
--oracle自动在当前时间立即进行第一次快速刷新，以后每隔30秒快速刷新一次

--2、COMPLETE
Alter snapshot sn_user refresh complete Start with 30/(24*60*60) next sysdate+30/24*60*60;
--oracle自动在30钞后进行第一次完全刷新，以后每隔1天完全刷新一次

--3、手动刷新快照
begin
dbms_refresh.refresh('sn_user');
end;

手动刷新2：EXEC DBMS_SNAPSHOT.REFRESH('sn_user','F');

--4、查询快照最后一次刷新时间与下一次的刷新时间

--更改ORACLE的日期显示时间
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES; 

－－＝＝end＝＝－－-