[20150705]LOGMINER分析當前日誌注意.txt

[20150705]LOGMINER分析當前日誌注意.txt

SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> @logfile

GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME              NEXT_CHANGE# NEXT_TIME           GROUP# STATUS     TYPE      MEMBER                                 IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ---------------- ------------------- ------ ---------- --------- -------------------------------------- ---
     1       1      1013    52428800       512       1 NO  CURRENT         19456728 2015-07-03 20:37:05  281474976710655                          1            ONLINE    D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG  NO
     2       1      1012    52428800       512       1 YES INACTIVE        19432400 2015-06-29 22:01:33         19456728 2015-07-03 20:37:05      2            ONLINE    D:\APP\ORACLE\ORADATA\TEST\REDO02.LOG  NO
     3       1      1011    52428800       512       1 YES INACTIVE        19421845 2015-06-29 20:34:46         19432400 2015-06-29 22:01:33      3            ONLINE    D:\APP\ORACLE\ORADATA\TEST\REDO03.LOG  NO

SYS@test> EXEC DBMS_LOGMNR.ADD_LOGFILE('D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG', DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.

SYS@test> EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS  =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
PL/SQL procedure successfully completed.

SYS@test> CREATE TABLE Tx AS SELECT * FROM V$LOGMNR_CONTENTS;
....

掛起.

SYS@test> @logfile
GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           GROUP# STATUS     TYPE       MEMBER                                             IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- -------------------------------------------------- ---
     1       1      1013    52428800       512       1 YES INACTIVE        19456728 2015-07-03 20:37:05     19469323 2015-07-03 22:01:33      1            ONLINE     D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG              NO
     2       1      1015    52428800       512       1 NO  CURRENT         19469636 2015-07-03 22:03:31   2.8147E+14                          2            ONLINE     D:\APP\ORACLE\ORADATA\TEST\REDO02.LOG              NO
     3       1      1014    52428800       512       1 YES INACTIVE        19469323 2015-07-03 22:01:33     19469636 2015-07-03 22:03:31      3            ONLINE     D:\APP\ORACLE\ORADATA\TEST\REDO03.LOG              NO

--可以發現seq=1015.實際上繼續切換歸檔沒有問題.檢視alert*.log,

Fri Jul 03 22:02:54 2015
LOGMINER: summary for session# = 2147484417
LOGMINER: StartScn: 19456728 (0x0000.0128e2d8)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag: 0x0
LOGMINER: Read buffers: 16
LOGMINER: Memory LWM limit: 10M, LWM 7M, 71%
LOGMINER: Memory Release Limit: 15000
Fri Jul 03 22:03:24 2015
LOGMINER: Begin mining logfile for session -2147482879 thread 1 sequence 1013, D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG
Fri Jul 03 22:03:26 2015
LOGMINER: skipped redo. Thread 1, RBA 0x0003f5.00000083.008c, nCV 5
LOGMINER: op 4.1 (Transaction Undo)
Fri Jul 03 22:03:31 2015
Thread 1 advanced to log sequence 1015 (LGWR switch)
  Current log# 2 seq# 1015 mem# 0: D:\APP\ORACLE\ORADATA\TEST\REDO02.LOG
Fri Jul 03 22:03:35 2015
Archived Log entry 892 added for thread 1 sequence 1014 ID 0x7e537b63 dest 1:
Fri Jul 03 22:03:40 2015
LOGMINER: End   mining logfile for session -2147482879 thread 1 sequence 1013, D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG
Fri Jul 03 22:03:40 2015
LOGMINER: Begin mining logfile for session -2147482879 thread 1 sequence 1014, D:\APP\ORACLE\ORADATA\TEST\REDO03.LOG
Fri Jul 03 22:03:41 2015
LOGMINER: End   mining logfile for session -2147482879 thread 1 sequence 1014, D:\APP\ORACLE\ORADATA\TEST\REDO03.LOG
Fri Jul 03 22:03:41 2015
LOGMINER: Begin mining logfile for session -2147482879 thread 1 sequence 1015, D:\APP\ORACLE\ORADATA\TEST\REDO02.LOG
Fri Jul 03 22:11:43 2015
ALTER SYSTEM ARCHIVE LOG
Fri Jul 03 22:11:43 2015
Thread 1 advanced to log sequence 1016 (LGWR switch)
  Current log# 1 seq# 1016 mem# 0: D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG
Fri Jul 03 22:11:44 2015
LOGMINER: End   mining logfile for session -2147482879 thread 1 sequence 1015, D:\APP\ORACLE\ORADATA\TEST\REDO02.LOG
Fri Jul 03 22:11:44 2015
LOGMINER: Begin mining logfile for session -2147482879 thread 1 sequence 1016, D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG
Fri Jul 03 22:11:45 2015
Archived Log entry 893 added for thread 1 sequence 1015 ID 0x7e537b63 dest 1:

--這樣會不停採集下去.create table tx 無法停止.

--正確的做法是:

SYS@test> EXEC DBMS_LOGMNR.ADD_LOGFILE('D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG', DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.

SYS@test>  EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS  =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.

SYS@test> create table tx  AS SELECT * FROM V$LOGMNR_CONTENTS;
Table created.

LOGMINER: summary for session# = 2147484673
LOGMINER: StartScn: 19470891 (0x0000.01291a2b)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag: 0x0
LOGMINER: Read buffers: 16
LOGMINER: Memory LWM limit: 10M, LWM 7M, 71%
LOGMINER: Memory Release Limit: 15000
Fri Jul 03 22:18:22 2015
LOGMINER: Begin mining logfile for session -2147482623 thread 1 sequence 1016, D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG
Fri Jul 03 22:18:23 2015
LOGMINER: skipped redo. Thread 1, RBA 0x0003f8.00000065.0010, nCV 9
LOGMINER: op 5.4 (Unknown)
Fri Jul 03 22:18:23 2015
LOGMINER: End   mining logfile for session -2147482623 thread 1 sequence 1016, D:\APP\ORACLE\ORADATA\TEST\REDO01.LOG

--說明要理解DBMS_LOGMNR.CONTINUOUS_MINE引數.
--我的建議最好使用歸檔日誌,在logminer前ALTER SYSTEM ARCHIVE LOG current.