oracle中MON_MODS$表ORA-600 13013報錯處理辦法

有朋友反饋資料庫啟動運行一點時間之後,然後就自動crash,讓我們幫忙找原因,通過分析是由於smon進程觸發ORA-600 13013導致資料庫異常
alert日誌報錯資訊
Thu Aug  4 18:39:44 2016
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=33, OS id=22935
Thu Aug  4 18:39:44 2016
Completed: ALTER DATABASE OPEN
Thu Aug  4 18:39:44 2016
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Aug  4 18:48:41 2016
Thread 1 advanced to log sequence 86746
  Current log# 3 seq# 86746 mem# 0: /opt/ora10/oradata/ora10g/redo03.log
Thu Aug  4 18:58:13 2016
Errors in file /opt/ora10/admin/ora10g/bdump/ora10g_smon_22449.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [482], [4198075], [40], [4198075], [17], []
Thu Aug  4 18:58:56 2016
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 8 out of maximum 100 non-fatal internal errors.
Thu Aug  4 18:59:06 2016
Errors in file /opt/ora10/admin/ora10g/bdump/ora10g_smon_22449.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [482], [4198075], [40], [4198075], [17], []
Thu Aug  4 18:59:08 2016
Errors in file /opt/ora10/admin/ora10g/bdump/ora10g_pmon_22413.trc:
ORA-00474: SMON process terminated with error
Thu Aug  4 18:59:08 2016
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 22413
通過trace檔案大概可以發現是由於ORA-600 13013錯誤導致資料庫crash,而且這裡有類似”SMON was doing flushing of monitored table stats”錯誤提示,根據經驗,很可能是smon把表的dml操作收集資訊相關.

ORA-600 [13013] 含義
ORA-600 [13013] [a] [b] {c} [d] [e] [f]
 
 
This format relates to Oracle Server 8.0.3 to 10.1
 
Arg [a] Passcount
Arg [b] Data Object number
Arg {c} Tablespace Relative DBA of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Relative DBA of block being updated (should be same as 1)
Arg [f] Code
根據這個錯誤資訊,以及How to resolve ORA-00600 [13013], [5001] [ID 816784.1]中的描述

ORA-600 13013 對應對象
SQL> select object_name from dba_objects where object_id=482
 
OBJECT_NAME
--------------------------------------------------------------------------------
MON_MODS$
該對象正是和監控dml變化相關的表,smon會對其進行相關操作,以前寫過一篇:MON_MODS$和MON_MODS_ALL$統計DML操作次數的文章
對於MON_MODS$表ORA-600 13013處理
SQL> analyze table mon_mods$ validate structure cascade;
 
analyze table mon_mods$ validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
 
 
SQL> select index_name from dba_indexes where table_name='MON_MODS$';
 
INDEX_NAME
------------------------------
I_MON_MODS$_OBJ
 
SQL> ALTER INDEX I_MON_MODS$_OBJ REBUILD;
 
Index altered.
 
SQL> analyze table mon_mods$ validate structure cascade;
analyze table mon_mods$ validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
 
SQL> CREATE TABLE MON_MODS_BAK AS SELECT * FROM MON_MODS$;
 
Table created.
 
SQL> SELECT COUNT(*) FROM MON_MODS$;
 
  COUNT(*)
----------
      1247
 
SQL> C/MON_MODS$/MON_MODS_BAK;
  1* SELECT COUNT(*) FROM MON_MODS_BAK
SQL> /
 
  COUNT(*)
----------
      1247
 
SQL> TRUNCATE TABLE MON_MODS$;
 
Table truncated.
 
SQL> INSERT INTO MON_MODS$ SELECT * fROM MON_MODS_BAK;
 
1247 rows created.
 
SQL> COMMIT;
 
Commit complete.
 
SQL>  analyze table mon_mods$ validate structure cascade;
 
Table analyzed.
自此關於MON_MODS$表相關的ORA-600 13013異常處理完全,當然也可以通過重建I_MON_MODS$_OBJ索引來解決,但是不能通過rebuild index解決.資料庫也就不會因此而crash了.