ORACLE ORA-01034 and ORA-27101

AIX: 5300-07
oslevel -r

Oracle: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
select * from v$version

我們有一個ORACLE 資料庫，作業系統是AIX，運行已經有好幾年了。最近發生JBOSS AP在啟動建立資料庫連線的時候報錯，但不全然都會這樣，有些連線建立成功，因此這狀況就變成時有時無。在AP主機重啟時，就會報這錯，但AP還算能登入使用，只是有時候會反應有些功能沒有作業成功而報錯，錯誤訊息依然是以下這個。

WARN  [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null

org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

IBM AIX RISC System/6000 Error: 2: No such file or directory)

get a new connection: null

Could not create connection 

一般來說是無法連到DB主機

ORA-01034, ORA-27101: 網路上很多人說，這是資料庫沒有啟動好，或配置不良，應該是泛指剛剛建立完成的資料庫，但我們的已經運行好幾年了，應該不可能。

剛開始，就是拼命地把AP主機重新啟動，觀察AP主機上的執行環境是否有異常蠕蟲或病毒，甚至想要分散一台AP主機的負載量來測試，但方向都不正確。

觀察DB 主機的使用情況，使用ORACLE 身分登入主機
參考這篇文章 http://intermediatesql.com/aix/how-oracle-uses-memory-on-aix-part-1-processes/

$ ps -elf |

egrep " oracle${ORACLE_SID} | ora_.*_${ORACLE_SID} " |

grep -v egrep | awk '{sum += $10} END {print sum/1024}'

53688.7

$ sysresv

IPC Resources for ORACLE_SID "xxxxxxxx" :

Shared Memory:

ID              KEY

1048577         0x6598f430

Oracle Instance alive for sid "xxxxxxxx"

$ ipcs -bm|grep 1048577

m   1048577 0x6598f430 --rw-r-----   oracle      dba 524296192 <--500mb p="">

總共使用的記憶體: 53 GB + 500 MB =53.5 GB

ps -ef|grep oracle${ORACLE_SID}|grep -v grep |wc -l 

540

select * from v$version
161
從資料庫裡查看SESSION 大約是161
很明顯兩邊的數量差很多，可能是程序已經離開，但ORACLE認為還在用沒放開，導致資源消耗。

關閉所有AP連線行為，將ORACLE DB 重新啟動

shutdown immediate  等待約莫10分鐘，還是沒有shutdown

改用

shutdown abort 等待約莫3分鐘

觀察主機ORACLE 的程序數量

ps -ef|grep oracle${ORACLE_SID}|grep -v grep |wc -l 

0

啟動資料庫

Startup

重新啟動AP主機服務，AP 啟動建立資料庫連線的時候，沒有在報錯

說實在的，AIX上的記憶體還真的是不太會看，以下列一些相關指令，參考看看

$ vmstat -v

              2035712 memory pages

              1957164 lruable pages

                 2663 free pages

                    1 memory pools

               472236 pinned pages

                 80.0 maxpin percentage

                 10.0 minperm percentage

                 20.0 maxperm percentage

                 11.0 numperm percentage

               215325 file pages

                  0.0 compressed percentage

                    0 compressed pages

                 11.0 numclient percentage

                 20.0 maxclient percentage

               215323 client pages

                    0 remote pageouts scheduled

                  483 pending disk I/Os blocked with no pbuf

                60078 paging space I/Os blocked with no psbuf

                 2484 filesystem I/Os blocked with no fsbuf

                    0 client filesystem I/Os blocked with no fsbuf

               211963 external pager filesystem I/Os blocked with no fsbuf

                    0 Virtualized Partition Memory Page Faults

                 0.00 Time resolving virtualized partition memory page faults

$ vmstat

System configuration: lcpu=4 mem=7952MB

kthr    memory              page                            faults                    cpu

-----  ---------------- ------------------------ -----------------  -----------

 r   b   avm          fre      re  pi  po  fr     sr         cy  in  sy      cs    us  sy  id  wa

 1  2   1977424  2671   0   0   0     192 1610   0    76 2486 480  8   1   89  1

$ df -k

Filesystem    1024-blocks      Free %Used    Iused %Iused Mounted on

/dev/hd4           524288    257492   51%     8417    13% /

/dev/hd2          6291456   1376552   79%    95241    24% /usr

/dev/hd9var        524288         0  100%     2921    61% /var

/dev/hd3          1048576    508024   52%     2081     2% /tmp

/dev/fwdump        524288    523536    1%        5     1% /var/adm/ras/platform

/dev/hd1           524288    249000   53%      524     1% /home

/proc                   -         -    -         -     -  /proc

/dev/hd10opt      1048576    532384   50%     8599     7% /opt

/dev/lv00          524288    507776    4%       18     1% /var/adm/csd

/dev/fslv00      10485760   4881856   54%     5167     1% /source

/dev/fslv01      94371840  21525676   78%    98843     3% /oradata

Vi Cheat Sheet

雖然現在都是用vim 比較多，但AIX 上面還是只能用vi,還是記一下操作的設定好了
以下資料摘自於http://www.lagmonster.org/docs/vi.html

Modes
Vi has two modes insertion mode and command mode. The editor begins in command mode, where the cursor movement and text deletion and pasting occur. Insertion mode begins upon entering an insertion or change command. [ESC] returns the editor to command mode (where you can quit, for example by typing :q!). Most commands execute as soon as you type them except for "colon" commands which execute when you press the ruturn key.



