场景描述： 领导callme，说pslql远程登录报错，报错信息： ORA-03135 connections lost contact Process ID:0
1，赶紧vpn，，登录后台去查看负载，看到负载很低：[oracle@powerlong4 ~]$ w up , @ rootpts/.16s 0.03s sqlplus as sysdbarootpts/.00s 0.18s 0.03s wrootpts/days .02s -bash[oracle@powerlong4 ~]$
<版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!> 原博客地址： 原作者：黄杉 (mchdba)
2，再去查看alert日志：Wed May Clearing Resource Manager plan via parameterWed 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 – ProductionOracle Bequeath NT Protocol Adapter – ProductionTCP/IP NT Protocol Adapter – Production Time: 27-MAY-2015 19:32:45 Tracing 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: 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 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 Stopping background process CJQ0Stopping background process QMNCStopping background process MMNLStopping background process MMONWed May AUD: Audit Commit Delay exceeded, written a copy to OS Audit TrailLicense high water mark = 28Stopping Job queue slave processes, flags = 7Wed 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 permittedORA-01089: immediate shutdown in progress – no operations are permittedProcess ID: Session ID: 0 Serial number: 0Wed May opiodr aborting ORA-1089Wed May opiodr aborting ORA-1089Wed May opiodr aborting ORA-1089Job queue slave processes stoppedWed May Instance 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 – 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日志也么有异常信息，留此纪念，以待后续观察。
接受失败等于回归真实的自我，接受失败等于打破完美的面具，
