ORA-03135connectionslostcontactProcessID:0

ORA-03135 connections lost contact Process ID:0

场景描述：

领导callme，，说pslql远程登录报错，报错信息：
ORA-03135 connections lost contact Process ID:0

1，赶紧vpn，登录后台去查看负载，看到负载很低： [Oracle@powerlong4 ~]$ w up , @ root pts/.16s 0.03s sqlplus as sysdba root pts/.00s 0.18s 0.03s w root pts/days .02s -bash [oracle@powerlong4 ~]$ 2，再去查看alert日志： Wed May Clearing Resource Manager plan via parameter Wed May Suspending MMON action Wed May Suspending MMON action Wed May Suspending MMON action Wed May Suspending MMON slave action kewrmafsa_ Wed May Suspending MMON action Wed May Suspending MMON slave action kewfmcpsa_ Wed May *********************************************************************** Fatal NI connect error 12170. VERSION INFORMATION: TNS - Production Oracle Bequeath NT Protocol Adapter - Production TCP/IP NT Protocol Adapter - Production Time: 27-MAY-2015 19:32:45 Tracing not turned on. Tns error struct: ns main err code: 12535 TNS-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=)(PORT=55693)) Wed May 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 operation SQL> 4，尝试重启下，卡住了,shutdown hang住了 SQL> shutdown immediate ^CORA-01013: user requested cancel of current operation SQL>

后台alert日志信息

Shutting down instance (immediate) Stopping background process SMCO Shutting down instance: further logons disabled Wed May Stopping background process CJQ0 Stopping background process QMNC Stopping background process MMNL Stopping background process MMON Wed May AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail License high water mark = 28 Stopping Job queue slave processes, flags = 7 Wed May Errors in file /oracle/app/oracle/diag/rdbms/pddev1/pddev1/trace/pddev1_j005_8455.trc: ORA- ORA-01089: immediate shutdown in progress - no operations are permitted ORA-01089: immediate shutdown in progress - no operations are permitted Process ID: Session ID: 0 Serial number: 0 Wed May opiodr aborting ORA-1089 Wed May opiodr aborting ORA-1089 Wed May opiodr aborting ORA-1089 Job queue slave processes stopped Wed May Instance shutdown cancelled

再去看下trace日志，如下：

[oracle@powerlong4 ~]$ more /oracle/app/oracle/diag/rdbms/pddev1/pddev1/trace/pddev1_j005_8455.trc Trace file /oracle/app/oracle/diag/rdbms/pddev1/pddev1/trace/pddev1_j005_8455.trc Oracle Database - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1 System name: Linux Node name: powerlong4 Release: 2.6.32-358.el6.x86_64 Version: #1 SMP Fri Feb 22 00:31:26 UTC 2013 Machine: x86_64 Instance name: pddev1 Redo thread mounted by this instance: 1 Oracle process number: 49 Unix 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.078 ORA-12012: error on auto execute of job 57381 ORA-01089: immediate shutdown in progress - no operations are permitted ORA-01089: immediate shutdown in progress - no operations are permitted Process ID: Session ID: 0 Serial number: 0 [oracle@powerlong4 ~]$ 5，没有看出啥问题，领导催的紧急，看来只好用绝招了shutdown abort; SQL> shutdown abort ORACLE instance shut down. SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1820540928 bytes Fixed Size 2214296 bytes Variable Size 1191183976 bytes Database Buffers 620756992 bytes Redo Buffers 6385664 bytes Database 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 selected SQL>

OK，能登录正常了。事后诸葛亮，我这台线上db完全是边缘业务，无压力的，怎么会plsql远程登录不上呢？奇怪。看alert日志也么有异常信息，留此纪念，以待后续观察。

本文永久更新链接地址：