Oracle travando durante login
 

Em um servidor com a versão 10.2.0.1.0, ao efetuar o login pelo sqlplus com um usuário normal, o processo simplesmente ficava travado.
Não aparecia nenhum andamento.

Mas ao efetuar usando "sqlplus / as sysdba", funcionava perfeitamente.

Também verifiquei e não encontrei nehum Lock ou algo parecido.

A solução foi executar os seguintes comandos:

SQL> ALTER SESSION SET EVENTS 'immediate trace name hanganalyze level 3';

Session altered.

SQL> oradebug hanganalyze 3
Hang Analysis in e:\oracle\product\10.2.0\admin\cm\udump\cm_ora_232 0.trc
SQL> oradebug hanganalyze 3
Hang Analysis in e:\oracle\product\10.2.0\admin\cm\udump\cm_ora_232 0.trc
SQL> oradebug hanganalyze 3
Hang Analysis in e:\oracle\product\10.2.0\admin\cm\udump\cm_ora_232 0.trc
SQL> oradebug hanganalyze 3
Hang Analysis in e:\oracle\product\10.2.0\admin\cm\udump\cm_ora_232 0.trc
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>

Depois abri uma outra seção e executei:

alter session set events 'immediate trace name SYSTEMSTATE level 10';

Arquivo gerado cm_ora_2276.trc

Ao observar o trace completo do banco acima, não havia nenhum erro aparente.

Dentro do arquivo cm_ora_2320.trc observei o seguinte:

==============
HANG ANALYSIS:
==============
Open chains found:
Other chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/141/1/0x59a85aec/2356/Streams AQ: qmn slave idle wait>
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/143/2/0x59a854fc/2968/Streams AQ: waiting for time man>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/149/1/0x59a8432c/1784/Streams AQ: qmn coordinator idle>
Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/158/25/0x59a8491c/1512/jobq slave wait>
Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/159/3/0x59a8257c/2320/No Wait>
Extra information that will be dumped at higher levels:
[level 5] : 5 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 10] : 14 node dumps -- [IGN]

State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[140]/0/141/1/0x59b5d004/2356/SINGLE_NODE/1/2//none
[142]/0/143/2/0x59b5f594/2968/SINGLE_NODE/3/4//none
[148]/0/149/1/0x59b66644/1784/SINGLE_NODE/5/6//none
[154]/0/155/1/0x59b6d6f4/1424/IGN/7/8//none
[155]/0/156/1/0x59b6e9bc/1816/IGN/9/10//none
[156]/0/157/1/0x59b6fc84/3920/IGN/11/12//none
[157]/0/158/25/0x59b70f4c/1512/SINGLE_NODE/13/14//none
[158]/0/159/3/0x59b72214/2320/SINGLE_NODE_NW/15/16//none
[159]/0/160/1/0x59b734dc/3868/IGN/17/18//none
[160]/0/161/1/0x59b747a4/3708/IGN/19/20//none
[161]/0/162/1/0x59b75a6c/2640/IGN/21/22//none
[162]/0/163/1/0x59b76d34/408/IGN/23/24//none
[163]/0/164/1/0x59b77ffc/2724/IGN/25/26//none
[164]/0/165/1/0x59b792c4/3852/IGN/27/28//none
[165]/0/166/1/0x59b7a58c/2676/IGN/29/30//none
[166]/0/167/1/0x59b7b854/3528/IGN/31/32//none
[167]/0/168/1/0x59b7cb1c/3944/IGN/33/34//none
[168]/0/169/1/0x59b7dde4/1760/IGN/35/36//none
[169]/0/170/1/0x59b7f0ac/1880/IGN/37/38//none
====================
END OF HANG ANALYSIS
====================

Usei os scripts do Tanel Poder, mas também sem nenhum suscesso:

A solução foi verificar o Archive Mode.

E o problema era: O database estava no modo archivelog, mas não estava configurado para automatic archiving quando o logo era completado.

NAME TYPE VALUE
------------------------------------ ----------- -----------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string ARC%S_%R.%T
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
SQL>

Simplesmente atribui para automatic archiving (log_archive_start = true)
e um destination (log_archive_dest=mydir).

ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_1='location=E:\oracle\product\10. 2.0\flash_recovery_area\CM\ARCHIVELOG' SCOPE=spfile;
ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=spfile;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ARCHIVE LOG START;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Atenciosamente,
Luiz Vaz