實(shí)現(xiàn)兩個(gè)數(shù)據(jù)庫(kù)間表數(shù)據(jù)同步張金總結(jié)版

1、1， 在目標(biāo)機(jī)上建立Oracle DB Link：-在本地（目標(biāo)機(jī)器）如下文件加入代碼，連接到目標(biāo)數(shù)據(jù)庫(kù)上面1.1，在network/admin/tnsname.ora文件中加入源庫(kù)的連接信息，如：AAA_.3=  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = .3)(PORT = 1521)    (CONNECT_DATA =

2、0;     (SERVER = DEDICATED)      (SERVICE_NAME = AAA)    )  )說(shuō)明： .3：源庫(kù)ipAAA_.3：創(chuàng)建的實(shí)例名（服務(wù)名）1,2，在目標(biāo)機(jī)器上用sqlplus user/pwd登錄：目標(biāo)機(jī)器（本地）的用戶(hù)名和密碼，不是源庫(kù)機(jī)器的用戶(hù)名和密碼。1.3，在本地機(jī)器（目標(biāo)庫(kù)）上用如下命令建立DB Link:create

3、0;public database link AAA_LINK connect to user identified by pwd using 'AAA_.3'命令說(shuō)明：AAA_LINK：dblink的名稱(chēng)user：源庫(kù)的用戶(hù)名 pwd ：源庫(kù)的密碼AAA_.3：配置的實(shí)例名（服務(wù)名）說(shuō)明：CREATE PUBLIC DATABASE LINK 數(shù)據(jù)庫(kù)鏈接名 CONNECT TO 用戶(hù)名 IDENTIFIED BY 密碼 USING 本地配置的數(shù)據(jù)的

