Social Icons

19 de jun. de 2012

Repositório de metadados/Versionamento de objetos no Oracle Database


Olá pessoal,

    No artigo de hoje vou compartilhar com vocês uma dica muito boa para complementar estratégias de backup e permitir recuperações mais rápidas de versões anteriores dos objetos do Banco de Dados. Irei demonstrar como criar um repositório de metadados dos principais objetos do Banco de Dados, tais como: tabelas, stored procedures, functions, views, packages, scheduler jobs, triggers, índices e visões materializadas!  Eu fui o idealizador e homologador deste repositório, mas quem fez o seu desenvolvimento foi o DBA Igor Ribeiro, que trabalha comigo! Atualmente estamos utilizando-o em todos os BDs de produção, homologação e desenvolvimento da empresa em que eu trabalho e já o utilizamos para voltar versões anteriores de alguns objetos. O tempo de recuperação de versões anteriores de um objeto através do repositório é muito mais rápido do que efetuar uma recuperação através de um backup.
 
     Percebi a necessidade e utilidade deste repositório ao participar do evento GUOB Tech Day 2010. Neste evento estive presente em uma palestra sobre dicas e truques do Oracle ACE Director Francisco Muñoz Alvarez e nela ele indicou a criação de um repositório de metadados. No evento GUOB Tech Day 2011 eu  estava dando um treinamento de SQL Tuning e por isso não pude comparecer ao evento, porém baixei as apresentações do site do GUOB e vi que o palestrante Arup Nanda, falou sobre o mesmo assunto. Os 2 palestrantes fizeram uma ótima apresentação, mas a sugestão era uma  forma  simples de implementar o repositório, que não era muito fácil de pesquisar. A solução proposta sugeria a criação de um dump para armazenar os metadados dos objetos do BD. O dump poderia ser gerado diariamente através do utilitário export do Datapump e em seguida teríamos que importá-lo através do utilitário import do Datapump, em um arquivo SQL. Os arquivos SQL diários é que constituiriam o nosso repositório. Todo dia teríamos que gerar um arquivo SQL novo contendo os scripts DDL de todos os objetos do BD.

     Antes de continuar, quero deixar bem claro que não estou fazendo uma crítica à solução proposta pelos palestrantes mencionados no parágrafo anterior, pois ambos são DBAs melhores e muito mais experientes do que eu. A solução que proponho neste artigo é uma melhoria do que eles propuseram, pois naquela solução mais simples é muito difícil e demorado pesquisar no repositório alterações de um determinado objeto e fazer consultas e recuperações mais complexas. Para isso teríamos que analisar as diferenças de código em todos os arquivos SQL diários. Existem ferramentas que poderiam nos ajudar nessa comparação, mas ainda assim o trabalho não seria muito produtivo. 

    Para entender a estrutura do repositório, precisamos entender primeiro o que é o repositório de metadados. O Repositório de Metadados é o local onde iremos armazenar scripts DDL (Data Definition Language) de criação dos principais objetos do BD. Este repositório irá contemplar o script DDL completo de cada objeto sempre que houver uma mudança na sua definição, ou seja, ele irá contemplar o versionamento dos objetos! O BD contém somente a versão atual de cada objeto. No repositório teremos além da versão atual, versões anteriores! 
  
     Os principais benefícios do repositório são:
          - Permite detectar mudanças nos objetos do BD;
          - Cria um controle de versões dos objetos do BD;
          - Permite recuperar de um modo mais rápido versões anteriores dos objetos do BD. 
  
     Para implementar um repositório, contemplando uma solução mais fácil de pesquisar, de gerenciar o versionamento e também mais rápida para efetuar recuperações, a minha proposta é a criação dos seguintes objetos:
   
          1- Uma tabela chamada REPOSITORY_METADATA que possui as seguintes colunas:
                  - TX_OBJECT_DDL: código DDL completo do objeto;
                  - NM_SCHEMA: nome do schema do objeto;
                  - NM_OBJECT: nome do objeto;
                  - DT_LAST_UPDATE: data última alteração do objeto;
                  - DS_OBJECT_TYPE: tipo do objeto (Ex.: Stored Procedure, Table, View etc.;
                  - DT_INSERT_REG: data de inserção do registro na tabela do repositório.
               
          2- Uma stored procedure (SP) chamada SP_UPDATE_REPOSITORY que consulta o dicionário de dados para recuperar o código DDL de todos objetos do BD e que grava na tabela REPOSITORY_METADATA somente as linhas que ainda não existem nela. Esse código permite gravar na tabela todas as versões de objetos que foram alterados em um determinado período (última execução até momento final de sua execução atual);

          3- Um scheduler job (SJ) chamado JOB_UPDATE_REPOSITORY que irá executar diariamente às  22h a SP SP_UPDATE_REPOSITORY. O código de criação deste objeto poderá ser alterado para se adequar à sua necessidade. Altere, se necessário, a frequência e horário de execução.

     O script para criar os objetos do repositório está disponível para download no Meu Sky Drive, arquivo script_repositorio_metadados.zip. Para descompactá-lo é necessário obter uma senha. Para obtê-la, inscreva-se na newsletter deste blog (ver painel direito desta página). Após descompactá-lo, execute-o com um usuário que tenha privilégios para criar tabelas, para consultar as visões que fazem parte do código da stored procedure, para criar stored procedures e para criar scheduler jobs. NÃO CRIE OS OBJETOS NO SCHEMA DO SYS. Crie os objetos e o scheduler job em um schema próprio e conecte-se com o usuário deste schema para executar o script. Este usuário também precisará de privilégios de SELECT nas visões SYS.OBJ$, SYS.DBA_USERS e SYS.DBA_OBJECTS.

      Para efetuar a restauração de um ou mais objetos, é necessário apenas ter conhecimentos básicos de SQL e seguir os passos abaixo:

          a) Execute uma instrução SELECT para retornar dados da tabela REPOSITORY_METADATA, filtrando-os pelo nome do schema e/ou nome do objeto e/ou data da atualização e/ou tipo do objeto. Para recuperar, por exemplo, uma versão anterior de uma stored procedure chamada SP_TESTE, de um schema chamado USUARIO,  efetue a consulta abaixo (ver imagem 01):

