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

Atraves 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]

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

Autor: Anderson Graf

Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Entusiasta da tecnologia Oracle, ACE Associate ♠, autor em vários blogs e OTN. Consultor Oracle Senior na Exímio Soluções em TI

Envolvido em soluções de:
– Implementação, migração, gerenciamento e suporte a produtos Oracle, multiplataforma
– Monitoramento de ambientes 24×7
– Backup e Recovery
– Performance e Tuning
– Alta disponibilidade (HA) – RAC, Data Guard
– EM database/grid/cloud control
– Particionamento & Advanced Compression
– Oracle Engineered Systems – ODA, Exadata

Blog pessoal: http://www.andersondba.com.br
Articulista na Oracle Technology Network (OTN) – https://goo.gl/99R6yW
ACE Associate – https://goo.gl/MBB51b
Articulista GPO – http://profissionaloracle.com.br