Usando o LogMiner para descobrir quais instruções SQL foram executadas no Banco de Dados

Olá pessoal,

     No artigo de hoje vou mostrar para vocês como descobrir quais instruções SQL de atualizações de dados (INSERT, UPDATE, DELETE e MERGE) foram executadas dentro de um Banco de Dados Oracle, utilizando o LogMiner, uma ferramenta poderosa e extremamente útil, que além de poder ser utilizada para rastrear o que ocorreu no Banco de Dados (ajudando a descobrir erros na aplicação ou a recuperar dados alterados erroneamente), também é muito utilizada para fins de auditoria (visando descobrir quem fez o que e em qual data/horário).

     O LogMiner é um utilitário que pode ser executado através de linha de comando (blocos PL/SQL) ou através do Oracle LogMiner Viewer, que está presente na interface gráfica do Enterprise Manager. Como todo bom DBA Oracle, eu prefiro a primeira opção! Essa ferramenta pode ser utilizada para ver um histórico de atividades do Banco de Dados, apontando quais instruções DML e DDL foram executadas, a ordem em que elas foram executadas, e até mesmo quem as executou. Como ela faz isso? É simples, ela lê o conteúdo de Redo Log Files e Archived Logs.

     Antes de usar o LogMiner é necessário habilitar "supplemental logging" e forçar um log switch para que informações adicionais sejam acrescentadas nos redo log files, permitindo deste modo, o uso apropriado da ferramenta. Sem ele algumas informações (Ex.: nome do usuário executor da instrução) seriam ocultadas.

     Existem 3 objetos básicos na configuração do LogMiner que você deve conhecer para aprender a utilizá-lo:

          1- O banco de dados de origem: banco de dados que produziu todos os redo log files que o LogMiner irá analisar;

          2- O dicionário do LogMiner: permite ao LogMiner fornecer nomes de tabelas e colunas, ao invés dos IDs de objetos internos;

          3-  Os redo log files contendo os dados de interesse.

     O dicionário do LogMiner é um objeto muito importante na utilização desta ferramenta, pois sem ele os dados retornariam para nós em um formato não inteligível, ou seja, ao invés das instruções SQL executadas retornarem para nós em um texto contendo o nome das tabelas, colunas e dados alterados (ver exemplo do SQL 1), elas retornariam com seus identificadores internos, no formato exato como são gravadas nos redo log files (ver exemplo do SQL 2):

-- SQL 1: instrução SQL retornada pelo LogMiner utilizando um dicionário:

INSERT INTO HR.JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES('IT_WT','Technical Writer', 4000, 11000);

-- SQL 2: instrução SQL retornada pelo LogMiner sem utilizar um dicionário:

insert into "UNKNOWN"."OBJ# 45522"("COL 1","COL 2","COL 3","COL 4") values
(HEXTORAW('45465f4748'),HEXTORAW('546563686e6963616c20577269746572'),
HEXTORAW('c229'),HEXTORAW('c3020b'));

     Para o LogMiner pesquisar as intruções SQL desejadas você deve configurá-lo para ler os redo log files, escolhendo 1 de 2 opções:
 

           1- Manualmente: use a procedure DBMS_LOGMNR.ADD_LOGFILE para manualmente criar uma lista de redo log files antes de iniciar o LogMiner. Você pode adicionar redo logs de BDs (origem) que não sejam o mesmo BD em que a ferramenta está sendo executada, porém todos eles tem que ser do mesmo BD e devem ter o mesmo RESETLOGS SCN.

           2- Automaticamente:  use a opção CONTINUOUS_MINE ao iniciar o LogMiner, se ele estiver sendo usado no próprio BD origem, para que ele pesquise e crie uma lista de redo logs automaticamente.
       
     Para aprender a utilizar o LogMiner siga o roteiro abaixo:

 
 

UTILIZANDO O LOGMINER

1- CRIANDO O DICIONÁRIO DO LOGMINER:
     Existem 3 formas de criar o dicionário do LogMiner. Execute apenas 1 delas ao efetuar uma análise:

    a) Criando o dicionário em um arquivo texto:
     Crie um arquivo de dicionário executando o script do exemplo abaixo, passando como valores para a procedure DBMS_LOGMNR_D.BUILD o nome do arquivo e diretório em que ele será criado. No valor do diretório informe o valor configurado no parâmetro da instância UTL_FILE_DIR. Em nosso exemplo criaremos um arquivo chamado dictionary.ora no diretório /oratmp (que no meu caso, é o valor configurado no parâmetro UTL_FILE_DIR):
   begin

      DBMS_LOGMNR_D.BUILD('dictionary.ora', '/tmp', 
                             DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

   end;

    b) Utilizando o dicionário do catálogo online:
      Esta opção é a mais rápida para você usar o dicionário, mas ela só pode ser usada quando o BD que você irá fazer a análise também é o BD origem, e quando você tem certeza de que nenhum DDL irá alterar a estrutura dos objetos a serem analisados. Para usá-la execute o script abaixo:

  begin
       DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
   end;

    c) Criando o dicionário nos próprios redo log files:
      Crie o dicionário dentro dos redo log files executando o script do exemplo abaixo, passando o valor DBMS_LOGMNR_D.STORE_IN_REDO_LOGS para o parâmetro option da procedure DBMS_LOGMNR_D.BUILD. Esta opção exige que archived logs estejam habilitados no BD:
   begin
       DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
   end;

  Obs.: No momento da criação do dicionário o BD deve estar aberto e nenhuma instrução DDL deve estar sendo executada.

