Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


sábado, 29 de julho de 2017

LogMiner - Analisando o conteúdo de um ARCHIVED REDO LOG através da package DBMS_LOGMNR

Por Eduardo Legatti

Olá,

Quando precisamos visualizar o conteúdo dos arquivos de redo log, utilizamos um recurso presente no banco de dados Oracle chamado LogMiner. O objetivo deste artigo é apresentar de forma simples como podemos visualizar o conteúdo de um ou mais arquivos de redo log arquivados através da package DBMS_LOGMNR.

Para facilitar a visualização física dos archived logs gerados, irei alterar o destino da criação dos mesmos conforme abaixo.

SQL> alter system set log_archive_dest_1='LOCATION=/oradata/archivelog';

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/archivelog
Oldest online log sequence     49
Next log sequence to archive   51
Current log sequence           51

De acordo com a documentação, existem dois tipos de logs sumplementares (supplemental logging) que podem ser habilitados no banco de dados:

Database-Level Supplemental Logging

There are two types of database-level supplemental logging: minimal supplemental logging and identification key logging, as described in the following sections. Minimal supplemental logging does not impose significant overhead on the database generating the redo log files. However, enabling database-wide identification key logging can impose overhead on the database generating the redo log files. Oracle recommends that you at least enable minimal supplemental logging for LogMiner.

Minimal Supplemental Logging

Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables.

Por enquanto não existe nenhum tipo de log suplementar ativado para os arquivos de redo log conforme demonstrado abaixo.

SQL> select
  2  supplemental_log_data_min,
  3  supplemental_log_data_pk,
  4  supplemental_log_data_ui
  5  from v$database;

SUPPLEME SUP SUP
-------- --- ---
NO       NO  NO

O próximo passo é simular algumas operações no banco de dados conforme a seguir.

SQL> create table scott.emp (id number, data date);

Table created.

