ORA-03135 connections lost contact Process ID:0

ORA-03135 connections lost contact Process ID:0

情境描述：

領導callme，說pslql遠程登入報錯，報錯資訊：
ORA-03135 connections lost contact Process ID:0

1，趕緊vpn，登入後台去查看負載，看到負載很低：

[Oracle@powerlong4 ~]$ w 20:05:16 up 22 days,  6:33,  3 users,  load average: 0.45, 0.58, 0.38USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHATroot     pts/0    192.168.120.218  19:32   19:30   0.16s  0.03s sqlplus   as sysdbaroot     pts/1    192.168.120.218  19:39    0.00s  0.18s  0.03s wroot     pts/2    :1.0             Fri15    5days  2:23   0.02s -bash[oracle@powerlong4 ~]$ 

2，再去查看alert日誌：

Wed May 27 02:00:00 2015Clearing Resource Manager plan via parameterWed May 27 07:15:39 2015Suspending MMON action 'Block Cleanout Optim, Undo Segment Scan' for 82800 secondsWed May 27 07:35:48 2015Suspending MMON action 'undo usage' for 82800 secondsWed May 27 07:56:08 2015Suspending MMON action 'metrics monitoring' for 82800 secondsWed May 27 08:11:17 2015Suspending MMON slave action kewrmafsa_ for 82800 secondsWed May 27 08:16:23 2015Suspending MMON action 'AWR Auto Purge Task' for 82800 secondsWed May 27 16:56:14 2015Suspending MMON slave action kewfmcpsa_ for 82800 secondsWed May 27 19:32:45 2015***********************************************************************Fatal NI connect error 12170.  VERSION INFORMATION:    TNS for Linux: Version 11.2.0.1.0 - Production    Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production    TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production  Time: 27-MAY-2015 19:32:45  Tracing not turned on.  Tns error struct:    ns main err code: 12535TNS-12535: TNS:operation timed out    ns secondary err code: 12606    nt main err code: 0    nt secondary err code: 0    nt OS err code: 0  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.120.218)(PORT=55693))Wed May 27 19:39:43 2015 

3，alert日誌中沒有看出啥問題來，去check是否有鎖，卡住了，只好ctrl+c停住

執行sql檢查是否有鎖

SQL> select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;^Cselect t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time                                                        *ERROR at line 1:ORA-01013: user requested cancel of current operationSQL> 

4，嘗試重啟下，卡住了,shutdown hang住了

SQL> shutdown immediate^CORA-01013: user requested cancel of current operationSQL> 

後台alert日誌資訊

Shutting down instance (immediate)Stopping background process SMCOShutting down instance: further logons disabledWed May 27 19:39:44 2015Stopping background process CJQ0Stopping background process QMNCStopping background process MMNLStopping background process MMONWed May 27 19:39:48 2015AUD: Audit Commit Delay exceeded, written a copy to OS Audit TrailLicense high water mark = 28Stopping Job queue slave processes, flags = 7Wed May 27 19:39:48 2015Errors in file /oracle/app/oracle/diag/rdbms/pddev1/pddev1/trace/pddev1_j005_8455.trc:ORA-12012: error on auto execute of job 57381ORA-01089: immediate shutdown in progress - no operations are permittedORA-01089: immediate shutdown in progress - no operations are permittedProcess ID: Session ID: 0 Serial number: 0Wed May 27 19:39:48 2015opiodr aborting process unknown ospid (7674) as a result of ORA-1089Wed May 27 19:39:48 2015opiodr aborting process unknown ospid (7002) as a result of ORA-1089Wed May 27 19:39:48 2015opiodr aborting process unknown ospid (9183) as a result of ORA-1089Job queue slave processes stoppedWed May 27 19:42:46 2015Instance shutdown cancelled

再去看下trace日誌，如下：

[oracle@powerlong4 ~]$ more /oracle/app/oracle/diag/rdbms/pddev1/pddev1/trace/pddev1_j005_8455.trcTrace file /oracle/app/oracle/diag/rdbms/pddev1/pddev1/trace/pddev1_j005_8455.trcOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1System name:    LinuxNode name:  powerlong4Release:    2.6.32-358.el6.x86_64Version:    #1 SMP Fri Feb 22 00:31:26 UTC 2013Machine:    x86_64Instance name: pddev1Redo thread mounted by this instance: 1Oracle process number: 49Unix process pid: 8455, image: oracle@powerlong4 (J005)*** 2015-05-27 19:39:48.078*** SESSION ID:(79.110) 2015-05-27 19:39:48.078*** CLIENT ID:() 2015-05-27 19:39:48.078*** SERVICE NAME:(SYS$USERS) 2015-05-27 19:39:48.078*** MODULE NAME:() 2015-05-27 19:39:48.078*** ACTION NAME:() 2015-05-27 19:39:48.078ORA-12012: error on auto execute of job 57381ORA-01089: immediate shutdown in progress - no operations are permittedORA-01089: immediate shutdown in progress - no operations are permittedProcess ID: Session ID: 0 Serial number: 0[oracle@powerlong4 ~]$ 

5，沒有看出啥問題，領導催的緊急，看來只好用絕招了shutdown abort;

SQL> shutdown abortORACLE instance shut down.SQL> startupORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 1820540928 bytesFixed Size          2214296 bytesVariable Size        1191183976 bytesDatabase Buffers      620756992 bytesRedo Buffers            6385664 bytesDatabase mounted.Database opened.SQL> select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;no rows selectedSQL> 

OK，能登入正常了。事後諸葛亮，我這台線上db完全是邊緣業務，無壓力的，怎麼會plsql遠程登入不上呢？奇怪。看alert日誌也麼有異常資訊，留此紀念，以待後續觀察。