Conectando na SGA usando uma conexão SQL*Plus Preliminar

Diversas vezes encontramos ambientes onde o numero de processos permitidos estourou (ORA-00020: maximum number of processes (%s) exceeded), e agora qual a solução? Reiniciar o database ou começar a matar sessões? 

Estas ações muitas vezes podem mascarar o problema ou não serem permitidas pois podem significar mais trabalho.. 

Uma “solução” introduzida na versão 10G é a conexão preliminar, onde permite a conexão diretamente com a SGA sem abrir uma sessão no bando de dados. 

Como não temos nenhuma sessão aberta no database não conseguimos realizar nenhuma consulta pois retorna erro conforme demonstrado abaixo, contudo, atraves da conexão preliminar (sqlplus -prelim) conseguimos executar qualquer comando ORADEBUG, logo, podemos apontar e analisar o que esta causando esta contenção no ambiente seja lock ou qualquer outro fator. 

Abrindo uma conexão preliminar e tentando executar um select (erro):

[oracle@orcl ~]$ sql -prelim / as sysdba
 
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 22 12:18:22 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

Outra forma de iniciarmos a conexão preliminar é:

[oracle@orcl ~]$ sql /nolog
 
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 24 13:36:58 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
SQL> set _prelim on
SQL> conn / as sysdba
Prelim connection established

Através do comando ORADEBUG podemos rastrear qualquer processo ou sessão gerando assim um arquivo trace para dentro do diretório configurado no parâmetro background_dump_dest.

[oracle@orcl ~]$ ps -ef| grep -i ora_dbw| grep -v grep
oracle   11749     1  0 13:29 ?        00:00:00 ora_dbw0_orcl11g
[oracle@orcl ~]$

[oracle@orcl ~]$ sql -prelim / as sysdba
 
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 24 13:44:46 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
SQL> oradebug setorapname DBW0
Oracle pid: 10, Unix process pid: 11749, image: oracle@orcl.anderson (DBW0)
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_dbw0_11749.trc

SQL> !tail -f /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_dbw0_11749.trc
 
*** 2012-02-24 13:46:49.252
Finished processing ORADEBUG command (#6) 'tracefile_name'
WAIT #0: nam='rdbms ipc message' ela= 995049 timeout=300 p2=0 p3=0 obj#=-1 tim=1330102009264004
 
*** 2012-02-24 13:46:51.275
WAIT #0: nam='rdbms ipc message' ela= 2010785 timeout=201 p2=0 p3=0 obj#=-1 tim=1330102011274975
 
*** 2012-02-24 13:46:54.276
WAIT #0: nam='rdbms ipc message' ela= 3001026 timeout=300 p2=0 p3=0 obj#=-1 tim=1330102014276241
 
*** 2012-02-24 13:46:57.277
WAIT #0: nam='rdbms ipc message' ela= 3000305 timeout=300 p2=0 p3=0 obj#=-1 tim=1330102017277006

Um case muito interessante utilizando a conexão preliminar foi publicado pelo Arup Nanda em seu blog, vale a pena conferir! Diagnosing Library Cache Latch Contention: A Real Case Study 

Para obtermos mais detalhes e formas de utilizar o ORADEBUG seguem NOTES relacionadas no Metalink:

How To Connect Using A sql Preliminary Connection [ID 986640.1]

Interpreting HANGANALYZE trace files to diagnose hanging and performance problems [ID 215858.1]

ORA-7445 [kgllkd] With -prelim Option When Running System State Dump [ID 417879.1]