oracle 无法分配32字节,ORA-04031: 无法分配 280 字节的共享内存

今天遇见了一个如下错误：数据库莫名连接不上，然后自动重启，就正常了，但是原因却是不明，现在查找原因中。

Errors in file /opt/app/oracle/admin/xateldb/bdump/xateldb_smon_25538.trc:

ORA-04031: 无法分配 280 字节的共享内存 ("shared pool","select o.name, u.name from o...","CCursor","kglob")

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:37:26 2010

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:37:29 2010

Errors in file /opt/app/oracle/admin/xateldb/bdump/xateldb_smon_25538.trc:

ORA-04031: 无法分配 280 字节的共享内存 ("shared pool","select o.name, u.name from o...","CCursor","kglob")

PMON failed to acquire latch, see PMON dump

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:37:38 2010

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:37:40 2010

Errors in file /opt/app/oracle/admin/xateldb/bdump/xateldb_smon_25538.trc:

ORA-04031: 无法分配 280 字节的共享内存 ("shared pool","select o.name, u.name from o...","CCursor","kglob")

PMON failed to acquire latch, see PMON dump

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:37:50 2010

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:37:50 2010

Errors in file /opt/app/oracle/admin/xateldb/bdump/xateldb_smon_25538.trc:

ORA-04031: 无法分配 280 字节的共享内存 ("shared pool","select o.name, u.name from o...","CCursor","kglob")

PMON failed to acquire latch, see PMON dump

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:38:01 2010

Errors in file /opt/app/oracle/admin/xateldb/bdump/xateldb_smon_25538.trc:

ORA-04031: 无法分配 280 字节的共享内存 ("shared pool","select o.name, u.name from o...","CCursor","kglob")

Thu Feb 25 09:38:02 2010

PMON failed to acquire latch, see PMON dump

PMON failed to acquire latch, see PMON dump

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:38:11 2010

Errors in file /opt/app/oracle/admin/xateldb/bdump/xateldb_smon_25538.trc:

ORA-04031: 无法分配 280 字节的共享内存 ("shared pool","select o.name, u.name from o...","CCursor","kglob")

Thu Feb 25 09:38:14 2010

PMON failed to acquire latch, see PMON dump

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:38:22 2010

Errors in file /opt/app/oracle/admin/xateldb/bdump/xateldb_smon_25538.trc:

ORA-04031: 无法分配 280 字节的共享内存 ("shared pool","select o.name, u.name from o...","CCursor","kglob")

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:38:26 2010

PMON failed to acquire latch, see PMON dump

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:38:32 2010

Errors in file /opt/app/oracle/admin/xateldb/bdump/xateldb_smon_25538.trc:

ORA-04031: 无法分配 280 字节的共享内存 ("shared pool","select o.name, u.name from o...","CCursor","kglob")

PMON failed to acquire latch, see PMON dump

Thu Feb 25 09:38:38 2010

ALTER DATABASE CLOSE NORMAL

Thu Feb 25 09:38:40 2010

SMON: disabling tx recovery

Thu Feb 25 09:38:42 2010

CLOSE: Error 604 during database close

Thu Feb 25 09:38:42 2010

ORA-604 signalled during: ALTER DATABASE CLOSE NORMAL...

Thu Feb 25 09:38:42 2010

SMON: enabling tx recovery

Thu Feb 25 09:38:42 2010

Shutting down instance (abort)

License high water mark = 443

Instance terminated by USER, pid = 19213

Thu Feb 25 09:38:50 2010

Starting ORACLE instance (normal)

Thu Feb 25 09:39:01 2010

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =61

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.1.0.

System parameters with non-default values:

processes                = 500

sessions                 = 555

__shared_pool_size       = 10317987840

__large_pool_size        = 16777216

__java_pool_size         = 16777216

__streams_pool_size      = 0

nls_language             = SIMPLIFIED CHINESE

nls_territory            = CHINA

sga_target               = 10485760000

control_files            = /oradata1/xateldb/sys/control01.ctl, /oradata1/xateldb/sys/control02.ctl, /oradata1/xateldb/sys/

control03.ctl

db_block_size            = 8192

__db_cache_size          = 117440512

compatible               = 10.2.0.1.0

log_archive_format       = %t_%s_%r.dbf

db_file_multiblock_read_count= 16

db_recovery_file_dest    = /redo_log/flash_recovery_area

db_recovery_file_dest_size= 214748364800

undo_management          = AUTO

undo_tablespace          = UNDOTBS1

remote_login_passwordfile= EXCLUSIVE

db_domain                =

job_queue_processes      = 10

background_dump_dest     = /opt/app/oracle/admin/xateldb/bdump

user_dump_dest           = /opt/app/oracle/admin/xateldb/udump

core_dump_dest           = /opt/app/oracle/admin/xateldb/cdump

