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/