ANBOB™

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

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.

打赏

微信扫一扫，打赏作者吧～