Páginas

19 de dez. de 2012

Entendendo e recuperando dados com Flashback

Olá pessoal,

     Conforme prometido no artigo Configurando o UNDO Tablespace que eu escrevi recentemente neste blog, irei explicar neste artigo o que é Flashback e como utilizá-lo para efetuar recuperações de dados mais rápidas e mais simples. Com Flashback você pode reduzir o tempo de recuperação de dados de horas para minutos.
  
     Flashback é uma tecnologia que pode ser utilizada para efetuar apenas recuperações lógicas, portanto, ela não substitui as estratégias de backup/recover tradicionais, que permitem além de recuperações lógicas, efetuar recuperações físicas. Agora vem uma pergunta, qual a diferença entre recuperações lógicas e físicas? Ou melhor... o que é uma perda de dados lógica e o que é uma perda de dados física?

     Basicamente, podemos entender como perda de dados física, o exemplo de um corrompimento dos arquivos de dados de um Banco de Dados, decorrente de um problema físico em disco. Quando um arquivo está corrompido, precisamos restaurar uma cópia deste arquivo a partir de um backup.

     Uma perda de dados lógica é uma perda de dados em que o arquivo de dados não está corrompido ou não sofreu danos. Uma perda lógica pode ser ocasionada por uma falha humana, decorrente de uma instrução SQL executada acidentalmente. Exemplo: Ao tentar apagar uma linha de uma tabela, o desenvolvedor esqueceu de implementar a cláusula WHERE ao executar uma instrução DELETE e todos os dados da tabela foram apagados. Para piorar a situação, a transação já foi finalizada com um COMMIT. Em cenários semelhantes a este é que podemos utilizar Flashback.
  
     Na versão 10G do Oracle Database existiam 5 tipos de tecnologia Flashback: Flashback DatabaseFlashback DropFlashback TableFlashback QueryFlashback Versions Query. Na versão 11G foram acrescentadas mais 3:  Flashback Data Archive, Flashback Transaction e Flashback Transaction Query. Segue abaixo um quadro  (Quadro 01) contendo um resumo sobre as tecnologias Flashback existentes no Oracle 11G. O quadro apresenta as seguintes informações:
         - Tecnologia Flashback: nome da tecnologia Flashback;
         - Nível de objeto: nível de objeto em que a tecnologia atua;
         - Descrição/ Exemplos de cenários de utilização: Descrição sobre a tecnologia e exemplos de perdas de dados em que a tecnologia pode ser aplicada.

Tecnologia Flashback
Nível de objeto
Descrição / Exemplos de cenários de utilização
Database
Banco de Dados
Permite restaurar o BD para um específico ponto no tempo, usando Flashback logs.

Pode ser usada p/ desfazer TRUNCATE TABLE ou alterações DDL.
Drop
Tabela
Permite recuperar tabelas inteiras de uma espécie de lixeira, conhecida como recycle bin.

Pode ser usada para recuperar uma tabela apagada acidentalmente (sem a cláusula PURGE).
Table
Tabela
Permite recuperar tabelas inteiras para um específico ponto no tempo, usando dados do UNDO Tablespace.

Pode ser usada para desfazer uma operação de UPDATE ou DELETE acidental.
Query
Tabela
Permite pesquisar dados em algum ponto no passado, usando dados do UNDO Tablespace.

Pode ser usada para pesquisar dados que foram apagados ou alterados acidentalmente.
Versions Query
Tabela
Permite pesquisar diferentes versões de uma linha em um específico intervalo de tempo (ao invés de um único ponto no tempo), usando dados do UNDO Tablespace.

Pode ser usada para comparar versões anteriores, apagadas ou alteradas, de linhas de uma tabela.
Data Archive
Tabela
Permite manter e pesquisar dados históricos de uma tabela, por um período pré-configurado, usando um arquivo próprio, totalmente independente de dados de UNDO.

