Páginas

29 de dez. de 2014

Recuperando a versão anterior de objetos PL/SQL (Stored Procedures, Functions etc.)


Olá pessoal,

     No artigo de hoje vou demostrar como recuperar a versão anterior de uma Stored Procedure, Função ou qualquer objeto PL/SQL armazenado em um Banco de Dados Oracle. Antes disso, porém, iremos entender em que situação essa recuperação é útil.

     Ao longo dos meus quase 15 anos de trabalho na área de TI, já vi diversas vezes, Desenvolvedores alterarem um objeto PL/SQL qualquer, como por exemplo, uma Stored Procedure (Procedure ou SP) ou Function (Função) em um Banco de Dados (BD) de Homologação ou Produção (sem entrar nos detalhes do ambiente), onde o objeto inicialmente aparentava não ter problemas, mas passado algum tempo (minutos, horas ou dias), vários erros surgiram na aplicação. Em sala de aula, descobri que muitos alunos também já passaram pelo mesmo problema! Para resolvê-lo, a solução mais rápida para que tudo volte ao normal, é recuperar a versão anterior do objeto PL/SQL no BD, tarefa que pode ser executada através de diversos métodos, entre os quais irei descrever 3 deles abaixo:

     1- Recuperar um backup:
          Este é o método mais trabalhoso e mais demorado! Faça de tudo para evitá-lo, utilize-o somente se as próximas opções não puderem ser aplicadas! Se há um backup disponível com a versão anterior do objeto, o Desenvolvedor pede ao DBA (ou responsável pelo BD) a sua recuperação através de um backup. Esse tipo de recuperação poderá demorar horas e muitas vezes, poderá aborrecer um ou outro DBA... que de certa forma tem alguma razão, pois o bom Desenvolvedor (ou Desenvolvedor mais experiente) teria feito uma cópia do código da versão anterior do objeto e gravado este código em um arquivo texto para posterior recuperação. Isso possibilitaria que o próprio Desenvolvedor fizesse a recuperação de forma rápida e sem gerar trabalho extra para o DBA (veremos isso em mais detalhes no próximo item)

     2- Recuperar o código de um arquivo em um repositório de versionamento de arquivos:
          Este é o metódo que eu mais indico, pois não exige conhecimentos técnicos avançados, e muito rápido e simples de executar! A única preocupação aqui é com a organização do Desenvolvedor! Ele não precisa ter conhecimentos técnicos avançados, mas precisa ser organizado e saber usar preferencialmente alguma ferramenta de versionamento de arquivos.
     Eu fui Desenvolvedor durante 6 anos da minha carreira e tive a sorte de logo no início desta profissão trabalhar em um grande projeto onde atuavam aproximadamente 15 Desenvolvedores. Aprox. metade deles tinham mais conhecimentos e experiência que eu, então pude aprender muito com eles! Essa equipe estava desenvolvendo um CRM para uma empresa multinacional brasileira, com a linguagem Dot Net, utilizando o Microsoft Visual Studio, e BD Oracle, utilizando o TOAD para criar objetos PL/SQL e as instruções SQL. No projeto deste sistema, a Metodologia de Desenvolvimento que utilizávamos incluía o uso do Visual Source Safe  (VSS) para gerenciar o versionamento de todo código-fonte da aplicação, incluindo o código de objetos de BD. 
          Quando a gente tinha problemas na aplicação decorrentes de alterações em objetos PL/SQL, rapidamente a gente começava o processo de "debug", descobria o objeto que estava gerando o erro, e voltávamos a versão anterior deste objeto recuperando o código dele que estava gravado em um arquivo dentro do nosso repositório do VSS. Este para mim, é o cenário perfeito!
     Se você não tem um repositório de versionamento de arquivos como este que eu acabei de mencionar e/ou se você quer implementar um repositório automático de versionamento de código de objetos PL/SQL, leia o artigo Repositório de metadados/Versionamento de objetos no Oracle Database.

     3- Recuperar o código do objeto utilizando Flashback Query:
     Este método poderá ser utilizado somente no Oracle 9i ou versão superior e depende de uma configuração adequada do UNDO Tablespace (ver artigo Configurando o UNDO Tablespace). Utilize-o preferencialmente se você não tiver o repositório mencionado no item 2. Esse método de recuperação depende de um recurso chamado Flashback Query, que apesar de pouca gente conhecê-lo, é bem simples de usar!
     Para que você aprenda a utilizar este método, faremos a recuperação da versão anterior de uma Stored Procedure chamada SP_EMPREGADO_S, que permite consultar dados dos empregados da tabela HR.EMPLOYEES, Segue abaixo o roteiro:

       


