logminer 的相关对象损坏引起的oracle宕机（效果观察中）

一台oracle生产库偶尔会宕机，经过分析错误日志以及trace文件发现如下信息

-----alert.log----------
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Fri Aug 17 22:00:12 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j000_52171.trc  (incident=23372):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23372/down_j000_52171_i23372.trc
------------------------

vim /var/u01/app/diag/rdbms/down/down/trace/down_j000_52171.trc
*** 2012-08-17 22:00:21.764
ORA-12012: error on auto execute of job 11300
ORA-01301: error writing to file during flat file build
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 5378
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 5747
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 5869
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 2

发现是由于logminer的包引起。通过查询MOS，找到Ora-01301: Error Writing To File During Flat File Build [ID 602149.1]这篇文档，虽然里面的例子是10g版本的，但是有很多相似之处。

大概结论就是：Problems with logminer objects.（logminer 相关的对象有问题），这篇文档里介绍了10g中recreate logminer objects的办法，但是最后一部不适用于11g。


经过搜索，找到了11g的办法.How To Rebuild LogMiner Metadata Tables [ID 550197.1]   

On 11g (Release 1 and Release 2)

SQL> connect / as sysdba
SQL> drop table SYSTEM.LOGMNR_SESSION$ purge;

NOTE: you will need to recreate the LOGMNR_SESSION$ view manually

CREATE TABLE SYSTEM.LOGMNR_SESSION$ (
session# number,
client# number,
session_name varchar2(128) not null,
db_id number,
resetlogs_change# number,
session_attr number,
session_attr_verbose varchar2(400),
start_scn number,
end_scn number,
spill_scn number,
spill_time date,
oldest_scn number,
resume_scn number,
global_db_name varchar2(128) default null,
reset_timestamp number,
branch_scn number,
version varchar2(64),
redo_compat varchar2(20),
spare1 number,
spare2 number,
spare3 number,
spare4 number,
spare5 number,
spare6 date,
spare7 varchar2(1000),
spare8 varchar2(1000),
constraint logmnr_session_pk primary key (session#)
using index tablespace SYSTEM logging,
constraint logmnr_session_uk1 unique (session_name)
using index tablespace SYSTEM logging enable validate)
tablespace SYSTEM logging
/

SQL> @?/rdbms/admin/dbmslm.sql
SQL> @?/rdbms/admin/prvtlm.plb
SQL> @?/rdbms/admin/dbmslmd.sql
SQL> @?/rdbms/admin/prvtlmd.plb
SQL> @?/rdbms/admin/prvtlmcs.plb
SQL> @?/rdbms/admin/prvtlmcb.plb
SQL> @?/rdbms/admin/utlrp.sql

If you continue to see invalid objects after these steps, you may have to rerun the utlrp.sql step a number of times.

 utlrp.sql可以查看无效对性或者错误对象

按照上面的办法重建了logminer的相关对象，至于是否有效还需观察。   


===========================================================
附件altert.log

Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=::ffff:219.142.212.235)(PORT=56731))
Fri Aug 17 15:00:37 2012
Thread 1 advanced to log sequence 1616
  Current log# 2 seq# 1616 mem# 0: /var/u01/app/oradata/down/redo02.log
