如何跨Oracle數(shù)據(jù)庫(kù)實(shí)現(xiàn)表級(jí)別的實(shí)時(shí)同步

一. 前言

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛(ài)。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶，將通過(guò)不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名注冊(cè)、網(wǎng)絡(luò)空間、營(yíng)銷(xiāo)軟件、網(wǎng)站建設(shè)、巴南網(wǎng)站維護(hù)、網(wǎng)站推廣。

這個(gè)問(wèn)題是上一篇文章《Oracle跨數(shù)據(jù)庫(kù)實(shí)現(xiàn)定時(shí)同步指定表中的數(shù)據(jù)》中所提問(wèn)題的進(jìn)一步延伸?？紤]到對(duì)數(shù)據(jù)的實(shí)時(shí)性要求比較高，設(shè)置成定時(shí)同步，有點(diǎn)不妥，需要改善升級(jí)更改為實(shí)時(shí)同步。

下面介紹到的方式，嚴(yán)格意義上說(shuō)，并沒(méi)有實(shí)現(xiàn)實(shí)時(shí)同步。是通過(guò)Oracle數(shù)據(jù)庫(kù)創(chuàng)建同義詞+DBlink的方式，來(lái)建立遠(yuǎn)程映射。在查看數(shù)據(jù)時(shí)，直接去遠(yuǎn)程查詢?cè)磶?kù)中的表。

而我一直關(guān)注的是如何進(jìn)行同步數(shù)據(jù)，卻沒(méi)有考慮到可以建立映射。另外，還有一點(diǎn)就是需要交代，就是同步到目標(biāo)數(shù)據(jù)庫(kù)中的表，不進(jìn)行增刪改操作，只進(jìn)行查詢的操作，這也是可以使用同義詞方式解決問(wèn)題的關(guān)鍵。

這種解決思路的出現(xiàn)，多虧了在CSDN問(wèn)答時(shí)，熱心網(wǎng)友的回答，下面就是問(wèn)題詳情描述。

二. 問(wèn)題描述

有兩個(gè)Oracle數(shù)據(jù)庫(kù)，分別布置在不同的服務(wù)器上，系統(tǒng)均為windows2003；

這里暫且說(shuō)成是一個(gè)主數(shù)據(jù)庫(kù)和從數(shù)據(jù)庫(kù)：

(1) 主數(shù)據(jù)庫(kù):oracle_A；

(2) 從數(shù)據(jù)庫(kù):oracle_B；

在oracle_A中有一個(gè)表table_A與oracle_B中的表table_B結(jié)構(gòu)相同；

我是處在oracle_B，oracle_A數(shù)據(jù)庫(kù)分配給我有一個(gè)訪問(wèn)oracle_A表table_A的用戶，該用戶只擁有查詢的權(quán)限；

另外，需要說(shuō)明的一點(diǎn)，就是在oracle_B處,只需對(duì)table_B表進(jìn)行查詢的操作，不進(jìn)行其他增刪改的操作。

場(chǎng)景介紹完了，我的問(wèn)題的是,如何在oracle_A中表table_A發(fā)生變化時(shí),實(shí)時(shí)更新同步到oracle_B的table_B中?

我原來(lái)的處理方式:

通過(guò)建立遠(yuǎn)程連接DBLink+JOB定時(shí)任務(wù)+存儲(chǔ)過(guò)程的方式,實(shí)現(xiàn)了定時(shí)同步更新,但不能做到實(shí)時(shí)同步。

三. 采用同義詞+DB_Link的方式結(jié)果步驟

之所以能夠選擇采用同義詞的方式，處理這個(gè)問(wèn)題。主要還是源于在問(wèn)題描述中提到一個(gè)點(diǎn)，那就是我們只需要對(duì)同步后的表進(jìn)行查詢操作。這點(diǎn)是使用同義詞方式的重要要素。

