Estudo de Caso: Como Resolvi um Problema Crítico de Library Cache Lock e ORA-1017

Recentemente, em um ambiente crítico de um cliente, nos deparamos com uma situação peculiar que resultou em indisponibilidade do serviço em um ambiente transacional de grande escala.
Durante o pico de atividade, observamos um número significativo de sessões esperando no evento library cache lock, acompanhadas por repetidas falhas de login (erro ORA-1017). Esses bloqueios e falhas estavam impactando seriamente a performance do sistema como um todo, causando lentidão e indisponibilidade.

Nota: Esse caso é real; no entanto, todos os dados do post foram mascarados para preservar a privacidade e segurança do cliente.

Análise Detalhada

Uma revisão dos logs de auditoria do Oracle mostrou uma quantidade excessiva de tentativas de login falhas de várias aplicações, especialmente de usuários de serviços como ‘APP_PROXY’ e ‘USU_APP’, que estavam tentando se conectar ao banco de dados com credenciais inválidas. Esse comportamento estava, relacionado aos bloqueios de library cache lock, já que o Oracle tenta validar essas credenciais no cache de biblioteca e, ao enfrentar repetidas falhas, acaba a colocar as sessões em um estado de espera. A dificuldade foi associar as sessões com library cache lock com os erros ORA-1017.

Sintomas Observados

A situação era evidente a partir da seguinte saída de diagnóstico, que mostrava múltiplas sessões impactadas com library cache lock, segue uma amostragem dos dados:

Após observar vários library cache lock sem uma identificação clara de usuário, realizamos uma análise detalhada dos logs de auditoria e pudemos constatar repetidos erros de autenticação (ORA-1017). A consulta abaixo ajudou a ilustrar o problema:

select username ORACLE_USER, os_username OS_USER, userhost server, client_id, to_char(timestamp,'yyyy-mm-dd') ACTION_TIME, count(*) total 
from dba_audit_trail 
where returncode = 1017 
and timestamp > sysdate - 30
group by username, os_username, userhost, client_id,  to_char(timestamp,'yyyy-mm-dd')
having   count(*) > 10
order by ACTION_TIME;

ORACLE_USER          OS_USER              SERVER            CLIEN ACTION_TIME       TOTAL
-------------------- -------------------- ----------------- ----- ------------ ----------
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE20          2024-06-30           15
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE23          2024-06-30           13
USU_APP              zabbix               GINGASZBXPRR            2024-06-30         1580
APP_PROXY            jboss                XPTKAPPSBNPP01          2024-07-01          310
APP_PROXY            jboss                XPTKAPPSBNPP02          2024-07-01          323
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE10          2024-07-01           11
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE11          2024-07-01           15
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE12          2024-07-01           13
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE14          2024-07-01           21
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE15          2024-07-01           21
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE16          2024-07-01           24
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE17          2024-07-01           22
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE18          2024-07-01           21
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE19          2024-07-01           30
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE20          2024-07-01           24
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE21          2024-07-01           22
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE22          2024-07-01           20
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE23          2024-07-01           27
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE24          2024-07-01           32
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE25          2024-07-01           31
APP_PROXY            svciisfvsprd         XCPBCRRTQQSE26          2024-07-01         1466
USU_APP              zabbix               GINGASZBXPRR            2024-07-01         1583
USU_APP              zabbix               GINGASZBXPRR            2024-07-02           15

A saída dessa consulta mostrou que vários usuários de serviços críticos estavam tentando se conectar ao banco de dados com credenciais inválidas, resultando em centenas e milhares de falhas de login.

Esta análise foi crucial para conectar os pontos entre as tentativas de login falhas e os library cache lock, permitindo-nos formular uma estratégia eficaz de resolução.

AWR

Para tentar entender o problema do ambiente, geramos um AWR (Automatic Workload Repository) do momento do problema. Ficou claro que o banco de dados estava gastando mais de 98% do tempo no com “Connection Management Call Elapsed Time“. Este indicador mostra o tempo total gasto pelo Oracle em operações de gerenciamento de conexões, incluindo login, autenticação e gerenciamento de sessões.

