最近在已有資料庫伺服器上又新建了4個資料庫例項，目前一共有9個資料庫例項。環境搭好以後做檢查的時候。根據反饋，有個job報如下的錯誤。

Failed to create connection for url=jdbc:oracle:thin:@xxxxx and dbUser=xxxxxx
Nested Exception/Error:
java.sql.SQLRecoverableException: IO Error: Got minus one from a read call
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
        at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:536)
        at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:228)

這個問題，根據以往的經驗就是process的值太低了。需要調大一些。檢視資料庫process的值，一看還真是，當時只設定了150，這個job平均要佔用100多個session,其他的一些環境加進來，肯定不夠。

因為調高process需要重啟資料庫，和他們協調了下，就開始操作了。
我先把process調到3000，session也相應調整，然後儲存在spfile裡面。
alter system set processes=3000 scope=spfile;

然後就按照計劃開始停庫，一個一個重啟。
前面的3個很快就做完了，然後到最後一個，還是小心的按照nomount,mount的一步一步來。結果還是出問題了。
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
SQL> exit

根據錯誤資訊，是空間問題。但是檢視的時候，空間還多著呢。
/dev/vx/dsk/vgusgsPT301/lvol6
                       20G 8.5G   11G 44% /opt/app/oracle/db1
/dev/vx/dsk/vgusgsPT401/lvol6
                       20G 8.5G   11G 44% /opt/app/oracle/db2
/dev/vx/dsk/vgusgsPT501/lvol6
                       20G   11G 8.7G 56% /opt/app/oracle/db3
/dev/vx/dsk/vgusgsPT601/lvol6
                       20G   11G 8.7G 56% /opt/app/oracle/db4

用top檢視空餘記憶體，也還有不少。
Tasks: 1000 total,   1 running, 999 sleeping,   0 stopped,   0 zombie
Cpu(s): 0.3%us, 0.1%sy, 0.0%ni, 99.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 189675188k total, 183821096k used, 5854092k free,   678412k buffers
Swap: 376009348k total, 1892592k used, 374116756k free, 176567228k cached

開始排查是否是kernel引起的。
> /sbin/sysctl -a | grep sem
error: permission denied on key 'kernel.cad_pid'
error: permission denied on key 'kernel.cap-bound'
kernel.sem = 256        32000   100     500

檢視sysctl.conf
> less /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 = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296

### DBA REQUEST FOR ORACLE11 ###
kernel.shmmni = 4096
kernel.sem = 256 32000 100 500
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_max = 4194394
net.core.wmem_max = 1048576
net.core.rmem_default = 262144
net.core.wmem_default = 262144
fs.aio-max-nr = 3145728

但是檢視當前的程式數，也才不到10000，離32000還遠呢。
檢視了一下其他的庫的process情況，終於明白了。
原有的5臺，有4臺process都是5000，一臺是3000,一共就是23000
新加的4臺，process都是3000，一共就是12000
加起來還真是超了。也難怪新加的第3個資料庫例項沒有問題，23000+9000=32000，剛好佔完了，一點都沒剩下。

和部門協調了一下，原有的5臺process平時測試用不了那麼多，3000多process已經足夠了。
就調整了一下，再次重啟就沒有問題了。

> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 16 15:05:37 2014

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.1742E+10 bytes
Fixed Size                  2251264 bytes
Variable Size            5234492928 bytes
Database Buffers         6492782592 bytes
Redo Buffers               12193792 bytes

資料庫shutdown之後無法啟動的問題

相關文章