oracle11g服务管理问题，高手指教

[c497100315]

os环境：windows server 2003 enterprise edition
service pack2 .
32bit; 内存：16g
数据库：Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
oracle服务启动一段时间后，异常中断，重启又好一段时间，之后又会中断了。

log.xml:
…………
<msg time='2014-10-28T13:50:36.515+08:00' org_id='oracle' comp_id='rdbms'
msg_id='dbgetWarn:73:1181063835' type='WARNING' group='diag_dde'
level='16' host_id='20140929-1515' host_addr='172.18.144.202'>
<txt>DDE: Problem Key 'ORA 4030' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
</txt>
</msg>
<msg time='2014-10-28T13:50:38.359+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='20140929-1515' host_addr='172.18.144.202' module=''
pid='1936'>
<txt>Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_lgwr_1936.trc:
ORA-04030: out of process memory when trying to allocate 82444 bytes (pga heap,control file i/o buffer)
</txt>
</msg>
<msg time='2014-10-28T13:50:38.359+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='20140929-1515' host_addr='172.18.144.202' module=''
pid='1936'>
<txt>LGWR (ospid: 1936): terminating the instance due to error 4030
</txt>
</msg>
<msg time='2014-10-28T13:50:38.421+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='20140929-1515' host_addr='172.18.144.202' module=''
pid='972'>
<txt>Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_j000_972.trc:
ORA-04030: 在尝试分配 字节 (,) 时进程内存不足
</txt>
</msg>
<msg time='2014-10-28T13:50:38.421+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='20140929-1515' host_addr='172.18.144.202' module=''
pid='972'>
<txt>ORA-1092 : opiodr aborting process unknown ospid (2484_2992)
</txt>
</msg>
<msg time='2014-10-28T13:50:39.546+08:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='20140929-1515'
host_addr='172.18.144.202' pid='2992'>
<txt>ORA-1092 : opitsk aborting process
</txt>
</msg>
<msg time='2014-10-28T13:50:39.562+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='20140929-1515' host_addr='172.18.144.202' module='OEM.DefaultPool'
pid='1152'>
<txt>ORA-1092 : opiodr aborting process unknown ospid (2484_1152)
</txt>
</msg>
<msg time='2014-10-28T13:50:39.562+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='20140929-1515' host_addr='172.18.144.202' module='STREAMS'
pid='2952'>
<txt>Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_q001_2952.trc:
ORA-04030: out of process memory when trying to allocate bytes (,)
</txt>
</msg>
<msg time='2014-10-28T13:50:39.562+08:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='20140929-1515'
host_addr='172.18.144.202' pid='1152'>
<txt>ORA-1092 : opitsk aborting process
</txt>
</msg>
<msg time='2014-10-28T13:50:39.562+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='20140929-1515' host_addr='172.18.144.202' module='STREAMS'
pid='2952'>
<txt>ORA-1092 : opidrv aborting process Q001 ospid (2484_2952)
</txt>
<msg time='2014-10-28T13:50:46.453+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='20140929-1515' host_addr='172.18.144.202' module=''
pid='1936'>
<txt>Instance terminated by LGWR, pid = 1936
</txt>
</msg>
……

oracle参数如下：

SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 2G
memory_target big integer 2G
shared_memory_address integer 0
SQL> show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1232M
sga_target big integer 0
SQL> show parameter pga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL> show parameter shared_pool_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL> show parameter log_buffer;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 12252160
SQL> show parameter large_pool_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0

[小灰狼W]

检查内存使用情况 32位的系统，16G的内存用不上吧

[bw555]

楼上好眼力啊，我还真没注意是32位系统

[huangdh12]

在trace里面有一个 alert.log 比这个xml直观多了。

[sych888]

看看ALERT有么有价值的错误信息 也看看控制面板里面 事件探测器 有么有提示

[c497100315]

