oracle数据字典损坏恢复,oracle 9i删除public用户造成数据字典损坏所有sql语句不能操作的故障处理...-CSDN博客

操作系统sun,oracle 9.2.0.8.一开始维护人员新建一个用户只授予connect权限.用新建用户登录后发现能查询生产用户的数据.然后从dba_sys_privs视图将public用户给删除了.然后应用程序执行sql语句就报ORA-06553：PLS-213：Standard包不可访问的问题,解决这个问题的方法是执行catalog.sql和catproc.sql重建数据字典.

SQL>sqlplus /nolog

SQL> conn sys/密码 as sysdba

SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

SQL>@$ORACLE_HOME/rdbms/admin/dbmsutil.sql

SQL>alter package standard compile

SQL>alter package dbms_standard compile

ORA-04020: deadlock detected while trying to lock object SYS.CDC_ALTER_CTABLE_BEFORE

在执行alter package dbms_standard compile语句时出错了

在MOS上有一篇关于oracle 9.2.0.8中关于ORA-04020错误的bug,信息如下:

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

解决方法，用spfile 创建pfile, 在pfile里添加如下内容，然后用修改之后的pfile启动数据库，在执行脚本。

_system_trig_enabled=false

aq_tm_processes=0

job_queue_processes=0

脚本执行完后，在去掉这些参数，正常启动数据库即可

在成功执行alter package dbms_standard compile语句后再执行下面的脚本来编译无效对象:

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

然而再次遇到bug

ORA-00600: internal error code, arguments: [4412], [0x8BAA483C], [0x0], [], [], [], [], []

在mos上ORA-00600 [4406] or ORA-00600 [4412] in alert Log (文档 ID 742118.1)是关于这个bug的描述

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Server – Enterprise Edition – Version 9.2.0.1 to 9.2.0.8 [Release 9.2]

Information in this document applies to any platform.

***Checked for relevance on 10-Aug-2012***

SYMPTOMS

On Oracle 9i, the following errors could be reported in the alert log

ORA-00600: internal error code, arguments: [4412], [0x8BAA483C], [0x0], [], [], [], [], []

ORA-00600: internal error code, arguments: [4406], [0x8BAA483C], [0x0], [], [], [], [], []

The Call Stack should look something like:

ktcrcm ktcsod kssdch_stage ksuxds ksudel opilof opiodr ttcpip opitsk opiino opiodr opidrv sou2o

CAUSE

Unpublished Bug 2628920 ORA-600 [4412] AND [4406] WHEN EXITING SESSION AFTER CANCELLING DELETESCHEMA.

The bug explains that the problem may occur when the user terminates or cancels a ‘Delete schema’ operation using CTRL + C for example. The function opilof in the Call Stack above is an indication that the error is only seen at session log off.

SOLUTION

1. Upgrade to 10g where the bug is fixed.

2. Ignore the error as it is completely harmless, the error is generated at session log off.

Note that in some cases the errors have also been encountered on Oracle 10.2 releases. These may be related to a different bug, but remain harmless.

REFERENCES

@ BUG:2628920 – ORA-600:[4412] AND [4406] WHEN EXITING SESSION AFTER CANCELLING DELETESCHEMA

解决方法是升级到oracle 10g.这里选择手工对无效对象进行编译.