Fri Aug 17 22:00:00 2012
Setting Resource Manager plan SCHEDULER[0x2C0D]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Fri Aug 17 22:00:02 2012
Fri Aug 17 22:00:02 2012
Logminer Bld: Lockdown Complete.  DB_TXN_SCN is   UnwindToSCN (LockdownSCN) is 7607211731
Fri Aug 17 22:00:02 2012
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Fri Aug 17 22:00:12 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j000_52171.trc  (incident=23372):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23372/down_j000_52171_i23372.trc
Fri Aug 17 22:00:15 2012
Trace dumping is performing id=[cdmp_20120817220015]
Some DDE async actions failed or were cancelled
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j000_52171.trc  (incident=23373):
ORA-00700: soft internal error, arguments: [kgerev1], [600], [600], [700], [], [], [], []
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23373/down_j000_52171_i23373.trc
Fri Aug 17 22:00:15 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j002_52175.trc  (incident=23468):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23468/down_j002_52175_i23468.trc
Trace dumping is performing id=[cdmp_20120817220017]
Fri Aug 17 22:00:17 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_ora_55281.trc  (incident=23444):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
ORA-25228: timeout or end-of-fetch during message dequeue from SYS.ALERT_QUE
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23444/down_ora_55281_i23444.trc
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j002_52175.trc  (incident=23469):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23469/down_j002_52175_i23469.trc
Fri Aug 17 22:00:18 2012
Sweep Incident[23372]: completed
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j000_52171.trc  (incident=23374):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23374/down_j000_52171_i23374.trc
Fri Aug 17 22:00:18 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j003_52177.trc  (incident=23492):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23492/down_j003_52177_i23492.trc
SYS_AUTO_SQL_TUNING_TASK exiting with error "600" for execution "EXEC_1_19".  See DBA_ADVISOR_EXECUTIONS for more details.
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Trace dumping is performing id=[cdmp_20120817220019]
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j002_52175.trc  (incident=23470):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
ORA-06512: at "SYS.PRVT_ADVISOR", line 2293
ORA-06512: at "SYS.DBMS_ADVISOR", line 241
ORA-06512: at "SYS.DBMS_SQLTUNE", line 643
ORA-06512: at line 4
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23470/down_j002_52175_i23470.trc
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j003_52177.trc:
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_m001_52227.trc  (incident=23532):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
ORA-00448: normal completion of background process
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23532/down_m001_52227_i23532.trc
Trace dumping is performing id=[cdmp_20120817220020]
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j002_52175.trc:
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
ORA-06512: at "SYS.PRVT_ADVISOR", line 2293
ORA-06512: at "SYS.DBMS_ADVISOR", line 241
ORA-06512: at "SYS.DBMS_SQLTUNE", line 643
ORA-06512: at line 4
ORA-600 : opidrv aborting process M001 ospid (52227_140020947867392)
Fri Aug 17 22:00:21 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_m002_52230.trc  (incident=23540):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
ORA-00448: normal completion of background process
ORA-600 : opidrv aborting process M002 ospid (52230_139972686960384)
Fri Aug 17 22:00:21 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_ora_52237.trc  (incident=23548):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_ora_52237.trc:
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Trace dumping is performing id=[cdmp_20120817220021]
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j000_52171.trc:
ORA-12012: error on auto execute of job 11300
ORA-01301: error writing to file during flat file build
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 5378
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 5747
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 5869
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_HM", line 286
Fri Aug 17 22:00:22 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_j003_52177.trc  (incident=25615):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_25615/down_j003_52177_i25615.trc
Errors in file /var/u01/app/diag/rdbms/down/down/incident/incdir_25615/down_j003_52177_i25615.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Fri Aug 17 22:00:22 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_ora_52237.trc  (incident=25623):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_25623/down_ora_52237_i25623.trc
Errors in file /var/u01/app/diag/rdbms/down/down/incident/incdir_25623/down_ora_52237_i25623.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Fri Aug 17 22:00:23 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_m003_52276.trc  (incident=23556):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_m003_52276.trc  (incident=23557):
ORA-00600: internal error code, arguments: [kewrose_1], [600], [ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
], [], [], [], [], []
Incident details in: /var/u01/app/diag/rdbms/down/down/incident/incdir_23557/down_m003_52276_i23557.trc
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_m003_52276.trc:
ORA-00600: internal error code, arguments: [kewrose_1], [600], [ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
], [], [], [], [], []
Fri Aug 17 22:00:25 2012
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_dbrm_29873.trc  (incident=23252):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
Errors in file /var/u01/app/diag/rdbms/down/down/trace/down_dbrm_29873.trc:
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []
DBRM (ospid: 29873): terminating the instance due to error 56710
Instance terminated by DBRM, pid = 29873
Sat Aug 18 09:43:04 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =36
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile /var/u01/app/product/11.1.0/db_1/dbs/spfiledown.ora
System parameters with non-default values:

trace文件

Trace file /var/u01/app/diag/rdbms/down/down/trace/down_j000_52171.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /var/u01/app/product/11.1.0/db_1
System name:    Linux
Node name:      to-01
Release:        2.6.32-71.el6.x86_64
Version:        #1 SMP Fri May 20 03:51:51 BST 2011
Machine:        x86_64
Instance name: to
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 52171, image: oracle@dxt-boss-01 (J000)


*** 2012-08-17 22:00:12.965
*** SESSION ID:(281.50978) 2012-08-17 22:00:12.966
*** CLIENT ID:() 2012-08-17 22:00:12.966
*** SERVICE NAME:(SYS$USERS) 2012-08-17 22:00:12.966
*** MODULE NAME:(DBMS_SCHEDULER) 2012-08-17 22:00:12.966
*** ACTION NAME:(HM_CREATE_OFFLINE_DICTIONARY) 2012-08-17 22:00:12.966

Incident 23372 created, dump file: /var/u01/app/diag/rdbms/down/down/incident/incdir_23372/down_j000_52171_i23372.trc
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [17], [1], [], [], [], [], []


*** 2012-08-17 22:00:15.773
Incident 23373 created, dump file: /var/u01/app/diag/rdbms/down/down/incident/incdir_23373/down_j000_52171_i23373.trc
ORA-00700: soft internal error, arguments: [kgerev1], [600], [600], [700], [], [], [], []


*** 2012-08-17 22:00:18.874
Incident 23374 created, dump file: /var/u01/app/diag/rdbms/down/down/incident/incdir_23374/down_j000_52171_i23374.trc
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []


*** 2012-08-17 22:00:21.763
IR ADR Errors: 604 1301 6512 600 6512 6512 6512 6512

*** 2012-08-17 22:00:21.764
ORA-12012: error on auto execute of job 11300
ORA-01301: error writing to file during flat file build
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 5378
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 5747
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 5869
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_HM", line 286
*** CLIENT ID:() 2012-08-17 22:00:21.872

kgsksysstop: from KGSKWT_INTSTATECHNG6 retry TRUE mode
~

==================MOS文档=====================
How To Rebuild LogMiner Metadata Tables [ID 550197.1]
Ora-01301: Error Writing To File During Flat File Build [ID 602149.1]