?ORACLE會話超時
ORACLE會話超時
自從數據庫遷移后連接數據庫總是提示會話不可用，和客戶溝通才知到他們連接數據庫的程序是從早上連上數據庫后就一直保持連接狀態，一天中需要執行幾次操作，由于數據庫中的會話連接有超時限制，造成客戶端長時間沒有操作，就會斷開連接。
經過這次事件總結了Oracle關于超時退出的一些設置：
1 profile的idle_time限制
2 profile的connect_time限制
3 sqlnet的expire_time限制
4 sqlnet的INBOUND_CONNECT_TIMEOUT_listener_name限制
5 listener的CONNECT_TIMEOUT_listener_name限制
1，profile的idle_time限制
oracle用戶的配置文件的密碼策略是實時生效的，所見即所得。但是用戶配置文件的資源限制是由resource_limit參數決定的。
單位是分鐘，默認沒記錯的話應該是10分鐘
要是修改的資源生效，需要設置resource_limit設置為true
alter system set resource_limit=true ;
如果在idle的時間內用戶沒有執行任何操作，會提示ORA-02396：exceeded maximum idle time, please connect again
2，profile的connect_time限制
默認是UNLIMITED，單位是分鐘
用戶在到達設置的時候后，不可以在繼續操作，會提示ORA-02399: exceeded maximum connect time, you are being logged off
如果是設置1分鐘，他會在將近2分鐘的時候提醒你重新登錄。
3，sqlnet的expire_time限制：
expire_time主要是在指定的時間去探測客戶端是否可以連通，如果可以的話重新計時，否則就會斷開
在執行rman的時候遇見：ORA-03135: connection lost contact，可以嘗試將此值設置大些。(ID 729811.1)
4，sqlnet的inbound_connect_timieout_listener_name限制
單位是秒，默認值是60
Use the INBOUND_CONNECT_TIMEOUT_listener_name parameter to specify the time, in seconds, for the client to complete its connect request to the listener after the network connection had been established.
If the listener does not receive the client request in the time specified, then it terminates the connection. In addition, the listener logs the IP address of the client and an ORA-12525:TNS: listener has not received client's request in time allowed error message to the listener.log file.
To protect both the listener and the database server, Oracle Corporation recommends setting this parameter in combination with theSQLNET.INBOUND_CONNECT_TIMEOUT parameter in the sqlnet.ora file. When specifying values for these parameters, consider the following recommendations:
-
Set both parameters to an initial low value.
-
Set the value of the INBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.
For example, you can set INBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and INBOUND_CONNECT_TIMEOUT parameter to 3 seconds. If clients are unable to complete connections within the specified time due to system or network delays that are normal for the particular environment, then increment the time as needed.
如果客戶端在指定的時間內沒有連接上數據庫，會在listener.log日志中出現 ORA-12525,同時在alert日志中會報錯ora-03136
5，listener的connect_timeout_listener-name限制，很少使用
CONNECT_TIMEOUT_listener_name=number
This parameter sets the number of seconds that the listener waits to get a valid SQL*Net version 2 connection request after a connection has been started. The listener drops the connection if the timeout is reached. Default is 10; if set to 0, it will wait forever.
參數值單位是秒，默認值是10s，如果建立監聽后在設置的時間內沒有反應將會斷開連接。設置為0表示沒有等待時間限制。
