有朋友反馈数据库启动运行一点时间之后,然后就自动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 )
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了.
