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

《实现两个数据库间表数据同步张金总结版》由会员分享，可在线阅读，更多相关《实现两个数据库间表数据同步张金总结版（10页珍藏版）》请在装配图网上搜索。

1、1,在目标机上建立Oracle DB Link：-在本地(目标机器)如下文件加入代码，连接到目标数据库上面丄在 network/admin/tnsname ora文件中加入源库的连接信息，如：AAA_10.5.1.3 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.5.1.3)(PORT = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = AAA)说明：10.5.1.3：源库ipAAA_10.5.1.3:创建的实例名(服务名)丄,2，在目标机器上用sqlplus user/

2、pwd登录：目标机器(本地)的用户名和密码，不是源库机器的用户名和密码。13,在本地机器(目标库)上用如下命令建立DB Link:create public database link AAA_LINK connect to user identified by pwd using AAA_10.5.1.3;命令说明：AAA_LINK： dblink的名称user:源库的用户名pwd :源库的密码AAA_10.5.1.3：配置的实例名(服务名)说明：CREATE PUBLIC DATABASE LINK数据库 链接名CONNECT TO用户名IDENTIFIED BY密码USING 本地配置的

3、数据的实例名； 如果建立成功，会提示：Database link1.4-测试使用链接的数据库(表dblink 名称)select * from FA_CARD AAA_LINK;15创建一个测试表：在目标机器和原机器上都创建createtable dblink_test(user_id NUMBER,user_name VARCHAR2(100);-在其中增加一条记录：insertinto dblink_test (user_id, user_name)values (1,钟德荣);21将源库的数据插入到目标库方法一、truncatetable dblink_test;-删除目标库的相应表数据

4、插入目标机器数据，来源于源库的数据insertinto dblink_testselect * from cztbd.dblink_testKNG_LINK; -#这里是扌旨向要同步的来源表表名 必须是表所有者（源库的用户）.表名（源库的表）dblinkcomm it;方法二、mergeinto dblink_test b using dblink_testKNG_LINK c on （b.USER_ID=c.USER_ID）如果pk （主键）值是相同则将目标库将对应表的数据更新到源库的对应表中whenmatchedthenupdateset b.USER_NAME=c.USER_NAME-如

5、果pk （主键）值不一至，则将目标表中的数据整条插入到源表中whennotmatchedtheninsertvalues （C.USER_ID,C.USER_NAME）;记 得merge 后必须 commit, 否则更改未能提交 comm it;可以将以上语句作为sql脚本，然后写一个类似2.2的bat命令来作为任务执行2.2 将目标库的数据插入或者更新到源库-从目标库c将对应表的数据插入到源库的对应表中方法一insertinto cztbd.dblink_testKNG_LINKselect * from dblink_test;方法二mergeinto dblink_testKNG_LIN

6、K b using dblink_test c on （b.USER_ID=c.USER_ID）-如果pk （主键）值是相同则将目标库将对应表的数据更新到源库的对应表中 whenmatchedthenupdateset b.USER_NAME=c.USER_NAME-如果pk （主键）值不一至，则将目标表中的数据整条插入到源表中whennotmatchedtheninsertvalues （C.USER_ID,C.USER_NAME）;记 得merge 后必须 commit, 否则更改未能提交comm it;将以上脚本写入m erge.sq 1中，然后建立merge.bat文件，双击运行m e

7、rge.bat文件，那么系 统会自动将目标库c将对应表的数据插入到源库的对应表中，可以将merge.bat作为任务定期执行（也可以按照3创建存储过程和任务来做）mergebat如下图所示Merge, bat -记事本文件()编辑(1)格式(jO)查看(V)帮助01)sqlplus uFgou/uFgouQzjcz cdemerge .sqlmergesql如下图所示吕Merge, sql -记事本文件（I）编辑 格式（0）查看过）帮助（H）卜-从目标库C将对应表的数据插入到源库b的对应表中merge into dblink_testKNG_LINK b using dblink test c

8、on （b.USERD=c.USERD）如果PK主犍）值是相同则将目标库c将对应表的薮据更新到源库啲对应表中uhen matched then update set b.USER_NfiME=c.USERNAME如杲Pk主键）值不一至，则将目标表中的数据整泵插入到源表中uhen not matched theninsert values （C.USER ID,C.USER NAME）;一记得昵$ g e后必须comnit,则更改未能提交commit;3, 实现数据同步方式一、通过2所描述的方式实现数据库数据同步（已测试）方法二、创建存储过程和任务（待测试）创建一个存储过程create proc

