场景描述：
领导callme，说pslql远程登录报错，报错信息：
ORA-03135 connections lost contact Process ID:0
[Oracle@powerlong4 ~]$ w 20:05:16 up 22 days, 6:33, 3 users, load average: 0.45, 0.58, 0.38 USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT root pts/0 192.168.120.218 19:32 19:30 0.16s 0.03s sqlplus as sysdba root pts/1 192.168.120.218 19:39 0.00s 0.18s 0.03s w root pts/2 :1.0 Fri15 5days 2:23 0.02s -bash [oracle@powerlong4 ~]$2，再去查看alert日志：
Wed May 27 02:00:00 2015
Clearing Resource Manager plan via parameter
Wed May 27 07:15:39 2015
Suspending MMON action 'Block Cleanout Optim, Undo Segment Scan' for 82800 seconds
Wed May 27 07:35:48 2015
Suspending MMON action 'undo usage' for 82800 seconds
Wed May 27 07:56:08 2015
Suspending MMON action 'metrics monitoring' for 82800 seconds
Wed May 27 08:11:17 2015
Suspending MMON slave action kewrmafsa_ for 82800 seconds
Wed May 27 08:16:23 2015
Suspending MMON action 'AWR Auto Purge Task' for 82800 seconds
Wed May 27 16:56:14 2015
Suspending MMON slave action kewfmcpsa_ for 82800 seconds
Wed 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: 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=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 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 27 19:39:44 2015 Stopping background process CJQ0 Stopping background process QMNC Stopping background process MMNL Stopping background process MMON Wed May 27 19:39:48 2015 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 27 19:39:48 2015 Errors in file /oracle/app/oracle/diag/rdbms/pddev1/pddev1/trace/pddev1_j005_8455.trc: 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 Wed May 27 19:39:48 2015 opiodr aborting process unknown ospid (7674) as a result of ORA-1089 Wed May 27 19:39:48 2015 opiodr aborting process unknown ospid (7002) as a result of ORA-1089 Wed May 27 19:39:48 2015 opiodr aborting process unknown ospid (9183) as a result of ORA-1089 Job queue slave processes stopped Wed May 27 19:42:46 2015 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 11g Enterprise Edition Release 11.2.0.1.0 - 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日志也么有异常信息，留此纪念，以待后续观察。
：