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