Quando observamos um alto valor no Connection Management Call Elapsed Time, é um indício de que o banco de dados está enfrentando problemas de autenticação. No nosso caso, as múltiplas tentativas de login falhadas com o erro ORA-1017 causaram um aumento significativo no tempo de gerenciamento de conexões indicando claramente que as tentativas de conexão estavam utilizando uma quantidade desproporcional de recursos do banco de dados, afetando negativamente o desempenho do sistema. Essas tentativas falhadas geraram um ciclo de espera e bloqueio, onde novas tentativas de conexão não conseguiam ser processadas rapidamente, agravando ainda mais a situação.

Diagnóstico Final

Foi constatado que recentemente as senhas dos usuários de aplicação haviam sido modificadas devido a uma auditoria, no entanto, diante aos fatos, ficou evidente que haviam pontos da aplicação que ainda usavam a senha antiga e não foram mapeadas. Múltiplas tentativas de login com credenciais inválidas (ORA-1017) resultaram em uma espera de recurso. Quando essas sessões tentavam se conectar ao banco de dados, encontravam um library cache lock devido ao bloqueio do processo de autenticação.

Exemplos de Como Isso Pode Acontecer

Um aplicativo cliente tenta se conectar ao banco de dados repetidamente com uma senha inválida, causando diversos erros ORA-1017.

Usuários tentando se conectar com credenciais válidas podem encontrar dificuldades devido ao congestionamento causado pelas múltiplas tentativas falhas.

Isso resulta em sessões válidas também sendo bloqueadas e aparecendo como library cache lock.

Solução Proposta

A resolução desse caso real foi um processo desafiador que envolveu a identificação rápida da causa raiz e a implementação de uma solução eficaz para restaurar a estabilidade do ambiente transacional. Após identificar que múltiplas tentativas de login com credenciais inválidas (ORA-1017) estavam causando um library cache lock, compreendemos que o problema estava relacionado à recente troca de senhas dos usuários da aplicação, devido a uma auditoria.

Foi crucial entender que as sessões tentavam se conectar ao banco de dados e, ao falharem repetidamente na autenticação, geravam um bloqueio no cache de biblioteca. Esse bloqueio impedia que outras sessões legítimas se conectassem, criando uma cadeia de espera que culminava em uma degradação significativa do desempenho do sistema.

Ao investigar mais a fundo, descobrimos que pontos da aplicação ainda utilizavam a senha antiga, e esses pontos não foram mapeados corretamente durante a troca de senhas. Isso causava múltiplas falhas de autenticação, levando ao problema observado.

Para resolver o problema, retornamos o hash da senha anterior dos usuários da aplicação. Isso permitiu que as conexões fossem autenticadas corretamente, eliminando os bloqueios no library cache e restaurando a performance do sistema.

Esse caso real ilustra a importância de realizar uma troca de senha de forma completa e meticulosa, garantindo que todas as partes da aplicação sejam atualizadas com as novas credenciais. Além disso, mostra como uma análise cuidadosa dos sintomas e uma compreensão aprofundada do funcionamento do Oracle Database são essenciais para solucionar problemas complexos de performance.

Este estudo de caso é baseado em um incidente real, mas todos os dados foram mascarados para preservar a privacidade e a segurança do cliente envolvido. A resolução de problemas como este requer uma abordagem detalhada e metódica, bem como a capacidade de correlacionar sintomas e causas para implementar soluções eficazes.

Referências Adicionais:

🚀🚀🚀 Cacete de Agulha! 💉💉💉 #OracleDatabase #MarceloÉMeuAmigo #DBA #DBAProblemático #GuinaNãoTinhaDó #OPipocaAbraço #AVidaÉLoka #RacionaisMCs #EngenheiroDeObraPronta #TamancoMulher #ÉProibidoCochilar

Compartilhe

Pesquisar

Roberto Sobrinho

Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.