2- ADICIONANDO LOGS:
     Uma lista de logs (ou no mínimo 1) a serem analisados devem ser adicionados utilizando o pacote DBMS_LOGMNR. O primeiro log deve ser adicionado passando o valor DBMS_LOGMNR.new para o parâmetro option, enquanto que os demais logs, devem ser adicionados passando o valor DBMS_LOGMNER.ADD_LOGFILE, como no exemplo abaixo:
BEGIN

   DBMS_LOGMNR.add_logfile(options => DBMS_LOGMNR.new, 

           logfilename => '/oraredo/redo01.rdo'); 

   DBMS_LOGMNR.add_logfile(options => DBMS_LOGMNR.addfile,

        logfilename => '/oraredo/redo02.rdo'); 

END;

Obs.: Para fazer testes descubra o nome dos últimos redo log files ou archived logs consultando as visões V$LOGFILE ou V$ARCHIVED_LOG, e substitua os nomes dos arquivos em cinza no script acima.

3- INICIANDO O LOGMINER:
     Para iniciar o LogMiner execute a procedure DBMS_LOGMNR.START_LOGMNR escolhendo um dos modos abaixo:

    a) Sem passar parâmetros, para analisar todo o conteúdo dos logs adicionados, quando um arquivo de dicionário está sendo utilizado:

BEGIN

   -- iniciando o LogMiner lendo o conteúdo de todos os logs adicionados (passar p/ o param dictfilename o caminho e nome do arquivo de dicionário gerado no passo 1) :

   DBMS_LOGMNR.start_logmnr(

         dictfilename => '/tmp/dictionary.ora');
END;

    b) Sem passar parâmetros, para analisar todo o conteúdo dos logs adicionados, quando o dicionário está armazenado nos redo logs:
BEGIN 
    DBMS_LOGMNR.START_LOGMNR(
         OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
END;

    c) Especificando um range de tempo que deve corresponder ao período a ser analisado, utilizando um arquivo de dicionário:  

BEGIN
   DBMS_LOGMNR.start_logmnr(
      dictfilename => '/tmp/dictionary.ora', 
      starttime => TO_DATE('01/12/2015 00:00:00', 'DD/MM/YYYY HH24:MI:SS'),
      endtime => TO_DATE('21/12/2001 10:00:00', 'DD/MM/YYYY HH24:MI:SS')); 
END;

    d) Especificando um range de tempo que deve corresponder ao período a ser analisado, quando o dicionário está armazenado nos redo logs, configurando o LogMiner para adicionar novos logs automaticamente:  

BEGIN
   DBMS_LOGMNR.start_logmnr (      
     starttime => TO_DATE('01/12/2015 00:00:00', 'DD/MM/YYYY HH24:MI:SS'),
     endtime => TO_DATE('21/12/2001 10:00:00', 'DD/MM/YYYY HH24:MI:SS'),

     options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + 

                   DBMS_LOGMNR.CONTINUOUS_MINE

     );
END;

4- PESQUISANDO INFORMAÇÕES NOS LOGS:
    Depois do LogMiner ter sido iniciado você pode consultar as visões:

         - V$LOGMNR_DICTIONARY: Para ver o arquivo de dicionário em uso;
         - V$LOGMNR_PARAMETERS: Para ver os atuais parâmetros configurados p/ o Logminer;
         - V$LOGMNR_LOGS - Para ver os logs a serem analisados;

         - V$LOGMNR_CONTENTS: Para ler o conteúdo dos logs.

   A consulta abaixo retorna as principais informações de um redo log file. Ela possui filtros por nome do schema e tabela que você deseja pesquisar. Informe os valores desejados ao executá-la:

select    to_char(timestamp, 'dd/mm/yyyy hh24:mi:ss') as timestamp,
          operation, seg_owner, table_name, sql_redo, 
          SQL_UNDO, seg_type_name, table_space, row_id, username
from      v$logmnr_contents
where     seg_owner = UPPER('&SCHEMA')
AND       TABLE_NAME = UPPER('&TABLENAME')
order by  timestamp desc;

     Veja na imagem abaixo o resultado da consulta na visão V$LOGMNR_CONTENTS, após eu ter executado a instrução "update hr.employees set first_name = first_name; commit;", e fornecido os valores HR e EMPLOYEES no filtro solicitado:

Pesquisando a visão V$LOGMNR_CONTENTS

  
5- PARANDO O LOGMINER:
    Depois que você terminar o seu trabalho de análise, pare o LogMiner executando a procedure DBMS_LOGMNR.END_LOGMNR:
BEGIN

     DBMS_LOGMNR.END_LOGMNR;
END;

   

    Existem ainda diversas outras formas de verificar quais instruções SQL foram executadas em um BD Oracle, tais como Flashback e Auditoria, porém uma das vantagens de usar o LogMiner, é que ele não permite que alguém apague os seus rastros. Na auditoria padrão do Oracle, por exemplo, o DBA (ou qualquer usuário com privilégios administrativos) poderia apagar os dados da tabela AUD$, e qualquer vestígio de um SQL executado indevidamente seria apagado. Com flasback, existem várias limitações, como por exemplo, o tempo de retenção padrão do Undo Tablespace, que poderia expurgar os dados necessários para análise, após 15 minutos da execuçao do SQL.

Bom pessoal, por é hoje é só!

Se você gostou ou tem qualquer dúvida, deixe um comentário.

[]s

  

Referências:
   - Using LogMiner to Analyze Redo Log Files