SMON Following Errors ORA-21779

yyp2009發表於2016-01-22
Error

db版本資訊：
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 22 09:15:52 2016
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
一套10.2.0.4的RAC資料庫
日誌報錯資訊：
Errors in file /oracle/admin/resdb/bdump/resdb2_smon_6357384.trc:
ORA-21779: 持續時間不活動
ORA-06512: 在 line 1
Sun Jan 17 21:40:07 2016
Errors in file /oracle/admin/resdb/bdump/resdb2_smon_6357384.trc:
ORA-21779: 持續時間不活動
ORA-06512: 在 line 1
Sun Jan 17 21:40:12 2016
Errors in file /oracle/admin/resdb/bdump/resdb2_smon_6357384.trc:
ORA-21779: 持續時間不活動
ORA-06512: 在 line 1
Sun Jan 17 21:40:17 2016
Errors in file /oracle/admin/resdb/bdump/resdb2_smon_6357384.trc:
ORA-21779: 持續時間不活動
ORA-06512: 在 line 1
Sun Jan 17 21:40:22 2016
Errors in file /oracle/admin/resdb/bdump/resdb2_smon_6357384.trc:
ORA-21779: 持續時間不活動
ORA-06512: 在 line 1

trace日誌資訊：
SMON: following errors trapped and ignored:
ORA-21779: 持續時間不活動
ORA-06512: 在 line 1
         Drop transient type:   SYSTPKVEcg41YADbgU4eUR4IANg==
*** 2016-01-17 23:56:32.782
SMON: following errors trapped and ignored:
ORA-21779: 持續時間不活動
         Drop transient type:   SYSTPKVEcg41YADbgU4eUR4IANg==
*** 2016-01-17 23:56:37.723
SMON: following errors trapped and ignored:
ORA-21779: 持續時間不活動
         Drop transient type:   SYSTPKVEcg41YADbgU4eUR4IANg==xRESGS鼰
*** 2016-01-17 23:56:42.723
SMON: following errors trapped and ignored:
ORA-21779: 持續時間不活動

可惜沒有做system dump否則可以進一步看到是smon程式清理一個transient type時報錯，大約過程呢（ SMON performs periodic cleanup of temporary segments that are no longer needed）：
Cleaning Up Stray Temporary Segments 
Some temporary segments are not cleaned up as expected, and can often persist for hours. This sometimes results in tablespaces running out of space inappropriately. To avoid such problems, DBAs can trigger the cleanup of straggling temporary segments. 
What causes stray temporary segments? 
When a segment is dropped, its extents are not freed immediately. Initially the process dropping the segment just changes the segment's type to a temporary segment. This operation can be rolled back if the statement fails for any reason. The temporary segment is normally cleaned up and its extents freed upon the conclusion of the call. However, if the data dictionary cache row representing the segment is still dirty or in use, temporary segment cleanup cannot occur at this time. This applies in particular to temporary segments released by recursive calls. Because the parent transaction has not yet committed, such temporary segments cannot be cleaned up immediately. 
How do stray temporary segments get cleaned up? 
The task of cleaning up straggling temporary segments and freeing their extents falls to the SMON process. Although SMON wakes up every five minutes, unless it is explicitly posted by another process, it only checks for temporary segments to clean up once every two hours and five minutes. Even then it will only clean up five temporary segments at most, and only if it can get the required locks within 5 seconds. So temporary segment cleanup can appear to take a long time, even days. 
However, SMON also performs temporary segment cleanup when it is posted explicitly by another process. You can make use of this fact to force SMON to clean up temporary segments more promptly. SMON is posted whenever a space transaction fails. So you can trigger temporary segment clean up by attempting to create a table of 2 extents with a small INITIAL and an impossible NEXT extent size. However, a more elegant way of posting SMON is to use the ORADEBUG WAKEUP command from within SQL*Plus (or SVRMGRL before 8.1). There is an APT script that does this, called post_smon.sql.
果然呢，這個過程大約是，要清理臨時物件，需要呼叫oracle核心函式，而這個核心函式呢需要申請lock（row cache instance lock）如果申請不到就會大量產生鎖阻塞。
解決辦法參考：
1 Receiving ORA-21780 Continuously in the Alert Log and SMON Trace Reports "Drop transient type". (文件 ID 1081950.1)
2 Receiving ORA-21780 Continuously in the Alert Log and SMON Trace Reports "Drop transient type". (文件 ID 1081950.1)
3 SMON: Following Errors Trapped And Ignored ORA-21779 (文件 ID 988663.1)


######################################################################
- Script:        post_smon.sql
-- Purpose:        to post SMON to cleanup stray temporary segments
-- For:                8.1
--
-- Copyright:        (c) Ixora Pty Ltd
-- Author:        Steve Adams
--
-------------------------------------------------------------------------------
@save_sqlplus_settings

column pid new_value Smon

set termout off
select
  p.pid
from
  sys.v_$bgprocess b,
  sys.v_$process p
where
  b.name = 'SMON' and
  p.addr = b.paddr
/
set termout on

oradebug wakeup &Smon

undefine Smon
@restore_sqlplus_settings

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-1980569/，如需轉載，請註明出處，否則將追究法律責任。

相關文章