Oracle使用dblink同步数据_oracle数据库同步解决方案

两台Oracle之间进行数据同步，可以借助Oracle的dblink完成，如果是Oracle与其他数据库之间，则可以使用Oracle提供的Database Gateways+dblink实现，当然，也可以使用开源ETL工具如kettle完成。

这里提供两种在Oracle之间同步数据的简单解决方案。

0 需求

A（192.168.68.129）、B（192.168.68.143）两个数据库，当 A 中的表 T_TEST 数据发生改变时，B 数据库中的 表 T_TEST 自动更新，实现数据主动推送或者主动拉取

1 dblink + 触发器

1.1 源库 A 创建dblink

在 源数据库 A 中创建 dblink，指向 目标库 B

CREATE database link DBLINK_TEST CONNECT TO SCOTT IDENTIFIED BY "123456" using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.143)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = ORCL) ) )';
1

查询是否创建成功

SELECT * FROM T_TEST @DBLINK_TEST;
1

1.2 源库 A 创建触发器

在 源数据库 A 中创建触发器，更新 目标库 B

CREATE OR REPLACE TRIGGER TRIGGER_SYN_TEST
AFTER INSERT OR UPDATE OR DELETE ON T_TEST FOR each ROW
BEGIN
	IF INSERTING THEN
		INSERT INTO T_TEST @DBLINK_TEST
		VALUES
			( : new.ID,: new.ROLE_NAME,: new.NOTE );
	ELSIF UPDATING THEN
		UPDATE T_TEST @DBLINK_TEST 
		SET ROLE_NAME = : new.ROLE_NAME,NOTE = : new.NOTE
		WHERE
			ID = : new.ID;
	ELSIF DELETING THEN
		DELETE 
		FROM
			T_TEST @DBLINK_TEST 
		WHERE
			ID = : old.ID;
	END IF;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

测试是否生效

INSERT INTO T_TEST VALUES(2, '测试角色','测试角色');

UPDATE T_TEST SET ROLE_NAME = '测试角色更新' WHERE ID = 2;

DELETE FROM T_TEST WHERE ID = 2;
1
2
3
4
5

2 dblink + 物化视图

2.1 源库 A 创建物化视图日志表

在源库中创建物化视图日志表

CREATE MATERIALIZED VIEW LOG ON "T_TEST" WITH ROWID;
1

2.2 目标库 B 创建dblink

在 目标库 B 中创建指向 源库 A 的dblink

CREATE PUBLIC database link DBLINK_TEST CONNECT TO SCOTT IDENTIFIED BY "123456" using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.129)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = ORCL) ) )';
1

2.3 目标库 B 创建物化视图

# 创建，不会自动刷新
CREATE MATERIALIZED VIEW MV_TEST refresh fast ON demand WITH ROWID AS 
SELECT * FROM T_TEST @DBLINK_TEST;

# 每分钟刷新一次
CREATE MATERIALIZED VIEW MV_TEST refresh fast ON demand WITH ROWID NEXT to_date( to_char( SYSDATE + 1/1440, 'dd-mm-yyyy hh24:mi:ss' ), 'dd-mm-yyyy hh24:mi:ss' ) AS 
SELECT * FROM T_TEST @DBLINK_TEST;

# 删除
DROP MATERIALIZED VIEW mv_name
1
2
3
4
5
6
7
8
9
10

一天为24个小时 1440分钟 86400秒
故：
一个小时后时间为 sysdate+1/24

select sysdate + 1/24 from dual
1

一分钟后时间为 sysdate+1/1440

select sysdate + 1/1440 from dual
1

一秒钟后时间为 sysdate+1/86400

select sysdate + 1/86400 from dual
1

2.4 手动同步数据

call dbms_mview.refresh ( 'MV_TEST' );
1