Supplemental log VS add trandata on OGG for oracle

最新推荐文章于 2021-04-05 06:43:20 发布

congmao4272

最新推荐文章于 2021-04-05 06:43:20 发布

阅读量183

点赞数

转自：http://tomszrp.itpub.net/post/11835/520322

Oracle的补充日志分为三个级别
Database level
Schema Level(注意版本哦)
Table Level

[@more@] Database 级别的补充日志测试

Oracle补充日志(Supplemental logging)特性因其作用的不同可分为以下几种：最小(Minimal),支持所有字段(all),支持主键(primary key),支持唯一键(unique index),支持外键(foreign key)。
LOBs, LONGS, and ADTs等类型的列无法使用补充日志。
最小(Minimal)补充日志开启后可以使得logmnr工具支持链式行，簇表和索引组织表。使用Goldnegate也必须要求打开最小补充日志，可以通过以下SQL检查最小补全日志是否已经开启：
SELECT supplemental_log_data_min FROM v$database;
若结果返回YES或IMPLICIT则说明已开启最小补全日志，当使用ALL,PRIMARY,UNIQUE或FOREIGN补全日志时最小补全日志默认开启(即检查结果为IMPLICIT)。

下面分别针对如下几种场景测试一些打开primary key,unique index两种补充日志后， Oracle redo 中记录的信息
（1）场景一：有PK
（2）场景二：无PK,UI
（3）场景三：无PK,有1个限定not null的唯一索引
（4）场景四：无PK,有1个不限定not null的唯一索引
（5）场景五：无PK,有2个限定not null的唯一索引
（6）场景六：无PK,有1个限定not null的唯一索引、1个不限定not null的唯一索引、1个普通索引
（7）场景七：无PK,UK,有普通index（等同场景2）

准备工作
打开支持主键(primary key),支持唯一键(unique index)的补充日志
SQL> alter database add supplemental log data (primary key,unique index) columns;
Database altered.
切换一组日志让其生效
SQL> alter system switch logfile;
System altered.
确认补充日志是否打开
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui,
supplemental_log_data_fk, supplemental_log_data_all
from v$database;

SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
IMPLICIT YES YES NO NO

SQL>
节下来启用Logminer进行redo 挖掘（过程略）

场景一：有PK
create table test1( a int, b int, c varchar2(32), d date, e char(1), f int);
alter table test1 add constraint pk_test1 primary key (a);
insert into test1 values(1,100,'a',sysdate,'1',1000);
commit;
update test1 set b=b+1;
commit;

使用LOGMNR工具分析针对表test1的DML操作,可以看到REDO中记录的SQL形式如下:
insert into "STUDY"."TEST1"("A","B","C","D","E","F") values ('1','100','a',TO_DATE('2011-07-07 11:18:54', 'yyyy-mm-dd hh24:mi:ss'),'1','1000');
update "STUDY"."TEST1" set "B" = '101' where "A" = '1' and "B" = '100' and ROWID = 'AAAM87AAGAAAAOuAAA';

其中针对update语句where字句后分别记录了主键值、被修改字段的值和原行的ROWID。

接着我针对某有PK,UI的表做个测试，参阅场景二
（2）场景二：无PK,UI
create table test2( a int, b int, c varchar2(32), d date, e char(1), f int);
insert into test2 values(2,200,'b',sysdate,'2',2000);
commit;
update test2 set b=b+1;
commit;

create table test2( a int, b int, c varchar2(32), d date, e char(1), f int);
insert into "STUDY"."TEST2"("A","B","C","D","E","F") values ('2','200','b',
TO_DATE('2011-07-07 11:27:34', 'yyyy-mm-dd hh24:mi:ss'),'2','2000');
update "STUDY"."TEST2" set "B" = '201' where "A" = '2' and "B" = '200' and "C" = 'b' and
"D" = TO_DATE('2011-07-07 11:27:34', 'yyyy-mm-dd hh24:mi:ss') and "E" = '2' and "F" = '2000'
and ROWID = 'AAAM9IAAGAAAAQWAAA';
当没有主键和唯一约束的情况下，where子句后记录了所有列值和ROWID。
显然，当某个表上的列数量较多时且没有主键或唯一索引和非空约束的情况下，开启补全日志可能导致重做日志总量大幅提高

