oracle 表同步

数据同步概述

利用oracle的dblink和materialized view功能实现表的数据同步，目的是为应急系统提供数据，备库同步后数据只用做查询。

此方案有以下优缺点：

1.      可按照要求设置同步的间隔时间，不能时时同步。

2.      数据同步为增量同步，只同步增删改数据，不适合变化频繁的表。

3.      此方案为表数据同步，同步设置需要表有主键。

4.      此同步设置简单易维护，不支持DDL操作。

5.      此方案为单向同步。

环境说明

同步环境如下表：

Item

primary

standby

database

Oracle 10G

Oracle 10G

Character set

zhs16gbk

zh16gbk

ip

192.168.98.238

192.168.98.104

sid

orcl

esp

Listener port

1521

1521

username

iam20130420

iam20130529

password

iam20130420

iam20130529

配置过程

创建数据库链路

在备库上修改tnsname.ora文件增加主库tnsname：

ORCL =

(DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.98.238)(PORT = 1521))

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = orcl)

   )

)

在备库sql输入如下命令创建dblink：

SQL>create database link dblink_to_orcl connect to iam20130420 identified by iam20130420 using 'orcl';

创建同步LOG表

在主库内输入如下命令：

SQL> create materialized view log on ast_asset with primary key,sequence;

Materialized view log created

创建同步任务

在备库上创建物化视图：

SQL> CREATE MATERIALIZED VIEW ast_asset ON PREBUILTTABLE REFRESH FASTas select * from ast_asset@dblink_to_orcl;

Materialized view created

在备库手动做次完全更新：

SQL> EXEC DBMS_MVIEW.REFRESH('ast_asset', 'C');

PL/SQL procedure successfully completed

在备库创建更新组：

SQL> exec dbms_refresh.make('mv_group1', 'ast_asset', sysdate, 'sysdate+1/1440',FALSE,TRUE);

PL/SQL procedure successfully completed

SQL> commit;

Commit complete

有多个表需要同步加到一个组里，用逗号隔开，同步时间依照要求设置本次设置的是1分钟：

dbms_refresh.make('mv_group1', 'a,b,c,d', sysdate, 'sysdate+1/1440',FALSE,TRUE);

管理与维护

创建好的LOG表可以查到表结构：

SQL> desc mlog$_ast_asset

名称是否为空?类型

----------------------------------------- -------- -------------

AST_ID                                            NUMBER(20)

SEQUENCE$$                                        NUMBER

SNAPTIME$$                                        DATE

DMLTYPE$$                                         VARCHAR2(1)

OLD_NEW$$                                         VARCHAR2(1)

CHANGE_VECTOR$$                                   RAW(255)

此表记录需要同步的数据，同步完成后自动删除同步完成数据，实现表数据的增量更新。

输入dbms_refresh.make命令创建完更新组，在数据库里可查出对应的job，通过这个可查询到更新的间隔是否出现了错误。如出现错误首先检查dblink链路，是否能远程登录。

select JOB ,SCHEMA_USER,LAST_DATE,NEXT_DATE,FAILURES from user_jobs;

同步删除过程，先删除更新组：

EXECUTE DBMS_REFRESH.DESTROY(NAME=>'mv_group1');

删除创建的LOG表：

drop materialized view log on R_RESGROUP_RES;

删除物化视图：

drop MATERIALIZED VIEW R_RESGROUP_RES;

注意删除前确认dblink是好的，删除物化视图过程包含删除主备库信息，如果dblink不通会出现删除的信息不完全有残留信息。