使用ORACLE10G VLM特性 使32位ORACLE突破1.7G SGA限制

最近公司在中心机房新增一台数据库用来存储解析前的原始数据使用，由于对该数据的安全性和实用性要求不高故采用了浪潮的英信系列服务器作为数据库服务器。考虑到数据库的性能，公司给该服务器配置了4G内存，由于一直听说在32位及其上ORACLE有SGA不能超过1.7G的限制。所以查找相关文档解决该问题，以下是解决办法。 

步骤 
1、启动数据库，查看原有主要参数配置情况： 
为了不更改原有的初始化参数文件，在本次实验中将初始化参数文件导出，更改导出的参数文件的配置，在启动数据库时以导出的参数文件启动。 
启动sqlplus，导出初始化参数文件： 
SQL>create pfile from spfile 
在本次实验中，导出的参数文件为/oracle/initora.ora，打开参数文件，可以看到主要的参数设置情况： 
db_block_size=8192 
db_file_multiblock_read_count=16 
sga_target=1228931072 
pga_aggregate_target=209715200 
db_cache_size=905969664 
java_pool_size=16777216 
large_pool_size=16777216 
shared_pool_size=838860800 
sort_area_size=1048576 
2、配置linux 
（1）添加如下内容到/etc/rc.local： 
umount /dev/shm 
mount -t ramfs ramfs /dev/shm 
chown oracle:dba /dev/shm 
（2）增加“max locked memory”ulimit 
为了适应VLM 窗口大小（默认为512M）需要调整Oracle锁定的内存地址空间的大小（在默认情况下为4k）在本次实验中将Oracle锁定的内存设置为接近4G，添加如下内容到/etc/security/limits.conf： 
oracle soft memlock 4194303 
oracle hard memlock 4194303 
注：在32位的Oracle中，锁定内存的大小不能超过4G。 
（3）切换到Oracle用户，输入如下命令，使第（2）步的设置生效： 
$ ulimit 
（4）输入“ulimit -l”可以查看当前设置的结果 
3、配置Oracle 
（1）修改initZBDB.ora文件中如下参数设置，如果参数文件中没有，则添加下述参数设置。 
use_indirect_data_buffers=true 
db_block_buffers＝256000（本次实验计划将SGA区调整到2G左右，由于db_block_size=8192，所以设置db_block_buffers为256000） 
将db_cache_size参数设置注释，使用VLM技术增大SGA到2.6G时db_cache_size参数将失效，必须使用db_block_buffers参数。 
# db_cache_size=905969664 
将sga_target参数注释： 
# sga_target=1228931072 
（2）以initZBDB.ora参数文件启动并关闭数据库，查看参数设置是否生效： 
SQL>startup pfile=’/oracle/initorig.ora’ 
显示内容如下： 
Total System Global Area 2986344448 bytes 
Fixed Size                   1219784 bytes 
Variable Size              872416056 bytes 
Database Buffers          2097152000 bytes 
Redo Buffers                15556608 bytes 
4、配置hugetlb 
（1）确保shmmax参数已经设置的足够大，在实验中，在安装数据库时，已经将shmmax参数设置为4G－1，可以通过如下命令查看shmmax参数设置： 
# sbin/sysctl –a | grep shm 
可以通过如下命令更改其大小： 
# vi /etc/sysctl.conf 
执行下述命令，使得更改生效： 
# sbin/sysctl –p 
（2）执行下述命令查看当前共享内存段的大小 
#ipcs 
显示内容如下： 
------ Shared Memory Segments -------- 
key shmid owner perms bytes nattch status 
0x00000000 1572864 oracle 660 337641472 16 
0x154b1f50 1605633 oracle 660 4096 0 

------ Semaphore Arrays -------- 
key semid owner perms nsems 
0x8eded478 1933312 oracle 660 104 

------ Message Queues -------- 
key msqid owner perms used-bytes messages 
（3）关闭oracle 
（4）将337641472 bytes转换为MB为单位，约等于338 MB 
（5）在这个数值的基础上加一个小的数，比如加4，结果为342MB 
（6）将这个数值写入到/proc/sys/vm/hugetlb_pool文件中： 
echo 342 >/proc/sys/vm/hugetlb_pool 
注：可以将上述命令添加到rc.local文件中，这样每次系统启动后都可立即获取内存。 
（7）启动Oracle 
SQL>startup pfile=’/opt/oracle/product/10.1.0/db_1/dbs/initZBDB.ora’ 
（8）执行命令：＃. cat /proc/meminfo，在最下方，可以看到显示的内容如下： 
HugePages_Total:171 
HugePages_Free: 171 

Hugepagesize: 2048 

对于绝大部分32位系统上的32位数据库，内存最大的设置都不能超过2G，有的系统最大值甚至不能超过1.7G左右。

不过有的系统可以利用USE_INDIRECT_DATA_BUFFERS参数来突破这个限制。

这篇文章介绍如何设置这个参数。

 

当前的操作系统是Rethat Enterprise Linux 4 32位：

[oracle@zjyy ~]$ uname -a
Linux zjyy 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686 i686 i386 GNU/Linux
[oracle@zjyy ~]$ more /etc/issue
Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
Kernel r on an m