RECUPERANDO A VERSÃO ANTERIOR DA PROCEDURE SP_EMPREGADO_S

1- Criando a procedure SP_EMPREGADO_S:
     Nosso primeiro passo é criar a procedure SP_EMPREGADO_S, no schema HR, executando no SQL Developer ou ferramenta similar, o código abaixo:

CREATE OR REPLACE PROCEDURE HR.SP_EMPREGADO_S(P_CR OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN P_CR FOR
    SELECT FIRST_NAME, LAST_NAME FROM HR.EMPLOYEES;  
END;

     Obs.: A procedure acima retorna o primeiro e último nome dos empregados.

2- Alterando a procedure SP_EMPREGADO_S:
     Supondo que os usuários da aplicação pediram uma alteração para incluir na tela de consulta de empregados não mais apenas o nome deles, mas também a data de contratação e e-mail, você precisará alterar a procedure do passo anterior, executando o código abaixo:

CREATE OR REPLACE PROCEDURE HR.SP_EMPREGADO_S(P_CR OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN P_CR FOR
    SELECT  FIRST_NAME, LAST_NAME,
            HIRE_DATE, EMAIL
    FROM    HR.EMPLOYEES;  
END;

     Obs.: Agora a procedure retornará o primeiro e último nome, data de contratação e e-mail dos empregados.     

3- Analisando o problema:
     A procedure do Passo 1 foi criada às 10h do dia 01/01/2014 e foi alterada pelo código do Passo 3 às 15h do dia 10/10/2014. No dia 11/10/2014, por volta das 11h o usuário informou que ocorreram erros na aplicação ao acessar a tela de consulta de empregados. De bate-pronto você desconfia que o problema teve como origem a alteração da procedure e obtém esta confirmação quando verifica no código-fonte da tela de consulta de empregados que ela não estava preparada para receber os novos dados. Para resolver este problema rapidamente, você decide recuperar a versão anterior da procedure e liberar a nova versão somente com a alteração que você ainda irá fazer no código da aplicação.
     
4- Recuperando a versão anterior da procedure SP_EMPREGADO_S:
     Agora são 11:20h do dia 11/10/2014 e você vai começar a tarefa para recuperar a versão anterior da procedure. Como já se passaram 20 horas e 20 minutos desde o momento em que a procedure foi alterada, é necessário verificar se o Undo Tablespace do BD está configurado apropriadamente para permitir essa recuperaçãoSupondo que neste caso há a possibilidade de recuperação, execute a consulta abaixo para recuperar o código anterior da procedure:
     
select    text 
from      dba_source as of timestamp 
              TO_TIMESTAMP('10/10/2014 14:00:00', 'dd/mm/yyyy hh24:mi:ss')
where     name='SP_EMPREGADO_S' 
order by  line;

     A consulta acima na visão dba_source extrai do Undo Tablespace o código da procedure SP_EMPREGADO_S referente ao seu estado em um horário anterior (10/10/2014 14:00:00) ao da alteração que gerou o erro na aplicaçãoVeja na imagem abaixo o resultado desta consulta:


Imagem 01 - Consultando a visão DBA_SOURCE


     Ele é bem parecido com o código do Passo 1. Para recriar a procedure original, que não gera erros na aplicação, basta acrescentar as palavras CREATE OR no início do código resultante da consulta na imagem acima e executá-lo no BD. Pronto, o problema está resolvido!
   
Espero que o artigo seja útil!
Se possível, deixe um feedback através de um comentário. Se tiver dúvidas, também deixe um comentário. Se você gostou, compartilhe este artigo em suas redes sociais!

[]s
    

14 comentários:

  1. Muito legal essa dica. Obrigado pela contribuição.

    ResponderExcluir
  2. Fabio,
    Excelente artigo! Muito bom o conteúdo e super didático! \o/ \o/

    ResponderExcluir
  3. Oi Fábio! Gostei bastante do seu artigo, e gostaria de complementar com uma sugestão que ficaria entre os itens 1 e 2 que você enumerou! É uma configuração tranquila de se fazer e que pode ficar à cargo totalmente do DBA: Usando impdp com a opção SQLFILE.

    Se o DBA possuir o arquivo de dump gerado pelo DataPump com a versão da procedure que ele quer, basta usar o impdp apontando para um SQLFILE para gerar o arquivo SQL de criação do banco. Nesse arquivo teremos os códigos, tais como estavam no momento do dump.

    Outra coisa legal que dá pra fazer é colocar esse impdp num script diário que crie o dump do banco e logo em seguida crie o arquivo de SQL, compacte e mantenha num local seguro. O arquivo de dump pode ser mantido por 24h por segurança ou ser removido logo em seguida.

    Outra opção, caso não haja espaço ou janela para o dump, é usar o expdp com a opção CONTENT=METADATA_ONLY e manter os dumpfiles que serão gerados sem o "recheio". :)

    Eu reconheço que a melhor prática realmente é usar um versionador, mas caso não seja possível o DBA consegue "dar seus pulos" pra garantir que o código SQL do banco de dados vai permanecer disponível em caso de problemas.

    Grande abraço!

    ResponderExcluir
    Respostas
    1. Matheus, primeiramente obrigado pelo comentário.

      Leia o artigo que eu citei no item 2: http://www.fabioprado.net/2012/06/repositorio-de-metadados-no-oracle.html. Lá eu cito este método que vc recomendou e faço algo muito melhor!

      []s

      Excluir
    2. Oi Fábio! Realmente sua implementação é melhor do que manter diversos arquivos separados. Mas, se você me permite, gostaria de deixar uma sugestão: Manter o repositório fora do banco de dados, para o caso de falhas.

      Abraço, e um ótimo 2015!

      Excluir
    3. Matheus, obrigado pela sugestão e ela contribuirá muito com o artigo, pois nesses tipos de discussões as 2 partes sempre aprendem um pouco mais!

      Eu discordo sobre manter o repositório fora do BD. Um backup (físico ou lógico) feito via DataPump ou RMAN, por exemplo, já cumprem esse papel p/ permitir recuperações do BD no caso de falhas. Não quero o repositório fora do BD, pois o papel dele é me ajudar a fazer recuperações da versão anterior dos objetos de forma mais rápida e mais fácil, OK?

      []s

      Excluir
  4. Bacana Fabio ótimo post.
    Já utilizado . Recomendado .

    ResponderExcluir
  5. Tudo bem Prado?
    Você sabe me dizer se da pra usar esse recurso pra recuperar os dados de um scheduler job que foi excluído?

    ResponderExcluir
    Respostas
    1. Dá sim Diego!

      Para Scheduler Jobs ao invés de fazer um select na visão dba_source use a visão dba_scheduler_jobs e informe uma data/hora antes dela ser excluída. Você irá recuperar a linha dela na visão dba_scheduler_jobs que irá conter todos os dados necessários para você recriar o Scheduler Job usando a proc DBMS_SCHEDULER.CREATE_JOB, ok?

      []s

      Excluir
    2. Exemplo:
      select *
      from DBA_SCHEDULER_JOBS as of timestamp
      TO_TIMESTAMP('02/02/2017 14:00:00', 'dd/mm/yyyy hh24:mi:ss')
      where JOB_name='JOB_TESTE' ;

      Excluir
    3. Valeu Prado!!
      Usei o seu exemplo e consegui trazer o resultado que eu queria!
      Muito obrigado pela ajuda!!!

      Excluir