Imagem 01 - Exemplo de consulta ao repositório
   
          b)  Copie e cole o valor da coluna TX_OBJECT_DDL (resultante da execução do passo anterior) em uma janela de scripts do SQL Developer ou qualquer outra ferramenta de BD e por fim execute o código correspondente ao valor desta coluna!


Bom pessoal, por hoje é só! Se tiverem dúvidas, postem seus comentários!

[]s
   

40 comments:

  1. Estava dando uma olhada no artigo Repositório de Metadados no Oracle Database que está no seu blog. Ocorre que não sei se devido a alguma configuração da rede aqui da empresa ou do navegador, mas não consigo visualizar as imagens contidas no seu blog. Então queria ver contigo se poderias postar o exemplo da consulta contido na imagem 01 deste artigo.

    ResponderExcluir
    Respostas
    1. Douglas, a consulta é:

      SELECT *
      FROM REPOSITORY_METADATA
      WHERE NM_SCHEMA = 'USUARIO'
      AND NM_OBJECT = 'SP_TESTE'
      AND DS_OBJECT_TYPE = 'PROCEDURE'
      ORDER BY DT_LAST_UPDATE DESC;

      Excluir
  2. Olá Fabio, tudo bem?!
    Meu amigo, muito bom o seu artigo! Congratulations!
    Meu conhecimento em PL/SQL ainda é um tanto limitado, meu universo de banco de dados sempre foi SQL Server, mas há 6 meses aceitei o desafio de trabalhar com o excelentíssimo oracle.. hehe..
    Estava pesquisando sobre versionamento de objetos pois onde trabalho não há esse controle e volta e meia me deparo com situações de mudanças em objetos e daí em diante não preciso comentar sobre os impactos causados ne? rsrs..
    Mas Enfim.. Muito bom o artigo e melhor ainda a sua generosidade em compartilhar seu conhecimento conosco..
    Eu gostaria dos scrips e tudo que vc tiver de dicas, sites, artigos que acrescente no meu conhecimento, por favor, ficarei bem feliz!
    Grande Abraço!

    ResponderExcluir
    Respostas
    1. Botin, obrigado pelos comentários.

      Qto às dicas, para começar, sugiro que leia o livro do Tom Kyte, que vc encontrará em um post do meu blog e que é free: http://www.fabioprado.net/2012/05/e-book-gratuito-sobre-arquitetura-do.html

      []s

      Excluir
    2. Botin, se você tiver a oportunidade de fazer o curso presencial de PL/SQL do prof. Fábio Prado, faça-o. Verifique a agenda no blog e se programe para a próxima turma. Infelizmente não tenho essa oportunidade por morar em João Pessoa/PB, por isso optei em adquirir o curso na modalidade Videoaulas. Caso não possa fazer na modalidade presencial, sugiro que adquira também as videoaulas que não vai se arrepender. Sucesso em suas empreitadas!

      Excluir
    3. Antonio, muitíssimo obrigado pela recomendação! Eles são grande valor para mim e para a credibilidade das minhas aulas!

      Excluir
  3. Gostaria de parabenizar pelo excelente conteúdo do blog principalmente este artigo com informações do repositório de metadados, realmente é uma ótima idéia, e a sua sugestão de melhoria adicionou em muito a usabilidade deste recurso, realmente um excelente artigo postado.
    Grande abraço.

    ResponderExcluir
  4. Fábio aqui é o Rodrigo da Mega Sistemas em Itu onde você ministrou um curso de SQL tunnig, legal sua idéias mas você poderia utilizar o SVN Tortoise controlador de versão, não seria uma opção, muito boa por sinal porque consegue controlar cada versão do arquivo desde sua inclusão no repositório.
    Mas é muito legal fazer esse repositório.

    Grande abraço...

    ResponderExcluir
    Respostas
    1. Rodrigo, foi muito bom vc ter essa dúvida. Eu uso tbém o SVN Tortoise, mas para outras finalidades. Perceba que no repositório do meu artigo, o controle de versões é realizado automaticamente sobre todos os objetos do BD, e código DDL dos objetos fica em uma tabela do BD, que é muito fácil para pesquisar qdo for necessário! Se vc fosse controlar isso atráves do SVN, por exemplo, vc teria que fazer tudo manualmente. Vc acha q seria produtivo gerenciar milhares de objetos do Bd, manualmente, em arquivos txt, armazenados no SVN?

      []s

      Excluir
  5. Olá Fábio,

    Eu executei o seu script e deu tudo certo para a criação da tabela, SP e JOB, mas na Procedure esta dando um erro dizendo que a tabela SYS.DBA_USERS não existe, e para checar se a tabela não existia eu peguei somente o Select que retorna as informações desta tabela, e executando fora da Procedure o Select funcionou normal, me retornando os dados da mesma,e pude confirmar que o erro só ocorre dentro da Procedure.

    Conto com sua ajuda.

    Abraços!

    ResponderExcluir
    Respostas
    1. Tom, entre com um usuario que tenha privilégios de DBA e execute o comando "GRANT SELECT ON DBA_USERS TO XXX", substituindo XXX pelo nome do usuário onde vc está criando a Stored Procedure, ok?

      []s

      Excluir
  6. Fábio estou enviando um e-mail para vc, para que possa obter a senha dos scripts. Essa ferramenta me ajudará bastante. Minha versão do Oracle é a 10g e não tem nada para realizar versionamento. Obrigado.

    ResponderExcluir
  7. Boa Tarde Fabio,
    sou programador PL/SQL e aluno do curso de especialização em Banco de Dados do Centro Universitário do Triângulo em Uberlândia-MG. Eu e mais uma colega iniciamos uma pesquisa para elaboração do nosso artigo de conclusão de curso, e o tema escolhido é Versionamento de Banco de Dados. Li seu Artigo publicado na edição 107 da Revista SQL Magazine e achei interessante esta solução para versionar e recuperar versões do banco de dados Oracle. Nossa proposta é escrever sobre o tema e definir algumas métricas para comparação entre ferramentas que fazem o controle de versão de banco de dados. Portanto gostaria de saber se você possui algumas referências bibliográficas que podem nos ajudar em nossa pesquisa.

    ResponderExcluir
    Respostas
    1. Giovanni, só para não haver confusão, a solução que dou neste artigo artigo é para recuperar versões de objetos específicos do BD e não versionamento do BD inteiro, ok?

      Eu não conheço nenhuma ferramenta que faça o mesmo trabalho que eu indico neste artigo e nunca pesquisei muito sobre o assunto. Se vc conhece alguma, até gostaria de saber qual é para comparar também!

      Infelizmente não tenho referências bibliográficas sobre o assunto!

      []s

      Excluir
  8. Bom dia Fábio,

    Excelente informação, principalmente porque estou trabalhando em caso semelhante para versionamento de objetos e com esta dica fica bem mais fácil, mais uma vez parabéns pela dica.

    ResponderExcluir
  9. Fábio, muito obrigado por compartilhar conosco. Irei fazer alguns testes e pretendo implementar o repositório de metadados. Posteriormente farei novos comentários sobre os resultados.

    Obrigado.

    ResponderExcluir
  10. Quando tento executar a procedure ela me dá um erro ora-31600 e outro 06512

    ResponderExcluir
    Respostas
    1. Ronyer, vc tentou executar a proc em um BD 10G ou superior?

      Excluir
  11. Muito bom, muito simples e muito útil. Parabéns

    ResponderExcluir
  12. Fabio,

    Gostaria de saber sobre o tempo de retenção das versões anteriores , por exemplo se um objeto for atualizado 15 vezes em uma semana vai ter 15 versões ? tem como configurar algo to tipo janela de recuperação ou redundância ?

    ResponderExcluir
    Respostas
    1. Piero, aqui neste artigo não ensinei a fazer o expurgo dos dados da tabela do repositório, mas isso é muito simples. Monte um SQL para deletar registros mais antigos que uma determinada quantidade de dias e crie um job para executar este SQL diariamente. Quanto à janela de recuperação ou redundância, também não ensinei a fazer isso aqui, mas você pode adaptar meu código básico para aquilo que você precisar.
      []s

      Excluir
  13. Fabio.

    Criei os objetos no banco de dados e programei o job. Porém, a execução do job está falhando.

    Executei manualmente o proc., com o comando:
    BEGIN SP_UPDATE_REPOSITORY; END;

    e está retornando o seguinte erro:
    ORA-31603: objeto "XX" do tipo TABLE não encontrado no esquema "XX"
    ORA-06512: em "SYS.DBMS_METADATA", line 5805
    ORA-06512: em "SYS.DBMS_METADATA", line 8344
    ORA-06512: em line 1
    31603. 00000 - "object \"%s\" of type %s not found in schema \"%s\""
    *Cause: The specified object was not found in the database.
    *Action: Correct the object specification and try the call again.

    Fiz algumas pesquisas, que apontaram que seria necessário adicionar o privilegio select_catalog_role ao usuário. Concedi mas mesmo assim não funcionou.

    O usuário que executa o procedimento SP_UPDATE_REPOSITORY tem permissão de select no objeto apontado pelo erro acima.

    Sabe me dizer se fiz algo errado ou poderia orientar como posso resolver?

    ResponderExcluir
    Respostas
    1. Cristian, nunca vi esse erro, e não vou conseguir tempo para analisar e entender o seu problema. Sugiro que você peça para alguém que tenha bons conhecimentos de PL/SQL tente fazer o DEBUG da procedure e entender onde e porque está ocorrendo o erro, ok?

      []s

      Excluir
  14. Mega excelete FP, de muitos outro bons, esse artigo trás uma informação muito rica, para somar muito numa organização. Parabéns!

    ResponderExcluir
  15. Bom primeiro agradecer pelo artigo assim como o script que utilizo a muito tempo.
    Fiz a aplicação em outro servidor e me deparei com os erros de compilação da SP / Job.
    Solução simples, realizar Grant select das tabelas\views de sistema abaixo utilizando o usuario SYS.
    SYS.OBJ$
    SYS.DBA_USERS
    SYS.DBA_OBJECTS

    Tambem inseri ; após a palavra REPOSITORY
    JOB_ACTION =>'BEGIN SP_UPDATE_REPOSITORY; END;',

    Abraço.

    ResponderExcluir
    Respostas
    1. Olá, os GRANTS eu explico neste artigo que é necessário atribuir, talvez você não tenha visto. Quanto à vírgula depois de REPOSITORY, isso faltava e eu acabei de acrescentar.

      Obrigado pelo comentário!
      []s

      Excluir
  16. Uma parte importante no script que gostaria de implementar, seria o usuário do OS e/ou do BD que fez a alteração no objeto é muito complexo?

    ResponderExcluir
  17. Estou analisando e vi que é possível implementar uma trigger a nível de schema/database, que desta forma teria as informações de usuário que comentei.

    Outro ponto, não entendi a checagem de dados que você implementou, pois se a query usa o minus, só vai trazer o que não tem na tabela REPOSITORY_METADATA, você passou por alguma situação que precisou fazer essa checagem?

    Como estou em um sistema transacional, o get ddl pode falhar, pois alguns processos criam tabelas temporárias que são apagados posteriormente, então fiz um controle de exceção, para ignorar este erro.

    ResponderExcluir
    Respostas
    1. O MINUS elimina da comparação posterior os objetos cujo "código" e "data de alteração" não tiveram modificação. Se não houve modificação em nada o código do objeto não deve ser inserido na tabela do repositório. Isso elimina linhas redundantes, ok?

      Excluir
  18. Bom dia Fabio, seu artigo sobre versionamento é excelente, vou implementar aqui em meus bancos de dados.

    Verifiquei que há um script compactado e gostaria de ter acesso a ele.

    Assinei a NEWSLETTER , preciso da senha para descompactar o script, como consigo esta senha?

    ResponderExcluir
    Respostas
    1. Edward, qdo vc assina a newsletter vc recebe um email para ativar ela e em seguida vc já recebe um e-mail com a senha, ok?

      Excluir
  19. Fabio, recebi um email para ativar a newletter, mas não recebi a senha.

    ResponderExcluir
    Respostas
    1. Carlos, consta aqui nos logs que você recebeu o e-mail de resposta automática contendo a senha. Verifique por favor se ele não está na sua caixa de spam.

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)