动态修改PGA_AGGREGATE_TARGET 导致ORA-600[723]

发表于 2012 年 6 月 6 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：动态修改PGA_AGGREGATE_TARGET 导致ORA-600[723]

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在以前分析过ORA-600[729](SGA内存泄露),这次遇到ORA-600[723](PGA内存泄露)
操作系统数据库信息

ORACLE V9.2.0.3.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: dsdata

alert报错ORA-600[723]

Tue Jun 05 12:16:35 2012
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 274
Tue Jun 05 12:16:40 2012
alter database close normal 
Tue Jun 05 12:16:40 2012
SMON: disabling tx recovery
SMON: disabling cache recovery
Tue Jun 05 12:16:40 2012
Shutting down archive processes
Archiving is disabled
Tue Jun 05 12:16:40 2012
ARCH shutting down
Tue Jun 05 12:16:40 2012
ARCH shutting down
ARC1: Archival stopped
Tue Jun 05 12:16:40 2012
ARC0: Archival stopped
Tue Jun 05 12:16:40 2012
Thread 1 closed at log sequence 406
Successful close of redo thread 1.
Tue Jun 05 12:16:41 2012
Completed: alter database close normal
Tue Jun 05 12:16:41 2012
alter database dismount
Completed: alter database dismount
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Tue Jun 05 12:16:43 2012
Errors in file d:\oracle\admin\dsdata\udump\dsdata_ora_504.trc:
ORA-00600: internal error code, arguments: [723], [20664], [20664], [memory leak], [], [], [], []

通过alert日志可以知道,数据库shutdown immediate的时候报ORA-600[723]

分析trace文件

…………
EXTENT 147 addr=062ACCBC
  Chunk  62accc4 sz=     1252    free      "               "
  Chunk  62ad1a8 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  62ad9b4 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  62ae1c0 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  62ae9cc sz=     2060    freeable  "qesmmaLogInitia"
…………
EXTENT 153 addr=04232414
  Chunk  423241c sz=     4476    perm      "perm           "  alo=2868
  Chunk  4233598 sz=    18516    free      "               "
  Chunk  4237dec sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  42385f8 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  4238e04 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  4239610 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  4239e1c sz=     2060    freeable  "qesmmaLogInitia"
…………
--查询发现没有释放的内容都是在qesmmaLogInitia部分

*** 2012-06-05 12:16:43.000
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [723], [20664], [20664], [memory leak], [], [], [], []
Current SQL information unavailable - no SGA.
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_ksedmp+147          CALLrel  _ksedst+0            
_ksfdmp.108+e        CALLrel  _ksedmp+0            3
_kgeriv+89           CALLreg  00000000             217190 3
_kgesiv+4e           CALLrel  _kgeriv+0            217190 0 2D3 3 418FC2C
_ksesic3+3b          CALLrel  _kgesiv+0            217190 0 2D3 3 418FC2C 2D3 3
                                                   418FC2C
__VInfreq__ksmdpg+e  CALLrel  _ksesic3+0           2D3 0 50B8 0 50B8 1 B 26A3F28
f                                                  
_opidcl+1db          CALLrel  _ksmdpg+0            
_opidrv+3bf          CALLrel  _opidcl+0            21D328 0
_sou2o+19            CALLrel  _opidrv+0            
_opimai+150          CALLrel  _sou2o+0             418FE20 32 0 0
_BackgroundThreadSt  CALLrel  _opimai+0            
art@4+164                                          
77E6482C             CALLreg  00000000             
 
--------------------- Binary Stack Dump ---------------------

通过查询MOS发现[ID 242260.1]上的Stack Trace比较匹配.上面说到通过sql来直接修改pga_aggregate_target导致,查找alert日志,果然发现:

Mon May 21 15:18:33 2012
ALTER SYSTEM SET pga_aggregate_target='1048576000' SCOPE=MEMORY;
Mon May 21 15:18:33 2012
ALTER SYSTEM SET pga_aggregate_target='1048576000' SCOPE=SPFILE;

现在基本上可以确定引起整个ORA-600[723]的原因是:用户直接修改pga_aggregate_target参数,然后关闭数据库引起Bug:2975617导致

处理建议
Don’t alter the pga_aggregate_target dynamically Change it in init.ora file
针对本库,再次开启数据库应该处于正常状态(spfile已经修改),无需继续关注该问题.

此条目发表在 ORA-xxxxx 分类目录，贴了 , 标签。将固定链接加入收藏夹。

《动态修改PGA_AGGREGATE_TARGET 导致ORA-600[723]》有 2 条评论

  1. 惜分飞 说：
    2012 年 6 月 6 日13:22 
    Hdr: 2975617 9.2.0.3.0 RDBMS 9.2.0.3.0 SQL EXECUTION PRODID-5 PORTID-100 ORA-600
Abstract: ORA-600 [723] WHEN PGA_AGGREGATE_TARGET IS SET AT A SESSION LEVEL
 
*** 05/23/03 11:48 am ***
TAR:
----
 
PROBLEM:
--------
Oracle 9.2.0.3
 
ORA-600 [723] on shutdown when PGA_AGGREGATE_TARGET is set at the session 
level to a higher amount than the level in the init.ora.
 
PGA_AGGREGATE_TARGET = 83886080 is set in the init.ora.
 
DIAGNOSTIC ANALYSIS:
--------------------
 
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- --------
bitmap_merge_area_size                integer                          1048576
create_bitmap_area_size              integer                          8388608
hash_area_size                       integer                          0
sort_area_retained_size              integer                          0
sort_area_size                       integer                          0
 
Change:
ALTER SYSTEM SET pga_aggregate_target='94371840' SCOPE=MEMORY;
ALTER SYSTEM SET pga_aggregate_target='94371840' SCOPE=SPFILE;
 
Then shutdown immediate results in the following:
ORA-600: internal error code, arguments: [723], [10332], [10332],  [memory 
leak], [], [], [], []
 
Also tried:
 
disk_asynch_io=FALSE
 
 
WORKAROUND:
-----------
As long as he does not change at the session level, no memory leak on 
shutdown.
 
Ct does not change PGA_AGGREGATE_TARGET very often.
 
RELATED BUGS:
-------------
Stact trace similar to 2789432.
 
REPRODUCIBILITY:
----------------
Reproduces at customer site consistently.
 
TEST CASE:
----------
none.
 
STACK TRACE:
------------
Call stack is: _ksedmp _ksfdmp.108 _kgeriv _kgesiv _ksesic3 __VInfreq__ksmdpg 
f _opidcl _opidrv _sou2o.
  2. 惜分飞 说：
    2012 年 6 月 6 日13:20 
    Symptom(s)
~~~~~~~~~~
- ORA-00600: internal error  code, arguments: [723], [10332], [10332],
[memory leak], [], [], [], []

Stack Trace:
   ksesic3 ksmdpg opidcl opidrv sou2o

- Low setting of the parameter PGA_AGGREGATE_TARGET

- Memory being leaked  is "kopolal dvoid", i.e. some code is allocating this 
type of memory but it fails to release it. When the process terminates, we
detect that the memory was allocated but never freed and signal a leak.


Change(s)
~~~~~~~~~~
Shutdown after changing the pga_aggregate_target value at the session level.

Cause
~~~~~~~
This is Bug:2975617
Abstract: ORA-600 [723] WHEN PGA_AGGREGATE_TARGET IS SET AT A SESSION LEVEL



Fix
~~~~
The bug 2975617 is fixed in Oracle release 10g and above.

Workaround: 
 Don't alter the pga_aggregate_target dynamically
 Change it in init.ora file