Quitting
:x Exit, saving changes
:q Exit as long as there have been no changes
ZZ Exit and save changes if any have been made
:q! Exit and ignore any changes



Inserting Text
i Insert before cursor
I Insert before line
a Append after cursor
A Append after line
o Open a new line after current line
O Open a new line before current line
r Replace one character
R Replace many characters



Motion
h Move left
j Move down
k Move up
l Move right
w Move to next word
W Move to next blank delimited word
b Move to the beginning of the word
B Move to the beginning of blank delimted word
e Move to the end of the word
E Move to the end of Blank delimited word
( Move a sentence back
) Move a sentence forward
{ Move a paragraph back
} Move a paragraph forward
0 Move to the begining of the line
$ Move to the end of the line
1G Move to the first line of the file
G Move to the last line of the file
nG Move to nth line of the file
:n Move to nth line of the file
fc Move forward to c
Fc Move back to c
H Move to top of screen
M Move to middle of screen
L Move to botton of screen
% Move to associated ( ), { }, [ ]



Deleting Text
Almost all deletion commands are performed by typing d followed by a motion. For example, dw deletes a word. A few other deletes are:
x Delete character to the right of cursor
X Delete character to the left of cursor
D Delete to the end of the line
dd Delete current line
:d Delete current line



Yanking Text
Like deletion, almost all yank commands are performed by typing y followed by a motion. For example, y$ yanks to the end of the line. Two other yank commands are:
yy Yank the current line
:y Yank the current line



Changing text
The change command is a deletion command that leaves the editor in insert mode. It is performed by typing c followed by a motion. For wxample cw changes a word. A few other change commands are:
C Change to the end of the line
cc Change the whole line



Putting text
p Put after the position or after the line
P Put before the poition or before the line



Buffers
Named buffers may be specified before any deletion, change, yank or put command. The general prefix has the form "c where c is any lowercase character. for example, "adw deletes a word into buffer a. It may thereafter be put back into text with an appropriate "ap.



Markers
Named markers may be set on any line in a file. Any lower case letter may be a marker name. Markers may also be used as limits for ranges.
mc Set marker c on this line
`c Go to beginning of marker c line.
'c Go to first non-blank character of marker c line.



Search for strings
/string Search forward for string
?string Search back for string
n Search for next instance of string
N Search for previous instance of string



Replace
The search and replace function is accomplished with the :s command. It is commonly used in combination with ranges or the :g command (below).
:s/pattern/string/flags Replace pattern with string according to flags.
g Flag - Replace all occurences of pattern
c Flag - Confirm replaces.
& Repeat last :s command



Regular Expressions
. (dot) Any single character except newline
* zero or more occurances of any character
[...] Any single character specified in the set
[^...] Any single character not specified in the set
^ Anchor - beginning of the line
$ Anchor - end of line
\< Anchor - begining of word
\> Anchor - end of word
\(...\) Grouping - usually used to group conditions
\n Contents of nth grouping

[...] - Set Examples
[A-Z] The SET from Capital A to Capital Z
[a-z] The SET from lowercase a to lowercase z
[0-9] The SET from 0 to 9 (All numerals)
[./=+] The SET containing . (dot), / (slash), =, and +
[-A-F] The SET from Capital A to Capital F and the dash (dashes must be specified first)
[0-9 A-Z] The SET containing all capital letters and digits and a space
[A-Z][a-zA-Z] In the first position, the SET from Capital A to Capital Z
In the second character position, the SET containing all letters

Regular Expression Examples
/Hello/ Matches if the line contains the value Hello
/^TEST$/ Matches if the line contains TEST by itself
/^[a-zA-Z]/ Matches if the line starts with any letter
/^[a-z].*/ Matches if the first character of the line is a-z and there is at least one more of any character following it
/2134$/ Matches if line ends with 2134
/\(21|35\)/ Matches is the line contains 21 or 35
Note the use of ( ) with the pipe symbol to specify the 'or' condition
/[0-9]*/ Matches if there are zero or more numbers in the line
/^[^#]/ Matches if the first character is not a # in the line
Notes:
1. Regular expressions are case sensitive
2. Regular expressions are to be used where pattern is specified


Counts
Nearly every command may be preceded by a number that specifies how many times it is to be performed. For example, 5dw will delete 5 words and 3fe will move the cursor forward to the 3rd occurence of the letter e. Even insertions may be repeated conveniently with thismethod, say to insert the same line 100 times.



Ranges
Ranges may precede most "colon" commands and cause them to be executed on a line or lines. For example :3,7d would delete lines 3-7. Ranges are commonly combined with the :s command to perform a replacement on several lines, as with :.,$s/pattern/string/g to make a replacement from the current line to the end of the file.
:n,m Range - Lines n-m
:. Range - Current line
:$ Range - Last line
:'c Range - Marker c
:% Range - All lines in file
:g/pattern/ Range - All lines that contain pattern



Files
:w file Write to file
:r file Read file in after line
:n Go to next file
:p Go to previos file
:e file Edit file
!!program Replace line with output from program



Other
~ Toggle upp and lower case
J Join lines
. Repeat last text-changing command
u Undo last change
U Undo all changes to line