ORA-21780 error is continuously reported in the alert log file

郑全

ORA-21780 error is continuously reported in the alert log file (Doc ID 2085188.1)

In this Document
Symptoms
Changes
Cause
Solution
References

APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
Below ORA-21780 error is continuously reported in the alert log file.

Wed Nov 04 16:51:22 2015
Errors in file <PATH>/<SID>_smon_1448.trc:
ORA-21780: Maximum number of object durations exceeded.
Errors in file <PATH>/<SID>_smon_1448.trc:
ORA-21780: Maximum number of object durations exceeded.

<<SID>_smon_1448.trc>
...
*** 2015-11-06 14:25:35.964
SMON: following errors trapped and ignored:
ORA-21780: Maximum number of object durations exceeded.
*** 2015-11-06 14:25:40.925
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-21780: Maximum number of object durations exceeded.
----- Current SQL Statement for this session (sql_id=6fjksztb0tu0g) -----
select dummy from dual where (sys.sysevent in ('ALTER', 'RENAME') and sys.dictionary_obj_type in ('INDEX','TABLE')) or
(sys.sysevent = 'CREATE' and sys.dictionary_obj_type in ('INDEX', 'PROCEDURE', 'TRIGGER', 'VIEW')) or
(sys.sysevent = 'DROP' and sys.dictionary_obj_type in ('INDEX', 'PROCEDURE', 'ROLE', 'TABLE', 'TRIGGER', 'TYPE', 'USER', 'VIEW'))

----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
...
kgeselv()+276 call dbkePostKGE_kgsf() 00BB130E0 ? 7F02C6192168 ?
000005514 ? 000000000 ?
100000000 ? 000000002 ?
kgesecl0()+134 call kgeselv() 00BB130E0 ? 7F02C6192168 ?
000005514 ? 000000000 ?
100000000 ? 000000000 ?
kohbgu()+314 call kgesecl0() 00BB130E0 ? 7F02C6192168 ?
000005514 ? 000000000 ?
100000000 ? 0A8542A90 ?
kocbgd()+114 call kohbgu() 00BB130E0 ? 00000000F ?
000000001 ? 000000000 ?
100000000 ? 0A8542A90 ?
kokmgdur()+61 call kocbgd() 00BB130E0 ? 000000000 ?
00000000A ? 000000001 ?
100000000 ? 0A8542A90 ?
kkxmsiag()+226 call kokmgdur() 7F02C5BB5EC8 ? 000000000 ?
00000000A ? 000000001 ?
100000000 ? 0A8542A90 ?
evapls()+405 call kkxmsiag() 0A853FC00 ? 0A853FB00 ?
100000000 ? 000000000 ?
7FFF9260C860 ? 000000000 ?
evaopn2()+812 call evapls() 0A853FC00 ? 000000000 ?
7F02C5E88360 ? 00BB130E0 ?
0A853FB98 ? 000000000 ?
evaorno()+850 call evaopn2() 0A853FB98 ? 7F02C5BB6C28 ?
7F02C5E88360 ? 7F02B5226410 ?
0A853FC00 ? 000000000 ?
expepr()+47 call evaorno() 0A853FCF8 ? 7F02C5BB6C28 ?
7F02C5E88360 ? 7F02B5226410 ?
0A853FC00 ? 000000000 ?
evaiand()+46 call expepr() 0A853FCF8 ? 7F02C5BB6C28 ?
7F02C5E88360 ? 7F02B5226410 ?
0A853FC00 ? 000000000 ?
expepr()+47 call evaiand() 7F02C5E88360 ? 7F02C5BB6C28 ?
7F02C5E88360 ? 7F02B5226410 ?
0A853FC00 ? 000000000 ?
evaior()+48 call expepr() 7F02C5E88360 ? 7F02C5BB6C28 ?
7F02C5E88360 ? 7F02B5226410 ?
0A853FC00 ? 000000000 ?
...
SO: 0x11bc54de8, type: 4, owner: 0x11876fde0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x11876fde0, name=session, file=ksu.h LINE:12624, pg=0
(session) sid: 584 ser: 1 trans: 0x112832978, creator: 0x11876fde0
flags: (0x1100051) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x48409) -/DDLT2/INC
DID: , short-term DID:
txn branch: (nil)
oct: 78, prv: 0, sql: 0x7f02c61bb9d0, psql: 0x7f02c61bb9d0, user: 0/SYS
ksuxds FALSE at location: 0
service name: SYS$BACKGROUND
Current Wait Stack:
Not in wait; last wait ended 0.755142 sec ago
Wait State:
fixed_waits=0 flags=0x21 boundary=(nil)/-1
...

LibraryHandle: Address=0x7f02c61bb9d0 Hash=c1b7edce LockMode=N PinMode=X LoadLockMode=0 Status=VALD
ObjectName: Name=DROP TYPE "SYSMAN"."SYSTPI9xk3nz5HszgUwZyIgosVw==" FORCE

...

