oracle数据同步方案

oracle 数据同步方案（DBLink）一、什么是dbLink两台不同的数据库服务器，从一台数据库服务器（例如A数据库服务器）的一个用户读取另一台数据库服务器（例如B数据库服务器）下的某个用户的数据，这个时候可以使用dblink。

其实dblink和数据库中的view差不多，建dblink的时候需要知道待读取数据库的ip地址，ssid以及数据库用户名和密码。

二、DbLink的创建步骤说明：A数据库服务器是指-需要同步的数据库服务器，B数据库服务器是指-被同步的数据库服务器，以下文档中简称A数据库与B数据库。

1、在目的数据库上（A数据库），创建dblinkdrop public database link dblink_orc92_182;Create public DATABASE LINK dblink_orc92_182 CONNECT TO bst114IDENTIFIED BY password USING ''orc92_192.168.254.111'';注释：--dblink_orc92_182 是dblink_name即创建的dblink名称--bst114 是username即A数据库的用户--password 是password即A数据库用户名密码--''orc92_192.168.254.111'' 是远程数据库名即B数据库的名称，为了方便期间命名最好是“数据库名称+ip”2、在源数据库（B数据库）和目的数据库（A数据库）上创建要同步的表说明：不管是A数据库还是B数据库上创建的表最好有主键约束,快照才可以快速刷新drop table test_user;create table test_user(id number(10) primary key,name varchar2(12),age number(3));3、在目的数据库（A数据库）上，测试dblink说明：在A数据库上执行如下查询语句select * from test_user@dblink_orc92_182; //查询的是源数据库的表select * from test_user;注释：--dblink_orc92_182是刚才一步创建的dblink名称--test_user同步表名称4、在源数据库（B数据库）上，创建要同步表的快照日志说明：在B数据库上执行如下查询语句Create snapshot log on test_user;5、创建快照，在目的数据库（A数据库）上创建快照Create snapshot sn_test_user as select * from test_user@dblink_orc92_182;注释：-- sn_test_user 快照的名称6、设置快照刷新时间说明：在B数据库上执行如下查询语句，只能选择一种刷新方式,推荐使用快速刷新,这样才可以用触发器双向同步。

快速刷新Alter snapshot sn_test_user refresh fast Start with sysdate next sysdate with primary key;--oracle马上自动快速刷新，以后不停的刷新,只能在测试时使用.真实项目要正确权衡刷新时间.完全刷新Alter snapshot sn_test_user refresh complete Start with sysdate+30/24*60*60 next sysdate+30/24*60*60;--oracle自动在30秒后进行第一次完全刷新，以后每隔30秒完全刷新一次7、手动刷新快照,在没有自动刷新的情况下,可以手动刷新快照说明：在B数据库上执行如下查询语句手动刷新方式1begindbms_refresh.refresh(''sn_test_user'');end;手动刷新方式2EXEC DBMS_SNAPSHOT.REFRESH(''sn_test_user'',''F''); //第一个参数是快照名,第二个参数F 是快速刷新C 是完全刷新.8、修改会话时间格式说明：在B数据库上执行如下查询语句ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'';9、查看快照最后一次刷新时间说明：在B数据库上执行如下查询语句SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;10、查看快照下次执行时间说明：在B数据库上执行如下查询语句select last_date,next_date,what from user_jobs order by next_date;11、打印调试信息说明：在B数据库上执行如下查询语句dbms_output.put_line(''use ''||''plsql'');12、如果你只想单向同步（B同步到A）说明：那么在目的数据库（A数据库）创建以下触发器(当源数据库（B数据库）表改变时,目的数据库（A数据库）表跟着改变,但目的数据库（A数据库）表改变时,源数据库（B数据库）表不改变).说明：在B数据库上执行如下查询语句create or replace trigger TRI_test_user_AFRafter insert or update or delete on sn_test_userfor each rowbeginif deleting thendelete from test_user where id=:old.id;end if;if inserting theninsert into test_user(id,name)values(:new.id,:);end if;if updating thenupdate test_user set name=: where id=:old.id;end if;end TRI_test_user_AFR;13、如果你想双向同步（A同步到B，B也可以同步到A）说明：请在源数据库（B数据库）中执行前6步,并在双方都创建以下触发器(当源数据库（B 数据库）表改变时,目的数据库（A数据库）表跟着改变,目的数据库（A数据库）表改变时, 源数据库（B数据库）表也改变)CREATE OR REPLACE TRIGGER BST114.TRI_TEST_USER_AFRAFTER DELETE OR INSERT OR UPDA TEON BST114.SN_TEST_USERREFERENCING NEW AS NEW OLD AS OLDFOR EACH ROWdeclaretmp_id number(10):=-1;begindbms_output.put_line(''begin'');if inserting then--select id into tmp_id from test_user where id=:new.id;for p in(select id from test_user where id=:new.id)looptmp_id:=p.id;end loop;dbms_output.put_line(tmp_id||''===------------'');if (tmp_id=-1) theninsert into test_user(id,name,age)values(:new.id,:,:new.age);end if;end if;if updating thendbms_output.put_line(''updated'');for p in(select name,age from test_user where id=:old.id)loopif (!=:) or (p.age!=:new.age) thenupdate test_user set name=:,age=:new.age where id=:old.id;end if;end loop;end if;if deleting thendbms_output.put_line(''deleted'');delete from test_user where id=:old.id;end if;dbms_output.put_line(''end'');end TRI_test_user_AFR;--为防止双向同步触发器死循环,所以要在触发器中增加一些判断,阻止死循环.--以上同步原理概括：1.首先创建一个dblink,可以访问远程数据库2.在本地创建一个快照,映射远程数据表,当远程数据表有变化时,会反应到快照中.3.由于快照类似于视图表,所以在本地为快照创建一个触发器,当快照有变化时,会触发相应事件.4.在触发器中写同步数据的代码.--附:快照刷新时间参数说明一天的秒数=24小时*60分钟*60钞所以要想在30秒后刷新,参数应该这样写sysdate+30/(24*60*60)1分钟==sysdate+60/(24*60*60)一天的分钟数=24小时*60分钟一分钟也可以这样写sysdate+1/(24*60)30分钟==sysdate+30/(24*60)60分钟==sysdate+60/(24*60)以此类推1小时==sysdate+1/24==sysdate+60/(24*60)1天==sysdate+1一个月==sysdate+30。