goldengate supports sync data from no-partition table to partition table(OGG同步非分区表到分区表)

2013/01/07
Goldengate
311 views
goldengate supports sync data from no-partition table to partition table(OGG同步非分区表到分区表)已关闭评论

.昨天开发部门问了一个问题：目前数据库一些查询存在性能问题，APP 代码修改工作量比较大，目前因为是ORACLE 10G已使用OGG实现实时同步查询分离。当时解决性能问题的几个查询的数据源是分发给了OGG 的TARGET DB分挡压力，现在想只改那几个查询使用分区表，问题就是OGG可以是否从SOURCE 一个非分区表到TARGET 的一个分区表的同步么？DDL/DML是否都可以？

下面看我的实验

TIP:

icmedb> 标示符开头代表是SOURCE DB

GGS标示符开头代表是target DB

我已配置了OGG DDL同步

1，创建测试表

icmedb>alter session set current_schema=icme;

Session altered.

icmedb>create table test_par as select rownum id,rownum||’a’ name from dual connect by rownum<=100;

Table created.

icmedb>alter table test_par add constraint pk_id primary key (id);

sys@GGS>select count(*) from icme.test_par;

        COUNT(*)

——————–

sys@GGS>select constraint_name,constraint_type from dba_constraints where table_name=’TEST_PAR’ AND owner=’ICME’;

CONSTRAINT_NAME C

—————————— –

PK_ID P

2,看到测试表已同步创建，在TARGET DB上做分区的在线重定义

2.1 创建中间表，分区表

sys@GGS>create table test_par_mid partition by range(id)

2 (partition p1 values less than (100),

3 partition p2 values less than(200),

4 partition p3 values less than(maxvalue))as select * from test_par where 1=0;

sys@GGS>select object_id,object_name,data_object_id from dba_objects where object_name in(‘TEST_PAR’,’TEST_PAR_MID’) and owner=’ICME’;

       OBJECT_ID OBJECT_NAME                          DATA_OBJECT_ID

——————– —————————— ——————–

           59494 TEST_PAR                                      59494

           59497 TEST_PAR_MID                                  59497

           59498 TEST_PAR_MID                                  59498

           59499 TEST_PAR_MID                                  59499

           59496 TEST_PAR_MID

2.2 确认表是否可以在线重定义

sys@GGS>exec dbms_redefinition.can_redef_table(‘ICME’,’TEST_PAR’,dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

2.3 重定义对应关系

sys@GGS>exec dbms_redefinition.start_redef_table(‘ICME’,’TEST_PAR’,’TEST_PAR_MID’);

PL/SQL procedure successfully completed.

–在线重定义为了不影响DML事务，其实是用物化视图+物化日志实现

sys@GGS>@printtab “SELECT mview_name,query FROM dba_mviews “;

old 17: replace( ‘&1’, ‘”‘, ””),

new 17: replace( ‘SELECT mview_name,query FROM dba_mviews ‘, ‘”‘, ””),

MVIEW_NAME : TEST_PAR_MID

QUERY : SELECT “TEST_PAR”.”ID” “ID”,”TEST_PAR”.”NAME” “NAME” FROM “ICME”.”TEST_PAR” “TEST_PAR”

2.4 同步数据，根据数据量大小决定时间，这步必要的，但是内部不可能绕过，无非是在finish_redef_table时隐式调用，另外10G起还可以用COPY_TABLE_DEPENDENTS支持同步IDX,CONS,TRIG

sys@GGS>exec dbms_redefinition.sync_interim_table(‘ICME’,’TEST_PAR’,’TEST_PAR_MID’);

PL/SQL procedure successfully completed.

–此过程中我们在SOURCE模仿一个事务，过会儿确认是否影响在线事务

icmedb>insert into test_par values(150,’anbob’);

icmedb>commit;

2.5 完成重定义，内部修改字典表删除mv

sys@GGS>exec dbms_redefinition.finish_redef_table(‘ICME’,’TEST_PAR’,’TEST_PAR_MID’);

PL/SQL procedure successfully completed.

2.6 确认表结构已重定义

–可以看对object_name被修改

sys@GGS>select object_id,object_name,data_object_id from dba_objects

    where object_name in(‘TEST_PAR’,’TEST_PAR_MID’) and owner=’ICME’;

       OBJECT_ID OBJECT_NAME                          DATA_OBJECT_ID

——————– —————————— ——————–

           59497 TEST_PAR                                      59497

           59498 TEST_PAR                                      59498

           59499 TEST_PAR                                      59499

           59496 TEST_PAR

           59494 TEST_PAR_MID                                  59494

sys@GGS>SELECT count(*) FROM dba_mviews ;

        COUNT(*)

——————–

sys@GGS>select table_name,partition_name from dba_tab_partitions

    where TABLE_owner=’ICME’ AND TABLE_NAME=’TEST_PAR’;

TABLE_NAME PARTITION_NAME

—————————— ——————————

TEST_PAR P1

TEST_PAR P2

TEST_PAR P3

2.7 确认重定义期间的事务未丢失

sys@GGS>SELECT count(*) from test_par;

        COUNT(*)

——————–

sys@GGS>SELECT * from test_par partition(p2);

              ID NAME

——————– —————————————–

             100 100a

             150 anbob

2.8 删除中间表(实为原表)

sys@GGS>drop table test_par_mid;

Table dropped.

3 确认重定义后 OGG可以继续从未分区表到分区表同步

icmedb>select table_name,partition_name from dba_tab_partitions

2 where TABLE_owner=’ICME’ AND TABLE_NAME=’TEST_PAR’;

no rows selected

icmedb>insert into test_par values(300,’zhangweizhao’);

icmedb>commit;

sys@GGS>SELECT count(*) from test_par;

        COUNT(*)

——————–

sys@GGS>SELECT * from test_par partition(p3);

              ID NAME

——————– —————————————–

             300 zhangweizhao

–重定义时未同步constraint,记的重建

sys@GGS>select constraint_name,constraint_type from dba_constraints where table_name=’TEST_PAR’ AND owner=’ICME’;

no rows selected

icmedb>alter table icme.test_par add sex number(1);

Table altered.

sys@GGS>desc test_par;

Name Null? Type

—————————————– ——– —————————-

ID NUMBER

NAME VARCHAR2(41)

SEX NUMBER(1)

icmedb>drop table icme.test_par;

sys@GGS>SELECT count(*) from icme.test_par;

SELECT count(*) from icme.test_par

ERROR at line 1:

ORA-00942: table or view does not exist

–版本信息

sys@GGS>select * from v$version where rownum<2;

BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bi

sys@GGS>exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ggsdb ~]$ cd $OGG_HOME

[oracle@ggsdb ogg11r2]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 10g on Apr 23 2012 07:30:46

Summary：

      ogg支持未分区到分区表的同步DDL/DML

      在线重定义是建立中间表，COPY 数据到中间表，用MV LOG记录重定义过程中的DML事务，简短的lock 表后 修改数据字典，刷新MV.

打赏.oracle goldengate .#### 对不起，这篇文章暂时关闭评论。 .上一篇： Script: TOP logical read within a specific time period(指定时间逻辑读最多的对象)