Pode ser usada para pesquisar dados históricos e versões anteriores de linhas de uma tabela.
Transaction
Transação
Permite investigar e desfazer uma transação e opcionalmente, as transações dependentes, usando dados do UNDO Tablespace.

Pode ser usada para desfazer todos os passos de uma transação executada acidentalmente.
Transaction  Query
Transação
Permite pesquisar dados de todas as mudanças afetadas por uma transação.

Pode ser usada para pesquisar dados que foram apagados ou alterados acidentalmente por uma transação.
Quadro 01- Tecnologias Flashback existentes no Oracle Database 11GR2

     Há muito o que falar sobre todas as tecnologias Flashback e isso é assunto para vários artigos. A idéia principal aqui, é passar uma visão geral sobre o assunto e demonstrar apenas uma dessas tecnologias, que eu acho muito útil e simples de usar, e que pode ajudar muitos desenvolvedores ou DBAs, que precisam voltar o estado anterior de dados alterados por uma instrução SQL acidental ou por uma alteração indevida de dados via sistema: a tecnologia Flashback Versions Query (ver imagem 01).

Imagem 01 - Representação gráfica de como a tecnologia Flash Version Query pode ser utilizada

     Flashback Versions Query pode ser utilizada para pesquisar dados que foram alterados ou apagados de uma tabela, e que ainda estão no UNDO tablespace. Para quem não sabe o que é ou para que serve o UNDO tablespace, e como configurá-lo adequadamente para usar Flashback, recomendo a leitura prévia do artigo Configurando o UNDO Tablespace.
 
     Segue abaixo um passo-a-passo que pode ser executado em qualquer Banco de Dados que tenha o schema HR instalado (ver artigo Instalando o schema de exemplo HR), por qualquer usuário que tenha apenas os privilégios de SELECT e UPDATE + privilégio FLASHBACK, na tabela HR.EMPLOYEES .

--------------------------------------------------------------------
Recuperando dados através de Flashback Versions Query
--------------------------------------------------------------------

Passo 1: Pesquisando valor atual de salário do empregado 107:
     Execute a instrução SQL abaixo para pesquisar o salário atual do empregado 107:

     SELECT  SALARY   
     FROM     HR.EMPLOYEES
     WHERE   employee_id = 107;

Resultado:
SALARY
----------
33600 
  
  
Passo 2: Atualizando o salário do empregado 107:
     Execute a instrução SQL abaixo para triplicar e atualizar o salário do empregado 107:

     UPDATE  HR.EMPLOYEES
     SET           SALARY = SALARY * 3
     WHERE   EMPLOYEE_ID = 107;
     COMMIT;

Resultado:
1 linhas atualizado.
submetido a commit.
   

Passo 3: Atualizando novamente o salário do empregado 107:
     Supondo que na instrução SQL do passo anterior você executou uma atualização indevida, pois a atualização correta deveria apenas ter dobrado o salário do empregado 107, execute a instrução SQL abaixo para corrigir os valores:

     UPDATE  HR.EMPLOYEES 
     SET           SALARY = SALARY / 3 * 2
     WHERE   EMPLOYEE_ID = 107;
     COMMIT;

Resultado:
1 linhas atualizado.
submetido a commit.

      
Passo 4: Consultando todas as alterações de salário do empregado 107:
  Neste momento, chegou um novo pedido do RH para dar um aumento de salário de somente 30% para o empregado 107. Para dar este aumento você precisa descobrir qual era o valor do salário antes das últimas alterações. Para identificar este valor, basta olhar o resultado da consulta abaixo, que utiliza Flashback Versions Query para consultar no UNDO Tablespace todas as versões de linhas (anteriores e atual) do empregado 107:
     SELECT     to_char(VERSIONS_STARTTIME, 'dd/mm/yyyy hh24:mi:ss') as v_start,
                         to_char(VERSIONS_ENDTIME, 'dd/mm/yyyy hh24:mi:ss') as v_end,          
                         VERSIONS_OPERATION as v_op,   
                         EMPLOYEE_ID, 
                         SALARY, 
                         VERSIONS_ENDSCN as v_scn
     FROM        HR.EMPLOYEES 
                         VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
     WHERE     EMPLOYEE_ID = 107
     ORDER  BY VERSIONS_ENDTIME;

