[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.