ORA-04031引起的宕機

今天發現數據庫異常宕機情況
出現錯誤ORA-04031: 无法分配 ORA-04031: 无法分配 32 字节的共享内存
數據庫版本 目前版本10.2.0.4 但是compatible還是10.2.0.3.0
按道理講此bug，在10.2.0.4已解決

1.請問是否是由於此參數沒改，導致此BUG的出現？待解答
2.要驗證此疑問，有好的法子沒？

日誌如下：
Thread 1 advanced to log sequence 34798 (LGWR switch)
  Current log# 4 seq# 34798 mem# 0: /u01/product/oradata/hremploy/redo04.log
  Current log# 4 seq# 34798 mem# 1: /data/hremploy/redo04a.log
Thu Jan 12 04:51:57 2012
Thread 1 advanced to log sequence 34799 (LGWR switch)
  Current log# 5 seq# 34799 mem# 0: /u01/product/oradata/hremploy/redo05.log
  Current log# 5 seq# 34799 mem# 1: /data/hremploy/redo05a.log
Thu Jan 12 05:18:06 2012
Errors in file /u01/product/admin/hremploy/bdump/hremploy_cjq0_6576.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","sql area","tmp")
Thu Jan 12 05:18:06 2012
Errors in file /u01/product/admin/hremploy/bdump/hremploy_j000_21902.trc:
ORA-12012: 自动执行作业 80857 出错
ORA-04031: 无法分配 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","SELECT COUNT (1) FROM KQM_CO...","sql area","tmp")
ORA-06512: 在 "hremploy.TIMER_GET_CONTINUEWORK", line 68
ORA-06512: 在 line 2
 字节的共享内存 ("","","","")
Thu Jan 12 05:18:06 2012
Errors in file /u01/product/admin/hremploy/bdump/hremploy_cjq0_6576.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select count(*) from sys.job...","sql area","tmp")
Thu Jan 12 05:18:06 2012
Errors in file /u01/product/admin/hremploy/bdump/hremploy_j000_21902.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select privilege# from sysau...","sql area","tmp")
ORA-12012: 自动执行作业 80857 出错
ORA-04031: 无法分配 ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","SELECT COUNT (1) FROM KQM_CO...","sql area","tmp")
ORA-06512: 在 "hremploy.TIMER_GET_CONTINUEWORK", line 68
ORA-06512: 在 line 2
 字节的共享内存 ("","","","")
Thu Jan 12 05:18:08 2012
Errors in file /u01/product/admin/hremploy/bdump/hremploy_reco_6574.trc:
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select host,userid,password,...","sql area","tmp")
Thu Jan 12 05:18:08 2012
RECO: terminating instance due to error 4031
Instance terminated by RECO, pid = 6574
Thu Jan 12 07:59:32 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =36
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 300
  __shared_pool_size       = 603979776
  __large_pool_size        = 16777216
  __java_pool_size         = 16777216
  __streams_pool_size      = 0
  sga_target               = 1610612736
  control_files            = /u01/product/oradata/hremploy/control01.ctl, /u01/product/oradata/hremploy/control02.ctl, /data/hremploy/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 956301312
  compatible               = 10.2.0.3.0
  log_archive_dest_1       = LOCATION=/data/hremploy/arch
  log_archive_format       = %t_%s_%r.arc
  archive_lag_target       = 1800
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /u01/product/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 900
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=hremployXDB)
  job_queue_processes      = 30
  background_dump_dest     = /u01/product/admin/hremploy/bdump
  user_dump_dest           = /u01/product/admin/hremploy/udump
  core_dump_dest           = /u01/product/admin/hremploy/cdump
  audit_file_dest          = /u01/product/admin/hremploy/adump
  db_name                  = hremploy
  open_cursors             = 300
  optimizer_index_cost_adj = 50
  pga_aggregate_target     = 610271232
PMON started with pid=2, OS id=13674
PSP0 started with pid=3, OS id=13676
MMAN started with pid=4, OS id=13678
DBW0 started with pid=5, OS id=13680
LGWR started with pid=6, OS id=13682
CKPT started with pid=7, OS id=13684
SMON started with pid=8, OS id=13686
RECO started with pid=9, OS id=13688
CJQ0 started with pid=10, OS id=13690
MMON started with pid=11, OS id=13692
Thu Jan 12 07:59:33 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=13694
Thu Jan 12 07:59:33 2012
starting up 1 shared server(s) ...
Thu Jan 12 07:59:33 2012
ALTER DATABASE   MOUNT
Thu Jan 12 07:59:37 2012
Setting recovery target incarnation to 2
Thu Jan 12 07:59:37 2012
Successful mount of redo thread 1, with mount id 3984872997
Thu Jan 12 07:59:37 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Thu Jan 12 07:59:37 2012
ALTER DATABASE OPEN
Thu Jan 12 07:59:38 2012
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Thu Jan 12 07:59:39 2012
Started redo scan
Thu Jan 12 07:59:39 2012
Completed redo scan
 47254 redo blocks read, 865 data blocks need recovery
Thu Jan 12 07:59:39 2012
Started redo application at
 Thread 1: logseq 34799, block 89218
Thu Jan 12 07:59:39 2012
Recovery of Online Redo Log: Thread 1 Group 5 Seq 34799 Reading mem 0
  Mem# 0: /u01/product/oradata/hremploy/redo05.log
  Mem# 1: /data/hremploy/redo05a.log
Thu Jan 12 07:59:39 2012

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

转载于:http://blog.itpub.net/25850100/viewspace-714777/