There is an option that is helpful for the Oracle DBA when the database is hanging and we can’t connect to the database. We can execute commands like oradebug and sqlplus -prelim
We can connect to the sga but not to the database, in this case no session is created.

We wants to connect to the sga but it’s impossible to create a sql*plus session:

 sqlplus -prelim / as sysdba
(or)
sqlplus /nolog
set _prelim on
conn / as sysdba

Example:

$sqlplus -prelim /nolog

SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 12
Hang Analysis in C:\oracle\admin\orcl\udump\orcl_ora_5564.trc
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
C:\oracle\admin\orcl\udump\orcl_ora_5598.trc


In a RAC environment:

SQL> oradebug setinst all
SQL> oradebug -g def hanganalyze 12

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug -g all dump systemstate 266

We can generate the hanganalyze and systemstate dumps in a normal SQL*Plus session too:

 For the HangAnalyze
SQL>alter session set events 'immediate trace name hanganalyze level 12';
To get the SystemState:
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';

Author: Vincent Fenoll (DBA Oracle)
Compatible: Oracle 10g 11g 12c

 
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/02/24
-- # Name: ORA-4031 Analysis and Diagnosis
--  ################################################
-- #
-- # Compatible: Oracle 8i 9i 10g 11g
-- #
-- ################################################
-- #
-- # Steps to find and solve fragmentation issues
-- # in the shared pool after ORA-4031 raised
-- #
-- ################################################
 

"ORA-4031 - Unable to allocate bytes of shared memory"
ORA-4031 is a very common error that many dba's face in their day to day activities. This error can commonly occur due to the SHARED POOL SIZE. This error can be due to an inadquate sizing of the SHARED POOL or due to  fragmentation of the shared pool. 


- Increase Shared_Pool if too small (and therefore SGA_TARGET)


- _shared_pool_reserved_pct = 10 (default parameter value = 5%,  10% is better and advised by Oracle)
alter system set "_shared_pool_reserved_pct"= 10 scope = spfile;


- begin traces:
alter system set events '4031 trace name errorstack level 3: 4031 trace name HEAPDUMP level 536870914 ';


- Stop traces:
alter system set events '4031 trace name HEAPDUMP off ';


- Trace analysis:
Search query / module has asked for the memory


- Run SGAStat query every 30 minutes to check the evolution of dispatch of memory:

SELECT *
FROM v $ sgastat
WHERE name IN ('free memory', 'db_block_buffers', 'log_buffer'
'dictionary cache', 'sql area', 'library cache');



- Search queries that are not shared:
	Cf script: "unshared_queries.sql" (Tuning/SQL)