ORA-36 encountered when generating server alert SMG-3503

January 24, 2014

Recently, I faced the following issue into alert.log file:
ORA-36 encountered when generating server alert SMG-3503

Here is what the documentation says about ORA-36:

ORA-00036: maximum number of recursive SQL levels (string) exceeded     Cause: An attempt was made to go more than the specified number of recursive SQL levels.     Action: Remove the recursive SQL, possibly a recursive trigger.

I tried to understand what is recursive SQL and found that into the doc (Concepts):

recursive SQL

SQL that the database executes in the background to obtain space for database objects. You can think of recursive SQL as “side effect” SQL.

So I understand that a recursive SQL is sql command ran in the background of your SQL command to do it, such as querying the data dictionnary, updating internal values…

There is a limit of 50 recursive sessions (Support Doc ID 48793.1) and this hard coded limit is NOT configurable.

The second part of the error message I got is “when generating server alert SMG-3503″.

What is an SMG alert?

SMG stands for the Server Manageability component of the Server Generated Alert feature (Support Doc ID 1428693.1).

Message “ORA-%d encountered when generating server alert ” is reported to the alert.log when the internal functionality of alerts encounter a specific error. Most of these occur when DML is performed on the wri$ tables.

SMG-XXXX are internal messages within the alert system. They are not Oracle errors.
Receiving errors during an internal Adviser operation does not affect the database.

So, SMG is one of the components of the Server Generated Alert feature. The error is only related to the Server Generated Alert system and not to the database.

As a reminder here is the concept of the Server Generated Alert feature:

Self-monitoring take place as the database performs its regular operation, ensuring that the database is aware of problems as they arise. Oracle Database can send a server-generated alert to notify you of an impending problem.

Alerts are automatically generated when a problem occurs or when data does not match expected values for metrics such as physical reads per second or SQL response time. A metric is the rate of change in a cumulative statistic. Server-generated alerts can be based on user-specified threshold levels or because an event has occurred.

They are alerts raised when thresholds are crossed. Those thresholds you define on metrics with DBConsole for example.

Even if there is not a specific Support Doc ID for my error (ORA-36), I found support Doc ID 1428693.1 very similar, dealing with “ORA-21780 encountered when generating server alert SMG-3503″.

The ORA-21780 means :

ORA-21780: Maximum number of object durations exceeded.     Cause: This typically happens if there is infinite recursion in the PL/SQL function that is being executed.     Action: User should alter the recursion condition in order to prevent infinite recursion.

In summary, if you unsubscribe an agent from the Server-Generated Alerts System this will generate a recursive plsql which leads to this error message in alert.log.

I guess my database raised the maximum number of recursive sql (ORA-36) before raising this ORA-21780.

The conclusion of the note is that we can safely ignore this message