ORA-04030: 在尝试分配 1044 字节 (Typecheck,kggfaDoKghAlloc) 时进程内存不足 ORA-04030: 在尝试分配 2556 字节 (kxs-heap-b,bind var buf) 时进程内存不足 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_ora_3292.trc (incident=9901): ORA-04030: 在尝试分配 1044 字节 (Typecheck,kggfaDoKghAlloc) 时进程内存不足 ORA-04030: 在尝试分配 1044 字节 (Typecheck,kggfaDoKghAlloc) 时进程内存不足 ORA-04030: 在尝试分配 2556 字节 (kxs-heap-b,bind var buf) 时进程内存不足 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_ora_3292.trc (incident=9902): ORA-04030: 在尝试分配 5392 字节 (kxs-heap-w,Scan compression ctx) 时进程内存不足 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_ora_3292.trc (incident=9903): ORA-04030: 在尝试分配 2068 字节 (Typecheck,kggfaDoKghAlloc) 时进程内存不足 Fri Oct 24 05:35:01 2014 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_lgwr_1904.trc (incident=8761): ORA-04030: out of process memory when trying to allocate 82444 bytes (pga heap,control file i/o buffer) Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_lgwr_1904.trc: ORA-04030: out of process memory when trying to allocate 82444 bytes (pga heap,control file i/o buffer) LGWR (ospid: 1904): terminating the instance due to error 4030 Fri Oct 24 05:35:01 2014 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_cjq0_3848.trc (incident=9321): ORA-04030: out of process memory when trying to allocate bytes (,) Fri Oct 24 05:35:01 2014 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_pmon_2992.trc (incident=8689): ORA-04030: out of process memory when trying to allocate bytes (,) Fri Oct 24 05:35:01 2014 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_mmnl_404.trc (incident=8801): ORA-04030: out of process memory when trying to allocate bytes (,) Fri Oct 24 05:35:01 2014 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_j001_3492.trc (incident=9874): ORA-04030: out of process memory when trying to allocate bytes (,) Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_j001_3492.trc: ORA-04030: out of process memory when trying to allocate bytes (,) ORA-1092 : opidrv aborting process J001 ospid (3864_3492) Fri Oct 24 05:35:01 2014 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_ckpt_2612.trc (incident=8769): ORA-04030: out of process memory when trying to allocate bytes (,) Fri Oct 24 05:35:01 2014 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_smon_1824.trc (incident=8777): ORA-04030: out of process memory when trying to allocate bytes (,) Fri Oct 24 05:35:02 2014 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_diag_1252.trc (incident=8705): ORA-04030: out of process memory when trying to allocate bytes (,) DDE: Problem Key 'ORA 4030' was completely flood controlled (0x6) Further messages for this problem key will be suppressed for up to 10 minutes Fri Oct 24 05:35:02 2014 Errors in file e:\app\administrator\diag\rdbms\sbdb\sbdb\trace\sbdb_q001_3280.trc: ORA-04030: out of process memory when trying to allocate bytes (,) ORA-1092 : opidrv aborting process Q001 ospid (3864_3280) Fri Oct 24 05:35:03 2014 ORA-4030 : opidrv aborting process S000 ospid (3864_2216) Fri Oct 24 05:35:03 2014 ORA-1092 : opidrv aborting process W000 ospid (3864_2436) Instance terminated by LGWR, pid = 1904

[c497100315]

引用 4 楼 sych888 的回复:

看看ALERT有么有价值的错误信息 也看看控制面板里面 事件探测器 有么有提示

ALERT信息见5楼。 我给分配了2g，服务器现在用的人不多，但是不知道oracle内存占用一直往上蹦

[bw555]

ora-4030小结

[huangdh12]

32位windows是不能超过2g的，你把sga设置小一点试试。

[huangdh12]

或者把window的config.ini修改一下，让window能突破1.7g的限制，用到3g或者4g（整个oracle服务）

[c497100315]

内存限制为1.7g怎么理解， 我看任务管理器里面内存可以飙升到3,4G

[tomshenhao]

32位的数据库，内存使用量是默认只有1.7GB. http://www.itpub.net/thread-1030911-1-1.html http://ibmcn.blog.51cto.com/510174/655741 http://blog.itpub.net/10834762/viewspace-445622 可以参考这些设置。 但是最好装64位