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


quinta-feira, 10 de maio de 2007

Utilizando o Flashback Query a partir do Oracle 9i

Por Eduardo Legatti

Olá,

Em tempos de Oracle 10g na qual o mesmo implementa várias tecnologias de Flashback como Flashback Database, Flashback Table, Flashback Drop entre outros, abordarei o uso do recurso de Flashback Query para administradores de banco de dados Oracle 9i.

Introduzido com o Oracle 9i, este recurso fornece a habilidade de visualizar os dados como eles estavam em um determinado tempo no passado. Por padrão, operações no banco de dados usam os dados disponíveis mais recentemente "comitados". Se você quiser pesquisar determinados dados em algum ponto no passado, você precisará utilizar o recurso de Flashback Query na qual será necessário especificar um "horário" ou um SCN (System change Number) para efetuar a pesquisa.

Este recurso também é muito útil, quando você precisa restaurar dados que foram erroneamente deletados ou alterados. Antes da versão Oracle 9i, você tinha que recuperar o banco de dados até um determinado ponto que lhe interessasse. Dependendo do tamanho do banco de dados, este processo poderia ser lento e demorado.

Antes de mais nada, para você poder usar o recurso de Flashback Query, é necessário configurar o seu banco de dados para usar o gerenciamento automático de UNDO (Automatic Undo Management).

Portando, verifique se o seu banco de dados já está com esta configuração setada, caso contrário, altere o parâmetro com o valor e privilégios apropriados.

SYSTEM> show parameter undo_management

NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
undo_management            string      AUTO


Depois, verifique o parâmetro undo_retention, na qual especifica quanto tempo os blocos de dados Oracle alterados ficarão disponíveis no segmento de undo até serem sobregravados, e certifique-se que o tablespace de UNDO é grande o suficiente para que alterações realizadas no banco de dados geradas durante o período não sejam sobrescritas.

SYSTEM@teste> show parameter undo_retention

NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
undo_retention             integer     10800


No parâmetro acima, o valor é especificado (em segundos) e no meu caso eu coloquei o valor 10800 que significa 180 minutos ou 3 horas. O valor padrão para este parâmetro é 900 o que significa 15 minutos de retenção de dados de undo.


Criarei um usuário no banco de dados para exemplificar o uso do recurso de Flashback Query no Oracle 9i.

