記憶體不足導致安裝時報錯ORA-3113（二）

其實這個問題是在上一個問題後發生的，當時將記憶體引數調整到1.5G左右。

安裝RAC資料庫時，執行到新增資料字典的步驟報錯：ORA-3113錯誤。

檢查資料庫的alert檔案：

Fri Dec 17 13:51:00 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Interface type 1 ce1 10.0.0.0 configured from GPnP Profile for use as a cluster interconnect
Interface type 1 ce0 172.25.0.0 configured from GPnP Profile for use as a public interface
Picked latch-free SCN scheme 3
WARNING: db_recovery_file_dest is same as db_create_file_dest
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options.
Using parameter settings in client-side pfile /data/oracle/admin/testrac/pfile/init.ora on machine racnode1
System parameters with non-default values:
  processes                = 300
  sessions                 = 472
  nls_language             = "SIMPLIFIED CHINESE"
  nls_territory            = "CHINA"
  memory_target            = 1600M
  db_block_size            = 16384
  compatible               = "11.2.0.0.0"
  log_archive_dest_1       = "LOCATION=+DATA"
  log_archive_format       = "%t_%s_%r.dbf"
  db_create_file_dest      = "+DATA"
  db_recovery_file_dest    = "+DATA"
  db_recovery_file_dest_size= 64G
  undo_tablespace          = "UNDOTBS1"
  instance_number          = 1
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=testracXDB)"
  remote_listener          = ""
  audit_file_dest          = "/data/oracle/admin/testrac/adump"
  audit_trail              = "DB"
  db_name                  = "testrac"
  open_cursors             = 300
  diagnostic_dest          = "/data/oracle"
Cluster communication is configured to use the following interface(s) for this instance
  10.0.0.1
cluster interconnect IPC version:Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
Fri Dec 17 13:51:01 2010
PMON started with pid=2, OS id=8367
Fri Dec 17 13:51:01 2010
VKTM started with pid=3, OS id=8369 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Fri Dec 17 13:51:01 2010
GEN0 started with pid=4, OS id=8373
Fri Dec 17 13:51:02 2010
DIAG started with pid=5, OS id=8375
Fri Dec 17 13:51:02 2010
DBRM started with pid=6, OS id=8380
Fri Dec 17 13:51:02 2010
PING started with pid=7, OS id=8385
Fri Dec 17 13:51:02 2010
PSP0 started with pid=8, OS id=8388
Fri Dec 17 13:51:02 2010
ACMS started with pid=9, OS id=8392
Fri Dec 17 13:51:02 2010
DIA0 started with pid=10, OS id=8394
Fri Dec 17 13:51:02 2010
LMON started with pid=11, OS id=8396
Fri Dec 17 13:51:04 2010
LMD0 started with pid=12, OS id=8398
Fri Dec 17 13:51:04 2010
RMS0 started with pid=13, OS id=8418
Fri Dec 17 13:51:04 2010
LMHB started with pid=14, OS id=8420
Fri Dec 17 13:51:05 2010
MMAN started with pid=15, OS id=8422
Fri Dec 17 13:51:05 2010
DBW0 started with pid=16, OS id=8427
Fri Dec 17 13:51:05 2010
LGWR started with pid=17, OS id=8432
Fri Dec 17 13:51:05 2010
CKPT started with pid=18, OS id=8434
Fri Dec 17 13:51:05 2010
SMON started with pid=19, OS id=8439
Fri Dec 17 13:51:05 2010
RECO started with pid=20, OS id=8441
Fri Dec 17 13:51:05 2010
MMON started with pid=21, OS id=8443
Fri Dec 17 13:51:06 2010
MMNL started with pid=22, OS id=8445
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
DISM started, OS id=8457
lmon registered with NM - instance number 1 (internal mem no 0)
Reconfiguration started (old inc 0, new inc 2)
List of instances:
 1 (myinst: 1)
 Global Resource Directory frozen
* allocate domain 0, invalid = TRUE
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Post SMON to start 1st pass IR
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
Reconfiguration complete
Fri Dec 17 13:51:11 2010
ORACLE_BASE from environment = /data/oracle
Fri Dec 17 13:51:11 2010
CREATE DATABASE "testrac"
MAXINSTANCES 32
MAXLOGHISTORY 1
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE SIZE 1024M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 4096M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 4096M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 2048M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1  SIZE 512000K,
GROUP 2  SIZE 512000K
USER SYS IDENTIFIED BY *USER SYSTEM IDENTIFIED BY
Starting background process ASMB
Fri Dec 17 13:51:11 2010
ASMB started with pid=26, OS id=8524
Starting background process RBAL
Fri Dec 17 13:51:12 2010
RBAL started with pid=27, OS id=8534
NOTE: initiating MARK startup
Starting background process MARK
Fri Dec 17 13:51:12 2010
MARK started with pid=28, OS id=8539
NOTE: MARK has subscribed
NOTE: Loaded library: System
SUCCESS: diskgroup DATA was mounted
Fri Dec 17 13:51:20 2010
NOTE: dependency between database testrac and diskgroup resource ora.DATA.dg is established
Database mounted in Exclusive Mode
Lost write protection disabled
Fri Dec 17 13:51:49 2010
Successful mount of redo thread 1, with mount id 115643663
Assigning activation ID 115643663 (0x6e4950f)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: +DATA/testrac/onlinelog/group_1.258.737992281
  Current log# 1 seq# 1 mem# 1: +DATA/testrac/onlinelog/group_1.259.737992287
