Oracle Database LogMiner – …::: César DBA :::…

Pessoal segue abaixo como configurar e testar uma feature bem legal do Oracle Database.

Verificando se Archive Mode esta habilitado:

SQL> select log_mode from v$database;
 
LOG_MODE
------------
ARCHIVELOG
 
SQL>

SQL

Habilitando a feature:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL

Verificando se a feature esta habilitada:

SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database; 
 
SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO

SQL

Gerando informações de update de delete:

SQL> INSERT INTO CESAR.tb_teste values ('3');
 
1 row created.
 
SQL> INSERT INTO CESAR.tb_teste values ('4');
 
1 row created.
 
SQL> INSERT INTO CESAR.tb_teste values ('5');
 
1 row created.
 
SQL> commit;
 
SQL> INSERT INTO CESAR.tb_teste values ('CESAR');
 
1 row created.
 
SQL> commit;
 
Commit complete.

SQL

Verificando informações:

SQL> SELECT * FROM CESAR.tb_teste;
 
NOME
--------------------------------------------------
1
3
4
5
CESAR

SQL

Efetuando delete:

SQL> DELETE FROM CESAR.tb_teste WHERE NOME LIKE '%CESAR%';
 
1 row deleted.
 
SQL> commit;
 
Commit complete.

SQL

Verificando informações:

SQL> SELECT * FROM CESAR.tb_teste;
 
NOME
--------------------------------------------------
1
3
4
5

SQL

Forçar a geração do archive:

SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.

SQL

Verificando os archives gerados no periodo de tempo especificado:

SELECT L.NAME,
       L.NEXT_TIME
FROM V$ARCHIVED_LOG L
WHERE TO_CHAR(L.NEXT_TIME, 'yyyy-mm-dd hh24:mi') >= '2017-07-19 06:00'
ORDER BY L.NAME;
 
NAME
--------------------------------------------------
/u02/backup/archivelog/1_25_940605227.arc
/u02/backup/archivelog/1_26_940605227.arc
/u02/backup/archivelog/1_27_940605227.arc
/u02/backup/archivelog/1_28_940605227.arc
/u02/backup/archivelog/1_29_940605227.arc
/u02/backup/archivelog/1_30_940605227.arc

SQL

Habilitando a leitura dos archives pelo logminer:

BEGIN
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/backup/archivelog/1_25_940605227.arc',OPTIONS => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/backup/archivelog/1_26_940605227.arc');
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/backup/archivelog/1_27_940605227.arc');
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/backup/archivelog/1_28_940605227.arc');
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/backup/archivelog/1_29_940605227.arc');
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/backup/archivelog/1_30_940605227.arc');
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
END;

SQL

Usando o logminer:

SELECT  START_TIMESTAMP,
        TIMESTAMP,
        SEG_OWNER,
        SEG_NAME,
        TABLE_NAME,
        USERNAME,
        OS_USERNAME,
        MACHINE_NAME,
        SQL_REDO,
        STATUS
FROM V$LOGMNR_CONTENTS WHERE SQL_REDO IS NOT NULL 
AND SEG_OWNER LIKE 'CESAR';

SQL

Parando a leitura logminer:

BEGIN
  DBMS_LOGMNR.END_LOGMNR();
END;

SQL

Referência

Leave a Reply Cancel reply