9、edure proc_sjtbasbegindelete from swdx_gzryxx where is_tb=1;insert into swdx_gzryxx(gzrybh,mc,jgdm,phone,is_tb)selectdistinct(t.zgswry_dm),t.zgswry_mc,t.nsr_swjg_dm,t.tel_zgy,1 fromdxpt_sjtbmydblink t;delete from gdzc_jg where is_tb=1;insert into gdzc_jg(jg_dm,jg_mc,is_tb)selectdistinct(t.nsr_swjg_d

10、m),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)selectt.nsrsbh,t.nsrmc,t.nsr_swjg_dm,t.zgswry_dm,1 from dxpt_sjtbmydblinkt;insert into swdx_nsrphone(nsrsbh,lbbh,tel) selectt.nsrsbh,001,t.TEL_NSR_CW from dxpt_sjtbmy

11、dblink t;insert into swdx_nsrphone(nsrsbh,lbbh,tel) selectt.nsrsbh,002,t.TEL_NSR_FR from dxpt_sjtbmydblink t; insert into swdx_nsrphone(nsrsbh,lbbh,tel) selectt.nsrsbh,003,t.TEL_NSR_QT from dxpt_sjtbmydblink t;delete from xt_czy where is_tb=1;insert into xt_czy(czy_id, czy_mc, jg_code, czy_lxdh, is_

12、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_czy(czy_id, czy_mc, jg_code, is_zgy,is_tb,is_jgczy,CZY_YXBZ) selectdistinct(t.nsr_swjg_dm),t.nsr_swjg_mc,t.nsr_swjg_dm,0,1,1,Y from dxpt_sjtbmydblink t;end

13、;创建任务(每分钟执行一次)DECLARE X NUMBER;BEGINSYS.DBMS JOB.SUBMIT(job,wha t= X=,nex thh24:mi:ss),intervaldateproc_sj tb;= to date(09-12-2008= TRUNC(sysdate) + 100:00:00,dd/mm/yyyy+2 / (24)END;一执行任务-RUN （参数）是我们建立任务的时候自动生成的，指定任务号方可启动任务和删除任务 beginSYS.DBMS JOB.RUN（25）;end;一删除任务beginsys.DBMS_JOB.REMOVE（23）;end;方法三

14、、创建触发器实现实时同步（测试通过）-KNG_LINK数据链名称-cztbd用户名-cztbd 源数据库的密码-KNG ORACLE SlD实例名（源库丿1、在源数据库上，创建要同步表的快照日志（不是很确定是否是在源库建立这个快照日志） -DBLINK_TEST:源库表，LOG：快照名称，不虚伪丄og不能修改，否则不能创建成功CreatesnapshotLOGon DBLINK_TEST;-2、在目标数据库上创建快照被同步（源）数据库服务必须启动DB_LINK_KZ :快照名称 Createsnapshot DB_LINK_KZ asselect * from DBLINK_TESTKNG_L

15、INK;3、设置刷新快照时间：如下写法为每隔一分钟刷新一次Altersnapshot DB_LINK_KZ refreshfastStartwithsysdatenextsysdate+1/1440;-删除快照的方法dropsnapshot DB_LINK_KZ;DROPMATERIALIZEDVIEW DB_LINK_KZ;-手动刷新快照begindbms_refresh.refresh DB_LINK_KZ);end;-查看快照最后一次刷新时间SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;-查看快照下次执行时间select

16、 last_date,next_date,what from user_jobs order by next_date;4、创建触发器在目标库创建未通过，创建用于同步本地表与远程表(源数据库)数据的触发器由于创建快照后，快照在目标数据库表 现为会创建一个数据表DB_LINK_KZ，此表根据上一步骤设置的快照刷新时间自动与源数据库的 表user同步，要想实现两个数据库的真正同步，只需针对DB_LINK_KZ表创建一个触发器即可 解决，即通过触发器实现往目的数据库的指定数据表中插入数据，实现本地数据与目的数据库表 的数据同步。写法一：(双向同步，源库表变化，则目标库表变化，目标库表变化,则源库表变

17、化)说明：如果你想双向同步,请在源数据库中执行和目标库一样的操作(即：建立dblink,建立快照，快照刷新等)，并在双方都创建以下触 发器CREATEORREPLACETRIGGER TRI_KNG_LINKAFTERDELETEORINSERTORUPDATEON DB_LINK_KZBSTl14.SN_userREFERENCINGNEWASNEWOLDASOLDFOREACHROWdeclaretmp_id number(10):=-1;flag number(3):=0;begindbms_output.put_line( begin);if inserting thenfor p i

18、n(select user_id from DBLINK_TEST where user_id=:new.user_id) -DBLINK_TEST: 目标库表 looptmp_id:=p.user_id;endloop;dbms_output.put_line(tmp_id| =);if (tmp_id=-1) theninsertinto DBLINK_TEST(user_id,user_name) values(:new.user_id,:new.user_name);endif;endif;if updating thendbms_output.put_lineupdated);for