4、實(shí)例名;如果建立成功，會(huì)提示：Database link created.1.4-測(cè)試使用鏈接的數(shù)據(jù)庫(kù)（表名dblink名稱(chēng)）select * from FA_CARD AAA_LINK;1.5創(chuàng)建一個(gè)測(cè)試表：在目標(biāo)機(jī)器和原機(jī)器上都創(chuàng)建createtable dblink_test(user_id NUMBER,user_name VARCHAR2(100);-在其中增加一條記錄：insertinto dblink_test (user_id, user_name)values (1, '鐘德榮');2， 使用如下腳本，即可同步數(shù)據(jù)表： 2.1 將源庫(kù)的數(shù)據(jù)插入到目標(biāo)庫(kù)方法一、

5、truncatetable dblink_test;-刪除目標(biāo)庫(kù)的相應(yīng)表數(shù)據(jù)-插入目標(biāo)機(jī)器數(shù)據(jù)，來(lái)源于源庫(kù)的數(shù)據(jù)insertinto dblink_testselect * from cztbd.dblink_testKNG_LINK; -#這里是指向要同步的來(lái)源表,表名必須是<表所有者（源庫(kù)的用戶(hù)）>.<表名（源庫(kù)的表）><dblink name>commit; 方法二、mergeinto dblink_test b using dblink_testKNG_LINK c on (b.USER_ID=c.USER_ID) -如果pk（主鍵）值是相同則將目標(biāo)

6、庫(kù)c將對(duì)應(yīng)表的數(shù)據(jù)更新到源庫(kù)b的對(duì)應(yīng)表中whenmatchedthenupdateset b.USER_NAME=c.USER_NAME -如果pk（主鍵）值不一至，則將目標(biāo)表中的數(shù)據(jù)整條插入到源表中whennotmatchedtheninsertvalues (C.USER_ID,C.USER_NAME);-記得merge后必須commit,否則更改未能提交commit;可以將以上語(yǔ)句作為sql腳本，然后寫(xiě)一個(gè)類(lèi)似2.2的bat命令來(lái)作為任務(wù)執(zhí)行2.2 將目標(biāo)庫(kù)的數(shù)據(jù)插入或者更新到源庫(kù)-從目標(biāo)庫(kù)c將對(duì)應(yīng)表的數(shù)據(jù)插入到源庫(kù)b的對(duì)應(yīng)表中方法一insertinto cztbd.dblink_tes

7、tKNG_LINKselect * from dblink_test;方法二mergeinto dblink_testKNG_LINK b using dblink_test c on (b.USER_ID=c.USER_ID) -如果pk（主鍵）值是相同則將目標(biāo)庫(kù)c將對(duì)應(yīng)表的數(shù)據(jù)更新到源庫(kù)b的對(duì)應(yīng)表中whenmatchedthenupdateset b.USER_NAME=c.USER_NAME -如果pk（主鍵）值不一至，則將目標(biāo)表中的數(shù)據(jù)整條插入到源表中whennotmatchedtheninsertvalues (C.USER_ID,C.USER_NAME);-記得merge后必須co

8、mmit,否則更改未能提交commit;將以上腳本寫(xiě)入merge.sql中，然后建立merge.bat文件，雙擊運(yùn)行merge.bat文件，那么系統(tǒng)會(huì)自動(dòng)將目標(biāo)庫(kù)c將對(duì)應(yīng)表的數(shù)據(jù)插入到源庫(kù)b的對(duì)應(yīng)表中，可以將merge.bat作為任務(wù)定期執(zhí)行（也可以按照3創(chuàng)建存儲(chǔ)過(guò)程和任務(wù)來(lái)做）merge.bat如下圖所示merge.sql如下圖所示3， 實(shí)現(xiàn)數(shù)據(jù)同步方式一、通過(guò)2所描述的方式實(shí)現(xiàn)數(shù)據(jù)庫(kù)數(shù)據(jù)同步（已測(cè)試）方法二、創(chuàng)建存儲(chǔ)過(guò)程和任務(wù)（待測(cè)試）-創(chuàng)建一個(gè)存儲(chǔ)過(guò)程create procedure proc_sjtbasbegindelete from swdx_gzryxx where is_tb=

9、'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,jg_mc,is_tb)select distinct(t.nsr_swjg_dm),t.nsr_swjg_mc,'1'

10、; 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_nsrphone(nsrsbh,lbbh,tel) select t.nsrsbh,'001',t.TEL_NSR_CW f

11、rom 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_sjtbmydblink t;delete from xt_czy where is_tb='1'insert into

12、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_czy(czy_id, czy_mc, jg_code, is_zgy,is_tb,is_jgczy,CZY_YXBZ) select d

13、istinct(t.nsr_swjg_dm),t.nsr_swjg_mc,t.nsr_swjg_dm,'0','1','1','Y' from dxpt_sjtbmydblink t;end;-創(chuàng)建任務(wù)(每分鐘執(zhí)行一次)DECLARE X NUMBER;BEGIN (job => X ,what => 'proc_sjtb;' ,next_date => to_date('09-12-2008 00:00:00','dd/mm/yyyy hh24:mi:ss') ,

14、interval => 'TRUNC(sysdate) + 1 +2 / (24)' );END;-執(zhí)行任務(wù)-RUN(參數(shù))是我們建立任務(wù)的時(shí)候自動(dòng)生成的，指定任務(wù)號(hào)方可啟動(dòng)任務(wù)和刪除任務(wù)begin SYS.DBMS_JOB.RUN(25);end;-刪除任務(wù)begin sys.DBMS_JOB.REMOVE(23);end;方法三、創(chuàng)建觸發(fā)器實(shí)現(xiàn)實(shí)時(shí)同步（測(cè)試通過(guò)）-KNG_LINK 數(shù)據(jù)鏈名稱(chēng)-cztbd 源數(shù)據(jù)庫(kù)的用戶(hù)名-cztbd 源數(shù)據(jù)庫(kù)的密碼-KNG ORACLE SID實(shí)例名(源庫(kù))-1、在源數(shù)據(jù)庫(kù)上，創(chuàng)建要同步表的快照日志（不是很確定是否是在源庫(kù)建立這個(gè)快

15、照日志）-DBLINK_TEST：源庫(kù)表，LOG：快照名稱(chēng),不虛偽log不能修改，否則不能創(chuàng)建成功CreatesnapshotLOGon DBLINK_TEST;-2、在目標(biāo)數(shù)據(jù)庫(kù)上創(chuàng)建快照(被同步（源）數(shù)據(jù)庫(kù)服務(wù)必須啟動(dòng))DB_LINK_KZ：快照名稱(chēng)Createsnapshot DB_LINK_KZ asselect * from DBLINK_TESTKNG_LINK;-3、設(shè)置刷新快照時(shí)間：如下寫(xiě)法為每隔一分鐘刷新一次Altersnapshot DB_LINK_KZ refreshfastStartwithsysdatenextsysdate+1/1440;-刪除快照的方法dropsn

16、apshot DB_LINK_KZ; DROPMATERIALIZEDVIEW DB_LINK_KZ;-手動(dòng)刷新快照begin dbms_refresh.refresh('DB_LINK_KZ');end;-查看快照最后一次刷新時(shí)間    SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;-查看快照下次執(zhí)行時(shí)間   select last_date,next_date,what from user_jobs order by next_date;-4、創(chuàng)建

17、觸發(fā)器:在目標(biāo)庫(kù)創(chuàng)建未通過(guò)，創(chuàng)建用于同步本地表與遠(yuǎn)程表(源數(shù)據(jù)庫(kù))數(shù)據(jù)的觸發(fā)器由于創(chuàng)建快照后，快照在目標(biāo)數(shù)據(jù)庫(kù)表現(xiàn)為會(huì)創(chuàng)建一個(gè)數(shù)據(jù)表DB_LINK_KZ，此表根據(jù)上一步驟設(shè)置的快照刷新時(shí)間自動(dòng)與源數(shù)據(jù)庫(kù)的表user同步，要想實(shí)現(xiàn)兩個(gè)數(shù)據(jù)庫(kù)的真正同步，只需針對(duì)DB_LINK_KZ表創(chuàng)建一個(gè)觸發(fā)器即可解決，即通過(guò)觸發(fā)器實(shí)現(xiàn)往目的數(shù)據(jù)庫(kù)的指定數(shù)據(jù)表中插入數(shù)據(jù)，實(shí)現(xiàn)本地?cái)?shù)據(jù)與目的數(shù)據(jù)庫(kù)表的數(shù)據(jù)同步。寫(xiě)法一：（雙向同步，源庫(kù)表變化，則目標(biāo)庫(kù)表變化，目標(biāo)庫(kù)表變化，則源庫(kù)表變化）說(shuō)明：如果你想雙向同步,請(qǐng)?jiān)谠磾?shù)據(jù)庫(kù)中執(zhí)行和目標(biāo)庫(kù)一樣的操作（即：建立dblink，建立快照，快照刷新等）,并在雙方都創(chuàng)建以下觸發(fā)

