ALERT_QUE表重建方法

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ALERT_QUE表重建方法

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

最近关注了下网络上,出现了很多AQ$_ALERT_QT_N的index SYS_IOT_TOP_NNNN坏块引起的数据库异常(主要是SYSAUX表空间),因为他们是IOT表和主键的关系,不能简单的rebuild.查询了一些资料,得到一些信息

ALERT_QUE表用途
The ALERT_QUE is used by the Grid Control and DB Control Management Agents to monitor server-generated alerts

ALERT_QUE表重建方法

--方法1
SQL> connect / as sysdba

SQL>alter system enable restricted session;

To drop server alert schema.

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

To re-create tables, sequence, type and queue for server alert

SQL>@$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
SQL>@$ORACLE_HOME/rdbms/admin/catalrt.sql

To recompile the invalid objects

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

SQL> alter system disable restricted session; 
 

--方法2
SQL> connect / as sysdba

SQL>alter system enable restricted session;

To drop server alert schema.

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

Rerun catproc.sql

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

SQL> alter system disable restricted session; 

补充说明

By running the script up, the queue tables will be recreated and the messages 
in the queue will be lost.

For 11g you can use catmwin.sql which has the steps to recreate the ALERT_QT. 
Alternatively, for 11g you can use the catproc.sql to recreate.  

If this option may leave DBSNMP.MGMT_BSLN_INTERNAL invalid. 
To validate the same run catsnmp.sql [NOTE:603289.1]
此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

ALERT_QUE表重建方法》有 2 条评论

  1. 惜分飞 说:

    OBJECTS MGMT_BSLN_INTERNAL and MGMT_BSLN INVALID FOR DBSNMP

    Applies to:
    Enterprise Manager for RDBMS - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
    Information in this document applies to any platform.
    Oracle Server - Enterprise Edition - Version: 10.2.0.1
    Database version 10.2.0.4
    Grid Control 10.2.0.3, 10.2.0.4 or DB control 10.2.0.3, 10.2.0.4
    Purpose
    
    After upgrading the database version from one version to another, the following objects for
    the DBSNMP owner may be invalid:
    
    OWNER          OBJECT                  TYPE                    STATUS
    
    DBSNMP         MGMT_BSLN_INTERNAL      PACKAGE BODY            INVALID
    DBSNMP         MGMT_BSLN               PACKAGE BODY            INVALID         
    
    
    The issue may happen when the below packages are not compiled properly during the upgrade.
    packages dbsnmp.mgmt_bsln and dbsnmp.mgmt_bsln_internal 
    Last Review Date
    
    June 17, 2008
    Instructions for the Reader
    
    A Troubleshooting Guide is provided to assist in debugging a specific issue. 
    When possible, diagnostic tools are included in the document to assist in troubleshooting.
    
    Troubleshooting Details
    
    Try to compile the individual packages as follows:
    
    SQL> alter package dbsnmp.mgmt_bsln compile;
    
    SQL> alter package dbsnmp.mgmt_bsln_internal compile;
    
    The above steps may not help in resolving this issue most of the times.
    
    Follow the below steps to resolve the issue:
    
    1. Log into the database as the SYS user as SYSDBA.
    2. Shutdown the database
    3. Start the database in restriced/upgrade mode
    4. Run catalog.sql and catproc.sql
    5. Shutdown the database
    6. Start the database
    7. Run utlrp.sql
    
    
    
    
    Run the below query to check if these objects are still invalid.
    
    SQL>select substr(owner,1,12) owner, substr(object_name,1,30) object,
    substr(object_type,1,30) type, status from dba_objects 
    where status <> 'VALID';
    
    Explanation:
    When script catalog.sql is run, it will create the data dictionary and the 
    catproc.sql is run, it creates all structures required for PL/SQL. 
    
    The main purpose for running the scripts is if there were any failures 
    during the repository upgrade such as failure in creating 
    the objects etc.. will be recovered.
    
    By running the above scripts, the data dictionary is recreated which 
    includes database objects and the structures only.After recreating the objects, 
    runing the utlrp.sql script is helpful to validate the invalid objects.
    
    
    
  2. 惜分飞 说:

    How to recreate the SYS.ALERT_QUE

    Applies to:
    Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
    Information in this document applies to any platform.
    ***Checked for relevance on 25-Jan-2011***
    Goal
    
    The purpose of this article is to outline the steps for recreating the SYS.ALERT_QUE 
    
    The ALERT_QUE is used by the Grid Control and DB Control Management Agents to monitor server-generated alerts 
    
    NOTE: 
    By running the script below, the queue tables will be recreated and the messages 
    in the queue will be lost.
    
    For 11g you can use catmwin.sql which has the steps to recreate the ALERT_QT. 
    Alternatively, for 11g you can use the catproc.sql to recreate.  
    
    Solution
    SQL> connect / as sysdba
    
    SQL>alter system enable restricted session;
    
    To drop server alert schema.
    
    SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql
    
    To re-create tables, sequence, type and queue for server alert
    
    SQL>@$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
    SQL>@$ORACLE_HOME/rdbms/admin/catalrt.sql
    
    To recompile the invalid objects
    
    SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
    
    SQL> alter system disable restricted session; 
     
    
    Note: This option may leave DBSNMP.MGMT_BSLN_INTERNAL invalid. 
               To validate the same run catsnmp.sql
    
    OR
    
    
    SQL> connect / as sysdba
    
    SQL>alter system enable restricted session;
    
    To drop server alert schema.
    
    SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql
    
    Rerun catproc.sql
    
    SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
    
    SQL> alter system disable restricted session;