用LOGMNR分析200M大小归档日志的问题
用LOGMNR分析200M大小归档日志的问题

环境
Oracle 9.2.0.8
ARCHIVELOG，归档日志全。
数据库相关参数
utl_file_dir string /oracle/logmnr
workarea_size_policy string auto
sort_area_retained_size integer 2097152
sort_area_size integer 5242880

问题 一个表的数据被误删除几千条，希望通过LOGMNR分析该时间段的归档日志。每个归档日志大小为200M


操作
exec dbms_logmnr.add_logfile(LogFileName=>'指定时间的归档日志');
exec dbms_logmnr.start_logmnr(Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
exec dbms_logmnr.start_logmnr();

select sql_undo from v$logmnr_contents where DATA_OBJD# = 查询删除表的ID ;

每次执行到select一段时间后报错误ora-00600：internal error code, arguments: [723], [8592], [pga heap], [], [], [], [], []

trace记录为

****** ERROR: PGA size limit exceeded: 104864352 > 104857600 *****
******************************************************
HEAP DUMP heap name="pga heap" desc=1037f2570
extent sz=0x2190 alt=168 het=32767 rec=0 flg=3 opc=1
parent=0 owner=0 nex=0 xsz=0x2190

记得曾有人提出dedicated server模式下每个用户的PGA最大为100M，增加数据库参数
pga_aggregate_target big integer 1073741824

重新运行，执行到相同地方同样ora-00600: internal error code, arguments: [723], [65472], [top uga heap], [], [], [], [], []


Trace记录为
****** ERROR: PGA size limit exceeded in rfg: 104870008 > 104857600 *****
******************************************************
HEAP DUMP heap name="pga heap" desc=1037f2570
extent sz=0x2190 alt=168 het=32767 rec=0 flg=2 opc=3
parent=0 owner=0 nex=0 xsz=0x2190
EXTENT 0 addr=10456c278
Chunk 10456c288 sz= 320 free " "

错误稍有差异。

现在怀疑是logmnr无法处理过大的归档日志文件，或者是BUG。

请大家给出意见，在这种情况下如何不完全恢复误删除表的数据。谢谢！

ORA-600 [723] "PGA memory leak" [ID 39308.1]
修改时间 15-JUN-2011 类型 REFERENCE 状态 PUBLISHED


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

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

ERROR:

Format: ORA-600 [723] [a]

VERSIONS:
versions 6.0 to 11.1

DESCRIPTION:
This is a memory leak in the Program Global Area (PGA)

PGA is checked for Space leaks at logoff time and a leak was found.

There is no data corruption with this error.

ARGUMENTS:
Arg [a] Logoff PGA size in bytes
Arg "memory leak"

FUNCTIONALITY:
MEMORY COMPONENT

IMPACT:
PROCESS FAILURE - but only during session delete 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 can be diagnosed further by sending
the alert.log and trace files to Oracle Support Services.

Known Issues:




Known Bugs

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:


The list below is restricted to show only bugs believed to affect version 9.2.0.8.
Other bugs may affect this version but have not been confirmed as being relevant yet.

There is 1 bug listed.

NB Bug Fixed Description
2319840 10.1.0.2 OERI:723 possible on exit of MTS or Oracle7 XA server process when Oracle Trace enabled

'*' against a bug indicates that an alert exists for that issue.
'+' indicates a particularly notable bug.
'P' indicates a port specific bug.
"OERI:nnnnn" is used as shorthand for ORA-600 [nnnnn].

添加
event = "10262 trace name context forever, level 4000"
后运行正常。