ORA-21779:duration not active 处理

王亮

RAC环境数据库在日志中产生大量ORA-21799错误信息，alert日志部分截取如下：

Errorsin file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_9425.trc:

ORA-21779:duration not active

ORA-06512:at line 1

Errorsin file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_9425.trc:

ORA-21779:duration not active

ORA-06512:at line 1

Errorsin file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_9425.trc:

ORA-21779:duration not active

ORA-06512:at line 1

WedJun 10 10:59:23 2020

Errorsin file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_9425.trc:

ORA-21779:duration not active

ORA-06512: at line 1


进一步分析日志中提到/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_9425.trc
*** 2020-05-14 01:05:49.650
*** SESSION ID:(2134.1) 2020-05-14 01:05:49.650
*** CLIENT ID:() 2020-05-14 01:05:49.650
*** SERVICE NAME:(SYS$BACKGROUND) 2020-05-14 01:05:49.650
*** MODULE NAME:() 2020-05-14 01:05:49.650
*** ACTION NAME:() 2020-05-14 01:05:49.650
  ChildNode:  ChildNumber=0 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed
  ChildNode:  ChildNumber=1 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=6 original_oacflg=16 original_oacmxl=2000 upgradeable_new_oacmxl=128
  ChildNode:  ChildNumber=0 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=6 original_oacflg=16 original_oacmxl=128 upgradeable_new_oacmxl=2000
  ChildNode:  ChildNumber=0 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1585760885 ksugctm=1585761310          Drop transient type:   SYSTPp2Z5D255yRDgU2XwEKyabA==

*** 2020-06-07 03:39:13.436
SMON: following errors trapped and ignored:
ORA-21779: duration not active
ORA-06512: at line 1

*** 2020-06-07 03:39:14.478
         Drop transient type:   SYSTPp2Z5D255yRDgU2XwEKyabA==

*** 2020-06-07 03:39:14.478
SMON: following errors trapped and ignored:
ORA-21779: duration not active
ORA-06512: at line 1

*** 2020-06-07 03:39:15.545
         Drop transient type:   SYSTPp2Z5D255yRDgU2XwEKyabA==

*** 2020-06-07 03:39:15.545
SMON: following errors trapped and ignored:
ORA-21779: duration not active
ORA-06512: at line 1

出现该问题是由于oracle的smon进程无法清理掉 transient types,从而出现该问题,根据官方的说法,这个错误是不会影响数据库正常使用,但是可以通过以下方法暂时规避这种错误:
1)通过设置alter system set events ’22834 trace name context forever, level 1′禁止smon清理transient types,从而来规避该错误,但是可能会引起transient types对象越来越多,当然你可以通过以下sql查询出来

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

然后删除掉相关记录DROP TYPE “SYSTPp2Z5D255yRDgU2XwEKyabA==” FORCE;【这个删除不是必须的】
2) flush shared_pool也可以临时规避这个问题
3) 重启数据库,可以暂时规避

具体参考:
SMON: Following Errors Trapped And Ignored ORA-21779 (Doc ID 988663.1)
Receiving ORA-21780 Continuously in the Alert Log and SMON Trace Reports “Drop transient type”. (Doc ID 1081950.1)