ANBOB™

昨天开发部门问了一个问题：目前数据库一些查询存在性能问题，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(*)
——————–
100

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(*)
——————–
0

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(*)
——————–
101

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(*)
——————–
102

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

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

Summary：
ogg支持未分区到分区表的同步DDL/DML
在线重定义是建立中间表，COPY 数据到中间表，用MV LOG记录重定义过程中的DML事务，简短的lock 表后 修改数据字典，刷新MV.

打赏

微信扫一扫，打赏作者吧～