（3）场景三：无PK,只有一个non-null unique index
create table test3( a int not null, b int not null, c varchar2(32), d date, e char(1), f int);
create unique index ui_test3 on test3(a, b);
insert into test3 values(3,300,'c',sysdate,'3',3000);
commit;
update test3 set b=b+1,c='C';
commit;

使用LOGMNR分析可以发现，REDO中的SQL记录如下：
create table test3( a int not null, b int not null, c varchar2(32), d date, e char(1), f int);
insert into "STUDY"."TEST3"("A","B","C","D","E","F") values ('3','300','c',TO_DATE('2011-07-07 11:20:41', 'yyyy-mm-dd hh24:mi:ss'),'3','3000');
update "STUDY"."TEST3" set "B" = '301', "C" = 'C' where "A" = '3' and "B" = '300' and "C" = 'c' and ROWID = 'AAAM89AAGAAAAO+AAA';
可以看到，在有唯一索引并且限定not null的情况，在where字句后分别记录了唯一索引列值、被修改字段的值和原行的ROWID。这个情况基本和有主键是一样的。

（4）场景四：无PK,有1个不限定not null的唯一索引
在场景四中，是在场景三的基础上，假设表上无PK,但有一个唯一索引，但不限定列not null,看看会是什么情况
create table test4( a int , b int , c varchar2(32), d date, e char(1), f int);
create unique index ui_test4 on test4(a, b);
insert into test4 values(4,400,'d',sysdate,'4',4000);
commit;
update test4 set b=b+1,c='D';
commit;

使用LOGMNR分析可以发现，REDO中的SQL记录如下：
create table test4( a int , b int , c varchar2(32), d date, e char(1), f int);
insert into "STUDY"."TEST4"("A","B","C","D","E","F") values ('4','400','d',TO_DATE('2011-07-07 11:22:43', 'yyyy-mm-dd hh24:mi:ss'),'4','4000');
update "STUDY"."TEST3" set "B" = '401', "C" = 'D' where "A" = '4' and "B" = '400' and "C" = 'd' and ROWID = 'AAAM86AAGAAAAO+AAA';
可以看到，如以上SQL所示，在存在唯一索引(不限定not null)的情况下where子句后仍记录了所有列和ROWID

（5）场景五：无PK,有2个限定not null的唯一索引
create table test5( a int not null, b int not null, c varchar2(32) not null, d date, e char(1), f int not null);
create unique index ui_test51 on test5(a, b);
create unique index ui_test52 on test5(a, c, f);
insert into test5 values(51,501,'e1',sysdate,'5',5100);
insert into test5 values(52,502,'e2',sysdate,'5',5200);
commit;
update test5 set d=sysdate;
commit;

使用LOGMNR分析可以发现，REDO中的SQL记录如下：
create table test5( a int not null, b int not null, c varchar2(32) not null, d date, e char(1), f int not null);
insert into "STUDY"."TEST5"("A","B","C","D","E","F") values ('51','501','e1',TO_DATE('2011-07-07 11:23:38', 'yyyy-mm-dd hh24:mi:ss'),'5','5100');
insert into "STUDY"."TEST5"("A","B","C","D","E","F") values ('52','502','e2',TO_DATE('2011-07-07 11:23:38', 'yyyy-mm-dd hh24:mi:ss'),'5','5200');
update "STUDY"."TEST5" set "D" = TO_DATE('2011-07-07 11:23:40', 'yyyy-mm-dd hh24:mi:ss') where "A" = '51' and "B" = '501'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/73920/viewspace-1057102/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/73920/viewspace-1057102/