[20160805]logminer使用问题2.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
SCOTT@book> create table emp1 as select * from emp;
Table created.
--//安全期间，我还重启数据库。
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 418 52428800 512 1 NO CURRENT 13245390164 2016-08-05 10:49:19 2.814750E+14 1 ONLINE /mnt/ramdisk/book/redo01.log NO
2 1 416 52428800 512 1 YES INACTIVE 13245389809 2016-08-05 10:36:39 13245389934 2016-08-05 10:39:39 2 ONLINE /mnt/ramdisk/book/redo02.log NO
3 1 417 52428800 512 1 YES ACTIVE 13245389934 2016-08-05 10:39:39 13245390164 2016-08-05 10:49:19 3 ONLINE /mnt/ramdisk/book/redo03.log NO
--当前使用/mnt/ramdisk/book/redo01.log。seq=418.
SCOTT@book> alter table emp1 move tablespace users;
Table altered.
--我现在移动emp1表,再来分析归档seq=411的日志情况。参看链接http://blog.itpub.net/267265/viewspace-2123023/
--//使用logminer分析：
Begin
SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle/archivelog/book/1_411_907434361.dbf', sys.dbms_logmnr.New);
end;
Begin
SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle/archivelog/book/1_412_907434361.dbf', sys.dbms_logmnr.AddFile);
end;
Begin
SYS.DBMS_LOGMNR.START_LOGMNR ( Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG+sys.dbms_logmnr.COMMITTED_DATA_ONLY);
end;
--注意：我前面的测试在下一个日志才提交的。
SCOTT@book> @ &r/pt2 'select *from v$logmnr_contents where seg_name=''EMP1'' and rownum=1';
old 6: from table(xmlsequence(cursor( &1 )))
new 6: from table(xmlsequence(cursor( select *from v$logmnr_contents where seg_name='EMP1' and rownum=1 )))
ROW_NUM COL_NUM COL_NAME COL_VALUE
------- ------------ ----------------- ----------------------------------------------------------------------------------------------------
1 1 SCN 13245387377
2 START_SCN 13245387377
3 COMMIT_SCN 13245387689
4 TIMESTAMP 2016-08-05 09:41:35
5 START_TIMESTAMP 2016-08-05 09:41:35
6 COMMIT_TIMESTAMP 2016-08-05 09:54:30
7 XIDUSN 10
8 XIDSLT 30
9 XIDSQN 54930
10 XID 0A001E0092D60000
11 PXIDUSN 10
12 PXIDSLT 30
13 PXIDSQN 54930
14 PXID 0A001E0092D60000
15 OPERATION UPDATE
16 OPERATION_CODE 3
17 ROLLBACK 0
18 SEG_OWNER SCOTT
19 SEG_NAME EMP1
20 TABLE_NAME EMP1
21 SEG_TYPE 2
22 SEG_TYPE_NAME TABLE
23 TABLE_SPACE USERS
24 ROW_ID AAAXY4AAEAAAAIzAAA
25 USERNAME SCOTT
26 OS_USERNAME UNKNOWN
27 MACHINE_NAME UNKNOWN
28 AUDIT_SESSIONID 35982267
29 SESSION_x0023_ 232
30 SERIAL_x0023_ 5
31 SESSION_INFO UNKNOWN
32 THREAD_x0023_ 1
33 SEQUENCE_x0023_ 2
34 RBASQN 411
35 RBABLK 8
36 RBABYTE 16
37 UBAFIL 3
38 UBABLK 0
39 UBAREC 0
40 UBASQN 0
41 ABS_FILE_x0023_ 3
42 REL_FILE_x0023_ 4
43 DATA_BLK_x0023_ 563
44 DATA_OBJ_x0023_ 95800
45 DATA_OBJV_x0023_ 1
46 DATA_OBJD_x0023_ 95800
47 SQL_REDO update "SCOTT"."EMP1" set "ENAME" = '1234567DEF' where "ENAME" = 'SMITH' and ROWID = 'AAAXY4AAEAAAAI
48 SQL_UNDO update "SCOTT"."EMP1" set "ENAME" = 'SMITH' where "ENAME" = '1234567DEF' and ROWID = 'AAAXY4AAEAAAAI
49 RS_ID 0x00019b.00000008.0010
50 SSN 0
51 CSF 0
52 STATUS 0
53 REDO_VALUE 452
54 UNDO_VALUE 453
55 SAFE_RESUME_SCN 0
56 CSCN 13245387689
56 rows selected.
SCOTT@book> select * from dba_objects where owner=user and object_name='EMP1';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ -------------------- ---------- ------------ -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ------------ ------------------------------
SCOTT EMP1 95800 95806 TABLE 2016-08-05 09:39:11 2016-08-05 10:51:34 2016-08-05:09:39:11 VALID N N N 1
--我前面移动了对象，DATA_OBJECT_ID已经发生了变化。通过rowid已经无法访问。
SCOTT@book> select * from emp1 where rowid='AAAXY4AAEAAAAIzAAA';
select * from emp1 where rowid='AAAXY4AAEAAAAIzAAA'
*
ERROR at line 1:
ORA-01410: invalid ROWID
--而现在rowid是：
SCOTT@book> select rowid,emp1.* from emp1 where rownum=1;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
AAAXY+AAEAAAAODAAA 7369 1234567DEF CLERK 7902 1980-12-17 00:00:00 800 20
SCOTT@book> @ &r/rowid AAAXY+AAEAAAAODAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
95806 4 899 0 4,899 alter system dump datafile 4 block 899 ;
-- DATA_OBJECT_ID 现在是95806。这样你拿到sql_undo 修改也要注意的。
3.测试2.
--如果删除了对象呢？
SCOTT@book> drop table emp1 purge ;
Table dropped.
SCOTT@book> @ &r/pt2 'select *from v$logmnr_contents where seg_name=''EMP1'' and rownum=1';
no rows selected
--这样无法查询到。
SCOTT@book> @ &r/pt2 'select *from v$logmnr_contents where row_id=''AAAXY4AAEAAAAIzAAA''';
ROW_NUM COL_NUM COL_NAME COL_VALUE
------- ------- ----------------- ----------------------------------------------------------------------------------------------------
1 1 SCN 13245387377
2 START_SCN 13245387377
3 COMMIT_SCN 13245387689
4 TIMESTAMP 2016-08-05 09:41:35
5 START_TIMESTAMP 2016-08-05 09:41:35
6 COMMIT_TIMESTAMP 2016-08-05 09:54:30
7 XIDUSN 10
8 XIDSLT 30
9 XIDSQN 54930
10 XID 0A001E0092D60000
11 PXIDUSN 10
12 PXIDSLT 30
13 PXIDSQN 54930
14 PXID 0A001E0092D60000
15 OPERATION UPDATE
16 OPERATION_CODE 3
17 ROLLBACK 0
18 SEG_OWNER UNKNOWN
19 SEG_NAME OBJ# 95800
20 TABLE_NAME OBJ# 95800
21 SEG_TYPE 0
22 SEG_TYPE_NAME UNKNOWN
23 TABLE_SPACE UNKNOWN
24 ROW_ID AAAXY4AAEAAAAIzAAA
25 USERNAME SCOTT
26 OS_USERNAME UNKNOWN
27 MACHINE_NAME UNKNOWN
28 AUDIT_SESSIONID 35982267
29 SESSION_x0023_ 232
30 SERIAL_x0023_ 5
31 SESSION_INFO UNKNOWN
32 THREAD_x0023_ 1
33 SEQUENCE_x0023_ 2
34 RBASQN 411
35 RBABLK 8
36 RBABYTE 16
37 UBAFIL 3
38 UBABLK 0
39 UBAREC 0
40 UBASQN 0
41 ABS_FILE_x0023_ 3
42 REL_FILE_x0023_ 4
43 DATA_BLK_x0023_ 563
44 DATA_OBJ_x0023_ 95800
45 DATA_OBJV_x0023_ 1
46 DATA_OBJD_x0023_ 95800
47 SQL_REDO update "UNKNOWN"."OBJ# 95800" set "COL 2" = HEXTORAW('31323334353637444546') where "COL 2" = HEXTORA
48 SQL_UNDO update "UNKNOWN"."OBJ# 95800" set "COL 2" = HEXTORAW('534d495448') where "COL 2" = HEXTORAW('3132333
49 RS_ID 0x00019b.00000008.0010
50 SSN 0
51 CSF 0
52 INFO Dictionary Mismatch
53 STATUS 2
54 REDO_VALUE 934
55 UNDO_VALUE 935
56 SAFE_RESUME_SCN 0
57 CSCN 13245387689
57 rows selected.
--虽然能取到，显示的是UNKNOWN。
