在线重定义对OGG影响测试报告1127.docx

1、在线重定义对OGG影响测试报告1127在线重定义普通表转换分区表对OGG影响测试报告作者：陈迪曙Email：cdshrewd目录1. 总体说明 42. 测试细节 4软件环境 4测试数据表 4读写过程 7测试有主键表 7测试无主键表 123. 测试结果 15文档标识文档名称在线重定义普通表转换分区表对OGG影响测试报告版本号状况文档修订历史版本日期描述文档所有者0.12015-11-27创建初稿陈迪曙分发拷贝No.姓名单位1231. 总体说明本技术方案主要的目的是测试在线重定义普通表转换分区表对OGG影响，重点关注两个方面。第一个是数据同步是否丢失，第二个是附加日志是否丢失以及附加日志丢失的影响

2、。经过测试，结论如下：1. 在线重定义对OGG数据同步有影响，可能会导致数据丢失。但是通过合理的停机，可以避免数据丢失。具体来说，就是在线重定义在最后finish阶段（即切换表名的过程）完成后，如果不重启抽取进程，会发现数据无法抽取，直到下次重启OGG抽取进程。规避的方法是，在要做finish操作前，要求业务停机，避免数据写入，然后重启extract进程，然后再恢复数据写入即可。2. 如果在线重定义使用的中间表没有附加日志，那么进行在线重定义的过程中，原始表上的附加日志除主键外会丢失，但是如果表存在主键，那么所有的数据复制不受影响。无主键表测试过程中也未发现有问题。2. 测试细节软件环境相关信

3、息primary实例名oradbIP192.168.2.20服务名oradbOGG版本Version 11.1.1.1.3_02 13878881 OGGCORE_11.1.1.1.4_PLATFORMS_120323.1345和Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO操作系统Oracle Linux Server release 5.8数据库版本Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production测试数据

4、表测试涉及到6张表：源表目标表在线重定义中间表test.test_srctest.test_tgttest.test_src_ptest.test_npk_srctest.test_npk_tgttest.test_npk_src_p以上6张表的建表脚本如下：- Create tablecreate table TEST.TEST_SRC( ID NUMBER(10) not null, NAME VARCHAR2(30), ZONE VARCHAR2(3) not null)tablespace TS_CBS_P_DATA ;alter table TEST.TEST_SRC add con

5、straint PK_TEST_SRC primary key (ID); - Create tablecreate table TEST.TEST_TGT( ID NUMBER(10) not null, NAME VARCHAR2(30), ZONE VARCHAR2(3) not null)tablespace TS_CBS_P_DATA ;alter table TEST.TEST_TGT add constraint PK_TEST_TGT primary key (ID);- Create table create table TEST.TEST_SRC_P ( ID NUMBER

6、(10) not null, NAME VARCHAR2(30), ZONE VARCHAR2(3) not null) partition by LIST(ZONE) ( PARTITION SH VALUES (SH) TABLESPACE TS_CBS_P_DATA, PARTITION WH VALUES (WH) TABLESPACE TS_CBS_P_DATA, PARTITION QD VALUES (QD) TABLESPACE TS_CBS_P_DATA, PARTITION DFT VALUES (default) TABLESPACE TS_CBS_P_DATA ); a

7、lter table TEST.TEST_SRC_P add constraint PK_TEST_SRC_P primary key (ID);- Create tablecreate table TEST.TEST_NPK_SRC( ID NUMBER(10) not null, NAME VARCHAR2(30), ZONE VARCHAR2(3) not null)tablespace TS_CBS_P_DATA ; - Create tablecreate table TEST.TEST_NPK_TGT( ID NUMBER(10) not null, NAME VARCHAR2(3

8、0), ZONE VARCHAR2(3) not null)tablespace TS_CBS_P_DATA ;- Create table create table TEST.TEST_NPK_SRC_P ( ID NUMBER(10) not null, NAME VARCHAR2(30), ZONE VARCHAR2(3) not null) partition by LIST(ZONE) ( PARTITION SH VALUES (SH) TABLESPACE TS_CBS_P_DATA, PARTITION WH VALUES (WH) TABLESPACE TS_CBS_P_DA

9、TA, PARTITION QD VALUES (QD) TABLESPACE TS_CBS_P_DATA, PARTITION DFT VALUES (default) TABLESPACE TS_CBS_P_DATA ); 读写过程测试有主键表OGG状态GGSCI (pure11g as oggoradb) 22 info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING DP_EXT 00:00:00 00:03:05 EXTRACT RUNNING PRI_EXT 0