18、器CREATEORREPLACETRIGGER TRI_KNG_LINKAFTERDELETEORINSERTORUPDATEON DB_LINK_KZ-BST114.SN_userREFERENCINGNEWASNEWOLDASOLDFOREACHROWdeclare tmp_id number(10):=-1; flag number(3):=0;begin dbms_output.put_line('begin');if inserting thenfor p in(select user_id from DBLINK_TEST where user_id=:new.us

19、er_id)-DBLINK_TEST：目標(biāo)庫(kù)表loop tmp_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 then dbms_output.put_line('updated');for p in(select user_name from DBLINK_

20、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 then dbms_output.put_line('deleted');deletefrom DBLINK_TEST where user_id=:old.user_id;endif; dbms_output.put_li

21、ne('end');end TRI_KNG_LINK;-為防止雙向同步觸發(fā)器死循環(huán),所以要在觸發(fā)器中增加一些判斷,阻止死循環(huán). 問(wèn)題描述：1、測(cè)試發(fā)現(xiàn)，在目標(biāo)庫(kù)的DB_LINK_KZ表中數(shù)據(jù)變化了，則目標(biāo)庫(kù)的表DB_LINK數(shù)據(jù)也跟著變化，但是源庫(kù)的表DB_LINK數(shù)據(jù)變化，目標(biāo)庫(kù)的表DB_LINK數(shù)據(jù)沒(méi)有著變化2、同時(shí)再次測(cè)試發(fā)現(xiàn)：使用手動(dòng)快照刷新之后，目標(biāo)庫(kù)的DB_LINK_KZ表中數(shù)據(jù)跟著源庫(kù)的表DB_LINK數(shù)據(jù)變化，目標(biāo)庫(kù)的表DB_LINK數(shù)據(jù)也跟著變化（這里實(shí)現(xiàn)是因?yàn)橛杏|發(fā)器）最終解決此問(wèn)題：原來(lái)是快照刷新時(shí)間問(wèn)題，快照沒(méi)有刷新所以才造成了最終功能沒(méi)有實(shí)現(xiàn)，非常郁悶

