[重慶思莊每日技術分享]-報警日誌中不停出現ORA-21780

Receiving ORA-21780 Continuously in the Alert Log and SMON Trace Reports "Drop transient type". (Doc ID 1081950.1)

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Oracle Database Cloud Exadata Service - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Information in this document applies to any platform.

SYMPTOMS

The following error appears repeatedly in the alert log:

ORA-21780: Maximum number of object durations exceeded.

Alert.log:

Mon Apr 12 17:51:45 2010

Errors in file /oracle_binaries/app/diag/rdbms/prod/PROD1/trace/PROD1_smon_13729.trc:

ORA-21780: Maximum number of object durations exceeded.

Errors in file /oracle_binaries/app/diag/rdbms/prod/PROD1/trace/PROD1_smon_13729.trc:

ORA-21780: Maximum number of object durations exceeded.

Mon Apr 12 17:51:56 2010

Errors in file /oracle_binaries/app/diag/rdbms/prod/PROD1/trace/PROD1_smon_13729.trc:

ORA-21780: Maximum number of object durations exceeded.

Errors in file /oracle_binaries/app/diag/rdbms/prod/PROD1/trace/PROD1_smon_13729.trc:

ORA-21780: Maximum number of object durations exceeded.

SMON trace contains:

*** 2010-04-12 17:52:36.012

Drop transient type: SYSTPg9CboAG5QgLgQKjA3AEn5Q==

*** 2010-04-12 17:52:36.012

SMON: following errors trapped and ignored:

ORA-21780: Maximum number of object durations exceeded.

*** 2010-04-12 17:52:41.014

Drop transient type: SYSTPg9CboAG5QgLgQKjA3AEn5Q==

*** 2010-04-12 17:52:41.014

SMON: following errors trapped and ignored:

ORA-21780: Maximum number of object durations exceeded.

*** 2010-04-12 17:52:46.010

Drop transient type: SYSTPg9CboAG5QgLgQKjA3AEn5Q==

CAUSE

A number of bugs have been filed against the same situation but the root cause as to why SMON has problems cleaning up these objects has not been determined. Of the bugs filed, all have been closed by development as non reproducible issues or the bug was abandoned.

The solution provided is from Bug 7485772, closed as "Could Not Reproduce".

This issue only seems to occur in RAC environments.

SOLUTION

There are two possible solutions.

Solution #1

------------

Step 1

Make sure you have a good full backup.

Step 2

spool obj.lis

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

set pagesize 1000

select o.* from obj$ o, type$ t

where o.oid$ = t.tvoid and

bitand(t.properties,8388608) = 8388608 and (sysdate-o.ctime) > 0.0007;

spool off

Step 3

Find the object owner:

Step 4

Drop the objects.

DROP TYPE "SYSTPf/r2wN4keX7gQKjA3AFMSw==" FORCE;

Solution #2

------------

You can prevent SMON to cleanup transient types by setting event 22834 like below.

However, this can cause transient types to increase.

SQL> alter system set events '22834 trace name context forever, level 1';

System altered.

To turn off:

SQL> alter system set events '22834 trace name context off' ;

System altered.