oracle11g预警日志解析,oracle11g告警日志报错ORA-04030

问题定位：每天晚上十点的时候运行自动SQL优化的任务时报错内存不足。

alert报错：

Errors in file /u01/app/oracle/diag/rdbms/cpty/cpty/trace/cpty_j003_28000.trc (incident=28312):

ORA-04030: 在尝试分配 432 字节 (kxs-heap-c,kprbalo temp memory) 时进程内存不足

Incident details in: /u01/app/oracle/diag/rdbms/cpty/cpty/incident/incdir_28312/cpty_j003_28000_i28312.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u01/app/oracle/diag/rdbms/cpty/cpty/trace/cpty_j003_28000.trc (incident=28313):

ORA-04030: 在尝试分配 8168 字节 (kxs-heap-c,kdbmal allocation) 时进程内存不足

ORA-04030: 在尝试分配 432 字节 (kxs-heap-c,kprbalo temp memory) 时进程内存不足

Incident details in: /u01/app/oracle/diag/rdbms/cpty/cpty/incident/incdir_28313/cpty_j003_28000_i28313.trc

解决：

适当增大pga，给sharepool、largerpool等设置一个最小值。

Mon Jan 07 08:30:49 2019

ALTER SYSTEM SET java_pool_size='100M' SCOPE=SPFILE;

ALTER SYSTEM SET large_pool_size='100M' SCOPE=SPFILE;

ALTER SYSTEM SET shared_pool_size='500M' SCOPE=SPFILE;

Mon Jan 07 08:31:26 2019

ALTER SYSTEM SET db_cache_size='1000M' SCOPE=SPFILE;

Mon Jan 07 08:32:03 2019

ALTER SYSTEM SET pga_aggregate_target='3000M' SCOPE=SPFILE;

内存自动管理的问题，建议不适用内存自动管理，设置sga_target=0.

永久解决关闭sql优化任务

关闭自动优化

BEGIN

dbms_auto_task_admin.disable(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;

/

开启自动优化

BEGIN

dbms_auto_task_admin.enable(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;

/