UTILIZANDO LOGMINER PARA AUDITAR ALTERAÇÕES NA BASE DE DADOS

Objetivo:

Existem situações em que precisamos obter informações de alguma alteração realizada na base de dados e não podemos contar com os recursos de auditoria pois estes não estavam habilitados, nesse caso uma alternativa seria recorrer ao Logminer. Esse recurso permite que sejam recuperados os comandos DDL e DML que foram executados na base e ainda fornece algumas informações históricas desses comandos. Essas informações são armazenadas normalmente nos arquivos de REDO (registros mais recentes) e nos arquivos de ARCHIVE (registros mais antigos).

Overview:

Oracle LogMiner permite que você consulte os arquivos de log redo através de uma interface SQL. Todas as alterações feitas nos dados dos usuários e dicionário do Banco são registradas nos arquivos de REDO do banco Oracle. Portanto, os arquivos de log redo contem todas as informações necessárias para realizar operações de recuperação.

A funcionalidade LogMiner está disponível através de uma interface de linha de comando ou através do Oracle LogMiner Viewer interface gráfica do usuário (GUI). O LogMiner Viewer é uma parte do Oracle Enterprise Manager.

A seguir temos algumas possibilidades de utilização das informações contidas em arquivos de log redo:

• Identificar quando uma corrupção lógica ocorreu no banco de dados, tais como quando pode ter iniciado os erros de uma determinada aplicação. Isso permite que você restaure o banco de dados para o estado em que estava antes da corrupção.
• Detectar e sempre que possível, corrigir erros do usuário, que é um cenário mais provável do que a corrupção lógica. Por erros de Usuário entende-se eliminar registros por engano por causa de valores incorretos em uma cláusula WHERE, atualizar registros com valores incorretos, eliminar um indice por engano, e assim por diante.
• Determinar as ações necessárias para executar uma recuperação fine-grained, ao nível da transação. Se você conseguir levantar todas as dependências existentes de uma determinada tabela ou conjunto de tabelas, será possível realizar uma operação de table-based undo para reverter um conjunto de alterações.
• Performance tuning e Capacity planning para análise de tendência. Você pode determinar quais tabelas sofrem mais atualizações e inserções. Esta informação fornece uma perspectiva histórica sobre as estatísticas de acesso ao disco, que pode ser utilizado para fins de tuning.
• Realizar pós-auditoria. Os arquivos de REDO contem todas as informações necessárias para rastrear qualquer DML e DDL executadas no banco de dados, a ordem em que elas foram executadas, e quem as executou.

Ambiente:

SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 10.2.0.5.0 com archive log habilitado

Considerações:

1. Certifique-se que o banco de dados estava com a opção supplemental_log_data_min ativada no momento em que os arquivos de REDO foram criados:

SQL> SELECT name, supplemental_log_data_min FROM v$database;

NAME SUPPLEME
------------------------------ --------
M10202WA YES

2. Para habilitar a opção supplemental_log_data_min utilize o comando a seguir:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

3. O LogMiner não armazena USERNAME e SESSION_INFO para todos os registros de REDO.
Para obter essas informações nas consultas ao LogMiner, devemos adicionar os arquivos de LOG que contem os registros de conexão da sessão cujos comandos estamos pesquisando. Se os arquivos de log adicionados não possuirem o comando de conexão da sessão que executou as DDL e/ou DML que estão sendo pesquisadas o LogMiner não será capaz de mostrar o USERNAME que executou o comando e vair exibir "UNKNOWN" no campo username, alem disso, o campo SESSION_INFO tambem exibirá "UNKNOWN".

Tópicos:

1. Setup
2. Identificando arquivos de log
3. Adicionando arquivos de log ao LogMiner
4. Iniciando o Logminer
5. Pesquisando informações desejadas
6. Encerrando o LogMiner

1. Setup

SQL> create user tstuser identified by tstuser default tablespace users temporary tablespace temp;

User created.

SQL> grant connect to tstuser;

Grant succeeded.

SQL> grant dba to tstuser;

Grant succeeded.

SQL> create table hr.db_objetos as select * from dba_objects;

Tabela criada.

SQL> select to_char(SYSDATE,'dd-mm-yyyy hh24:mi:ss') FIRST_TIME from dual;

FIRST_TIME
-------------------
15-06-2010 14:13:39

1 linha selecionada.

SQL> Alter system switch logfile;

Sistema alterado.

SQL> connect tstuser/tstuser@lab1;
Conectado.
SQL>
SQL> delete from hr.db_objetos where owner = 'HR';

87 linhas deletadas.

SQL> commit;

Validação completa.

SQL> Alter system switch logfile;

Sistema alterado.

SQL> drop table hr.db_objetos;

Tabela eliminada.

SQL> Alter system switch logfile;

Sistema alterado.

SQL> select to_char(SYSDATE,'dd-mm-yyyy hh24:mi:ss') FIRST_TIME from dual;

FIRST_TIME
-------------------
15-06-2010 14:15:56