SQL> insert into scott.emp values (1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (2,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (3,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (4,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (5,sysdate);

1 row created.

SQL> rollback;

Rollback complete.

SQL> insert into scott.emp values (6,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

A seguir irei habilitar o log suplementar mínimo (Minimal Supplemental Logging) que não causa nenhuma overhead e realizar mais algumas operações no banco de dados.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> select
  2  supplemental_log_data_min,
  3  supplemental_log_data_pk,
  4  supplemental_log_data_ui
  5  from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO

SQL> insert into scott.emp values (7,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (8,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into scott.emp values (9,sysdate);

1 row created.

SQL> rollback;

Rollback complete.

SQL> insert into scott.emp values (10,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

Após a execução dos comandos acima, os seguintes arquivos de redo log arquivados abaixo foram gerados.

[oracle@linux1 /]$ ls -lh /oradata/archivelog/
total 7,0M
-rw-r----- 1 oracle oinstall 2,9M Jul 29 12:55 1_51_804786092.dbf
-rw-r----- 1 oracle oinstall 1,9M Jul 29 13:26 1_52_804786092.dbf
-rw-r----- 1 oracle oinstall 1,0K Jul 29 13:26 1_53_804786092.dbf
-rw-r----- 1 oracle oinstall 2,2M Jul 29 14:16 1_54_804786092.dbf
-rw-r----- 1 oracle oinstall 111K Jul 29 14:24 1_55_804786092.dbf

SQL> select name,sequence#,first_time,next_time,completion_time
  2  from v$archived_log
  3  where name like '%1_5__804786092.dbf%';

NAME                                    SEQUENCE# FIRST_TIME          NEXT_TIME           COMPLETION_TIME
--------------------------------------- ---------- ------------------- ------------------- -------------------
/oradata/archivelog/1_51_804786092.dbf          51 29/07/2017 12:01:48 29/07/2017 12:55:52 29/07/2017 12:55:53
/oradata/archivelog/1_52_804786092.dbf          52 29/07/2017 12:55:52 29/07/2017 13:26:18 29/07/2017 13:26:19
/oradata/archivelog/1_53_804786092.dbf          53 29/07/2017 13:26:18 29/07/2017 13:26:26 29/07/2017 13:26:27
/oradata/archivelog/1_54_804786092.dbf          54 29/07/2017 13:26:26 29/07/2017 14:16:33 29/07/2017 14:16:33
/oradata/archivelog/1_55_804786092.dbf          55 29/07/2017 14:16:33 29/07/2017 14:24:57 29/07/2017 14:24:57

Como demonstrado acima, foi verificado que foram gerados arquivos de redo log arquivados da sequencia 51 à 55. Portanto, irei analisar o conteúdo dos mesmos com a package DBMS_LOGMNR conforme demonstrado abaixo.

SQL> BEGIN
  2   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_51_804786092.dbf',OPTIONS => DBMS_LOGMNR.NEW);
  3   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_52_804786092.dbf');
  4   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_53_804786092.dbf');
  5   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_54_804786092.dbf');
  6   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_55_804786092.dbf');
  7   DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
  8  END;
  9  /

PL/SQL procedure successfully completed.

Depois de adicionar os arquivos de redo log arquivados acima, irei visualizar o conteúdo dos mesmos através da view V$LOGMNR_CONTENTS.

SQL> SELECT rbasqn,
  2         timestamp,
  3         operation,
  4         seg_owner,
  5         table_name,
  6         table_space,
  7         username,
  8         os_username,
  9         machine_name,
 10         session#,
 11         serial#,
 12         session_info,
 13         sql_redo,
 14         sql_undo
 15  FROM v$logmnr_contents
 16  WHERE seg_owner IN ('SCOTT')
 17  ORDER BY timestamp;

    RBASQN TIMESTAMP           OPERATION      SEG_OWNER    TABLE_NAME   TABLE_SPACE  USERNAME   OS_USERNAME   MACHINE_NAME        SESSION#    SERIAL# SESSION_INFO                                                                                                                                                                      SQL_REDO                                                                                                      SQL_UNDO
---------- ------------------- -------------- ------------ ------------ ------------ ---------- ------------- ------------------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------
        51 29/07/2017 12:11:31 DDL            SCOTT        EMP                       UNKNOWN    UNKNOWN       UNKNOWN                      0          0 UNKNOWN                                                                                                                                                                           create table scott.emp (id number, data date);
        51 29/07/2017 12:12:23 INSERT         SCOTT        EMP          USERS        UNKNOWN    UNKNOWN       UNKNOWN                      0          0 UNKNOWN                                                                                                                                                                           insert into "SCOTT"."EMP"("ID","DATA") values ('1',TO_DATE('19/07/2017 12:12:18', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '1' and "DATA" = TO_DATE('19/07/2017 12:12:18', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAA';
        51 29/07/2017 12:35:19 DELETE         SCOTT        EMP          USERS        UNKNOWN    UNKNOWN       UNKNOWN                      0          0 UNKNOWN                                                                                                                                                                           delete from "SCOTT"."EMP" where ROWID = 'AAADZtAAEAAAACcAAE';
        54 29/07/2017 14:15:37 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('7',TO_DATE('19/07/2017 14:15:34', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '7' and "DATA" = TO_DATE('19/07/2017 14:15:34', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAF';
        54 29/07/2017 14:16:19 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('8',TO_DATE('19/07/2017 14:16:19', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '8' and "DATA" = TO_DATE('19/07/2017 14:16:19', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAG';
        55 29/07/2017 14:22:06 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('9',TO_DATE('19/07/2017 14:22:04', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '9' and "DATA" = TO_DATE('19/07/2017 14:22:04', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAH';
        55 29/07/2017 14:22:08 DELETE         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   delete from "SCOTT"."EMP" where ROWID = 'AAADZtAAEAAAACcAAH';
        55 29/07/2017 14:24:42 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('10',TO_DATE('19/07/2017 14:24:41', 'dd/mm/yyyy hh24:mi:ss')); delete from "SCOTT"."EMP" where "ID" = '10' and "DATA" = TO_DATE('19/07/2017 14:24:41', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAH';

8 linhas selecionadas.

Pelo resultado acima é possível perceber que as instruções realizadas antes de habilitar o log suplementar mínimo não estão completas ou ausentes e que somente após habilitar o log suplementar mínimo é que as informações e instruções SQL executadas foram logadas de forma mais completa.

Por fim, apra desabilitar o recurso LogMiner, basta apenas executar o comando abaixo.

SQL> exec DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.



Nenhum comentário:

Postagens populares