[20160805]logminer使用问题3.txt
--前一阵子看itpub有人想通过logminer来抽取归档同步数据库，实际上这个非常不科学，存在一定的风险，我觉得很多情况下会漏掉一些sql
--语句，通过例子来说明。
--首先说明logminer用来补救一些异常操作还是可行的。
1.测试环境说明：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> alter database add supplemental log data;
Database altered.
SCOTT@book> alter database FORCE LOGGING ;
Database altered.
SCOTT@book> select SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database ;
SUPPLEME FOR
-------- ---
YES YES
--上次我说明如果提交在另外一个归档里，你的分析可能会都掉一些dml操作语句。这次测试行迁移。
create table t (id number,name varchar2(2000));
insert into t (id) select rownum from dual connect by level<=10;
commit ;
2.测试1：
SCOTT@book> alter system archive log current ;
System altered.
SCOTT@book> @ &r/logfile
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME GROUP# STATUS TYPE MEMBER IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- ---------------------------- ---
1 1 412 52428800 512 1 YES INACTIVE 13245387388 2016-08-05 09:41:47 13245387725 2016-08-05 09:56:07 1 ONLINE /mnt/ramdisk/book/redo01.log NO
2 1 413 52428800 512 1 YES ACTIVE 13245387725 2016-08-05 09:56:07 13245388532 2016-08-05 10:09:36 2 ONLINE /mnt/ramdisk/book/redo02.log NO
3 1 414 52428800 512 1 NO CURRENT 13245388532 2016-08-05 10:09:36 2.814750E+14 3 ONLINE /mnt/ramdisk/book/redo03.log NO
--当前使用/mnt/ramdisk/book/redo02.log。
SCOTT@book> update t set name=lpad('x',2000,'x');
10 rows updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> analyze table t list chained rows;
Table analyzed.
SCOTT@book> analyze table t compute statistics;
Table analyzed.
SCOTT@book> select table_name, num_rows, chain_cnt, avg_row_len from user_tables where table_name='T';
TABLE_NAME NUM_ROWS CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- -----------
T 10 7 2013
--发生了行迁移。
SCOTT@book> @ &r/rowid AAAXY5AAEAAAALuAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
95801 4 750 0 4,750 alter system dump datafile 4 block 750 ;
SCOTT@book> alter system archive log current ;
System altered.
--分析这个归档日志。
$ ls -l /u01/app/oracle/archivelog/book/1_414_907434361.dbf
-rw-r----- 1 oracle oinstall 38912 2016-08-05 10:11:49 /u01/app/oracle/archivelog/book/1_414_907434361.dbf
$ strings /u01/app/oracle/archivelog/book/1_414_907434361.dbf | grep xxxxx | head -1
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
--//使用logminer分析：
Begin
SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle/archivelog/book/1_414_907434361.dbf', sys.dbms_logmnr.New);
end;
Begin
SYS.DBMS_LOGMNR.START_LOGMNR ( Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG+sys.dbms_logmnr.COMMITTED_DATA_ONLY);
end;
SCOTT@book> select row_id,substr(sql_redo,1,90) c90,substr(sql_undo,1,90) c90 from v$logmnr_contents where seg_name='T' order by 1;
ROW_ID C90 C90
------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
AAAXY5AAEAAAALuAAA update "SCOTT"."T" set "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "NAME" = NULL where "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
AAAXY5AAEAAAALuAAB update "SCOTT"."T" set "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "NAME" = NULL where "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
AAAXY5AAEAAAALuAAC update "SCOTT"."T" set "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "NAME" = NULL where "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
AAAXY5AAEAAAALuAAD update "SCOTT"."T" set "ID" = '4', "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "ID" = '4', "NAME" = NULL where "ID" = '4' and "NAME" = 'xxxxxxxxxx
AAAXY5AAEAAAALuAAE update "SCOTT"."T" set "ID" = '5', "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "ID" = '5', "NAME" = NULL where "ID" = '5' and "NAME" = 'xxxxxxxxxx
AAAXY5AAEAAAALuAAF update "SCOTT"."T" set "ID" = '6', "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "ID" = '6', "NAME" = NULL where "ID" = '6' and "NAME" = 'xxxxxxxxxx
AAAXY5AAEAAAALuAAG update "SCOTT"."T" set "ID" = '7', "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "ID" = '7', "NAME" = NULL where "ID" = '7' and "NAME" = 'xxxxxxxxxx
AAAXY5AAEAAAALuAAH update "SCOTT"."T" set "ID" = '8', "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "ID" = '8', "NAME" = NULL where "ID" = '8' and "NAME" = 'xxxxxxxxxx
AAAXY5AAEAAAALuAAI update "SCOTT"."T" set "ID" = '9', "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "ID" = '9', "NAME" = NULL where "ID" = '9' and "NAME" = 'xxxxxxxxxx
AAAXY5AAEAAAALuAAJ update "SCOTT"."T" set "ID" = '10', "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "ID" = '10', "NAME" = NULL where "ID" = '10' and "NAME" = 'xxxxxxxx
10 rows selected.
--注意看rowid=AAAXY5AAEAAAALuAAA的那行，与最后的几条不同.
SCOTT@book> select sql_redo c100 ,sql_undo c100 from v$logmnr_contents where seg_name='T' and row_id='AAAXY5AAEAAAALuAAA';
C100 C100
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
update "SCOTT"."T" set "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "NAME" = NULL where "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
.....
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' where "NAME" IS NULL and ROWID = 'AAAXY5AAEAAAALuAAA'; xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' and ROWID = 'AAAXY5AAEAAAALuAAA';
SCOTT@book> select sql_redo c100 ,sql_undo c100 from v$logmnr_contents where seg_name='T' and row_id='AAAXY5AAEAAAALuAAD';
C100 C100
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
update "SCOTT"."T" set "ID" = '4', "NAME" = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx update "SCOTT"."T" set "ID" = '4', "NAME" = NULL where "ID" = '4' and "NAME" = 'xxxxxxxxxxxxxxxxxxxx
...
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' where "ID" = '4' and "NAME" IS NULL and ROWID = 'AAAX xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' and ROWID = 'AAAXY
Y5AAEAAAALuAAD'; 5AAEAAAALuAAD';
--//注中间x被截断了。
BBED> x /rx *kdbr[0]
rowdata[4081] @6119
-------------
flag@6119: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6120: 0x02
cols@6121: 2
col 0[2] @6122: 0xc1 0x02
col 1[2000] @6125: 0x78 0x78 0x78 0x78 ....
BBED> x /rx *kdbr[3]
rowdata[54] @2092
-----------
flag@2092: 0x20 (KDRHFH)
lock@2093: 0x02
cols@2094: 0
nrid@2095:0x010002ef.0
--注意看发生行迁移的行与没有发生行迁移的行记录的格式不一样。
--另外我没有主键，也就是如果你去掉条件 ROWID = 'AAAXY5AAEAAAALuAAA'; 前面的操作会很危险。
3.重复测试建立所以看看。
create table tx (id number,name varchar2(2000));
insert into tx (id) select rownum from dual connect by level<=10;
create unique index pk_tx on tx(id);
alter table tx add constraint pk_tx primary key (id) enable validate;
commit ;
alter system archive log current ;
SCOTT@book> @ &r/logfile
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME GROUP# STATUS TYPE MEMBER IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- ---------------------------- ---
1 1 415 52428800 512 1 YES ACTIVE 13245388596 2016-08-05 10:11:49 13245389809 2016-08-05 10:36:39 1 ONLINE /mnt/ramdisk/book/redo01.log NO
2 1 416 52428800 512 1 NO CURRENT 13245389809 2016-08-05 10:36:39 2.814750E+14 2 ONLINE /mnt/ramdisk/book/redo02.log NO
3 1 414 52428800 512 1 YES INACTIVE 13245388532 2016-08-05 10:09:36 13245388596 2016-08-05 10:11:49 3 ONLINE /mnt/ramdisk/book/redo03.log NO
--当前redo seq=416.
SCOTT@book> update tx set name=lpad('y',2000,'y');
10 rows updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> analyze table tx list chained rows;
Table analyzed.
SCOTT@book> analyze table t compute statistics;
Table analyzed.
SCOTT@book> select table_name, num_rows, chain_cnt, avg_row_len from user_tables where table_name='TX';
TABLE_NAME NUM_ROWS CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- -----------
TX 10 7 2013
--分析这个归档日志。
Begin
SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle/archivelog/book/1_416_907434361.dbf', sys.dbms_logmnr.New);
end;
Begin
SYS.DBMS_LOGMNR.START_LOGMNR ( Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG+sys.dbms_logmnr.COMMITTED_DATA_ONLY);
end;
SCOTT@book> select row_id,substr(sql_redo,1,90) c90,substr(sql_undo,1,90) c90 from v$logmnr_contents where seg_name='TX' order by 1;
ROW_ID C90 C90
------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
AAAAAAAAAAAAAAAAAB analyze table tx compute statistics;
AAAAAAAAAAAAAAAAAB analyze table tx list chained rows;
AAAXY7AAEAAAAL0AAA update "SCOTT"."TX" set "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "NAME" = NULL where "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
AAAXY7AAEAAAAL0AAB update "SCOTT"."TX" set "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "NAME" = NULL where "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
AAAXY7AAEAAAAL0AAC update "SCOTT"."TX" set "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "NAME" = NULL where "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
AAAXY7AAEAAAAL0AAD update "SCOTT"."TX" set "ID" = '4', "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "ID" = '4', "NAME" = NULL where "ID" = '4' and "NAME" = 'yyyyyyyyy
AAAXY7AAEAAAAL0AAE update "SCOTT"."TX" set "ID" = '5', "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "ID" = '5', "NAME" = NULL where "ID" = '5' and "NAME" = 'yyyyyyyyy
AAAXY7AAEAAAAL0AAF update "SCOTT"."TX" set "ID" = '6', "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "ID" = '6', "NAME" = NULL where "ID" = '6' and "NAME" = 'yyyyyyyyy
AAAXY7AAEAAAAL0AAG update "SCOTT"."TX" set "ID" = '7', "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "ID" = '7', "NAME" = NULL where "ID" = '7' and "NAME" = 'yyyyyyyyy
AAAXY7AAEAAAAL0AAH update "SCOTT"."TX" set "ID" = '8', "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "ID" = '8', "NAME" = NULL where "ID" = '8' and "NAME" = 'yyyyyyyyy
AAAXY7AAEAAAAL0AAI update "SCOTT"."TX" set "ID" = '9', "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "ID" = '9', "NAME" = NULL where "ID" = '9' and "NAME" = 'yyyyyyyyy
AAAXY7AAEAAAAL0AAJ update "SCOTT"."TX" set "ID" = '10', "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "ID" = '10', "NAME" = NULL where "ID" = '10' and "NAME" = 'yyyyyyy
12 rows selected.
SCOTT@book> select sql_redo c100 ,sql_undo c100 from v$logmnr_contents where seg_name='TX' and row_id='AAAXY7AAEAAAAL0AAA';
C100 C100
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
update "SCOTT"."TX" set "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy update "SCOTT"."TX" set "NAME" = NULL where "NAME" = 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
...
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy' where "NAME" IS NULL and ROWID = 'AAAXY7AAEAAAAL0AAA'; yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy' and ROWID = 'AAAXY7AAEAAAAL0AAA';
--这样要在where条件里加入主键，必须要修改附加日志的级别内容。
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM v$database;
--这样务必带来日志的增加。要仔细评估这些风险。
--总之：你要实现它同步要打开更多的附加日志才行。