最近在做对不同服务器间的Oracle数据库,进行数据同步,下面是我的个人总结,以供大家参考。
需求:
两台Oracle数据库服务器A和B,A和B都有USER表,且表结构相同,A服务器上的Oracle库作为源库,B服务器上的Oracle库作为目标库。通过对源库的USER表中的数据进行增、删、改,目标库的USER表中的数据也实时做出相应的变化。表结构如下:
解决方案:
一、创建两个服务器数据库之间的连接DBLINK
1、赋予源库的用户QZL创建dblink的权限(用system或者sys账号)
grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to QZL;
2、查询是否赋予成功
select * from user_sys_privs where privilege like upper('%DATABASE LINK%');
3、用QZL用户登录,创建数据库连接DBLINK(连接的是目标库,目标库的用户名TEST,密码:123456,服务器IP:192.168.0.112,数据库服务名:orcl)
create public database link TEST_LINK connect to TEST identified by "123456" using '192.168.0.112/orcl';
4、删除dblink(创建错误时,可删除)
DROP DATABASE LINK TEST_LINK; --或 DROP PUBLIC DATABASE LINK TEST_LINK;
5、测试dblink是否创建成功
select * from dual@TEST_LINK; //访问目标库的USER表 select * from "USER"@TEST_LINK;
二、通过QZL用户创建同义词(synonym)
1、 赋予源库的用户QZL创建同义词的权限(用system或者sys账号)
GRANT CREATE SYNONYM TO QZL;
2、创建synonym
create or replace SYNONYM USER_COPY for "USER" @TEST_LINK;
3、删除同义词(创建错误时,可删除)
drop synonym USER_COPY;
4、通过创建的同义词,查询目标库的USER表数据
select * from USER_COPY;
三、通过QZL用户创建触发器
1、添加触发器,当源库中USER表发生变化时(增、删、改),目标库中的USER表也会发生相应的变化
CREATE OR REPLACE TRIGGER ds_trigger BEFORE INSERT OR UPDATE OR DELETE ON "USER" FOR EACH ROW declare integrity_error exception; errno integer; errmsg char(200); dummy integer; found boolean; BEGIN IF inserting THEN insert into USER_COPY(ID, NAME, SEX, PHONE) values(:NEW.ID, :NEW.NAME, :NEW.SEX, :NEW.PHONE); ELSIF updating THEN UPDATE USER_COPY SET ID=:NEW.ID, NAME=:NEW.NAME, SEX=:NEW.SEX, PHONE=:NEW.PHONE WHERE ID=:OLD.ID; ELSIF deleting THEN DELETE FROM USER_COPY WHERE ID=:OLD.ID; END IF; exception when integrity_error then raise_application_error(errno, errmsg); END;
2、删除触发器(创建错误时,可删除)
DROP TRIGGER ds_trigger;
四、数据测试,观察数据是否同步到目标库
1、源库中添加数据
INSERT INTO "USER"(ID, NAME, SEX, PHONE) VALUES(1,'Tom',0,'16912564532');
2、源库中更新数据
UPDATE "USER" SET NAME = 'Jerry',PHONE = '18666668888' WHERE id = 1;
3、源库中删除数据
DELETE FROM "USER" WHERE id = 1