10、0:00:00 00:00:05 REPLICAT RUNNING REP01 00:00:00 00:00:02 修改数据SQL begin 2 for i in 1.100 loop 3 insert into test_src values(i,name:|i,(case mod(i,3) when 0 then SH when 1 then WH when 2 then QD end); 4 end loop; 5 commit; 6 end; 7 /PL/SQL procedure successfully completed.SQL SQL select count(*) from

11、 test_tgt; COUNT(*)- 0SQL select count(*) from test_tgt; COUNT(*)- 100SQL 10:53:12 SQL select count(*) from test_src; COUNT(*)- 10010:53:15 SQL select count(*) from test_src; COUNT(*)- 10010:53:20 SQL 10:53:20 SQL select * from dba_log_group_columns t where t.table_name=TEST_SRC;OWNER LOG_GROUP_NAME

12、- -TABLE_NAME-COLUMN_NAME- POSITION LOGGIN- -TEST GGS_99854TEST_SRCID 1 LOG10:55:40 SQL $以上证明，初始状态，只有TEST_SRC有附加日志，初始数据同步正常。执行在线重定义10:57:17 SQL exec sys.dbms_redefinition.can_redef_table(TEST,TEST_SRC); PL/SQL procedure successfully completed.10:59:35 SQL exec sys.dbms_redefinition.start_redef_table

13、(TEST,TEST_SRC,TEST_SRC_P);PL/SQL procedure successfully completed.10:59:46 SQL begin for i in 200.300 loop insert into test_src values(i,name:|i,(case mod(i,3) when 0 then SH when 1 then WH when 2 then QD end); end loop; commit; end; / 11:00:28 2 11:00:28 3 11:00:28 4 11:00:28 5 11:00:28 6 11:00:28

14、 7 PL/SQL procedure successfully completed.11:00:30 SQL exec sys.dbms_redefinition.sync_interim_table(TEST,TEST_SRC,TEST_SRC_P); PL/SQL procedure successfully completed.11:01:02 SQL begin 11:01:26 2 for i in 400.450 loop 11:01:26 3 insert into test_src values(i,name:|i,(case mod(i,3) when 0 then SH

15、when 1 then WH when 2 then QD end); 11:01:26 4 end loop; 11:01:26 5 commit; 11:01:26 6 end; 11:01:26 7 / PL/SQL procedure successfully completed.11:01:28 SQL exec sys.dbms_redefinition.finish_redef_table(TEST,TEST_SRC,TEST_SRC_P);PL/SQL procedure successfully completed.11:01:57 SQL begin for i in 50

16、0.650 loop insert into test_src values(i,name:|i,(case mod(i,3) when 0 then SH when 1 11:03:08 2 11:03:08 3 then WH when 2 then QD end); 11:03:08 4 end loop; 11:03:08 5 commit; 11:03:08 6 end; 11:03:08 7 /PL/SQL procedure successfully completed.11:03:09 SQL select count(*) from test_src; COUNT(*)- 4

17、0311:04:18 SQL select count(*) from test_tgt; COUNT(*)- 252从以上记录数来看，直到exec sys.dbms_redefinition.finish_redef_table之前，数据都是同步的，执行finish后的数据没有再发生同步。然后我重启了extract进程后，再次插入数据，数据正常同步。11:11:58 SQL insert into test_src values(1111,name:1111,SH);1 row created.11:12:19 SQL commit;Commit complete.11:12:21 SQL

18、select count(*) from test_tgt where id=1111; COUNT(*)- 111:12:51 SQL select * from dba_log_group_columns t where t.table_name in(TEST_SRC,TEST_SRC_P);OWNER LOG_GROUP_NAME- -TABLE_NAME-COLUMN_NAME- POSITION LOGGIN- -TEST GGS_99854TEST_SRC_PID 1 LOG11:16:02 SQL update test_src set name=name:change whe

19、re id=1111;1 row updated.11:16:40 SQL commit;Commit complete.11:16:44 SQL select * from test_tgt where id=1111; ID NAME ZON- - - 1111 name:change SH11:16:55 SQL 11:22:33 SQL select * from dba_log_group_columns t where t.table_name in(TEST_SRC,TEST_SRC_P);OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME P

20、OSITION LOGGIN- - - - - -TEST GGS_99854 TEST_SRC_P ID 1 LOG11:22:36 SQL GGSCI (pure11g as oggoradb) 79 info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING DP_EXT 00:00:00 00:00:09 EXTRACT RUNNING PRI_EXT 00:00:00 00:00:07 REPLICAT RUNNING REP01 00:00:00 00:00:04

21、 这里说明一下，由于我怀疑可能和版本有关，所以我又切换到另外11.1的OGG，发现在12.1的OGG和11.1的OGG中现象略有不同，在12.1中重新启动可能会导致replicate报错，需要使用skiptransaction来启动，在11.1中根本就不报错，但是都是在finish后，如果不重启extract，数据不会被复制。重启extract后，数据正常复制。测试无主键表OGG状态GGSCI (pure11g) 1 view param pri_extExtract pri_extsetenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_

22、1)userid ogg,password oggTRANLOGOPTIONS CONVERTUCS2CLOBSTRANLOGOPTIONS ALTARCHIVELOGDEST instance oradb /u01/app/oradata/archivelogdiscardfile ./dirrpt/pri_ext01.dsc, appendEXTTRAIL ./dirdat/lt, megabytes 100dynamicresolutionDBOPTIONS ALLOWUNUSEDCOLUMNtable test.test_src;table test.test_npk_src;GGSC

23、I (pure11g) 2 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNING EXTRACT RUNNING DP_EXT 00:00:00 00:00:07 EXTRACT RUNNING PRI_EXT 00:00:00 00:00:03 REPLICAT RUNNING REP01 00:00:00 00:00:08 GGSCI (pure11g) 3修改数据SQL insert into test_npk_src values(235,name:235,SH);1 row created.SQL commi

24、t;Commit complete.SQL SQL SQL select count(*) from test_npk_tgt;SQL / COUNT(*)- 1SQL select * from test_npk_tgt; ID NAME ZON- - - 235 name:235 SHSQL select * from test_npk_src; ID NAME ZON- - - 235 name:235 SHSQL update test_npk_src set name=name:235-u where id=235;1 row updated.SQL commit;Commit co

25、mplete.SQL select * from test_npk_tgt; ID NAME ZON- - - 235 name:235-u SH.SQL select * from tesT_npk_src; ID NAME ZON- - - 235 name:235-u SH 1 name:1-1-rowid SHSQL select * from test_npk_tgt; ID NAME ZON- - - 235 name:235-u SH 1 name:1-1-rowid SHSQL delete from test_npk_src where id=235;1 row delete

26、d.SQL commit;Commit complete.SQL select * from tesT_npk_src; ID NAME ZON- - - 1 name:1-1-rowid SHSQL select * from test_npk_tgt; ID NAME ZON- - - 1 name:1-1-rowid SHSQL以上说明初始状态，增删查改都正常同步。执行在线重定义exec sys.dbms_redefinition.can_redef_table(TEST,TEST_NPK_SRC,dbms_redefinition.cons_use_rowid ); exec sys.dbms_redefinition.start_redef_table(TEST,TEST_NPK_SRC,TEST_NPK_SRC_P,options_flag = DBMS_REDEFINITION.CONS_USE_ROWID); exec sys.dbms_redefinition.sync_interim_table(TEST,TEST_NPK_SRC,TEST_NPK_SRC_P); exec sys.dbms_redefinition.finish_redef_table(TEST,TEST_NPK_SRC,TEST_NPK_SRC_P)