SGA與共享記憶體2

[@more@]

以下是個案例：shmmax設定不當導致dcba無法建立資料庫.

問題現象：在dbca建庫的時候報錯：ORA-03113: end-of-file on communication channel，需要說明的是，基本上是在建立例項的時候。

於是我想到會不會是jdk的問題呢？於是我就透過命令列來做了下：

SQL> startup nomount pfile="/home/oracle/orabase/admin/test/scripts/init.ora";

ORA-03113: end-of-file on communication channel

SQL> exit

Disconnected

可惜結果還是如此，看來不是這個問題。繼續排查吧。

還好基礎知識還是有一些，想到例項都起不來，例項就只包括記憶體和後臺程式啊。於是我就再次嘗試連線到sqlplus看看：

[oracle@IBMPOWER6 ~]$ sqlplus /"as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 6 10:10:37 2009

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

Connected.

SQL> exit

Disconnected

細心的讀者可能會發現問題的，這裡連線是發生在上的startup nomount之後的第一次連線，上面提示3113後我就退出了，再連線時發現是Connected.而不是idle instance。呵呵，於是就想看看後臺程式起來沒有：

[oracle@IBMPOWER6 ~]$ ps ef|grep ora_

16818 pts/4 S+ 0:00 _ grep ora_ HOSTNAME=IBMPOWER6 SHELL=/bin/bash TERM=vt100 HISTSIZE=1000 TMPDIR=/tmp NLS_LANG=AMERICAN_AMERICA.US7ASCII USER=oracle TEMP=/tmp LD_LIBRARY_PATH=/home/oracle/orahome/lib:/lib:/usr/lib:/usr/openwin/lib:/usr/td/lib:/usr/ucblib:/usr/local/lib LS_COLORS=no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=01;32:*.cmd=01;32:*.exe=01;32:*.com=01;32:*.btm=01;32:*.bat=01;32:*.sh=01;32:*.csh=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tz=01;31:*.rpm=01;31:*.cpio=01;31:*.jpg=01;35:*.gif=01;35:*.bmp=01;35:*.xbm=01;35:*.xpm=01;35:*.png=01;35:*.tif=01;35: ORACLE_SID=test ORACLE_BASE=/home/oracle/orabase MAIL=/var/spool/mail/oracle PATH=/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/opt/local/bin:/opt/NSCPnav/bin:/home/oracle/orahome/bin:/usr/local/samba/bin:/usr/ucb: INPUTRC=/etc/inputrc PWD=/home/oracle LANG=en_US ORACLE_TERM=xterm SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass SHLVL=1 HOME=/home/oracle LOGNAME=oracle CVS_RSH=ssh LESSOPEN=|/usr/bin/lesspipe.sh %s ORA_NLS33=/home/oracle/orahome/ocommon/nls/admin/data ORACLE_HOME=/home/oracle/orahome G_BROKEN_FILENAMES=1 _=/bin/grep

沒有瞅到想象中的那些熟悉的面孔，此時讀者們，會如何繼續下一步呢？反正本人是執行了以下命令：

[oracle@IBMPOWER6 ~]$ ipcs -a

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status

0x00000000 622593 oracle 640 16777216 0

0x00000000 655362 oracle 640 2147483648 0

0x5fd96188 688131 oracle 640 251658240 0

------ Semaphore Arrays --------

key semid owner perms nsems

------ Message Queues --------

key msqid owner perms used-bytes messages

比較怪異啊，oracle有三個共享記憶體短，但是卻沒有semaphore arrays和message queues。於是立即想到一個作業系統配置檔案sysctl.conf：

[oracle@IBMPOWER6 ~]$ more /etc/sysctl.conf

# Kernel sysctl configuration file for Red Hat Linux

#

# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and

# sysctl.conf(5) for more details.

# Controls IP packet forwarding

net.ipv4.ip_forward = 0

# Controls source route verification

net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing

net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel

kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename

# Useful for debugging multi-threaded applications

kernel.core_uses_pid = 1

# Controls the use of TCP syncookies

net.ipv4.tcp_syncookies = 1

# Controls the maximum size of a message, in bytes

kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue

kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes

kernel.shmmax = 137438953472

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 536870912

kernel.shmall = 2097152

kernel.shmmax = 2147483648

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

細心的讀者可能會發現一些問題的，有些引數設定了兩遍。於是根據ipcs的結果修改了sysctl.conf檔案為以下：

[oracle@IBMPOWER6 ~]$ more /etc/sysctl.conf

# Kernel sysctl configuration file for Red Hat Linux

#

# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and

# sysctl.conf(5) for more details.

# Controls IP packet forwarding

net.ipv4.ip_forward = 0

# Controls source route verification

net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing

net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel

kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename

# Useful for debugging multi-threaded applications

kernel.core_uses_pid = 1

# Controls the use of TCP syncookies

net.ipv4.tcp_syncookies = 1

# Controls the maximum size of a message, in bytes

kernel.msgmnb = 65536 < BR># Controls the default maxmimum size of a mesage queue

kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes

kernel.shmmax = 137438953472

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 536870912

#kernel.shmall = 2097152

#kernel.shmmax = 2147483648

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

然後重新建庫，一路平安。沒有什麼事情，喝著coffee在旁邊等著就行了。

這個案例和大家分享的目的有幾點：

1、以前只知道shmmax引數設定不當對oracle效能有非常大的影響，倒是不影響例項的啟動。所以任何事都有可能，還是要靠一步步分析。

2、任何問題的解決還是依靠分析，逐步排除，一一檢查過關，最後解決問題。

3、本案例中之所以shmmax引數設定兩次，是因為每次工程師都是直接複製到該檔案中，而沒有進行必要的檢查。而且在建庫的時候如果不修改記憶體引數的話，預設是實體記憶體的40%（本案例中3.2G），所以本文第一次配置的shmmax為一般的2G是不夠的，這樣就產生了多個共享記憶體段。導致例項無法啟動，這樣就無法建庫。

這個案例說明的是“產生了多個共享記憶體段。導致例項無法啟動，這樣就無法建庫。”個人覺得這句話值得懷疑，按照oracle的說法只是效能下降而已不會是例項無法啟動。不過在該案例操作的情況來看是記憶體無法分配。