Oracle启动报错ORA-27102解决

Alfred Zhao

发布于 2019-05-24 20:37:16

1.5K0

发布于 2019-05-24 20:37:16

文章被收录于专栏：从ORACLE起航，领略精彩的IT技术。从ORACLE起航，领略精彩的IT技术。

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

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

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值：