Successful open of redo thread 1
Fri Dec 17 13:51:49 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 17 13:51:50 2010
SMON: enabling cache recovery
processing ?/rdbms/admin/dcore.bsq
create tablespace SYSTEM datafile  SIZE 1024M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

  EXTENT MANAGEMENT LOCAL online
Fri Dec 17 13:52:05 2010
Completed: create tablespace SYSTEM datafile  SIZE 1024M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

  EXTENT MANAGEMENT LOCAL online
create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
processing ?/rdbms/admin/dsqlddl.bsq
processing ?/rdbms/admin/dmanage.bsq
CREATE TABLESPACE sysaux DATAFILE  SIZE 4096M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
Fri Dec 17 13:53:16 2010
Completed: CREATE TABLESPACE sysaux DATAFILE  SIZE 4096M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
processing ?/rdbms/admin/dplsql.bsq
processing ?/rdbms/admin/dtxnspc.bsq
CREATE SMALLFILE UNDO TABLESPACE UNDOTBS1 DATAFILE  SIZE 2048M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

Fri Dec 17 13:53:51 2010
Successfully onlined Undo Tablespace 2.
Completed: CREATE SMALLFILE UNDO TABLESPACE UNDOTBS1 DATAFILE  SIZE 2048M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

CREATE SMALLFILE TEMPORARY TABLESPACE TEMP TEMPFILE  SIZE 4096M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

Fri Dec 17 13:54:05 2010
Completed: CREATE SMALLFILE TEMPORARY TABLESPACE TEMP TEMPFILE  SIZE 4096M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM
processing ?/rdbms/admin/dfmap.bsq
processing ?/rdbms/admin/denv.bsq
processing ?/rdbms/admin/drac.bsq
processing ?/rdbms/admin/dsec.bsq
Fri Dec 17 13:54:17 2010
processing ?/rdbms/admin/doptim.bsq
processing ?/rdbms/admin/dobj.bsq
processing ?/rdbms/admin/djava.bsq
processing ?/rdbms/admin/dpart.bsq
Fri Dec 17 13:54:27 2010
processing ?/rdbms/admin/drep.bsq
Fri Dec 17 13:54:40 2010
processing ?/rdbms/admin/daw.bsq
processing ?/rdbms/admin/dsummgt.bsq
processing ?/rdbms/admin/dtools.bsq
processing ?/rdbms/admin/dexttab.bsq
processing ?/rdbms/admin/ddm.bsq
processing ?/rdbms/admin/dlmnr.bsq
Fri Dec 17 13:54:53 2010
processing ?/rdbms/admin/ddst.bsq
Fri Dec 17 13:54:54 2010
SMON: enabling tx recovery
Starting background process SMCO
Fri Dec 17 13:54:55 2010
SMCO started with pid=31, OS id=10574
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Fri Dec 17 13:54:57 2010
QMNC started with pid=32, OS id=10594
Completed: CREATE DATABASE "testrac"
MAXINSTANCES 32
MAXLOGHISTORY 1
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE SIZE 1024M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 4096M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 4096M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 2048M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1  SIZE 512000K,
GROUP 2  SIZE 512000K
USER SYS IDENTIFIED BY *USER SYSTEM IDENTIFIED BY
Fri Dec 17 13:54:57 2010
db_recovery_file_dest_size of 65536 MB is 1.56% 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.
Fri Dec 17 13:54:57 2010
CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE SIZE 2048M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
Fri Dec 17 13:55:30 2010
Completed: CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE SIZE 2048M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE SIZE 1024M AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT  AUTO
Fri Dec 17 13:55:46 2010
Completed: CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE SIZE 1024M AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGE
MENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO
ALTER DATABASE DEFAULT TABLESPACE "USERS"
Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
Fri Dec 17 14:04:28 2010
Create Relation IPS_PACKAGE_UNPACK_HISTORY
Fri Dec 17 14:21:34 2010
Errors in file /data/oracle/diag/rdbms/testrac/testrac1/trace/testrac1_ora_11048.trc  (incident=2601):
ORA-04030: 在嘗試分配 4088 位元組 (PLS CGA hp,pdz2M87_Allocate_Permanent) 時程式記憶體不足
Incident details in: /data/oracle/diag/rdbms/testrac/testrac1/incident/incdir_2601/testrac1_ora_11048_i2601.trc
Fri Dec 17 14:21:45 2010
Errors in file /data/oracle/diag/rdbms/testrac/testrac1/trace/testrac1_ora_11048.trc  (incident=2602):
ORA-04030: 在嘗試分配 16360 位元組 (callheap,kdbmal allocation) 時程式記憶體不足
ORA-04030: 在嘗試分配 4088 位元組 (PLS CGA hp,pdz2M87_Allocate_Permanent) 時程式記憶體不足
Incident details in: /data/oracle/diag/rdbms/testrac/testrac1/incident/incdir_2602/testrac1_ora_11048_i2602.trc
Fri Dec 17 14:21:54 2010
Trace dumping is performing id=[cdmp_20101217142154]
Fri Dec 17 14:21:58 2010
Errors in file /data/oracle/diag/rdbms/testrac/testrac1/trace/testrac1_ora_11048.trc  (incident=2603):
ORA-04030: 在嘗試分配 16360 位元組 (callheap,kcbtmal allocation) 時程式記憶體不足
Incident details in: /data/oracle/diag/rdbms/testrac/testrac1/incident/incdir_2603/testrac1_ora_11048_i2603.trc
Errors in file /data/oracle/diag/rdbms/testrac/testrac1/trace/testrac1_ora_11048.trc  (incident=2604):
ORA-04030: 在嘗試分配 16360 位元組 (callheap,kdbmal allocation) 時程式記憶體不足
ORA-04030: 在嘗試分配 16360 位元組 (callheap,kcbtmal allocation) 時程式記憶體不足
Incident details in: /data/oracle/diag/rdbms/testrac/testrac1/incident/incdir_2604/testrac1_ora_11048_i2604.trc
Fri Dec 17 14:22:10 2010
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xFFFFFFFF7FFF5F60] [PC:0x10777F280, sdbgrfcfp_convert_fileloc_pathfil
e()+64] [flags: 0x0, count: 1]
Errors in file /data/oracle/diag/rdbms/testrac/testrac1/trace/testrac1_psp0_8388.trc  (incident=2465):
ORA-07445: 出現異常錯誤: 核心轉儲 [sdbgrfcfp_convert_fileloc_pathfile()+64] [SIGSEGV] [ADDR:0xFFFFFFFF7FFF5F60] [PC:0x10777F280] [Ad
dress not mapped to object] []
Incident details in: /data/oracle/diag/rdbms/testrac/testrac1/incident/incdir_2465/testrac1_psp0_8388_i2465.trc
Fri Dec 17 14:22:20 2010
Doing block recovery for file 3 block 552
Resuming block recovery (PMON) for file 3 block 552
Errors in file /data/oracle/diag/rdbms/testrac/testrac1/trace/testrac1_ora_11048.trc  (incident=2605):
ORA-04030: 在嘗試分配 8392728 位元組 (pga heap,redo read buffer) 時程式記憶體不足
ORA-04030: 在嘗試分配 16360 位元組 (callheap,kcbtmal allocation) 時程式記憶體不足
Incident details in: /data/oracle/diag/rdbms/testrac/testrac1/incident/incdir_2605/testrac1_ora_11048_i2605.trc
Fri Dec 17 14:22:30 2010
PMON (ospid: 8367): terminating the instance due to error 490
Fri Dec 17 14:22:34 2010
Doing block recovery for file 3 block 552
Resuming block recovery (PMON) for file 3 block 552
Block recovery from logseq 1, block 762981 to scn 183875
Fri Dec 17 14:22:40 2010
Instance terminated by PMON, pid = 8367

