发现了ora-00600[729]

监控报警日志发现ORA-00600[729]
ORA-00600 [729] UGA内存泄露错误
在网上找到的文章(http://zhaolinjnu.blog.sohu.com/63509379.html)
最近数据库老是报ORA-00600 [729]错误，729错误是UGA内存泄露引起的，alert.log中的错误信息如下：

Fri Aug 24 10:15:25 2007
Errors in file /u01/oracle/admin/test/bdump/test_j005_2687840.trc:
ORA-00600: internal error code, arguments: [729], [1200], [space leak], [], [], [], [], []
Fri Aug 24 10:15:26 2007
Errors in file /u01/oracle/admin/test/bdump/test_j005_2687840.trc:
ORA-00600: internal error code, arguments: [729], [1200], [space leak], [], [], [], [], []

Mon Sep  3 16:29:09 2007
Errors in file /u01/oracle/admin/test/bdump/test_j000_3146712.trc:
ORA-00600: internal error code, arguments: [729], [600], [space leak], [], [], [], [], []
Mon Sep  3 16:29:10 2007
Errors in file /u01/oracle/admin/test/bdump/test_j000_3146712.trc:
ORA-00600: internal error code, arguments: [729], [600], [space leak], [], [], [], [], []

Tue Sep  4 10:25:01 2007
Errors in file /u01/oracle/admin/test/bdump/test_j002_3088582.trc:
ORA-00600: internal error code, arguments: [729], [600], [space leak], [], [], [], [], []
Tue Sep  4 10:25:01 2007
Errors in file /u01/oracle/admin/test/bdump/test_j002_3088582.trc:
ORA-00600: internal error code, arguments: [729], [600], [space leak], [], [], [], [], []

Sun Sep  9 15:34:10 2007
Errors in file /u01/oracle/admin/test/bdump/test_j000_3634104.trc:
ORA-00600: internal error code, arguments: [729], [1168], [space leak], [], [], [], [], []
Sun Sep  9 15:34:10 2007
Errors in file /u01/oracle/admin/test/bdump/test_j000_3634104.trc:
ORA-00600: internal error code, arguments: [729], [1168], [space leak], [], [], [], [], []

Sun Sep  9 20:43:50 2007
Errors in file /u01/oracle/admin/test/bdump/test_j000_2961774.trc:
ORA-00600: internal error code, arguments: [729], [2400], [space leak], [], [], [], [], []
Sun Sep  9 20:43:51 2007
Errors in file /u01/oracle/admin/test/bdump/test_j000_2961774.trc:
ORA-00600: internal error code, arguments: [729], [2400], [space leak], [], [], [], [], []

Sun Sep  9 20:49:18 2007
Errors in file /u01/oracle/admin/test/bdump/test_j001_2953286.trc:
ORA-00600: internal error code, arguments: [729], [9600], [space leak], [], [], [], [], []
Sun Sep  9 20:49:18 2007
Errors in file /u01/oracle/admin/test/bdump/test_j001_2953286.trc:
ORA-00600: internal error code, arguments: [729], [9600], [space leak], [], [], [], [], []

每次报729内存泄露的ORA-00600错误，生成了上面这些蓝色的跟踪文件，刚开始没有注意，后来发现这些蓝色的文件都有一个共同的特征，那就是都是job进程的dump文件,那么从现象来看，内存泄露与job有关了。但从trace文件看不出与哪个job有关，但看到了一条信息:wait Net message from dblink,从metalink上得到的信息看，有些过程里如果用到了database link，如果把这过程加到job里面，好像容易导致内存泄露的问题。究竟真实的原因如何，现在还很难下定论.

看看metalink上关于此错误的描述以及解决办法：

Subject:  ORA-600 [729] "UGA Space Leak"
  Doc ID:  Note:31056.1 Type:  REFERENCE
  Last Revision Date:  17-MAY-2007 Status:  PUBLISHED

Note: For additional ORA-600 related information please read Note 146580.1

PURPOSE:           
  This article discusses the internal error "ORA-600 [729]", what
  it means and possible actions. The information here is only applicable
  to the versions listed and is provided only for guidance.
 
ERROR:             
  ORA-600 [729] [a]

VERSIONS:          
  versions 7.0 to 10.1
 
DESCRIPTION:       

  A space leak has been detected in the User Global Area (UGA).

  There is NO data corruption as a result of this error.

  It is an internal memory housekeeping problem.
 
ARGUMENTS:
  Arg [a] This is the number of bytes leaked --内存泄露的大小，单位字节
 
FUNCTIONALITY:     
  MEMORY COMPONENT
 
IMPACT:            
  PROCESS FAILURE - But only during logoff so impact is minimal.
  NON CORRUPTIVE - No underlying data corruption.

 
SUGGESTIONS:       

  Event 10262 can be set to safely ignore small memory leaks.

  Set the following in init.ora for example to disable space
  leaks less than 4000 bytes:

        event = "10262 trace name context forever, level 4000"

  and stop and restart the database.

  Repeated errors or large memory leaks can be diagnosed further by
  sending the alert.log and trace files to Oracle support.

不过介绍的在系统参数文件中设置事件的方法是没有什么帮助的，内存泄露依然存在，只是不报出来而已。所以没有什么必要在参数文件中加此参数。因为oracle也没有其它的办法来解决此类错误，所以根源还是要我们自己来找到有问题的job,究竟具有什么样特征的存储过程容易导致此类内存泄露错误，也有可能每次泄露的原因都不尽相同，不知大家有什么诊断思路？

发生内存泄露时，常常发生的一个现象是，数据库中出现莫名其秒的大量的library cache pin等待，因为这个时候，我们根本没有进行DDL操作，所以排除由DDL操作引起，我们推测是由内存泄露引起。内存泄露有一个问题是导致有效的内存如果被覆盖，比如说一些sql语句或procedure对象的heap被部份覆盖,那么将会引起硬解析，所以出现莫名其妙的大量的library cache pin就找到了一些根据，任何事情发生都是有原因的，但在一个高度并发的，存在大量事务的数据库中，这时硬解析是非常危险的,极有可能使数据库down掉的。现在解决办法是：增大共享池大小，降低有效内存被覆盖的可能性，实践证明，这种办法是比较有效且可行的。
============================

metalink上的解释：
What is a space (memory) leak?

Memory leak problems generally occur when Oracle is trying to free memory allocated to a process. The memory leak dump is generally discovered during session logoff, when Oracle frees the heaps that are allocated for the user process.

When a user connects to Oracle, a user process is created and at that time the heap is allocated. Every process will have its own memory heap.

The memory is organized in to heaps and every heap consists of one or more extents. Each extent contains a series of contiguous memory chunks, and these chunks can be either FREE or ALLOCATED. The Generic Heap Manager takes care of allocating and deallocating the memory chunks, with the help of FREE LISTS and LRU LISTS.

Chunk types are as follows:

1. FREE
2. FREEABLE
3. RECREATABLE
4. PERMANENT
5. FREEABLE WITH MARK

It is not mandatory that each extent contain only one type of chunk. Extents can contain various types of chunks. When processes require memory chunks, they are allocated as needed. Oracle keeps track of the amount of memory allocated for the process internally.

When the process terminates, all of the memory that has been allocated for the process is automatically released. When the memory is released the allocated heaps are freed. Generally, when the heap is freed the only chunks that the process should identify as allocated are the PERMANENT chunks and FREE chunks on the freelist. If the process finds there are still FREEABLE or RECREATABLE chunks remaining, then the process has not properly deallocated the memory. This situation is considered a space leak.

Oracle generally performs space leak checks for the SGA Heap, UGA Heap, Large Pool Heap and PGA Heap. A space leak error will result in a trace file in the BACKGROUND_DUMP_DESTINATION or USER_DUMP_DESTINATION.

A space leak problem will generally capture the trace information and the heap dump.

In the alert.log, the error will be reported as:

Errors in file d:\oracle\admin\testdb\bdump\yourfilenamehere.trc:

ORA-00600: internal error code, arguments: [729], [560], [space leak], [], [], [], [], []

After the operating system and Oracle process header information you will see the following in the trace file:

*** 2006-10-03 18:43:11.598
*** SESSION ID:(34.50354) 2006-10-03 18:43:11.597
******** ERROR: UGA memory leak detected 560 ********

The above lines tells us that this memory leak is from the UGA and the number of bytes leaked is 560.

******************************************************
HEAP DUMP heap name="session heap" desc=0xaef81d0
extent sz=0xffb8 alt=32767 het=32767 rec=0 flg=3 pc=3
parent=0xaeb63e0 wner=0x7a4b7078 nex=(nil) xsz=0xffb8

The above few lines describe this dump as SESSION HEAP with the descriptor 0xaef81d0.

In the dump below, you can see the chunk not released is Chunk 4085a350. This is a recreatable chunk and the size is 560 bytes, which is exactly the number of bytes reported as leaked in the error ORA-00600 [729], [560], [space_leak].

When analyzing memory leak issues, identify the FREEABLE and RECREATABLE chunks. Ideally, they should equal the sum of the total amount of the memory bytes leaked in the error reported.

If you look at the memory dump below, you see the total size of freeable and recreatable chunks equals 560, which is the amount of the memory bytes leaked.

EXTENT 0 addr=0x407cf048
Chunk 407cf050 sz= 65456 free " "
EXTENT 1 addr=0x408a0048
Chunk 408a0050 sz= 65456 free " "
EXTENT 2 addr=0x40890048
Chunk 40890050 sz= 65456 free " "
EXTENT 3 addr=0x40850048
Chunk 40850050 sz= 41728 free " "
Chunk 4085a350 sz= 560 recreate "bind var heap " latch=(nil)
EXTENT 4 addr=0x407df048
Chunk 407df050 sz= 65456 free " "
EXTENT 5 addr=0x40f91048
Chunk 40f91050 sz= 65456 free " "
EXTENT 6 addr=0x40880048
Chunk 40880050 sz= 65456 free " "
EXTENT 7 addr=0x40870048
Chunk 40870050 sz= 65456 free " " 

How do I handle a space leak?

Step 1. Review the alert.log to verify the error and obtain the trace file information.

The alert.log will report an error similar to the following:

Sat Dec 02 21:52:17 2006
Errors in file d:\oracle\admin\testdb\udump\testdb_ora_5928.trc:
ORA-00600: internal error code, arguments: [729], [152], [space leak], [], [], [], [], []

a. the first bracketed number [729] is the common argument for space leak problems.
b. the second number [152] is the number of bytes leaked by the error.
c. the third argument is always [space leak].

Step 2. Open the associated trace file.

Below the operating system and Oracle process header information, you will see the following:

*** 2006-12-13 02:01:13.859
*** SESSION ID:(54.11635) 2006-12-13 02:01:13.859
******** ERROR: UGA memory leak detected 152 ********
******************************************************

The above error states:

a. the memory was leaked from the UGA area
b. the amount leaked is reported again in the text (152 bytes).

Step 3. Verify the leak was during session logoff.

a. Search the trace file for "Call Stack Trace". If "opilof" is referenced in the stack, the error is happening at session logoff. The section will look similar to the following:

----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+184 ? ksedst() 800000010000B938 ?
ksfdmp()+32 ? ksedmp() 800003FFBFFF6418 ?
kgeriv()+152 ? ksfdmp() 20000000B168 ?
kgesiv()+132 ? kgeriv() 40000000000002D9 ?
ksesic2()+124 ? kgesiv() 000000000 ?
ksmuhe()+1040 ? ksesic2() 000000000 ?
ksmugf()+400 ? ksmuhe() 000000000 ?
ksuxds()+2692 ? ksmugf() 800003FFBFFF4020 ?
ksudel()+104 ? ksuxds() 8000000100131B38 ?
opilof()+876 ? ksudel() 800003FFBFFF5808 ?
opiodr()+2416 ? opilof() 0650AB9D8 ?
ttcpip()+1320 ? opiodr() 8000000100004790 ?
opitsk()+1260 ? ttcpip() 000000100 ?
opiino()+1484 ? opitsk() 8000000100138268 ?
opiodr()+2416 ? opiino() 000001560 ?
opidrv()+752 ? opiodr() 800003FFBFFF0870 ?
sou2o()+40 ? opidrv() 000000000 ?
main()+228 ? sou2o() 000000000 ?

4. Are you using Dedicated Server or Multi Threaded Server?

a. If you are using Dedicated Server the impact of this error will end when the process exits. The result of this error is minimal and presents no real problem. The leak will be in the UGA.

b. If you are using Multi Threaded Servers(MTS) and/or an XA transaction process manager/monitor, the leaked memory is in the SGA. It is important to review the alert.log for other errors such as ORA-4030 or ORA-4031 to ensure the SGA is not experiencing additional resource related problems.

5. Can I ignore the leak?

a. Are there any other errors? If there are no other errors reported at the same time, this may be a case where the error was a rare occurrence and can be safely ignored. As a rule of thumb, leaks less than 90,000 bytes in size are considered to be of low significance. The solution in this case is to set event 10262 (see below).

b. Is the leak in the SGA? The alert.log should be reviewed for additional errors such as ORA-4030 and ORA-4031 to ensure there are no additional problems with the shared pool or operating system memory.

c. Does the error reproduce with a given task? If so, this is a case that should be investigated further because the leak could be a known bug. See Note 31056.1 ORA-600 [729] UGA Space Leak for a list of known bugs and fixes.

Setting EVENT 10262:

After the amount of bytes leaked has been determined to be minimal, and no other errors have been noted, you can choose to safely ignore the errors. Leaks up to 90,000 bytes in size are considered to be within the allowable range.

In cases where the amount of bytes leaked is allowable and you do not want the errors to be reported in the alert.log file, set event 10262. This will stop reporting of the ORA-600 [729] error in the alert log file up to the specified number of bytes.

a. Set the following event in init.ora parameter file. This example disables reporting for space leaks less than 90000 bytes:

event = "10262 trace name context forever, level 90000"

b. Stop and restart the database.

If the level is set to 1, space leak checking is disabled. This is not advised because large memory leaks will be missed.

If the event is set to a value greater than 1, any space leak up to the number specified in the event is ignored.

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7194105/viewspace-678482/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7194105/viewspace-678482/