一次ORA-3136的處理

最近收到一個警示，使用者說資料庫無法串連，但是從監控上看，oracle的後台進程已經偵聽進程還是在的，沒有任何的alert。

登入資料庫，已經恢複正常，但是在資料庫的alertlog中發現大量的ora-3136的報錯：

Thu Feb 17 09:07:31 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:31 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:31 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)
Thu Feb 17 09:07:32 2011
WARNING: inbound connection timed out (ORA-3136)

時間大約是在9點開始，到9點07分結束，曆時7分鐘，之後就自動回復了，後續沒有報錯。

而ora-3136的這個報錯，在大部分情況下，我們是可以忽略的，因為這個報錯一般是由於用戶端由於梅雨正確的密碼，連線逾時導致。舉個很簡單的例子，我們用sqlplus user/password@tnsname，但是輸入的密碼是錯誤的，oracle提示：ORA-01017: invalid username/password; logon denied，之後，什麼都別做，串連掛在那裡，等一分鐘之後，就可以在alertlog中看到這個報錯了。

因此，ora-3136報錯的一種可能性是用戶端使用率錯誤的密碼登入，但是之後沒有退出串連。

但是ora-3136的報錯不僅僅是這一種可能，另外還有當收到來自惡意用戶端的串連，如Dos攻擊，另外，還有當資料庫負載比較重的時候，也會有這樣的報錯。具體可見metalink 《Troubleshooting ORA – 3136 WARNING Inbound Connection Timed Out [ID 465043.1]》裡面說的3種可能性：

There can be three main reasons for this error -
 
   1. Server gets a connection request from a malicious client which is not supposed to connect to the database , in which case the error thrown is the correct behavior. You can get the client address for which the error was thrown via sqlnet log file.
   2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.
   3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

根據我的理解，總之，在oracle的偵聽接受到一個來自用戶端的請求，當fork到伺服器處理序的時候，如果在這個過程中發現意外，如密碼錯誤，如資料庫負載太重，都會參數ora-3136的報錯。

由於在alertlog中除了ora-3136之外沒有別的什麼資訊，於是拉了一份故障時間點左右的awr report來看，發現了比較嚴重的問題：
1.shared pool撐的比較大：

2.library cache命中率低：

3.等待事件中library cache的latch嚴重：

4. SQL的綁定變數使用的很糟糕，幾乎沒有綁定變數，某些語句類似的可以找到5000多個，僅僅是查詢條件中的值不同：

SQL> select substr(SQL_TEXT,1,80),count(*) from v$sqlarea group by substr(SQL_TEXT,1,80) order by 2
  2  /
 
SUBSTR(SQL_TEXT,1,80)                                                                COUNT(*)
---------------------------------------------------------------------------------- ----------
......
 SELECT a.costareacode AS costareacode,a.costmethod AS costmethod,a.itemcostpric         2056
 SELECT a.taxgroupcode AS taxgroupcode FROM item a  WHERE 1=1    and a.itemid=10         2233
   select a.sourceorderdetailid ,a.orderid,a.orderdetailid,a.baseqty,a.orderqty,         2307
 SELECT a.creditdays AS creditdays,a.relationid AS relationid,a.creditlevelcode          2329
 Select count(*) as count from orderdetail where 1=1  and orderdetailid =  11047         2629
   select nvl(sum(a.balanceamt-a.allocamt),0) as canuseamt from zmclrebatebalanc         4576
 SELECT a.optionvalue AS optionvalue FROM orgoption a  WHERE 1=1    and a.orgid=         5458
 
597 rows selected.
 
SQL>

ok，到這裡，我們從awrreport中可以暫時的理出一條線索：sql沒有很好的綁定變數->需要大量的library cache記憶體->申請記憶體的時候，可能機器負載高，導致ora3136的報錯。

我們繼續結合系統層面的NMON資料來看系統當時的負載情況：
1.八點半到九點多的那段時間CPU中的IO較高：

2. 八點半到九點多那段時間的hdisk0很忙，幾乎到100%：

由於hdisk0和hdisk1是屬於local disk，hdisk4和hdisk5是san storage。而local disk除了用於本地的一些檔案系統的使用，還有用於swap空間。我們繼續去看page in和page out的情況。

3. 八點半到九點多那段時間有大量的page in：

因此，我們再次可以進一步的推論：由於需要大量的library cache，資料庫向記憶體申請空間，由於空間不夠，或者配置的原因，申請的空間需要向swap空間發生置換，因此發生page in，而在swap空間中的library cache又遠遠比不上在實體記憶體內的效率，且hdisk0的繁忙程度為100%。

綜上，造成上述的故障：SQL沒有很好的綁定變數->需要大量的library cache->申請library cache記憶體的時候，與swap發生置換，page in增高->hdisk0繁忙100%->整體系統負載高->fork伺服器處理序失敗->ora-3136報錯。

因為該機器的實體記憶體有40G，而我們配置的SGA+PGA還不到20G，有這樣大的pipo，我們懷疑是不是有些aix的配置沒有正確，同時我們也希望設定lock_sga的參數，把sga鎖在實體記憶體中。檢查後，果然發現了些問題：

1. AIXTHREAD_SCOPE沒有設定成S：如果使用預設的值P，oracle的進程將會map到核心進程的pool中，當oracle處於一個等待事件時，該進程就會被swap出去，此時oracle進程將會置於到另一個核心進程上。oracle使用進程ID來提交等待的進程，所以保持同一個進程ID很重要。如果將AIXTHREAD_SCOPE設定成S，oracle進程就能靜態map到核心進程，而不會改變進程ID。
2. lru_file_repage 沒有設定成0：用於限制page。告訴VMM，page僅用於檔案型頁面，而不是計算型頁面（sga是計算型頁面）。
3. v_pinshm沒有設定成1。如果該值設定成1，那麼aix的VMM將不會pin住share memory頁面，因此oracle instance將不能用到large page。因此該值也應該設定成1來配合使用lock_sga。

上述問題，在測試機上修改設定後，進行一星期的測試，在生產系統上修改。

http://www.oracleblog.org/working-case/deal-with-ora3136/