下面詳細(xì)模擬一下整個(gè)實(shí)驗(yàn)測(cè)試的過(guò)程：

（1）首先在Oracle_A端創(chuàng)建一個(gè)對(duì)table_A只有查詢功能的用戶

1 創(chuàng)建用戶

sqlplus /nolog

conn /as sysdba;

create user username identified by password;

2 查看所有的用戶列表

用戶創(chuàng)建完成后,查看用戶是否創(chuàng)建成功

select * from all_users;

3授予權(quán)限

為了能夠保證能夠登陸,必須賦予如下權(quán)限

--授予username用戶創(chuàng)建session的權(quán)限,即登陸權(quán)限

grant create session to username;

--授予username用戶使用表空間的權(quán)限

grant unlimited tablespace to username;

--oracle對(duì)權(quán)限管理比較嚴(yán)謹(jǐn),普通用戶之間也是默認(rèn)不能互相訪問(wèn)的,需要互相授權(quán).

--如果scott用戶要授權(quán)給username用戶查看自己的test表的權(quán)限;

sqlplus scott/tiget@localhost:1521/orcl

--授予username用戶查看指定的權(quán)限

grant select on test to username;

--撤銷(xiāo)權(quán)限

基本語(yǔ)法同grant,關(guān)鍵字為revoke;

（2）驗(yàn)證用戶是否可以成功登錄，并進(jìn)行訪問(wèn)授權(quán)的表

--使用sqlplus登錄，并進(jìn)行查詢

sqlplus username/password@localhost:1521/orcl;

select * from scott.test;

注意：查詢表時(shí)，務(wù)必帶上用戶名，說(shuō)明是哪個(gè)用戶下的表。

（3）創(chuàng)建遠(yuǎn)程連接DB_Link

1 創(chuàng)建遠(yuǎn)程連接 db_link

create public database link db32　connect to tianzhi_test identified by "tianzhi_test" using '192.168.56.6:1521/ORCL'

2 測(cè)試遠(yuǎn)程連接是否成功

select * from tianzhi_smart.zh_item_news@db32;

（4）在Oracle_B端創(chuàng)建同義詞

1 使用sqlplus登錄自己的用戶

sqlplus tianzhi_smart/tianzhi_smart@localhost:1521/orcl

2 創(chuàng)建同義詞

create or replace public synonym TEST1130 for scott.TEST@db32;

3 查詢測(cè)試

select * from TEST1130;

可以看到這與在Oracle_A源數(shù)據(jù)庫(kù)中查到的table_A表中的數(shù)據(jù)一樣.

注意事項(xiàng):

當(dāng)遠(yuǎn)程查詢的數(shù)據(jù)庫(kù)中包含BLOB字段時(shí),會(huì)報(bào)出如下錯(cuò)誤.

ORA-22992: 無(wú)法使用從遠(yuǎn)程表選擇的 LOB 定位器

當(dāng)出現(xiàn)這個(gè)錯(cuò)誤的時(shí)候，那是因?yàn)榭鐜?kù)連接查詢中的表中存在BLOB類型的字段，所以一定要注意，所有表中存在blob類型字段，

不能用 select * from 連接的表

不能將blob類型的字段出現(xiàn)在腳本中。

如果這些blob類型的字段一定要導(dǎo)過(guò)來(lái)，可以先建立臨時(shí)表再插入本地表，方法如下.在pl/sql中執(zhí)行

第一步 建臨時(shí)表

create global temporary table foo ( X BLOB )

on commit delete rows;

第二步 插入本地表

insert into foo select blobcolumn from remoteTable@dl_remote ;

如何將兩個(gè)oracle數(shù)據(jù)庫(kù)同步

為方便完成指定數(shù)據(jù)表的同步操作，可以采用dblink與merge結(jié)合的方法完成。