22、！寫(xiě)法二：（單向同步，只在目標(biāo)庫(kù)進(jìn)行更新等操作）create or replace trigger TRI_test_user_AFR  after  insert or update or delete on DB_LINK_KZ  for each rowbegin  if deleting then      delete from DBLINK_TEST where user_id=:old.user_id;  end if;  if inserting then &

23、#160;    insert into DBLINK_TEST(user_id,user_name)      values(:new.user_id,:new.user_name);  end if;  if updating then     update DBLINK_TEST set user_name=:new.user_name where user_id=:old.user_id;-記?。焊驴隙ㄊ侵麈I一致才更新  end if;

24、end TRI_test_user_AFR;4， 下面再補(bǔ)充三點(diǎn)：1、  創(chuàng)建DB_Link時(shí)，"tns_xj_to_bj"是指服務(wù)器端tnsnames.ora文件中所定義的環(huán)境名，但在企業(yè)中，并沒(méi)有幾個(gè)人有權(quán)限查看這個(gè)文件中的內(nèi)容。      解決辦法：將tns_xj_to_bj改寫(xiě)成客戶(hù)端tnsnames.ora文件中對(duì)應(yīng)的實(shí)際連接串。如：(DESCRIPTION =    (ADDRESS_LIST =     

25、0; (ADDRESS = (PROTOCOL = TCP)(HOST = 11)(PORT = 1521)    )    (CONNECT_DATA =       (SERVICE_NAME = test)    )     注意：此處“(SERVER = DEDICATED)”可有可無(wú)，并沒(méi)什么影響。2、查看所有DBLink可采用以下兩種方式   

26、  SQL1：select owner,object_name from dba_objects where object_type='DATABASE LINK'     SQL2：select * from all_db_links;     但唵更偏向用SLQ2。No Reason！呵呵！3、刪除DB_Link     drop database link STAGEING.REGRESS.RDBMS.DEV.US.ORACLE.COM;

27、60;    其中database不能更改，STAGEING.REGRESS.RDBMS.DEV.US.ORACLE.COM為你創(chuàng)建的DB_Link值（采用SQL2），當(dāng)然需要用創(chuàng)建該DB_Link的用戶(hù)登錄才能成功刪除！5， 整個(gè)創(chuàng)建過(guò)程總結(jié)如下 5、1 dblink總結(jié)-創(chuàng)建dblink：在ufgov/ufgov登錄plsql，然后之下下列腳本createdatabaselink KNG_LINKconnecttocatbdidentifiedbycatbdusing'KNG_03'-查詢(xún)dblink：（表名dblink名稱(chēng)）：查詢(xún)的是源庫(kù)的表數(shù)據(jù)select *