This issue happens on Oracle RAC environment 11.2.0.3 , db alert log show ora-21780 frequently caused by the SMON is not able to clean some objects, This short article simply record it. and how to fixed it.
# db alert log
Note:
rais ORA-21780
# smon trace file
Note:
Seems to be trying to delete the same object multiple times, the object is transient type named "SYSTPeJCgNOyAXhHgVEgPz2hhrg==".This issue only seems to occur in RAC environments.The default cleanup period is 12 hours.
What's "transient type " objects?
Transient Objects: By the name, the transient objects are temporary objects which are created and destroyed any time during an application, whose lifespan does not exceed that of the application. These cannot be converted into persistent objects as these are meant only for storing temporary values for computational purposes.
The object types can be used as:
1. Data type for a column in an Oracle table.
2. Data type for an object instance during its declaration in the PL/SQL program units.
3. Attributes of another object or a collection type.
4. Formal parameters in the procedure and function signature.
5. The return type for a function.
TIPs: Beginning with Oracle Database release 12c, release 12.2, transient types can be created on Active Data Guard instance if:
Real Time Apply is running on Active Data Guard, and Logical Standby is not lagging far behind the Primary (typically, order of seconds).
Solutions:
1] First of all, you can disable the previously recommended event as we have already had the errorstack trace:
[2] Second, please manually drop the current transient types as follows:
Once the issue is encountered, Flushing the buffer cache could workaround it.
Note: we can re-schedule this automatic clean-up every small period of time. e.g. setting it to 30 minutes as follows:
You can prevent SMON to cleanup transient types by setting event 22834 like below.However, this can cause transient types to increase.
# db alert log
Fri Nov 02 09:55:59 2018
Errors in file /oracle/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_smon_12872.trc:
ORA-21780: Maximum number of object durations exceeded.
Errors in file /oracle/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_smon_12872.trc:
ORA-21780: Maximum number of object durations exceeded.
Errors in file /oracle/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_smon_12872.trc:
ORA-21780: Maximum number of object durations exceeded.
Errors in file /oracle/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_smon_12872.trc:
ORA-21780: Maximum number of object durations exceeded.
Errors in file /oracle/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_smon_12872.trc:
ORA-21780: Maximum number of object durations exceeded.
Errors in file /oracle/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_smon_12872.trc:
ORA-21780: Maximum number of object durations exceeded.
Note:
rais ORA-21780
# smon trace file
*** 2018-11-02 09:59:27.930
SMON: following errors trapped and ignored:
ORA-21780: Maximum number of object durations exceeded.
*** 2018-11-02 09:59:29.468
Drop transient type: SYSTPeJCgNOyAXhHgVEgPz2hhrg==
*** 2018-11-02 09:59:29.468
SMON: following errors trapped and ignored:
ORA-21780: Maximum number of object durations exceeded.
*** 2018-11-02 09:59:34.478
Drop transient type: SYSTPeJCgNOyAXhHgVEgPz2hhrg==
*** 2018-11-02 09:59:34.478
SMON: following errors trapped and ignored:
ORA-21780: Maximum number of object durations exceeded.
Drop transient type: SYSTPeJCgNOyAXhHgVEgPz2hhrg==
Note:
Seems to be trying to delete the same object multiple times, the object is transient type named "SYSTPeJCgNOyAXhHgVEgPz2hhrg==".This issue only seems to occur in RAC environments.The default cleanup period is 12 hours.
What's "transient type " objects?
Transient Objects: By the name, the transient objects are temporary objects which are created and destroyed any time during an application, whose lifespan does not exceed that of the application. These cannot be converted into persistent objects as these are meant only for storing temporary values for computational purposes.
The object types can be used as:
1. Data type for a column in an Oracle table.
2. Data type for an object instance during its declaration in the PL/SQL program units.
3. Attributes of another object or a collection type.
4. Formal parameters in the procedure and function signature.
5. The return type for a function.
TIPs: Beginning with Oracle Database release 12c, release 12.2, transient types can be created on Active Data Guard instance if:
Real Time Apply is running on Active Data Guard, and Logical Standby is not lagging far behind the Primary (typically, order of seconds).
Solutions:
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
SQL> select 'drop type "'||o.name ||'" FORCE; --'||o.owner#||o.ctime
from obj$ o, type$ t
where o.oid$ = t.tvoid and
bitand(t.properties,8388608) = 8388608 and (sysdate-o.ctime) > 0.0007;
Once the issue is encountered, Flushing the buffer cache could workaround it.
Note: we can re-schedule this automatic clean-up every small period of time. e.g. setting it to 30 minutes as follows:
$ sqlplus / as sysdba
alter system set event='22834 trace name context forever, level 30' scope=spfile;
shut immediate
startup
You can prevent SMON to cleanup transient types by setting event 22834 like below.However, this can cause transient types to increase.
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。




