一次数据库故障处理记录

1.某系统做数据库脚本升级后，发现job运行有错误，见下图：
[img]http://dl.iteye.com/upload/picture/pic/105070/855c456b-937e-33de-870a-3879a7d495cc.png[/img]

2.进一步查看，发现系统sys用户下，有些存储过程和包有编译错误：
[img]http://dl.iteye.com/upload/picture/pic/105072/3e02b863-19fb-3828-867b-a1d06699d11c.jpg[/img]
[img]http://dl.iteye.com/upload/picture/pic/105076/4acbb827-f8dd-3094-9aea-08e376fd6103.jpg[/img]
[img]http://dl.iteye.com/upload/picture/pic/105074/8d062f02-0532-3c48-9321-6581775b5dfa.jpg[/img]

3.后经过检查，发现数据库脚本升级前，原来的job可以运行（是否报错不确定），新的job运行虽然报错，但是也完成了要执行的操作，但是无法修改执行时间等信息。

4.尝试使用utlrp.sql脚本对无效对象重新编译：
SQL>$ORACLE_HOME\rdbms\admin\utlrp.sql
该脚本运行前有以下注意事项
4.1 确认以下包是否有错误，如果有错误尝试手工编译
STANDARD
DBMS_STANDARD
4.2 以下为运行脚本的注意事项，请仔细阅读
Rem NOTES
Rem * This script must be run using SQL*PLUS.
Rem * You must be connected AS SYSDBA to run this script.
Rem * There should be no other DDL on the database while running the
Rem script. Not following this recommendation may lead to deadlocks.
4.3 以sys/sysdba权限运行脚本

5.由于工作人员失误，没有按照要求在非生产时间执行脚本，出现数据库死锁，并且plsql和sql plus等工具无法连接，数据库错误信息如下：
日志报的错：ORA-04020: 尝试锁定对象 SYS.CDC_ALTER_CTABLE_BEFORE 时检测到死锁


6.经查，该问题可能与上面提到的死锁不同，是oracle的一个bug
bug信息：

This is cause by internal bug 3017048 fixed in 10.1.0.2.

Internal BUG:3017048 - Ora-4020, Functional Index Locking During Invalidation Causing Self-Deadlock

Researching the issue on ora-4020 and SYS.CDC_CREATE_CTABLE_BEFORE lead to BUG 3228083 which was

experiencing similar problems on the same object. This bug was closed as a duplicate of bug 3017048.

Internal BUG:3228083 - Appsst10g:R8:Utlrcmp Error: Ora-04045: Sys.Cdc_Create_Ctable_Before

fixed in 10.1.0.2.

7.解决方法

Set the following in the INIT.ORA then restart the database:

_system_trig_enabled=false

aq_tm_processes=0

job_queue_processes=0


Then rerun CATALOG.SQL.

After creating and running these scripts and the database is ok then restart the database with the parameters taken out of the init.ora.

8.参考资料
[url]http://ijavagos.iteye.com/blog/1209200[/url]
[url]http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php[/url]