19、 p in(select user_name from DBLINK_TEST where user_id=:old.user_id) loopif (p.user_name!=:new.user_name) thenupdate DBLINK_TEST set user_name=:new.user_name where user_id=:old.user_id;endif;endloop;endif;if deleting thendbms_output.put_linedeleted);deletefrom DBLINK_TEST where user_id=:old.user_id;e

20、ndif;dbms_output.put_line( end);end TRI_KNG_LINK;-为防止双向同步触发器死循环，所以要在触发器中增加一些判断，阻止死循环.问题描述：1、测试发现，在目标库的DB_LINK_KZ表中数据变化了，则目标库的表DB_LINK数据也跟 着变化，但是源库的表DB_LINK数据变化，目标库的表DB_LINK数据没有着变化2、同时再次测试发现：使用手动快照刷新之后，目标库的DB_LINK_KZ表中数据跟着源库的表 DB_LINK数据变化，目标库的表DB_LINK数据也跟着变化(这里实现是因为有触发器)最终解决此问题：原来是快照刷新时间问题，快照没有刷新所以才造

21、成了最终功能没有实 现，非常郁闷！写法二：(单向同步，只在目标库进行更新等操作)create or replace trigger TRI_test_user_AFRafter insert or update or delete on DB_LINK_KZfor each rowbeginif deleting thendelete from DBLINK_TEST where user_id=:old.user_id;end if;if inserting theninsert into DBLINK_TEST(user_id,user_name)values(:new.user id,:

22、new.user name);end if;if updating thenupdate 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文件中所定义的环境名，但在企业中，并没有几个人有权限查看这个文件中的内容。解决办法：将tns_xj_to_bj改写成客户端tnsnames.ora文件中对应的实际连接 串。如：

23、(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521) )(CONNECT_DATA =(SERVICE_NAME = test)注意：此处“(SERVER = DEDICATED)”可有可无，并没什么影响。2、查看所有DBLink可采用以下两种方式SQL1： select owner,object_name from dba_objects whereobject_type=DATABASE LINK;SQL2： select * from all_db_links

24、; 但唵更偏向用SLQ2。No Reason ！呵呵！3、删除 DB_Linkdrop database link 其中dat abase不能更改, 建的DB Link值(采用SQL2),STAGEING.REGRESS.RDBMS.DEV.US.ORACLE.COM; STAGEING.REGRESS.RDBMS.DEV.US.ORACLE.COM 为你仓【J 当然需要用创建该DB_Link的用户登录才能成功删除！5, 整个创建过程总结如下5、1 dblink 总结创建 dblink： 在ufgov/ufgov登录plsql，然后之下下列脚本 createdatabaselink KNG_L

25、INK connecttocatbdidentifiedbycatbd usingKNG_192.168.1.103;查询 dblink： (表名dblink名称)：查询的是源库的表数据 select * from FA_CARDKNG_LINK;删除dblinkdroppublicdatabaselink KNG_LINK;返回数据库的Global_name SELECT * FROMGLOBAL_NAME;-查看远程数据库是否支持咼级复制功能:alue=true为支持select * from v$option where PARAMETER=Advanced replication;-4

26、.查询已经建立的远程连接名：select owner,object_name from dba_objects where object_type=DATABASE LINK;-_5 删除 dblink：DROP PUBLIC DATABASE LINK KNG_LINK。如果仓【J建全局 dblink，必须使用 systm 或 sys 用户，在 database 前力口 publico5.2快照总结1, ORACLE的快照刷新方式refresh有三种：fast快速刷新，用snapshot log,只更新时间段变动部分complete完全刷新，运行SQL语句force自动判断刷新，介于fast和complete之间快照技术提供给我们三种刷新机制,分别是：1) Complete完全刷新机制，即对表的所有数据进行刷新，如果表的数据量十分庞大的，此法 会消耗相当的时间；2) Fast快速刷新，即只对数据增量进行刷新；3) Force强制刷新，首先判断是否能用快速刷新机制，如不行则用完全刷新机制。2, 若在数据库B上创建快速刷新，则要在数据库A上创建快照日志对象，否则没这个必要，即第4步。总结呈词：快照所实现的功能就是同步源库表和目标库表中的数据，而触发器 实现的是当前库的表与表之间的操作。