C:\>sqlplus system/*****

SQL*Plus:  Release 10.1.0.2.0 - Production on Ter Nov 7 11:38:35 2006

Copyright  (c) 1982, 2004, Oracle.  All rights  reserved.

Conectado  a:
Oracle9i  Enterprise Edition Release 9.2.0.4.0 - Production
With the  Partitioning, OLAP and Oracle Data Mining options
JServer  Release 9.2.0.4.0 - Production

SYSTEM> create user legatti identified by legatti
      2 default tablespace USERS
      3 quota unlimited on USERS; 

Usuário criado.

SYSTEM> grant connect to legatti;

Concessão bem-sucedida.
 
Agora, darei o privilégio de EXECUTE no pacote dbms_flashback para o usuário que realizará o flashback.

-- Conexão realizada como sys (sysdba)

SYSTEM> connect / as sysdba

Conectado.

SYS> grant execute on dbms_flashback to legatti;

Concessão bem-sucedida.

-- Conectarei com o usuário criado anteriormente e criarei uma tabela de exemplo

SYS> connect legatti/legatti

Conectado.

LEGATTI> create table produto (
       2 cod  number primary key,
       3 data date,
       4 descricao varchar2 (100));

Tabela criada.

-- Inserir dados para teste

LEGATTI> INSERT INTO produto VALUES (101,SYSDATE,'Computador');

1 linha criada.

LEGATTI> INSERT INTO produto VALUES (102,SYSDATE,'Laptop');

1 linha criada.

LEGATTI> INSERT INTO produto VALUES (103,SYSDATE,'Impressora');

1 linha criada.

LEGATTI> INSERT INTO produto VALUES (104,SYSDATE,'Monitor');

1 linha  criada.

LEGATTI> INSERT INTO produto VALUES (105,SYSDATE,'Mouse');

1 linha criada.

-- Commit das alterações

LEGATTI> commit;

Validação completa.

-- Irei selecionar os dados da tabela

LEGATTI> select cod, to_char(data,'dd/mm/yyyy hh24:mi:ss') data,
       2 descricao from produto;

  COD DATA                DESCRICAO
----- ------------------- -------------------------
  101 07/08/2006 10:41:15 Computador
  102 07/08/2006 10:41:15 Laptop
  103 07/08/2006 10:41:15 Impressora
  104 07/08/2006 10:41:15 Monitor
  105 07/08/2006 10:41:15 Mouse

5 linhas selecionadas.

-- Criarei uma nova linha na tabela

LEGATTI> INSERT INTO produto VALUES (106,SYSDATE,'Teclado');

1 linha criada.

-- Commit das alterações

LEGATTI> commit;

Validação completa.

-- Irei selecionar os dados da tabela

LEGATTI> select cod, to_char(data,'dd/mm/yyyy hh24:mi:ss') data,
       2 descricao from produto;

  COD DATA                DESCRICAO
----- ------------------- -------------------------
  101 07/08/2006 10:41:15 Computador
  102 07/08/2006 10:41:15 Laptop
  103 07/08/2006 10:41:15 Impressora
  104 07/08/2006 10:41:15 Monitor
  105 07/08/2006 10:41:15 Mouse
  106 07/08/2006 10:52:06 Teclado

6 linhas selecionadas.
 
Vou dizer ao Oracle que quero ver apenas os registros anteriores ao último registro, que no caso foi o produto de código 106.

LEGATTI> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME
      2 (TO_TIMESTAMP('07/08/2006  10:52:06','DD/MM/YYYY:HH24:MI:SS'));

Procedimento PL/SQL concluído com sucesso.

-- Irei selecionar os dados da tabela

LEGATTI> select * from produto;

  COD DATA       DESCRICAO
----- ---------- --------------------------
  101 07/08/2006 Computador
  102 07/08/2006 Laptop
  103 07/08/2006 Impressora
  104 07/08/2006 Monitor
  105 07/08/2006 Mouse

5 linhas selecionadas.
 
Neste momento, percebemos que o produto teclado não foi selecionado, pelo fato de o mesmo ter sido inserido após às 10:52:05. Abaixo irei desabilitar o recurso de FLASHBACK.

LEGATTI> EXECUTE DBMS_FLASHBACK.DISABLE;

Procedimento PL/SQL concluído com sucesso.

-- Irei selecionar os dados da tabela

LEGATTI> select * from produto;

  COD DATA       DESCRICAO
----- ---------- -------------------------
  101 07/08/2006 Computador
  102 07/08/2006 Laptop
  103 07/08/2006 Impressora
  104 07/08/2006 Monitor
  105 07/08/2006 Mouse
  106 07/08/2006 Teclado

6 linhas selecionadas. 
 
Vale a pena salientar que poderemos usar também o recurso "AS OF" que é uma forma mais simplificada.

LEGATTI> SELECT * FROM produto
       2 AS OF TIMESTAMP TO_TIMESTAMP('07/08/2006 10:52:06','DD/MM/YYYY:HH24:MI:SS'));

  COD DATA       DESCRICAO
----- ---------- --------------------------
  101 07/08/2006 Computador
  102 07/08/2006 Laptop
  103 07/08/2006 Impressora
  104 07/08/2006 Monitor
  105 07/08/2006 Mouse

6 linhas selecionadas.

Google+

5 comentários:

Ricardo disse...

Excelente!! Me safou de uma roubada!! Valeu! Parabéns!!

Anônimo disse...

Olá Eduardo Parabéns pela documentação.Muito bom. Mas tenho uma dúvida. Então quer dizer que o pacote dbms_flashback so server para visualizar o dados no passado assim como o select AS OF? o Pacote dbms_flashback não recupera apenas emitindo o comando EXECUTE DBMS_FALSHBACK?

Eduardo Legatti disse...

Olá Anônimo,

Exatamente. Não faça confusão com o recurso Flashback Database. O recurso de Flashback query lê os dados que estão nos segmentos de UNDO, ... é por isso que é possível ver os dados em um determinado ponto no passado. Já o recurso Flashback Database usa outros tipos de arquivos chamados de flashback logs que normalmente ficam localizados na FRA (Flash Recovery Area) ... e para utilizar este recurso, o banco precisa estar configurado no modo ARCHIVELOG e estar com o Flashback Logging habilitado (ALTER DATABASE FLASHBACK ON). Abaixo, com o procedimento DBMS_FLASHBACK.ENABLE_AT_TIME você verá que eu consigo ver os dados em um determinado ponto no passado, mas eu não consiguirei modificá-los. Já com o comando SELECT ... AS OF TIMESTAMP TO_TIMESTAMP() ..., eu consigo o mesmo resultado, mas com a diferença de poder manipulá-los de alguma forma.

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
------------------------------------------------
09/02/2009 17:20:49,295372

SQL> create table x (id number);

Tabela criada.

SQL> insert into x select level from dual connect by level <=10;

10 linhas criadas.

SQL> commit;

Validação completa.

SQL> select * from x;

ID
----------
1
2
3
4
5
6
7
8
9
10

10 linhas selecionadas.


SQL> select localtimestamp from dual;

LOCALTIMESTAMP
------------------------------------------------
09/02/2009 17:21:41,856968

SQL> update x set id=100;

10 linhas atualizadas.

SQL> commit;

Validação completa.

SQL> select * from x;

ID
----------
100
100
100
100
100
100
100
100
100
100

10 linhas selecionadas.


SQL> select localtimestamp from dual;

LOCALTIMESTAMP
------------------------------------------------
09/02/2009 17:22:32,776585

SQL> exec dbms_flashback.enable_at_time(to_timestamp('09/02/2009 17:22:10','dd/mm/yyyy hh24:mi:ss'));

Procedimento PL/SQL concluído com sucesso.

SQL> select * from x;

ID
----------
1
2
3
4
5
6
7
8
9
10

10 linhas selecionadas.

SQL> commit;

Validação completa.

SQL> delete from x;
delete from x
*
ERRO na linha 1:
ORA-08182: operação não suportada no modo Flashback


SQL> create table t1 as select * from x;
create table xxx as select * from x
*
ERRO na linha 1:
ORA-08182: operação não suportada no modo Flashback


SQL> create table t1 as select * from x as of timestamp to_timestamp('09/02/2009 17:22:10','dd/mm/yyyy hh24:mi:ss');

Tabela criada.

SQL> select * from t1;

ID
----------
1
2
3
4
5
6
7
8
9
10

10 linhas selecionadas.

SQL> select * from x;

ID
----------
100
100
100
100
100
100
100
100
100
100

10 linhas selecionadas.


Até mais ...

Anônimo disse...

Eduardo, aqui é o Norberto.
Seguinte e caso for várias tabelas?, neste caso como podemos utilizar o flashback query ?

Eduardo Legatti disse...

Olá Norberto,

Sim. O flashback query confia nos dados que estão atualmente nos segmentos de UNDO, independente da tabela, etc... Vale a pena salientar que o sucesso ou não da execução da sua consulta irá depender do valor que foi configurado no parâmetro undo_retention, que especifica quanto tempo os blocos de dados alterados ficarão disponíveis no segmento de undo até serem sobregravados.

Abraços e até mais...

Postagens populares