使用shell处理oracle数据库alert日志

核心提示： 输出样例$ sh check_alert.sh 2--错误汇总 开始时间：Wed Sep 10 00:11:39 2008---数量 错误号 ---3 ORA-006001 ORA-01555--ORA-00600 错误跟踪信息--mget /arraybill/home/oracle/a

输出样例

$ sh check_alert.sh 2
----------------------------------------------
错误汇总 开始时间：Wed Sep 10 00:11:39 2008
-----------------------------------------------
数量 错误号
---- -------------------
3 ORA-00600
1 ORA-01555
----------------------
ORA-00600 错误跟踪信息
----------------------
mget /arraybill/home/oracle/app/oracle/admin/hnbill/udump/hnbill_ora_5984.trc
找不到跟踪文件: /arraybill/home/oracle/app/oracle/admin/hnbill/udump/hnbill_ora_10650.trc
　　
Fri Sep 10 11:55:42 2008
Errors in file /arraybill/home/oracle/app/oracle/admin/hnbill/udump/hnbill_ora_15196.trc:
ORA-00600: internal error code, arguments: [qerrmObnd1], [932], [ORA-00932: inconsistent datatypes: expected got
Fri Sep 10 11:56:16 2008
Errors in file /arraybill/home/oracle/app/oracle/admin/hnbill/udump/hnbill_ora_15196.trc:
ORA-00600: internal error code, arguments: [qerrmObnd1], [932], [ORA-00932: inconsistent datatypes: expected got
Tue Sep 10 19:48:49 2008
Errors in file /arraybill/home/oracle/app/oracle/admin/hnbill/udump/hnbill_ora_5984.trc:
ORA-00600: internal error code, arguments: [729], [1200], [space leak], [], [], [], [], []
---------------
关于ORA-00600
---------------
00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause: This is the generic internal error number for Oracle program
// exceptions. This indicates that a process has encountered an
// exceptional condition.
// *Action: Report as a bug - the first argument is the internal error number
　　
-------------------------
ORA-01555 错误跟踪信息
-------------------------
Thu Sep 11 04:38:18 2008
ORA-01555 caused by SQL statement below (Query Duration=12429 sec, SCN: 0x0240.ba061afa):
Thu Sep 11 04:38:18 2008
SELECT * FROM BB_SERVICE_RELATION_T WHERE CITY_CODE = :B4 AND SERVICE_KIND = :B3 AND IF_VALID = 1 AND SERVING_STATUS <> 10 AND SERVING_STATUS <> 20 AND (PRESERVE02 <> 1 OR PRESERVE02 IS NULL) AND MOD(USER_ID,:B2 ) = :B1 - 1
---------------
关于ORA-01555
---------------
01555, 00000, "snapshot too old: rollback segment number %s with name "%s" too small"
// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
// setting. Otherwise, use larger rollback segments

4. 后处理与展望

本文只是对符合时间格式的alert文档进行处理，脚本还需要进一步完善，以提高其可扩展性，即能定义一个错误列表，相当于一个配置表，只需要修改错误列表，就能决定哪些错误是需要重点关注的。此外，还需要添加一个模块，如果在alert文件对应的时间戳上没有找到记录，需要进行一个异常处理。

对查找到的错误，要及时进行后处理，这些处理包括：对错误进行分析，查找文档，需要优化的语句进行优化，需要打补丁的bug，视情况下载并打补丁。