Resultado:
V_START                  V_END                   V_OP  EMPLOYEE_ID   SALARY       V_SCN
----------------------- -------------------------- ------- -------------------   -------------   ---------------
                                 18/12/2012 21:12:42              107                          33600   96377080437
18/12/2012 21:12:42   18/12/2012 19:13:06      U       107                        100800   96377080446
18/12/2012 21:13:06                                      U       107                         67200     

 De acordo com o resultado acima, o valor inicial do salário era 33600. Para dar o aumento correto (último valor informado de 30%), podemos realizar um novo UPDATE de diversas formas. Segue abaixo um exemplo, que irá fazer a nova atualização pegando o valor de salário da versão mais antiga, que teve seu valor alterado (coluna V_END ) em 18/12/2012 21:12:42:

     UPDATE  HR.EMPLOYEES 
     SET           SALARY = (SELECT SALARY FROM HR.EMPLOYEES
                                             WHERE  VERSIONS_ENDSCN  = 96377080437)  * 1.3
     WHERE   EMPLOYEE_ID = 107;
     COMMIT;



   Bom pessoal, agora que já corrigimos o valor, encerro por aqui o artigo!
   Espero que tenham gostado! Qualquer dúvida, é só deixar um comentário!

[]s


Referências:
     -  Oracle Flashback Technologies
     - Treinamento oficial Oracle Database 11g: Administration Workshop II Ed 2

9 comentários:

  1. Parabéns Fabio, EXCELENTES ARTIGOS, tanto o Entendendo e recuperando dados com Flashback como o Configurando o UNDO Tablespace. Obrigado ...

    ResponderExcluir
  2. Olá, gostei muito do seu artigo, inclusive li também o outro mencionado sobre o undo tablespace. Acontece que estou tentando testar um flashback diferente, que seria a view flashback_transaction_query, que armazenaria dados dos commit, inclusive a coluna undo_sql com uma query que reverteria o que o commit fez, poderia localizar através do XID, que podemos ver qual é o XID através da pseudolumn em uma flashback version query. Mas acontece que não estou conseguindo reproduzir nada no meu ambientes de testes, essa view sempre fica vazia, nunca é armazenado nada nela. Já aumentei o meu UNDO_RETENTION, coloquei o tablespace como RETENTION GUARANTEE. Já habilitei o temp_undo_enabled, coloquei o database no modo ARCHIVELOGe fiz as seguintes alterações: alter database add suplemental log data;
    alter database add suplemental log data (primary key) columns;
    O meu banco é a versão enterprise 12c. Poderia me ajudar?

    ResponderExcluir
    Respostas
    1. Tércio, primeiramente obrigado pelos comentários!
      O que você está tentando fazer provavelmente é utilizar Flashback Transaction Query. Eu particularmente nunca precisei dele, portanto, não estou apto a lhe instruir como utilizá-lo. Você começou certo habilitando o log suplementar. Para mais informações sugiro que você procure ajuda no artigo http://www.oracle.com/technetwork/pt/articles/sql/flashback-query-2206449-ptb.html e entre em contato com o autor do artigo, se tiver alguma dúvida, ok?

      []s

      Excluir
    2. Obrigado mais uma vez Fábio Prado!

      Excluir
  3. Pessoal, só tomem cuidado, isso é um recursos licenciado.

    ResponderExcluir
    Respostas
    1. Daniel, de todas as tecnologias de flashback que mencionei aqui a única que exige licenciamento adicional é a "Data Archive".

      Excluir