操作環(huán)境： 此數(shù)據(jù)庫(kù)服務(wù)器ip為192.168.196.76,有center與branch兩個(gè)庫(kù)，一般需要將center的表數(shù)據(jù)同步到branch，center為源庫(kù)，branch為目標(biāo)庫(kù)，具體步驟如下：

1.在源庫(kù)創(chuàng)建到目標(biāo)庫(kù)的dblink

create database link branch --輸入所要?jiǎng)?chuàng)建dblink的名稱，自定義

connect to dbuser identified by “password” --設(shè)置連接遠(yuǎn)程數(shù)據(jù)庫(kù)的用戶名和密碼

using '192.168.196.76/branch'; --指定目標(biāo)數(shù)據(jù)庫(kù)的連接方式，可用tns名稱

在創(chuàng)建dblink時(shí)，要注意，有時(shí)候可能會(huì)報(bào)用戶名和密碼錯(cuò)誤，但實(shí)際上我們所輸入的賬戶信息是正確的，此時(shí)就注意將密碼的大小寫(xiě)按服務(wù)器上所設(shè)置的輸入，并在賬號(hào)密碼前號(hào)加上雙引號(hào)(服務(wù)器版本不同造成的)。

2.成功后驗(yàn)證dblink

select * from tb_bd_action@branch; --查詢創(chuàng)建好的brach庫(kù)

正常情況下，如果創(chuàng)建dblink成功，可采用該方式訪問(wèn)到遠(yuǎn)程數(shù)據(jù)庫(kù)的表.

3.通過(guò)merge語(yǔ)句完成表數(shù)據(jù)同步

此例中需要將center庫(kù)中的tb_sys_sqlscripe表同步到branch，簡(jiǎn)單的語(yǔ)法如下:

merge into tb_sys_sqlscripe@branch b using tb_sys_sqlscripe c on (b.pk=c.pk) --從center將表merge到branch,同步的依據(jù)是兩個(gè)表的pk

when matched then update set b.sqlscripe=c.sqlscripe,b.author=c.author --如果pk值是相同則將指定表的值更新到目標(biāo)表

when not matched then --如果pk值不一至，則將源表中的數(shù)據(jù)整條插入到目標(biāo)表中

insert values (c.pk, c.fk, c.createtime, c.lastmodifytime,c.author,c.module,c.deleteflag, c.scripttype);

commit; --記得merge后必須commit,否則更改未能提交

4.為方便每次需要同步時(shí)自動(dòng)完成同步工作，可將該語(yǔ)句做成存儲(chǔ)過(guò)程或腳本來(lái)定時(shí)執(zhí)行或按要求手動(dòng)執(zhí)行,簡(jiǎn)單說(shuō)一下創(chuàng)建腳本的方法:

a.創(chuàng)建merge文件夾

b.先將merge語(yǔ)句寫(xiě)完整后，存到merge.sql文件中

c.新建merge.bat文件，編輯后寫(xiě)入以下內(nèi)容

sqlplus user/password@serverip/database @"%cd%\merge.sql"

如何實(shí)現(xiàn)Oracle數(shù)據(jù)庫(kù)之間的數(shù)據(jù)同步

1.所謂的物化視圖，

2手工寫(xiě)TRIGER，

3.通過(guò)ORACLE的CDC工具可以實(shí)現(xiàn)同步或者異步的表的變化。

4。關(guān)于你說(shuō)的表三和表一表二之間同步，俺沒(méi)有明白。猜測(cè)是對(duì)表一表二做ETL，然后放到表三中。只對(duì)增量進(jìn)行抽取和轉(zhuǎn)換。如果是這樣。通過(guò)上面的一二三步之一后。對(duì)增量進(jìn)行抽取和轉(zhuǎn)換。

ETL是個(gè)比較復(fù)雜也好玩的東西。多種方式可以實(shí)現(xiàn)，但是相互影響較大。容易顧此失彼。比如說(shuō)管理和性能的平衡問(wèn)題就是其中之一。