ORA-21779: duration not active問題解決方案

ORA-21779: duration not active問題解決方案

使用者告知一個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:

1. Bounce the DB and see if the error is persistent
   
   OR
2. 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 <smon pid>
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 <smon pid>
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

清理下共用池也可以解決這個問題，雙管齊下，該問題解決。