代码改变世界

Oracle启动报错ORA-27102解决

2015-08-04 11:04  AlfredZhao  阅读(16214)  评论(0编辑  收藏  举报

环境:RHEL5.5 + Oracle 10.2.0.4
此错误一般是因为数据库的初始化参数文件的内存设置不当导致。本例是因为操作系统参数设置问题导致。

  1. 当前现象:Oracle启动报错ORA-27102
  2. 检查各参数的配置情况
  3. 定位解决问题
  4. 延伸总结

1.当前现象:Oracle启动报错ORA-27102

[oracle@JYDB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on 星期四 7月 30 19:55:10 2015

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

已连接到空闲例程。

SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

2.检查各参数的配置情况

## 2.1 根据当前的spfile文件创建pfile文件 ##
SQL> create pfile='/tmp/pfile0730.bak' from spfile;

文件已创建。

查看当前的初始化参数配置信息:

[oracle@JYDB1 dbs]$ more /tmp/pfile0730.bak 
JYZHAO.__db_cache_size=10938744832
JYZHAO.__java_pool_size=117440512
JYZHAO.__large_pool_size=16777216
JYZHAO.__shared_pool_size=1442840576
JYZHAO.__streams_pool_size=33554432
*.audit_file_dest='/opt/app/oracle//admin/JYZHAO/adump'
*.background_dump_dest='/opt/app/oracle//admin/JYZHAO/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/usr3/oradata1/sysdata/control_file/control01.ctl','/usr3/oradata1/sysdata/control_file/control02.ctl','
/usr3/oradata1/sysdata/control_file/control03.ctl'
*.core_dump_dest='/opt/app/oracle//admin/JYZHAO/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_files=2048
*.db_name='JYZHAO'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JYZHAOXDB)'
*.job_queue_processes=10
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=1572864000
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.sessions=665
*.sga_target=12582912000
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/app/oracle//admin/JYZHAO/udump'

可以看到,sga=12G,pga=1.5G,

2.2 用pfile文件启动得到相同报错

startup pfile='/tmp/pfile0730.bak'

SQL> startup pfile='/tmp/pfile0730.bak'
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
SQL> !

2.3 检查主机的内存,/dev/shm,ipcs

2.3.1 内存空闲充足

[oracle@JYDB1 10.2.0]$ free -g
             total       used       free     shared    buffers     cached
Mem:            31          0         30          0          0          0
-/+ buffers/cache:          0         31
Swap:           31          0         31

2.3.2 /dev/shm设置为16G,符合当前需求

[oracle@JYDB1 10.2.0]$ df -h /dev/shm
文件系统              容量  已用 可用 已用% 挂载点
tmpfs                  16G     0   16G   0% /dev/shm

2.3.3 ipcs -a也没有未释放的共享内存

[oracle@JYDB1 10.2.0]$ ipcs -a

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status                    

------ Semaphore Arrays --------
key        semid      owner      perms      nsems          

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages    

查到这里我们发现系统的硬件完全可以支持sga=12G,pga=1.5G的配置。
但此时实验了下,大概是只能以sga=6.5G的大小启动数据库。sga再大都会报错:ORA-27102。

2.4 检查系统配置文件/etc/sysctl.conf

more /etc/sysctl.conf

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 25769803776
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

可以看到是按照11g 安装文档建议配置项配置的,其中kernel.shmmax是根据主机内存的75%计算来的。其他参数没有改变。
因为这里的环境是Oracle 10g,所以我们还是按照10g官档的建议,修改为10g版本的安装文档配置项:

kernel.shmall = 2097152
kernel.shmmax = 25769803776
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

sysctl -p 生效配置后,此时尝试启动数据库,结果很不幸,依旧报错ORA-27102。

3.定位解决问题

Google了一下,发现还有可能是kernel.shmall这个参数导致的。 查看当前的shmmax和shmall配置
[root@JYDB1 ~]# cat /proc/sys/kernel/shmmax
25769803776
[root@JYDB1 ~]# cat /proc/sys/kernel/shmall
2097152

注释掉新增的kernel.shmall参数的配置(原/etc/sysctl.conf中有kernel.shmall的配置值为4294967296)

#kernel.shmall = 2097152
kernel.shmmax = 25769803776
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

再次查看当前的shmmax和shmall配置

[root@JYDB1 ~]# cat /proc/sys/kernel/shmmax
25769803776
[root@JYDB1 ~]# cat /proc/sys/kernel/shmall
4294967296

此时重启数据库就可以sga=12G,pga=1.5G正常启动,那么之前的报错就是因为kernel.shmall参数的配置项过小导致了。

4.延伸总结

Oracle官档中,无论是10g还是11g,kernel.shmall参数的配置建议就是kernel.shmall = 2097152,那么在这里的环境有什么问题呢?

下面我们来弄清楚kernel.shmall这个参数的意义

[root@JYDB1 ~]# cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 5.5 (Tikanga)

在RedHat 5的文档中,关于kernel.shmall这个参数有这样一段描述:

7.3. Setting SHMALL Parameter
This parameter sets the total amount of shared memory pages that can be used system wide. Hence, SHMALL should always be at least ceil(shmmax/PAGE_SIZE).
The default size for SHMALL in Red Hat Enterprise Linux 2.1, 3, 4 and 5 is 2097152 which is also Oracle's recommended minimum setting for 9i and 10g on x86 and x86-64 platforms. In most cases this setting should be sufficient since it means that the total amount of shared memory available on the system is 2097152*4096 bytes (shmall*PAGE_SIZE) which is 8 GB. PAGE_SIZE is usually 4096 bytes unless you use Chapter 14, Large Memory Optimization, Big Pages, and Huge Pages which supports the configuration of larger memory pages.

查看系统默认的PAGE_SIZE值:

[root@JYDB1 ~]# getconf PAGESIZE
4096

计算shmmax/PAGE_SIZE的值shmmax/PAGE_SIZE=25769803776/4096=6291456,
这样问题根本原因找到了,在当前环境,kernel.shmall的值至少应该被设置为6291456。

总结:Oracle部署过程中,还要对建议的系统参数值有一些深入的了解。有些官档建议的参数可能不符合实际情况,这时候可以结合系统主机的文档来综合定位问题。