1 linha selecionada.

SQL> Alter system switch logfile;

Sistema alterado.

SQL> Alter system switch logfile;

Sistema alterado.

SQL> Alter system switch logfile;

Sistema alterado.

2. Identificando arquivos de log

Substitua a consulta abaixo com os horários de inicio e término do período onde você acredita que ocorreram os comandos que você esta buscando, neste exemplo vamos utilizar os horários das consultas listadas acima.

SQL> select NAME, to_char(FIRST_TIME,'dd-mm-yyyy hh24:mi:ss') FIRST_TIME
2 from V$ARCHIVED_LOG
3 where first_time > to_date('15-06-2010 14:13:39','dd-mm-yyyy hh24:mi:ss')
4 and first_time < to_date('15-06-2010 14:15:56','dd-mm-yyyy hh24:mi:ss');

NAME FIRST_TIME
------------------------------------------------------------------------------------------------------------------------ -------------------
/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_148_61hf8pbd_.arc 15-06-2010 14:15:01
/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_149_61hf9qd1_.arc 15-06-2010 14:15:02
/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_150_61hfbdl4_.arc 15-06-2010 14:15:35

3 linhas selecionadas.

3. Adicionando arquivos de log ao LogMiner

SQL> conn sys/oracle@lab1 as sysdba;
Conectado.
SQL> BEGIN
2 DBMS_LOGMNR.add_logfile (
3 options => DBMS_LOGMNR.new,
4 logfilename => '/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_148_61hf8pbd_.arc');
5 DBMS_LOGMNR.add_logfile (
6 options => DBMS_LOGMNR.addfile,
7 logfilename => '/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_149_61hf9qd1_.arc');
8 DBMS_LOGMNR.add_logfile (
9 options => DBMS_LOGMNR.addfile,
10 logfilename => '/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_150_61hfbdl4_.arc');
11 END;
12 /

Procedimento PL/SQL concluído com sucesso.

4. Iniciando o Logminer

SQL> BEGIN
2 DBMS_LOGMNR.START_LOGMNR(
3 options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
4 END;
5 /

Procedimento PL/SQL concluído com sucesso.

5. Pesquisando informações desejadas

Depois que o LogMiner é iniciado, o conteúdo dos arquivos de log podem ser consultados usando as seguintes VIEWS:

* V$LOGMNR_DICTIONARY - O arquivo de Dicionário de dados em uso.
* V$LOGMNR_PARAMETERS - Parametros definidos para o LogMiner.
* V$LOGMNR_LOGS - Quais arquivos de log estão sendo analisadas.
* V$LOGMNR_CONTENTS - O conteúdo dos arquivos de redo log que estão sendo analisados.

A seguir uma consulta que lista os comandos dos usuários durante um período analisado:

SQL> col SEG_OWNER for a15;
SQL> col SEG_NAME for a60;
SQL> col USERNAME for a15;
SQL> col OPERATION for a15;
SQL> select to_char(TIMESTAMP,'dd-mm-yyyy hh24:mi:ss') TIMESTAMP, SEG_OWNER, SEG_NAME, USERNAME, OPERATION, SESSION_INFO
2 from V$LOGMNR_CONTENTS
3 where SEG_OWNER not in ('SYS','SYSTEM','UNKNOWN')
4 and USERNAME <> 'UNKNOWN';

TIMESTAMP SEG_OWN SEG_NAME USERNAMEOPERATION
----------------------------------------------------------------------------------------------------------------------------
SESSION_INFO
----------------------------------------------------------------------------------------------------------------------------------------------------------
15-06-2010 14:15:02HR BIN$iRTAtNfUPxDgQKjABwtMvA==$0 TSTUSER DELETE
login_username=TSTUSER client_info= OS_username=Valter Machine_name=WORKGROUP\VALTER
.
.
.
.
login_username=TSTUSER client_info= OS_username=Valter Machine_name=WORKGROUP\VALTER
15-06-2010 14:15:02HR BIN$iRTAtNfUPxDgQKjABwtMvA==$0 TSTUSER DELETE
login_username=TSTUSER client_info= OS_username=Valter Machine_name=WORKGROUP\VALTER
15-06-2010 14:15:32HR DB_OBJETOS TSTUSER DDL
login_username=TSTUSER client_info= OS_username=Valter Machine_name=WORKGROUP\VALTER
15-06-2010 14:15:32HR DB_OBJETOS TSTUSER DDL
login_username=TSTUSER client_info= OS_username=Valter Machine_name=WORKGROUP\VALTER

89 linhas selecionadas.


Tambem podemos utilizar a consulta a seguir para listar os comandos emitidos e seus respectivos comandos para reverter as alterações realizadas.

SELECT scn, operation, sql_redo, sql_undo FROM v$logmnr_contents;

6. Encerrando o LogMiner

SQL> BEGIN
2 DBMS_LOGMNR.END_LOGMNR();
3 END;
4 /

Procedimento PL/SQL concluído com sucesso.