最近在做对不同服务器间的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