Archive

Archive for March, 2015

Messages ORA-0 and SMG-3503 in the alert log file.

Last night I got the following error in the alert log file on one of my database (Oracle 10g):

ORA-0 encountered when generating server alert SMG-3503

I started to search and found that probably the SYS.ALERT_QT is missing or corrupted. So I checked the SYS.ALERT_QT table more specifically and found that there are some bad blocks in the SYSAUX tablespace (object with bad blocks was of course the SYS.ALERT_QT table).
So solution is to recreate the SYS.ALERT_QT table – in the Metalink you can find something about it:
– note 430146.1 How to recreate the SYS.ALERT_QUE

For Oracle 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.
In some cases you had to force drop the queue before, please do the following:
Run sqlplus, log in as SYS AS SYSDB:

alter system enable restricted session;

To drop server alert schema and ALERT_QT table.

@$ORACLE_HOME/rdbms/admin/catnoalr.sql
exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => ‘sys.alert_qt’, FORCE => TRUE);

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

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

To recompile the invalid objects

@$ORACLE_HOME/rdbms/admin/utlrp.sql
alter system disable restricted session;

This procedure may leave DBSNMP.MGMT_BSLN_INTERNAL invalid. To validate the same run catsnmp.sql script.