ORA-03135 connections lost contact Process ID: 0

ORA-03135 connections lost contact Process ID: 0

Scenario Description:

The leader callme said that the remote logon to pslql reports an error and the error message is:
ORA-03135 connections lost contact Process ID: 0

1. Hurry to the vpn and log on to the backend to view the load. The load is very low:

[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. Check the alert Log again:

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. the alert Log does not see any problem. check whether there is a lock and the lock is stuck, so we have to stop using ctrl + c.

Execute SQL check for Lock

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. Try to restart, get stuck, and shutdown hang

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

Background alert Log Information

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

Check the trace log again, as shown below:

[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. I didn't see any problems. The leaders rushed to the emergency and it seemed I had to use 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. The logon is normal. After the event, Zhuge Liang showed that my online db is completely edge-based and has no pressure. How can I remotely log on to plsql? Strange. Check whether there is any exception in the alert Log. Leave it here for future observation.