OEM – rebuild sys_alert queue
Subject:Â How to recreate the SYS.ALERT_QUE
 Doc ID: 430146.1 Type: HOWTO
 Modified Date : 15-MAY-2009 Status: PUBLISHED
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1.0 to 10.2.0.4.0
Information in this document applies to any platform.
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
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;
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;
Â
Discussion ¬