In this Document
Goal
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2Information 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_QUEThe 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.
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;
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;
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;
References
NOTE:603289.1 - OBJECTS MGMT_BSLN_INTERNAL and MGMT_BSLN INVALID FOR DBSNMP
