ALERT_QUE表重建方法

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

1. 惜分飞 说：

   2012 年 7 月 19 日00:14

   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. 惜分飞 说：

   2012 年 7 月 19 日00:13

   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;