SQLPLUS HANGING

      sqlplus connection might hang due to a number of reasons for instance when maximum number of processes specified by the parameter file reached, when the new sessions need to allocate some memory from shared pool while no memory is available to do so, etc
      If you cannot connect, you have two choices, either wait while the performance is degraded impacting the end users or immediately bounce the instance.

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 16:56:40 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-00020: maximum number of processes (100) exceeded
Enter user-name: ^C
$

To bounce the Instance:

1. We can directly kill the process using unix id

First list the process id for that particular instance like this
$ps -fe|grep pmon
oracle 2031994     1    0  Oct 22 -  4:18 ora_pmon_TESTDB

Kill that particular instance by its unix process id like this
$kill -9 2031994
 And then export the instance and startit up once again
$export ORACLE_SID=TESTDB
$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 16:59:43 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Conected to an idle instance.
SQL>startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 310379864 bytes
Database Buffers 306184192 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL>

2.Alternatively you can use the –prelim option 
-prelim option stands for "preliminary connection".

       When we log on normally including SYSDBA connections, the following 3 steps are completed
1. A new Oracle process is started (either by the listener or by local sqlplus if using the local BEQ connection)
2. The new process attaches to SGA shared memory segments (so it could access all the needed SGA structures)
3. The new process allocates process and session state objects and initializes new session structures in SGA

        But when we use the prelim option only the first two steps are completed. Since allocating memory is not a possible operation on shared memory structures that are protected by some sort of locks usually latches or Oracle KGX mutexes

$sqlplus -prelim / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 17:13:24 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> shutdown abort
ORACLE instance shut down.
SQL>startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 310379864 bytes
Database Buffers 306184192 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL>
 Bouncing the instance is only possible while it is affordable, What about for 24/7 databases,still there is a work around provided by oracle to run the ORADEBUG commands while you are connected to a running database using the prilim option
$sqlplus -prelim "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 17:33:09 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> oradebug hanganalyze 3

Statement processed.

The command seems successful,However when looking into the resulting tracefile, we see this:

Processing Oradebug command 'HANGANALYZE 3'
====================================================================
HANG ANALYSIS:

ERROR: Can not perform hang analysis dump without a process
       state object and a session state object.
  ( process=(nil), sess=(nil) )

=====================================================================
We still can do this
SQL> oradebug setmypid
Statement processed.

SQL> oradebug hanganalyze 12
Hang Analysis in /u01/app/oracle/diag/rdbms/testdb/TESTDB_ora_1234.trc