利用oracle快照dblink解决数据库表同步问题
jopen
13年前
<p>本实例已完全通过测试,单向,又向同步都可使用.</p> <p>--名词说明：源——被同步的数据库<br /> 目的——要同步到的数据库</p> <p>前6步必须执行,第6以后是一些辅助信息.</p> <p><span style="font-size:small;"><strong>--1、在目的数据库上，创建dblink</strong></span><br /> drop public database link dblink_orc92_182;<br /> Create public DATABASE LINK dblink_orc92_182 CONNECT TO bst114 IDENTIFIED BY password USING ''orc92_192.168.254.111'';<br /> --dblink_orc92_182 是dblink_name<br /> --bst114 是 username<br /> --password 是 password<br /> --''orc92_192.168.254.111'' 是远程数据库名</p> <p><br /> <span style="font-size:small;"><strong>--2、在源和目的数据库上创建要同步的表(最好有主键约束,快照才可以快速刷新)</strong></span><br /> drop table test_user;<br /> create table test_user(id number(10) primary key,name varchar2(12),age number(3));</p> <p><strong><span style="font-size:small;">--3、在目的数据库上，测试dblink</span></strong><br /> select * from test_user@dblink_orc92_182; //查询的是源数据库的表<br /> select * from test_user;</p> <p><span style="font-size:small;"><strong>--4、在源数据库上，创建要同步表的快照日志</strong></span><br /> Create snapshot log on test_user;</p> <p><span style="font-size:small;"><strong>--5、创建快照，在目的数据库上创建快照</strong></span><br /> Create snapshot sn_test_user as select * from test_user@dblink_orc92_182;</p> <p><span style="font-size:small;"><strong>--6、设置快照刷新时间(只能选择一种刷新方式,推荐使用快速刷新,这样才可以用触发器双向同步)</strong></span><br /> 快速刷新<br /> Alter snapshot sn_test_user refresh fast Start with sysdate next sysdate with primary key;<br /> --oracle马上自动快速刷新，以后不停的刷新,只能在测试时使用.真实项目要正确权衡刷新时间.</p> <p>完全刷新<br /> Alter snapshot sn_test_user refresh complete Start with sysdate+30/24*60*60 next sysdate+30/24*60*60;<br /> --oracle自动在30秒后进行第一次完全刷新，以后每隔30秒完全刷新一次</p> <p><span style="font-size:small;"><strong>--7、手动刷新快照,在没有自动刷新的情况下,可以手动刷新快照.</strong></span><br /> 手动刷新方式1<br /> begin<br /> dbms_refresh.refresh(''sn_test_user'');<br /> end;</p> <p>手动刷新方式2<br /> EXEC DBMS_SNAPSHOT.REFRESH(''sn_test_user'',''F''); //第一个参数是快照名,第二个参数 F 是快速刷新 C 是完全刷新.</p> <p><span style="font-size:small;"><strong>--8.修改会话时间格式</strong></span><br /> ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'';</p> <p><span style="font-size:small;"><strong>--9.查看快照最后一次刷新时间</strong></span><br /> SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;</p> <p><strong><span style="font-size:small;">--10.查看快照下次执行时间</span></strong><br /> select last_date,next_date,what from user_jobs order by next_date;</p> <p><strong><span style="font-size:small;">--11.打印调试信息</span></strong><br /> dbms_output.put_line(''use ''||''plsql'');</p> <p><span style="font-size:small;"><strong>--12.如果你只想单向同步,那么在目的数据库创建以下触发器(当源数据库表改变时,目的数据库表跟着改变,但目的数据库表改变时,源数据库表不改变).</strong></span><br /> create or replace trigger TRI_test_user_AFR<br /> after insert or update or delete on sn_test_user<br /> for each row<br /> begin<br /> if deleting then<br /> delete from test_user where id=:old.id;<br /> end if;<br /> if inserting then<br /> insert into test_user(id,name)<br /> values(:new.id,:new.name);<br /> end if;<br /> if updating then<br /> update test_user set name=:new.name where id=:old.id;<br /> end if;<br /> end TRI_test_user_AFR;</p> <p><span style="font-size:small;"><strong>--13.如果你想双向同步,请在源数据库中执行前6步,并在双方都创建以下触发器(当源数据库表改变时,目的数据库表跟着改变,目的数据库表改变时,源数据库表也改变)</strong></span><br /> CREATE OR REPLACE TRIGGER BST114.TRI_TEST_USER_AFR<br /> AFTER DELETE OR INSERT OR UPDATE<br /> ON BST114.SN_TEST_USER <br /> REFERENCING NEW AS NEW OLD AS OLD<br /> FOR EACH ROW<br /> declare<br /> tmp_id number(10):=-1;<br /> begin</p> <p> dbms_output.put_line(''begin'');<br /> if inserting then<br /> --select id into tmp_id from test_user where id=:new.id; <br /> for p in(select id from test_user where id=:new.id)<br /> loop<br /> tmp_id:=p.id;<br /> end loop;<br /> <br /> dbms_output.put_line(tmp_id||''===------------'');<br /> if (tmp_id=-1) then<br /> insert into test_user(id,name,age)<br /> values(:new.id,:new.name,:new.age);<br /> end if;<br /> end if;<br /> <br /> if updating then<br /> dbms_output.put_line(''updated'');<br /> for p in(select name,age from test_user where id=:old.id)<br /> loop<br /> if (p.name!=:new.name) or (p.age!=:new.age) then<br /> update test_user set name=:new.name,age=:new.age where id=:old.id;<br /> end if;<br /> end loop;<br /> end if;<br /> <br /> if deleting then<br /> dbms_output.put_line(''deleted'');<br /> delete from test_user where id=:old.id;<br /> end if;<br /> dbms_output.put_line(''end'');<br /> end TRI_test_user_AFR;<br /> --为防止双向同步触发器死循环,所以要在触发器中增加一些判断,阻止死循环.</p> <p><strong>--以上同步原理<br /> </strong>1.首先创建一个dblink,可以访问远程数据库<br /> 2.在本地创建一个快照,映射远程数据表,当远程数据表有变化时,会反应到快照中.<br /> 3.由于快照类似于视图表,所以在本地为快照创建一个触发器,当快照有变化时,会触发相应事件.<br /> 4.在触发器中写同步数据的代码.</p> <p><span style="font-size:small;"><strong>--附:快照刷新时间参数说明</strong></span><br /> 一天的秒数=24小时*60分钟*60钞<br /> 所以要想在30秒后刷新,参数应该这样写 sysdate+30/(24*60*60)<br /> 1分钟==sysdate+60/(24*60*60)</p> <p>一天的分钟数=24小时*60分钟<br /> 一分钟也可以这样写 sysdate+1/(24*60)<br /> 30分钟==sysdate+30/(24*60)<br /> 60分钟==sysdate+60/(24*60)</p> <p>以此类推<br /> 1小时==sysdate+1/24==sysdate+60/(24*60)<br /> 1天==sysdate+1<br /> 一个月==sysdate+30</p>