Sunday, May 17, 2015

ORA-21780: Maximum number of object durations exceeded.

I noticed that the Oracle Enterprise Manager (OEM) repository database was throwing ORA-21780 errors every 10 seconds. This is what I found in the alertlog file:

Mon May 11 17:51:56 2010
Errors in file /oracle_binaries/app/diag/rdbms/oem/OEM1/trace/OEM1_smon_12465.trc:
ORA-21780: Maximum number of object durations exceeded.
Errors in file /oracle_binaries/app/diag/rdbms/oem/OEM1/trace/OEM1_smon_12465.trc:
ORA-21780: Maximum number of object durations exceeded.

Now looking at the SMON trace file:

*** 2015-05-11 17:56:57.999
SMON: following errors trapped and ignored:
ORA-21780: Maximum number of object durations exceeded.

*** 2015-05-11 17:57:08.155
         Drop transient type:   SYSTPE9CqEeDOZcXgUPAKvfBsfQ==

*** 2015-05-11 17:57:08.155
SMON: following errors trapped and ignored:
ORA-21780: Maximum number of object durations exceeded.

*** 2015-05-11 17:57:18.245
         Drop transient type:   SYSTPE9CqEeDOZcXgUPAKvfBsfQ==

*** 2015-05-11 17:57:18.245
SMON: following errors trapped and ignored:
ORA-21780: Maximum number of object durations exceeded.

As per MOS note 1081950.1, this is a non-reproducible issue and the cause is not identified.
It also states that this only happens in RAC environments.  The workaround provided is to manually drop those types, so let’s drop them.

First, we need to identify the problematic types and the owner.

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

NAME                         
------------------------------
115

Then we need to identify the username of the previous query:

SQL> select username from dba_users where user_id=115;

USERNAME
------------------------------
SYSMAN

And finally, identify and drop the types:

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set pagesize 1000
select 'DROP TYPE SYSMAN."'||o.name||'" FORCE;' from obj$ o, type$ t
where o.oid$ = t.tvoid and
bitand(t.properties,8388608) = 8388608 and (sysdate-o.ctime) > 0.0007;
Session altered.

SQL> SQL>   2    3

'DROPTYPESYSMAN."'||O.NAME||'"FORCE;'
--------------------------------------------------------
DROP TYPE SYSMAN."SYSTPE/jlp3fkcwzgUPAKvfAxHg==" FORCE;
DROP TYPE SYSMAN."SYSTPFHGYs/8tGgLgUPAKvfAKqg==" FORCE;
DROP TYPE SYSMAN."SYSTPFOpLxs22zYbgUPAKvfAnig==" FORCE;
DROP TYPE SYSMAN."SYSTPFe/P5cSmQTDgUPAKvfA/HQ==" FORCE;
DROP TYPE SYSMAN."SYSTPFWL+wF51Fc/gUPAKvfByCA==" FORCE;
DROP TYPE SYSMAN."SYSTPFiwpaJHEvQTgUPAKvfAwiw==" FORCE;
DROP TYPE SYSMAN."SYSTPFkBG80DWv1ngUPAKvfBD9Q==" FORCE;
DROP TYPE SYSMAN."SYSTPFkEdeM4CTOngUPAKvfBrTQ==" FORCE;

8 rows selected.

After those types were dropped, no more error messages appeared in the alertlog file.


Thanks,
Alfredo