而安装的数据库是32位的Oracle10.2.0.1：

[oracle@zjyy ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 23 10:24:29 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

通过设置USE_INDIRECT_DATA_BUFFERS参数，可以使得SGA超过3G的大小：

SQL> show sga

Total System Global Area 3288334336 bytes
Fixed Size 1217836 bytes
Variable Size 322439892 bytes
Database Buffers 2949120000 bytes
Redo Buffers 15556608 bytes
SQL> show parameter use_indirect_data_buffers

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
use_indirect_data_buffers boolean TRUE

使用这个参数并不是简单的将其置为TRUE就可以了，如果只是修改参数并重启数据库，会碰到各种的错误。下一篇文章中会专门介绍在使用这个参数的时候碰到的错误。

[oracle@zjyy bdump]$ su - 
Password: 
[root@zjyy ~]# umount /dev/shm
[root@zjyy ~]# mount -t ramfs ramfs /dev/shm
[root@zjyy ~]# chown oracle:oinstall /dev/shm
[root@zjyy ~]# vi /etc/rc.local

#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local
umount /dev/shm
mount -t ramfs ramfs /dev/shm
chown oracle:oinstall /dev/shm
~
"/etc/rc.local" 10L, 298C written 
[root@zjyy ~]# reboot

Broadcast message from root (pts/4) (Wed Sep 23 07:52:56 2009):

The system is going down for reboot NOW!

首先切换到root用户，执行umount /dev/shm操作，然后执行mount –t ramfs ramfs /dev/shm命令，并给Oracle用户授权。

为了确保这个操作可以在系统重启后生效，修改/etc/rc.local，将上面的命令添加进去，然后重启系统。

检查系统的配置：

[root@zjyy ~]# mount | grep shm
ramfs on /dev/shm type ramfs (rw)
[root@zjyy ~]# ls -ld /dev/shm
drwxr-xr-x 2 oracle oinstall 0 Sep 23 07:55 /dev/shm

下面修改系统配置，在/etc/security/limits.conf文件，添加Oracle用户所允许的内存限制：

* soft memlock 3145728
* hard memlock 3145728

修改/etc/init.d/sshd文件，在脚本中的start部分，添加ulimit –l命令：

start()
{
# Create keys if necessary
do_rsa1_keygen
do_rsa_keygen
do_dsa_keygen

ulimit -l
echo -n $"Starting $prog:"
initlog -c "$SSHD $OPTIONS" && success || failure
RETVAL=$?
[ "$RETVAL" = 0 ] && touch /var/lock/subsys/sshd
echo
}

下面修改/etc/ssh/sshd_config文件，在文件的最后添加下面的配置：

UseLogin yes

在操作系统级的设置完成，下面切换到Oracle用户检查设置是否生效：

[root@zjyy ~]# su - oracle
[oracle@zjyy ~]$ ulimit -l
3145728

如果Oracle用户执行ulimit –l的结果不是前面设置的3145728，那么说明前面的设置有问题，需要根据上面的步骤重新设置。

下面设置正确的Oracle数据库初始化参数，就可以启动数据库了：

SQL> host vi initcis.ora

*.audit_file_dest='/data/ora10g/admin/cis/adump'
*.background_dump_dest='/data/ora10g/admin/cis/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/webdata/oracle/oradata/cis/control01.ctl','/webdata/oracle/oradata/cis/control02.ctl','/webdata/oracle/oradata/cis/control03.ctl'
*.core_dump_dest='/data/ora10g/admin/cis/cdump'
*.db_block_size=16384
#*.db_cache_size=2202009600
db_block_buffers=180000
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='cis'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cisXDB)'
*.java_pool_size=0
*.job_queue_processes=10
*.large_pool_size=0
*.open_cursors=300
*.pga_aggregate_target=1048576000
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=0
*.sga_target=0
*.shared_pool_size=314572800
*.streams_pool_size=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/data/ora10g/admin/cis/udump'
use_indirect_data_buffers=true
~
~
"initcis.ora" 27L, 902C written

SQL> startup pfile=initcis.ora
ORACLE instance started.

Total System Global Area 3288334336 bytes
Fixed Size 1217836 bytes
Variable Size 322439892 bytes
Database Buffers 2949120000 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.

需要注意，使用USE_INDIRECT_DATA_BUFFERS参数，不能在使用其他9i以后新增的内存控制参数了，比如SGA_TARGET、DB_CACHE_SIZE等等，必须通过DB_BLOCK_BUFFERS参数来指定内存的容量。

SQL> show parameter db_block

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 180000
db_block_checking string FALSE
db_block_checksum string TRUE
db_block_size integer 16384
SQL> select 180000*16384 from dual;

180000*16384
------------
2949120000

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3136M
sga_target big integer 0
SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0

而对于没有使用这个参数的情况，将SGA设置到2.5G，也就是DB_CACHE_SIZE的大小超过1.7G，则Oracle启动报错：

SQL> startup
ORACLE instance started.

