Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1107695
  • 博文数量: 178
  • 博客积分: 2776
  • 博客等级: 少校
  • 技术积分: 2809
  • 用 户 组: 普通用户
  • 注册时间: 2012-03-22 15:36
文章分类

全部博文(178)

文章存档

2014年(3)

2013年(66)

2012年(109)

分类: Oracle

2012-11-30 10:01:40

obj$基表中NON-EXISTENT对象现象详解。

 

我们可以通过以下查询来了解obj$基表中NON-EXISTENT对象的条目总数(type#=10),若这个总数在不断减少说明smon正在执行清理工作

 

    select trunc(mtime), substr(name, 1, 3) name, count(*)

      from obj$

     where type# = 10

       and not exists (select * from dependency$ where obj# = p_obj#)

     group by trunc(mtime), substr(name, 1, 3);

 

      select count(*)

        from user$ u, obj$ o

       where u.user#(+) = o.owner#

         and o.type# = 10

         and not exists

       (select p_obj# from dependency$ where p_obj# = o.obj#);

 

如何禁止SMON清理obj$基表

 

我们可以通过设置诊断事件event=10052 trace name context forever’来禁止SMON清理obj$基表,当我们需要避免SMONcleanup obj$的相关代码而意外终止或spin从而开展进一步的诊断时可以设置该诊断事件。在Oracle并行服务器或RAC环境中,也可以设置该事件来保证只有特定的某个节点来执行清理工作。

10052, 00000, "don't clean up obj$"

 

alter system set events '10052 trace name context forever, level 65535';

 

Problem Description: We are receiving the below warning during db startup:

WARNING: kqlclo() has detected the following :

Non-existent object 37336 NOT deleted because an object

of the same name exists already.

Object name: PUBLIC.USER$

 

This is caused by the SMON trying to cleanup the SYS.OJB$.

SMON cleans all dropped objects which have a SYS.OBJ$.TYPE#=10.

This can happen very often when you create an object that have the same name as a public synonym.

 

When SMON is trying to remove non-existent objects and fails because there are duplicates,

multiple nonexistent objects with same name.

This query will returned many objects with same name under SYS schema:

 

select o.name,u.user# from user$ u, obj$ o where u.user# (+)=o.owner# and o.type#=10

and not exists (select p_obj# from dependency$ where p_obj# = o.obj#);

 

To cleanup this message:

 

Take a full backup of the database - this is crucial. If anything goes wrong during this procedure,

your only option would be to restore from backup, so make sure you have a good backup before proceeding.

We suggest a COLD backup. If you plan to use a HOT backup, you will have to restore point in time if any problem happens

 

Normally DML against dictionary objects is unsupported,

but in this case we know exactly what the type of corruption,

also you are instructing to do this under guidance from Support.

 

Data dictionary patching must be done by an experienced DBA.

This solution is unsupported.

It means that if there were problems after applying this solution, a database backup must be restored.

Oracle 10g 补丁:http://www.cuug.com/
阅读(2464) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~