audit_file_dest          = /opt/app/oracle/admin/xateldb/adump

db_name                  = xateldb

open_cursors             = 300

pga_aggregate_target     = 1258291200

_bloom_filter_enabled    = FALSE

PMON started with pid=2, OS id=19320

PSP0 started with pid=3, OS id=19322

MMAN started with pid=4, OS id=19324

DBW0 started with pid=5, OS id=19326

DBW1 started with pid=6, OS id=19328

LGWR started with pid=7, OS id=19330

CKPT started with pid=8, OS id=19332

SMON started with pid=9, OS id=19334

RECO started with pid=10, OS id=19336

CJQ0 started with pid=11, OS id=19338

MMON started with pid=12, OS id=19340

MMNL started with pid=13, OS id=19342

Thu Feb 25 09:39:02 2010

ALTER DATABASE   MOUNT

Thu Feb 25 09:39:06 2010

Setting recovery target incarnation to 1

Thu Feb 25 09:39:06 2010

Successful mount of redo thread 1, with mount id 611515574

Thu Feb 25 09:39:06 2010

Database mounted in Exclusive Mode

Completed: ALTER DATABASE   MOUNT

Thu Feb 25 09:39:06 2010

ALTER DATABASE OPEN

Thu Feb 25 09:39:06 2010

Beginning crash recovery of 1 threads

parallel recovery started with 15 processes

Thu Feb 25 09:39:07 2010

Started redo scan

Thu Feb 25 09:39:07 2010

Completed redo scan

5071 redo blocks read, 277 data blocks need recovery

Thu Feb 25 09:39:07 2010

Started redo application at

Thread 1: logseq 31588, block 32882

Thu Feb 25 09:39:07 2010

Recovery of Online Redo Log: Thread 1 Group 2 Seq 31588 Reading mem 0

Mem# 0 errs 0: /redo_log/xateldb/sys/redo02a.log

Mem# 1 errs 0: /redo_log/xateldb/sys/redo02b.log

Thu Feb 25 09:39:07 2010

Completed redo application

Thu Feb 25 09:39:07 2010

Completed crash recovery at

Thread 1: logseq 31588, block 37953, scn 16922071999

277 data blocks read, 277 data blocks written, 5071 redo blocks read

Thu Feb 25 09:39:07 2010

LGWR: STARTING ARCH PROCESSES

ARC0 started with pid=30, OS id=19376

Thu Feb 25 09:39:08 2010

ARC0: Archival started

ARC1: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC1 started with pid=31, OS id=19378

Thu Feb 25 09:39:08 2010

Thread 1 advanced to log sequence 31589

Thread 1 opened at log sequence 31589

Current log# 3 seq# 31589 mem# 0: /redo_log/xateldb/sys/redo03a.log

Current log# 3 seq# 31589 mem# 1: /redo_log/xateldb/sys/redo03b.log

Successful open of redo thread 1

Thu Feb 25 09:39:08 2010

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Thu Feb 25 09:39:08 2010

ARC1: STARTING ARCH PROCESSES

Thu Feb 25 09:39:08 2010

ARC0: Becoming the 'no FAL' ARCH

ARC0: Becoming the 'no SRL' ARCH

Thu Feb 25 09:39:08 2010

SMON: enabling cache recovery

Thu Feb 25 09:39:08 2010

db_recovery_file_dest_size of 204800 MB is 11.36% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Thu Feb 25 09:39:08 2010

ARC2: Archival started

ARC1: STARTING ARCH PROCESSES COMPLETE

ARC1: Becoming the heartbeat ARCH

ARC2 started with pid=32, OS id=19380

Thu Feb 25 09:39:08 2010

Successfully onlined Undo Tablespace 1.

Thu Feb 25 09:39:08 2010

SMON: enabling tx recovery

Thu Feb 25 09:39:08 2010

Database Characterset is ZHS16GBK

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

QMNC started with pid=34, OS id=19384

Thu Feb 25 09:39:09 2010

Completed: ALTER DATABASE OPEN

Thu Feb 25 09:44:25 2010

ALTER SYSTEM SET sga_target='1024M' SCOPE=BOTH;

Thu Feb 25 09:45:08 2010

Shutting down archive processes

Thu Feb 25 09:45:13 2010

ARCH shutting down

ARC2: Archival stopped

Thu Feb 25 10:08:54 2010

Thread 1 advanced to log sequence 31590

Current log# 1 seq# 31590 mem# 0: /redo_log/xateldb/sys/redo01a.log

Current log# 1 seq# 31590 mem# 1: /redo_log/xateldb/sys/redo01b.log

Thu Feb 25 10:26:11 2010

Thread 1 advanced to log sequence 31591

Current log# 2 seq# 31591 mem# 0: /redo_log/xateldb/sys/redo02a.log

Current log# 2 seq# 31591 mem# 1: /redo_log/xateldb/sys/redo02b.log