利用USE_INDIRECT_DATA_BUFFERS突破32位的2G内存限制

最新推荐文章于 2021-04-03 20:13:54 发布

lanmh

最新推荐文章于 2021-04-03 20:13:54 发布

阅读量433

点赞数

分类专栏： Oracle 文章标签： Oracle SQL Linux Cache SSH

本文链接：https://blog.csdn.net/lanmh/article/details/83808818

版权

Oracle 专栏收录该内容

145 篇文章 0 订阅

订阅专栏

对于绝大部分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

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.

最早碰到的错误是由于设置了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