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:
Postar um comentário