supplemental logging的一些测试

最新推荐文章于 2023-09-07 17:32:48 发布

cti27515

最新推荐文章于 2023-09-07 17:32:48 发布

阅读量120

点赞数

文章标签：数据库

测试环境10204

1.10g由于IMU的影响，必须开启min supplemental logging才能进行logmnr

2 dump logfile出来的文件，开了高级别的supplemental研究没得啥子区别，可能命令就只显示那么多，而logmnr很直观的反应出了变化

3.开启min supplemental logging，除去关闭了IMU功能外，还会多增加一些SESSION信息

4.supplemental logging(PK)主要是为update提供识别，其会在update时，将表的主键计入redo，没pk的表，将计入所有非LOB列

5.rely的主键，也可以只计入pk列

**********************

实验表

**********************

drop table test_pk;

create table test_pk(id number,name varchar(20),comments varchar2(20));

alter table test_pk add constraints pk_test_pk primary key(id);

drop table test_nopk;

create table test_nopk(id number,name varchar(20),comments varchar2(20));

drop table test_relypk;

create table test_relypk(id number,name varchar(20),comments varchar2(20));

alter table test_relypk add constraints pk_test_relypk primary key(id) rely disable novalidate;

**********************

实验脚本

**********************

select dbms_flashback.get_system_change_number from dual;

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

insert into test_pk values(1,'a','b');

commit;

update test_pk set name='c' where id=1;

commit;

update test_pk set id=2 where id=1;

commit;

delete test_pk where id=2;

commit;

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

insert into test_nopk values(1,'a','b');

commit;

update test_nopk set name='c' where id=1;

commit;

update test_nopk set id=2 where id=1;

commit;

delete test_nopk where id=2;

commit;

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

insert into test_relypk values(1,'a','b');

commit;

update test_relypk set name='c' where id=1;

commit;

update test_relypk set id=2 where id=1;

commit;

delete test_relypk where id=2;

commit;

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

select dbms_flashback.get_system_change_number from dual;

BEGIN

DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/oradata/o10204/redo01.log', PTIONS => DBMS_LOGMNR.NEW);

