How to recreate the SYS.ALERT_QUE

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

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

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;