CHANGES

CAUSE
The default cleanup period is 12 hours. It seems that at the end of these 12 hours, so may transient types are to be cleaned up. As the session duration takes long time, ORA-21780 message is posted in the alert log file.

<dba_types.html>
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.

SQL> select o.* from obj$ o, type$ t where o.oid$ = t.tvoid and bitand(t.properties,8388608) = 8388608 and (sysdate-o.ctime) > 0.0007;

OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
12066493 73 SYSTPJEFOfzQ2R7ngUwZyIgp5/w== 1 13 11-NOV-2015 09:26:47 11-NOV-2015 09:26:47 11-NOV-2015 09:26:47 1 0 24414E7F343747B9E0530672220A79FF 0 65535 73
12067703 73 SYSTPJEJAB4hFMvjgUwZyIgpX9g== 1 13 11-NOV-2015 11:41:07 11-NOV-2015 11:41:07 11-NOV-2015 11:41:07 1 0 24424007884632F8E0530672220A57F6 0 65535 73
12066496 73 SYSTPJEMaNuSXHs3gUwZyIgp8QA== 1 13 11-NOV-2015 11:27:48 11-NOV-2015 11:27:48 11-NOV-2015 11:27:48 1 0 24431A36E4981ECDE0530672220A7C40 0 65535 73
12067775 73 SYSTPJEMc2zHaqNjgUwVyIgoidQ== 1 13 11-NOV-2015 11:40:35 11-NOV-2015 11:40:35 11-NOV-2015 11:40:35 1 0 24431CDB31DBA8D8E0530572220A2275 0 65535 73
12068587 73 SYSTPJETKzbv1z4DgUwZyIgo9uQ== 1 13 11-NOV-2015 13:27:47 11-NOV-2015 13:27:47 11-NOV-2015 13:27:47 1 0 2444CACDBBF6CF80E0530672220A3DB9 0 65535 73
12069509 73 SYSTPJEaNa+Gugt7gUwZyIgrYUw== 1 13 11-NOV-2015 15:33:48 11-NOV-2015 15:33:48 11-NOV-2015 15:33:48 1 0 24468D6BE1AF82DEE0530672220AD853 0 65535 73

6 rows selected.

SQL> select * from dba_types where type_name like 'SYS%==' order by owner, type_name;

OWNER TYPE_NAME TYPE_OID TYPECODE ATTRIBUTES METHODS PRE INC FIN INS SUPERTYPE_OWNER SUPERTYPE_NAME LOCAL_ATTRIBUTES LOCAL_METHODS TYPEID
SYSMAN SYSTPJEFOfzQ2R7ngUwZyIgp5/w== 24414E7F343747B9E0530672220A79FF COLLECTION 0 0 NO NO YES YES
SYSMAN SYSTPJEJAB4hFMvjgUwZyIgpX9g== 24424007884632F8E0530672220A57F6 COLLECTION 0 0 NO NO YES YES
SYSMAN SYSTPJEMaNuSXHs3gUwZyIgp8QA== 24431A36E4981ECDE0530672220A7C40 COLLECTION 0 0 NO NO YES YES
SYSMAN SYSTPJEMc2zHaqNjgUwVyIgoidQ== 24431CDB31DBA8D8E0530572220A2275 COLLECTION 0 0 NO NO YES YES
SYSMAN SYSTPJETKzbv1z4DgUwZyIgo9uQ== 2444CACDBBF6CF80E0530672220A3DB9 COLLECTION 0 0 NO NO YES YES
SYSMAN SYSTPJEaNa+Gugt7gUwZyIgrYUw== 24468D6BE1AF82DEE0530672220AD853 COLLECTION 0 0 NO NO YES YES

6 rows selected.

SOLUTION
It could be good idea to re-schedule this automatic clean-up every small period of time. As a good start, I would suggest setting it to 30 minutes as follows:

[1] First of all, you can disable the previously recommended event as we have already had the errorstack trace:

$ sqlplus / as sysdba
alter system set events '21780 trace name context off';
exit

[2] Second, please manually drop the current transient types as follows:

$ sqlplus / as sysdba
drop type "SYSMAN"."SYSTPJEFOfzQ2R7ngUwZyIgp5/w==" force;
drop type "SYSMAN"."SYSTPJEJAB4hFMvjgUwZyIgpX9g==" force;
drop type "SYSMAN"."SYSTPJEMaNuSXHs3gUwZyIgp8QA==" force;
drop type "SYSMAN"."SYSTPJEMc2zHaqNjgUwVyIgoidQ==" force;
drop type "SYSMAN"."SYSTPJETKzbv1z4DgUwZyIgo9uQ==" force;
drop type "SYSMAN"."SYSTPJEaNa+Gugt7gUwZyIgrYUw==" force;

[3] Third, kindly set the new cleanup interval as follows:

$ sqlplus / as sysdba
alter system set event='22834 trace name context forever, level 30' scope=spfile;
shut immediate
startup