Oracle 在Linux X86上使用超过2G的SGA
转自(http://cnhtm.itpub.net/post/39970/496153)
有空测试一下!
=================================================
在Linux X86上,SGA最大使用2G内存,如果设置超过2G的SGA,会报如下错误
ORA-27123: unable to attach to shared memory segment |
可以通过使用shared memory file的方式使用超过2G的sga。
下面演示其过程(RedHat as 4+Oracle 10.2.0.1)
1、SGA过大的错误演示
SQL> alter system set sga_target=3G scope=spfile;
System altered.
SQL> startup force ORA-27123: unable to attach to shared memory segment SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options |
2、Mount ramfs 文件系统,并保证可以被oracle用户访问
以下下过程用root用户操作
[root@linux32 ~]# umount /dev/shm [root@linux32 ~]# mount -t ramfs ramfs /dev/shm [root@linux32 ~]# chown oracle:dba /dev/shm |
然后将上面的三个命令加入到/etc/rc.local文件最后,修改后的文件如下所示
[root@linux32 ~]# cat /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:dba /dev/shm |
3、设置shared pool可以使用的内存
编辑/etc/security/limits.conf文件,加入标记为红色的两行
最后数字的计算公式为(假设要使用1g的shared pool,计算公式为 1×1024×1024=1048576),
[root@linux32 ~]# cat /etc/security/limits.conf # /etc/security/limits.conf # ...... #@student - maxlogins 4
# End of file oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536
oracle soft memlock 1048576 oracle hard memlock 1048576 |
可以在另一个终端中重新用oracle用户登录,查看设置是否生效
[oracle@linux32 ~]$ ulimit -l 1048576 |
4、设置SHMMAX参数值
编辑/etc/sysctl.conf文件,按照如下规则设置如下3行
kernel.shmmax = 2147483648 #Linux主机内存的一半,单位为byte,但最大最不能超过4294967295 kernel.shmmni = 4096 #一般固定为4094 kernel.shmall = 2097152 #应该>或= kernel.shmmax/kernel.shmmni |
使用sysctl -p命令使设置生效
[root@linux32 ~]# sysctl -p |
5、修改oracle的pfile文件
以下操作使用oracle用户操作
使用spfile生产pfile文件
[oracle@linux32 dbs]$ strings spfileorcl.ora > init.ora.bak |
编辑init.ora.bak文件,增加标记为红色的三行
*.db_block_size=8192 ...... *.use_indirect_data_buffers=true *.db_block_buffers = 393216 *.shared_pool_size = 452984832 |
db_block_buffers表示db_block_size的大小,如欲使用3g的db_block_size,则公司为:(3×1024×1024/8=393216)(8代表db_block_size为8k)
shared_pool_size表示shared pool的大小,单位为byte,不能超过步骤3设置的内存大小,否则启动时会报告如下错误:
ORA-27102: out of memory Linux Error: 28: No space left on device |
然后将*.sga_max_size和*.sga_target行删掉
6、使用修改好的pfile文件启动
[oracle@linux32 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 20 21:52:40 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='?/dbs/init.ora.bak' ORACLE instance started.
Total System Global Area 3724541952 bytes Fixed Size 1218076 bytes Variable Size 486541796 bytes Database Buffers 3221225472 bytes Redo Buffers 15556608 bytes Database mounted. Database opened. |
生成spfile
SQL> create spfile from pfile='?/dbs/init.ora.bak';
File created. |
使用spfile启动
SQL> startup force; ORACLE instance started.
Total System Global Area 3724541952 bytes Fixed Size 1218076 bytes Variable Size 486541796 bytes Database Buffers 3221225472 bytes Redo Buffers 15556608 bytes Database mounted. Database opened. |
显示sga情况
SQL> show sga
Total System Global Area 3724541952 bytes Fixed Size 1218076 bytes Variable Size 486541796 bytes Database Buffers 3221225472 bytes Redo Buffers 15556608 bytes
SQL> show parameter sga
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 3552M sga_target big integer 0 |
--end--