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 TTYFROMLOGIN@ IDLE JCPU PCPU WHATroot pts/0192.168.120.21819:32 19:30 0.16s0.03s sqlplus as sysdbaroot pts/1192.168.120.21819:390.00s0.18s0.03s wroot pts/2:1.0 Fri155days2: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 - ProductionOracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - ProductionTCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - ProductionTime: 27-MAY-2015 19:32:45Tracing not turned on.Tns error struct:ns main err code: 12535TNS-12535: TNS:operation timed outns secondary err code: 12606nt main err code: 0nt secondary err code: 0nt OS err code: 0Client 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 Size2214296 bytesVariable Size1191183976 bytesDatabase Buffers620756992 bytesRedo Buffers6385664 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日志也么有异常信息，留此纪念，以待后续观察。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址