DBLINK实现两个数据库间表数据同步张金总结版

1、1， 在目标机上建立Oracle DB Link：-在本地（目标机器）如下文件加入代码，连接到目标数据库上面1.1，在network/admin/tnsname.ora文件中加入源库的连接信息，如：AAA_=  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)    (CONNECT_DAT

2、A =      (SERVER = DEDICATED)      (SERVICE_NAME = AAA)    )  )说明： ：源库ipAAA_：创建的实例名（服务名）1,2，在目标机器上用sqlplus user/pwd登录：目标机器（本地）的用户名和密码，不是源库机器的用户名和密码。1.3，在本地机器（目标库）上用

3、如下命令建立DB Link:create public database link AAA_LINK connect to user identified by pwd using 'AAA_'命令说明：AAA_LINK：dblink的名称user：源库的用户名 pwd ：源库的密码AAA_：配置的实例名（服务名）说明：CREATE PUBLIC DATABASE LINK 数据库链接名 CONNECT TO

4、 用户名 IDENTIFIED BY 密码 USING 本地配置的数据的实例名;如果建立成功，会提示：Database link created. 1.4-测试使用链接的数据库（表名dblink名称）select * from FA_CARD AAA_LINK;1.5创建一个测试表：在目标机器和原机器上都创建create table dblink_test(user_id NUMBER,user_name VARCHAR2(100);-在其中增加一条记录：insert into dblink_test (user_id, user_name)values (1, '钟德荣');

5、2， 使用如下脚本，即可同步数据表： 2.1 将源库的数据插入到目标库方法一、truncate table dblink_test;-删除目标库的相应表数据-插入目标机器数据，来源于源库的数据 insert into dblink_test select * from cztbd.dblink_testKNG_LINK; -#这里是指向要同步的来源表,表名必须是<表所有者（源库的用户）>.<表名（源库的表）><dblink name> commit; 方法二、merge into dblink_test b using dblink_testKNG_LINK

6、 c on (b.USER_ID=c.USER_ID) -如果pk（主键）值是相同则将目标库c将对应表的数据更新到源库b的对应表中when matched then update set b.USER_NAME=c.USER_NAME -如果pk（主键）值不一至，则将目标表中的数据整条插入到源表中when not matched then insert values (C.USER_ID,C.USER_NAME);-记得merge后必须commit,否则更改未能提交commit;可以将以上语句作为sql脚本，然后写一个类似2.2的bat命令来作为任务执行 2.2 将目标库的数据插入或者更新到源

7、库-从目标库c将对应表的数据插入到源库b的对应表中方法一insert into cztbd.dblink_testKNG_LINK select * from dblink_test;方法二merge into dblink_testKNG_LINK b using dblink_test c on (b.USER_ID=c.USER_ID) -如果pk（主键）值是相同则将目标库c将对应表的数据更新到源库b的对应表中when matched then update set b.USER_NAME=c.USER_NAME -如果pk（主键）值不一至，则将目标表中的数据整条插入到源表中when n

8、ot matched then insert values (C.USER_ID,C.USER_NAME);-记得merge后必须commit,否则更改未能提交commit;将以上脚本写入merge.sql中，然后建立merge.bat文件，双击运行merge.bat文件，那么系统会自动将目标库c将对应表的数据插入到源库b的对应表中，可以将merge.bat作为任务定期执行（也可以按照3创建存储过程和任务来做） merge.bat如下图所示merge.sql如下图所示3， 实现数据同步方式一、通过2所描述的方式实现数据库数据同步（已测试）方法二、创建存储过程和任务（待测试）-创建一个存储过程c

9、reate procedure proc_sjtbasbegindelete from swdx_gzryxx where is_tb='1'insert into swdx_gzryxx(gzrybh,mc,jgdm,phone,is_tb)select distinct(t.zgswry_dm),t.zgswry_mc,t.nsr_swjg_dm,t.tel_zgy,'1' from dxpt_sjtbmydblink t;delete from gdzc_jg where is_tb='1'insert into gdzc_jg(jg_dm

10、,jg_mc,is_tb)select distinct(t.nsr_swjg_dm),t.nsr_swjg_mc,'1' from dxpt_sjtbmydblink t;delete from swdx_nsrxx where is_tb='1'insert into swdx_nsrxx(nsrsbh,nsrmc,jgdm,zgydm,is_tb)select t.nsrsbh,t.nsrmc,t.nsr_swjg_dm,t.zgswry_dm,'1' from dxpt_sjtbmydblink t;insert into swdx_ns

11、rphone(nsrsbh,lbbh,tel) select t.nsrsbh,'001',t.TEL_NSR_CW from dxpt_sjtbmydblink t;insert into swdx_nsrphone(nsrsbh,lbbh,tel) select t.nsrsbh,'002',t.TEL_NSR_FR from dxpt_sjtbmydblink t;insert into swdx_nsrphone(nsrsbh,lbbh,tel) select t.nsrsbh,'003',t.TEL_NSR_QT from dxpt_s

12、jtbmydblink t;delete from xt_czy where is_tb='1'insert into xt_czy(czy_id, czy_mc, jg_code, czy_lxdh, is_zgy,is_tb,is_jgczy,CZY_YXBZ) select distinct (t.zgswry_dm), t.zgswry_mc, t.nsr_swjg_dm, t.tel_zgy,'1','1','0','Y' from dxpt_sjtbmydblink t;insert into xt_c

13、zy(czy_id, czy_mc, jg_code, is_zgy,is_tb,is_jgczy,CZY_YXBZ) select distinct(t.nsr_swjg_dm),t.nsr_swjg_mc,t.nsr_swjg_dm,'0','1','1','Y' from dxpt_sjtbmydblink t;end;-创建任务(每分钟执行一次)DECLARE X NUMBER;BEGIN SYS.DBMS_JOB.SUBMIT (job => X ,what => 'proc_sjtb;' ,

14、next_date => to_date('09-12-2008 00:00:00','dd/mm/yyyy hh24:mi:ss') ,interval => 'TRUNC(sysdate) + 1 +2 / (24)' );END;-执行任务-RUN(参数)是我们建立任务的时候自动生成的，指定任务号方可启动任务和删除任务begin SYS.DBMS_JOB.RUN(25);end;-删除任务begin sys.DBMS_JOB.REMOVE(23);end;方法三、创建触发器实现实时同步（测试通过）-KNG_LINK 数据链名称-

15、cztbd 源数据库的用户名-cztbd 源数据库的密码-KNG ORACLE SID实例名(源库)-1、在源数据库上，创建要同步表的快照日志（不是很确定是否是在源库建立这个快照日志） -DBLINK_TEST：源库表，LOG：快照名称,不虚伪log不能修改，否则不能创建成功Create snapshot LOG on DBLINK_TEST;-2、在目标数据库上创建快照(被同步（源）数据库服务必须启动) DB_LINK_KZ：快照名称Create snapshot DB_LINK_KZ as select * from DBLINK_TESTKNG_LINK;-3、设置刷新快照时间：如下写法

16、为每隔一分钟刷新一次Alter snapshot DB_LINK_KZ refresh fast Start with sysdate next sysdate+1/1440;-删除快照的方法drop snapshot DB_LINK_KZ; DROP MATERIALIZED VIEW DB_LINK_KZ;-手动刷新快照 begin dbms_refresh.refresh('DB_LINK_KZ');end;-查看快照最后一次刷新时间    SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_

17、TIMES;-查看快照下次执行时间   select last_date,next_date,what from user_jobs order by next_date;-4、创建触发器:在目标库创建未通过，创建用于同步本地表与远程表(源数据库)数据的触发器由于创建快照后，快照在目标数据库表现为会创建一个数据表DB_LINK_KZ，此表根据上一步骤设置的快照刷新时间自动与源数据库的表user同步，要想实现两个数据库的真正同步，只需针对DB_LINK_KZ表创建一个触发器即可解决，即通过触发器实现往目的数据库的指定数据表中插入数据，实现本地数据与目的数据库表的数据

18、同步。写法一：（双向同步，源库表变化，则目标库表变化，目标库表变化，则源库表变化）说明：如果你想双向同步,请在源数据库中执行和目标库一样的操作（即：建立dblink，建立快照，快照刷新等）,并在双方都创建以下触发器CREATE OR REPLACE TRIGGER TRI_KNG_LINKAFTER DELETE OR INSERT OR UPDATEON DB_LINK_KZ-BST114.SN_userREFERENCING NEW AS NEW OLD AS OLDFOR EACH ROWdeclare tmp_id number(10):=-1; flag number(3):=0;b

19、egin dbms_output.put_line('begin'); if inserting then for p in(select user_id from DBLINK_TEST where user_id=:new.user_id)-DBLINK_TEST：目标库表 loop tmp_id:=p.user_id; end loop; dbms_output.put_line(tmp_id|'=-'); if (tmp_id=-1) then insert into DBLINK_TEST(user_id,user_name) values(:new.

20、user_id,:new.user_name); end if; end if; if updating then dbms_output.put_line('updated'); for p in(select user_name from DBLINK_TEST where user_id=:old.user_id) loop if (p.user_name!=:new.user_name) then update DBLINK_TEST set user_name=:new.user_name where user_id=:old.user_id; end if; end

21、 loop; end if; if deleting then dbms_output.put_line('deleted'); delete from DBLINK_TEST where user_id=:old.user_id; end if; dbms_output.put_line('end');end TRI_KNG_LINK;-为防止双向同步触发器死循环,所以要在触发器中增加一些判断,阻止死循环. 问题描述：1、测试发现，在目标库的DB_LINK_KZ表中数据变化了，则目标库的表DB_LINK数据也跟着变化，但是源库的表DB_LINK数据变化，目标库

22、的表DB_LINK数据没有着变化 2、同时再次测试发现：使用手动快照刷新之后，目标库的DB_LINK_KZ表中数据跟着源库的表DB_LINK数据变化，目标库的表DB_LINK数据也跟着变化（这里实现是因为有触发器）最终解决此问题：原来是快照刷新时间问题，快照没有刷新所以才造成了最终功能没有实现，非常郁闷！写法二：（单向同步，只在目标库进行更新等操作） create or replace trigger TRI_test_user_AFR  after  insert or update or delete on DB_LINK_KZ  for each rowbe

23、gin  if deleting then      delete from DBLINK_TEST where user_id=:old.user_id;  end if;  if inserting then      insert into DBLINK_TEST (user_id,user_name)      values(:new.user_id,:new.user_name);  end if

24、;  if updating then     update DBLINK_TEST set user_name=:new.user_name where user_id=:old.user_id;-记住：更新肯定是主键一致才更新  end if;end TRI_test_user_AFR;4， 下面再补充三点：1、  创建DB_Link时，"tns_xj_to_bj"是指服务器端tnsnames.ora文件中所定义的环境名，但在企业中，并没有几个人有权限查看这个文件中的内容。  &#

25、160;   解决办法：将tns_xj_to_bj改写成客户端tnsnames.ora文件中对应的实际连接串。如：(DESCRIPTION =    (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 11)(PORT = 1521)    )    (CONNECT_DATA =    

26、60;  (SERVICE_NAME = test)    )     注意：此处“(SERVER = DEDICATED)”可有可无，并没什么影响。2、查看所有DBLink可采用以下两种方式     SQL1：select owner,object_name from dba_objects where object_type='DATABASE LINK'     SQL2：select * from all_db_

27、links;     但唵更偏向用SLQ2。No Reason！呵呵！3、删除DB_Link     drop database link STAGEING.REGRESS.RDBMS.DEV.US.ORACLE.COM;     其中database不能更改，STAGEING.REGRESS.RDBMS.DEV.US.ORACLE.COM为你创建的DB_Link值（采用SQL2），当然需要用创建该DB_Link的用户登录才能成功删除！5， 整个创建过程总结如下 5、1 dblink总结-创建dblink：在ufgov/ufgov登录plsql，然后之下下列脚本create database link KNG_LINK connect to catbd identified by catbd using 'KNG_03'-查询dblink：（表名dblink名称）：查询的是源库的表数据select * f