联系：手机/微信(+86 17813235971) QQ(107644445)
标题：动态修改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已经修改),无需继续关注该问题.