Total System Global Area 2214592512 bytes
Fixed Size 1220532 bytes
Variable Size 486539340 bytes
Database Buffers 1711276032 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.
SQL> show parameter use_ind

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
use_indirect_data_buffers boolean FALSE
SQL> alter system set sga_target = 2500m scope = spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27102: out of memory
Linux Error: 12: Cannot allocate memory
Additional information: 1
Additional information: 884739

最早碰到的错误是由于设置了SGA_TARGET，导致了启动时报错：

SQL> create pfile='/home/oracle/initcis.ora' 
2 from spfile='/data/ora10g/product/10.2.0/db_1/dbs/spfilecis.ora';

File created.

SQL> host echo "use_indirect_data_buffers=true" >> /home/oracle/initcis.ora

SQL> host more /home/oracle/initcis.ora
cis.__db_cache_size=1711276032
cis.__java_pool_size=16777216
cis.__large_pool_size=16777216
cis.__shared_pool_size=452984832
cis.__streams_pool_size=0
*.audit_file_dest='/data/ora10g/admin/cis/adump'
*.background_dump_dest='/data/ora10g/admin/cis/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/webdata/oracle/oradata/cis/control01.ctl','/webdata/oracle/oradata/cis/control02.ctl','/webdata/oracle/oradata/cis
/control03.ctl'
*.core_dump_dest='/data/ora10g/admin/cis/cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='cis'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cisXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1048576000
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2621440000
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/data/ora10g/admin/cis/udump'
use_indirect_data_buffers=true

SQL> startup pfile=/home/oracle/initcis.ora
ORA-00385: cannot enable Very Large Memory with new buffer cache parameters

查询ORA-385错误，发现Oracle的错误文档上描述的十分清晰：

ORA-00385: cannot enable Very Large Memory with new buffer cache parameters 
Cause: User specified one or more of { db_cache_size , db_recycle_cache_size, db_keep_cache_size, db_nk_cache_size (where n is one of 2,4,8,16,32) } AND use_indirect_data_buffers is set to TRUE. This is illegal.
Action: Very Large Memory can only be enabled with the old (pre-Oracle_8.2) parameters.

连DB_CACHE_SIZE等9i引入的内存参数都不支持的话，就更不要说10G的SGA_TARGET了，尝试去掉SGA_TARGET和DB_CACHE_SIZE参数：

SQL> alter system set sga_target = 0 scope = spfile;

System altered.

SQL> alter system set shared_pool_size = 300m scope = spfile;

System altered.

SQL> alter system set java_pool_size = 0 scope = spfile;

System altered.

SQL> alter system set large_pool_size = 0 scope = spfile;

System altered.

SQL> alter system set streams_pool_size = 0 scope = spfile;

System altered.

SQL> alter system set db_cache_size = 1400m scope = spfile;

System altered.

SQL> alter system set sga_max_size = 0 scope = spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1811939328 bytes
Fixed Size 1219952 bytes
Variable Size 318767760 bytes
Database Buffers 1476395008 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.
SQL> alter system set db_cache_size = 2000m scope = spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2432696320 bytes
Fixed Size 1220844 bytes
Variable Size 318770964 bytes
Database Buffers 2097152000 bytes
Redo Buffers 15552512 bytes
Database mounted.
Database opened.
SQL> show parameter use_indi

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
use_indirect_data_buffers boolean FALSE
SQL> alter system set db_cache_size = 2100m scope = spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27102: out of memory
Linux Error: 12: Cannot allocate memory
Additional information: 1
Additional information: 1835011
SQL> create pfile='/home/oracle/initcis.ora'
2 from spfile='/data/ora10g/product/10.2.0/db_1/dbs/spfilecis.ora';

File created.

SQL> host echo "use_indirect_data_buffers=true" >> /home/oracle/initcis.ora

SQL> host vi initcis.ora

*.audit_file_dest='/data/ora10g/admin/cis/adump'
*.background_dump_dest='/data/ora10g/admin/cis/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/webdata/oracle/oradata/cis/control01.ctl','/webdata/oracle/oradata/cis/control02.ctl','/webdata/oracle/oradata/cis/control03.ctl'
*.core_dump_dest='/data/ora10g/admin/cis/cdump'
*.db_block_size=16384
#*.db_cache_size=2202009600
db_block_buffers=134400
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='cis'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cisXDB)'
*.java_pool_size=0
*.job_queue_processes=10
*.large_pool_size=0
*.open_cursors=300
*.pga_aggregate_target=1048576000
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=0
*.sga_target=0
*.shared_pool_size=314572800
*.streams_pool_size=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/data/ora10g/admin/cis/udump'
use_indirect_data_buffers=true
~
~
~
"initcis.ora" 27L, 902C written

SQL> startup pfile=initcis.ora
ORA-27103: internal error
Linux Error: 11: Resource temporarily unavailable

构成这个错误的原因就是由于操作系统上面没有进行设置。

这时alert文件中对应的错误信息为：

Wed Sep 23 07:39:47 2009
ERROR: Unable to attach to VLM segment at (nil): window size=0x20000000 size=0x83400000

这时，只需要根据上一篇文章介绍的步骤进行配置，就可以最终启动数据库。