後臺出現了大量的ORA-4030錯誤，這說明共享池空間不足，可是現在利用的是MEMORY_TARGET方式，Oracle會自動平衡SGA、PGA以及SGA內部的記憶體分配，如果對於共享池記憶體要求比較大，理論上講Oracle會減少DB_BLOCK_SIZE以及PGA的大小，來滿足SHARED_POOL的需求。除非總體分配的記憶體真的不夠用。而前面分配2G，報錯系統記憶體不夠，現在分配1.5G，Oracle又報錯記憶體不夠，難道就沒有辦法在4G的環境中安裝資料庫嗎。

暫時不增加記憶體總體容量，而是採用9i中手工分配各個池的大小，儘量壓縮不必要的池，給出共享池足夠的空間，避免Oracle自己管理記憶體的損耗。

重啟資料庫記憶體引數配置如下：

System parameters with non-default values:
  processes                = 300
  sessions                 = 472
  shared_pool_size         = 752M
  large_pool_size          = 32M
  java_pool_size           = 48M
  nls_language             = "SIMPLIFIED CHINESE"
  nls_territory            = "CHINA"
  db_block_size            = 16384
  db_cache_size            = 480M
  compatible               = "11.2.0.0.0"
  log_archive_dest_1       = "LOCATION=+DATA"
  log_archive_format       = "%t_%s_%r.dbf"
  db_create_file_dest      = "+DATA"
  db_recovery_file_dest    = "+DATA"
  db_recovery_file_dest_size= 64G
  undo_tablespace          = "UNDOTBS1"
  instance_number          = 1
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=testracXDB)"
  remote_listener          = ""
  audit_file_dest          = "/data/oracle/admin/testrac/adump"
  audit_trail              = "DB"
  db_name                  = "testrac"
  open_cursors             = 300
  pga_aggregate_target     = 256M
  diagnostic_dest          = "/data/oracle"

這次其實總的配置記憶體比上一次MEMORY_TARGET還少，但是由於合理的固定了各個池的大小，因此整個資料庫建立過程沒有再出現任何的錯誤。