ORA-21779: duration not active问题解决方法

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

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

Mon Jan 06 10:27:24 2014
Errors in file d:\Oracle\product\10.2.0\admin\orcl\bdump\orcl1_smon_22688.trc:
ORA-21779: duration not active
ORA-06512: at line 1


Mon Jan 06 10:27:26 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl1_smon_22688.trc:
ORA-21779: duration not active
ORA-06512: at line 1

Mon Jan 06 10:27:27 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl1_smon_22688.trc:
ORA-21779: duration not active
ORA-06512: at line 1

Mon Jan 06 10:27:29 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl1_smon_22688.trc:
ORA-21779: duration not active
ORA-06512: at line 1

Mon Jan 06 10:27:31 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl1_smon_22688.trc:
ORA-21779: duration not active
ORA-06512: at line 1

Mon Jan 06 10:27:40 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl1_smon_22688.trc:
ORA-21779: duration not active
ORA-06512: at line 1

Mon Jan 06 10:27:41 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl1_smon_22688.trc:
ORA-21779: duration not active
ORA-06512: at line 1

进一步分析日志中提到的d:\oracle\product\10.2.0\admin\orcl\bdump\orcl1_smon_22688.trc日志，内容如下：

*** SERVICE NAME:(SYS$BACKGROUND) 2013-11-20 23:24:57.404
*** SESSION ID:(981.1) 2013-11-20 23:24:57.404
*** 2013-11-20 23:24:57.404
Start recovery for domain 0, valid = 0, flags = 0x0
Validate domain 0
Validated domain 0, flags = 0x0
*** 2013-11-30 11:23:32.519
Drop transient type: SYSTPte10f0JkTHW7T5AN5zERhA==
*** 2013-11-30 11:23:32.519
SMON: following errors trapped and ignored:
ORA-21779: duration not active
ORA-06512: at line 1
Drop transient type: SYSTPte10f0JkTHW7T5AN5zERhA==
*** 2013-11-30 11:23:33.299
SMON: following errors trapped and ignored:
ORA-21779: duration not active
ORA-06512: at line 1
Drop transient type: SYSTPte10f0JkTHW7T5AN5zERhA==
*** 2013-11-30 11:23:34.188
SMON: following errors trapped and ignored:
ORA-21779: duration not active
ORA-06512: at line 1
Drop transient type: SYSTPte10f0JkTHW7T5AN5zERhA==
*** 2013-11-30 11:23:35.202
SMON: following errors trapped and ignored:
ORA-21779: duration not active
ORA-06512: at line 1
Drop transient type: SYSTPte10f0JkTHW7T5AN5zERhA==
*** 2013-11-30 11:23:36.216
SMON: following errors trapped and ignored:
ORA-21779: duration not active
ORA-06512: at line 1

Linux-6-64下安装Oracle 12C笔记

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

可以清楚的看到，，错误提示是在进行Drop transient type: SYSTPte10f0JkTHW7T5AN5zERhA==操作的时候产生，这个是什么操作呢？通过查询metalink，有如下解释

SYMPTOMS

SMON generated the following errors in the alert log file :

SMON: following errors trapped and ignored:
ORA-21779: duration not active
ORA-06512: at line 1
Drop transient type:
SYSTPfQMEpjI7QJ7gQ6wVAuBAng==ÿ

CAUSE

This error is caused by SMON not able to clean up some transient types and this problem has been reported a few times in Oracle10gR2.

SOLUTION

Other then producing large trace files, this error has no impact. SMON is erroring whilst evaluating a SYSTEM trigger when executing cleanup of TYPEs that are no longer needed, so currently we have the following options to address this:

Bounce the DB and see if the error is persistent

OR

As suggested by Oracle Development in similar bugs, set the following event at system level to delay the cleanup of these types:

SQL> alter system set events '22834 trace name context forever, level 1'

When you see the errors again (ORA-21779), please tail the alert logs on all instances and find out which one spits the error continuously. Then run the oradebug below against those instances:

$ sqlplus / as sysdba
oradebug setospid
oradebug event 22834 trace name context forever, level 1

>> tail th SMON trace, it should generate tons of logs for event 22834, once it stops, also monitor alert log,

when ORA-21779 is not generating anymore, disable the event as follows:

oradebug setospid
oradebug event 22834 trace name context off

This issue does not affect database functionality and the only problem is that the alert.log is filling up with many error messages (also after restarting the instance the error should not occur again).

The command "alter system flush shared_pool" could also resolve the problem.

是个小BUG,说明上说不影响使用，但是会产生大量的日志，使得alert日志快速增长，解决办法是通过设置22834内部事件，但是这样会导致数据库内的 transient types 增长，显然在生产库这样也是不行的，最后提到可以重启下数据库实例，可能这个错误就不再产生，也可以通过命令alter system flush shard_pool

清理下共享池也可以解决这个问题，双管齐下，该问题解决。