DBMS_LOGMNR.START_LOGMNR( startscn=>752256,endscn=>752286, PTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

END;

select SQL_REDO from V$LOGMNR_CONTENTS

**********************

实验1

**********************

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

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

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

YES NO NO NO NO

执行测试脚本后，logmnr的结果

set transaction read write;

insert into "CTAIS2"."TEST_PK"("ID","NAME","COMMENTS") values ('1','a','b');

commit;

set transaction read write;

update "CTAIS2"."TEST_PK" set "NAME" = 'c' where "NAME" = 'a' and ROWID = 'AAAMrSAAEAAAAOMAAA';

commit;

set transaction read write;

update "CTAIS2"."TEST_PK" set "ID" = '2' where "ID" = '1' and ROWID = 'AAAMrSAAEAAAAOMAAA';

commit;

set transaction read write;

delete from "CTAIS2"."TEST_PK" where "ID" = '2' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID = 'AAAMrSAAEAAAAOMAAA';

commit;

set transaction read write;

insert into "CTAIS2"."TEST_NOPK"("ID","NAME","COMMENTS") values ('1','a','b');

commit;

set transaction read write;

update "CTAIS2"."TEST_NOPK" set "NAME" = 'c' where "NAME" = 'a' and ROWID = 'AAAMrUAAEAAAAOcAAA';

commit;

set transaction read write;

update "CTAIS2"."TEST_NOPK" set "ID" = '2' where "ID" = '1' and ROWID = 'AAAMrUAAEAAAAOcAAA';

commit;

set transaction read write;

delete from "CTAIS2"."TEST_NOPK" where "ID" = '2' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID = 'AAAMrUAAEAAAAOcAAA';

commit;

set transaction read write;

insert into "CTAIS2"."TEST_RELYPK"("ID","NAME","COMMENTS") values ('1','a','b');

commit;

set transaction read write;

update "CTAIS2"."TEST_RELYPK" set "NAME" = 'c' where "NAME" = 'a' and ROWID = 'AAAMrVAAEAAAAOkAAA';

commit;

set transaction read write;

update "CTAIS2"."TEST_RELYPK" set "ID" = '2' where "ID" = '1' and ROWID = 'AAAMrVAAEAAAAOkAAA';

commit;

set transaction read write;

delete from "CTAIS2"."TEST_RELYPK" where "ID" = '2' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID = 'AAAMrVAAEAAAAOkAAA';

commit;

可以看到：

1. INSERT语句不需要其他supplemental logging的支持，DML的redo中其实已经记录了足够的信息重构语句

2. DELETE语句也不需要其他supplemental logging的支持，在DML产生的undo条目的redo中，可以找到这条数据所有的列

3. supplemental logging其实主要提供给update使用，可以看到，update默认只产生被修改列的redo，然后从这个update的undo信息中可以找到该列修改前的值

**********************

实验2

**********************

SUPPLEMENTAL_LOG_DATA_PK

alter table test_pk add supplemental log data(primary key) columns;

alter table test_nopk add supplemental log data(primary key) columns;

alter table test_relypk add supplemental log data(primary key) columns;

set transaction read write;

insert into "CTAIS2"."TEST_PK"("ID","NAME","COMMENTS") values ('1','a','b');

commit;

set transaction read write;

update "CTAIS2"."TEST_PK" set "NAME" = 'c' where "ID" = '1' and "NAME" = 'a' and ROWID = 'AAAMrSAAEAAAAOMAAA';

commit;

set transaction read write;

update "CTAIS2"."TEST_PK" set "ID" = '2' where "ID" = '1' and ROWID = 'AAAMrSAAEAAAAOMAAA';

commit;

set transaction read write;

delete from "CTAIS2"."TEST_PK" where "ID" = '2' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID = 'AAAMrSAAEAAAAOMAAA';

commit;

set transaction read write;

insert into "CTAIS2"."TEST_NOPK"("ID","NAME","COMMENTS") values ('1','a','b');

commit;

set transaction read write;

update "CTAIS2"."TEST_NOPK" set "NAME" = 'c' where "ID" = '1' and "NAME" = 'a' and "COMMENTS" = 'b' and ROWID =

'AAAMrUAAEAAAAOcAAA';

commit;

set transaction read write;

update "CTAIS2"."TEST_NOPK" set "ID" = '2' where "ID" = '1' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID =

'AAAMrUAAEAAAAOcAAA';

commit;

set transaction read write;

delete from "CTAIS2"."TEST_NOPK" where "ID" = '2' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID = 'AAAMrUAAEAAAAOcAAA';

commit;

set transaction read write;

insert into "CTAIS2"."TEST_RELYPK"("ID","NAME","COMMENTS") values ('1','a','b');

commit;

set transaction read write;

update "CTAIS2"."TEST_RELYPK" set "NAME" = 'c' where "ID" = '1' and "NAME" = 'a' and ROWID = 'AAAMrVAAEAAAAOkAAA';

commit;

set transaction read write;

update "CTAIS2"."TEST_RELYPK" set "ID" = '2' where "ID" = '1' and ROWID = 'AAAMrVAAEAAAAOkAAA';

commit;

set transaction read write;

delete from "CTAIS2"."TEST_RELYPK" where "ID" = '2' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID = 'AAAMrVAAEAAAAOkAAA';

commit;

SQL> select * from dba_log_groups;

OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED

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

CTAIS2 SYS_C005248 TEST_RELYPK PRIMARY KEY LOGGING ALWAYS GENERATED NAME

CTAIS2 SYS_C005247 TEST_NOPK PRIMARY KEY LOGGING ALWAYS GENERATED NAME

CTAIS2 SYS_C005246 TEST_PK PRIMARY KEY LOGGING ALWAYS GENERATED NAME

可以看到：

1. 增加了SUPPLEMENTAL_LOG_DATA_PK后，有主键的表，会将主键的值也写入REDO

2. 没有主键的表，会将所有列写入REDO（出去LOB）

3. 就算了rely的主键，也能提供和有主键一样的功能，只将主键列写入REDO

4. 对于有PK的表，其实添加SUPPLEMENTAL_LOG_DATA_PK，对日志的产生量，应该没有过多影响。但是没PK的表就不好说

5. 这种语句定义的supplemental logging其实是always的，就是无论何种语句，都把主键的列计入REDO

**********************

实验3

**********************

alter table test_pk drop supplemental log data(primary key) columns;

alter table test_nopk drop supplemental log data(primary key) columns;

alter table test_relypk drop supplemental log data(primary key) columns;

alter table test_pk add supplemental log data(all) columns;

alter table test_nopk add supplemental log data(all) columns;

alter table test_relypk add supplemental log data(all) columns;

set transaction read write;

insert into "CTAIS2"."TEST_PK"("ID","NAME","COMMENTS") values ('1','a','b');

commit;

set transaction read write;

update "CTAIS2"."TEST_PK" set "NAME" = 'c' where "ID" = '1' and "NAME" = 'a' and "COMMENTS" = 'b' and ROWID =

'AAAMrSAAEAAAAOMAAB';

commit;

set transaction read write;

update "CTAIS2"."TEST_PK" set "ID" = '2' where "ID" = '1' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID =

'AAAMrSAAEAAAAOMAAB';

commit;

set transaction read write;

delete from "CTAIS2"."TEST_PK" where "ID" = '2' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID = 'AAAMrSAAEAAAAOMAAB';

commit;

set transaction read write;

insert into "CTAIS2"."TEST_NOPK"("ID","NAME","COMMENTS") values ('1','a','b');

commit;

set transaction read write;

update "CTAIS2"."TEST_NOPK" set "NAME" = 'c' where "ID" = '1' and "NAME" = 'a' and "COMMENTS" = 'b' and ROWID =

'AAAMrUAAEAAAAOcAAB';

commit;

set transaction read write;

update "CTAIS2"."TEST_NOPK" set "ID" = '2' where "ID" = '1' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID =

'AAAMrUAAEAAAAOcAAB';

commit;

set transaction read write;

delete from "CTAIS2"."TEST_NOPK" where "ID" = '2' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID = 'AAAMrUAAEAAAAOcAAB';

commit;

set transaction read write;

insert into "CTAIS2"."TEST_RELYPK"("ID","NAME","COMMENTS") values ('1','a','b');

commit;

set transaction read write;

update "CTAIS2"."TEST_RELYPK" set "NAME" = 'c' where "ID" = '1' and "NAME" = 'a' and "COMMENTS" = 'b' and ROWID =

'AAAMrVAAEAAAAOkAAB';

commit;

set transaction read write;

update "CTAIS2"."TEST_RELYPK" set "ID" = '2' where "ID" = '1' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID =

'AAAMrVAAEAAAAOkAAB';

commit;

set transaction read write;

delete from "CTAIS2"."TEST_RELYPK" where "ID" = '2' and "NAME" = 'c' and "COMMENTS" = 'b' and ROWID = 'AAAMrVAAEAAAAOkAAB';

commit;

可以看到：

1. 如果设置为supplemental_log_data_all,那么就是将所有的列（非LOB）都写入日志

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

转载于:http://blog.itpub.net/8242091/viewspace-761030/

cti27515

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
supplemental logging的一些测试

测试环境102041....
复制链接

扫一扫