Social Icons

16 de mar. de 2012

Processo de geração de exports dinâmicos


Olá Pessoal,

     Hoje vou compartilhar com vocês um artigo de um amigo de trabalho, o DBA Luciano Barros Pires. Nesse artigo ele irá demonstrar como gerar exports dinâmicos do Banco de Dados (BD) para automatizar a geração de exports sempre que um novo BD for adicionado ao ambiente corporativo. Meu trabalho aqui foi somente fazer uma pequena e rápida revisão. Segue abaixo o início do artigo.


     Em ambientes corporativos com muitos bancos de dados, o gerenciamento de backups e exports pode se tornar muito trabalhoso e sempre que um novo BD é criado, é preciso preparar uma série de scripts para geração dos exports. Para facilitar e automatizar este trabalho, este artigo propõe a criação de um processo de execução dinâmica de exports, centralizado, que possibilita a geração de exports para todo BD que existe no ambiente corporativo




     Exports geram dumps (arquivos contendo cópias lógicas dos dados) que são muito úteis para complementar estratégias de recuperação de dados.      Entre algumas vantagens dos exports, em comparação aos backups, podemos destacar:
          - Permitem recuperar apenas um objeto do Banco de Dados (Bd), como por exemplo uma tabela ou uma função;
        - Permitem importar os dados de um usuário no schema de outro usuário;
        -  São úteis para migrar sistemas do ambiente de desenvolvimento para ambientes de homologação e/ou produção, copiando somente estrutura de dados ou estrutura de dados + dados existentes.     
     
     O exemplo aqui descrito já foi implantado em produção e conta com o registro dos BDs no catálogo do RMAN (Recovery Manager, ferramenta Oracle para execução de backups e recovery). Se você não usa o RMAN para efetuar backups, sugerimos criar uma tabela para registrar os BDs que deseja incluir no procedimento de exports.

     Uma característica importante desse procedimento dinâmico é que todos os logs e arquivos de dumps ficarão em um diretório onde um job será executado. Isso é bom quando utiliza-se storages para guardar os dados, porém se não for o caso, é preciso estar atento para o espaço disponível em disco. Se não for o caso do leitor, opte por ter um scheduler_job para ser  executado ao final do export.

No ambiente onde foi desenvolvido o procedimento descrito nesse artigo optamos por criar um usuário no BD chamado userbkp. Pelo nome percebe-se que esse usuário será responsável pelos backups do ambiente. Caso você já tenha o usuário RMAN criado no BD, é melhor optar por usá-lo. Para isso,  troque tudo que estiver como userbkp para rman. Não recomendamos usar o usuário sys, ou system para executar este procedimento, pois será preciso criar alguns objetos como dblinks, scheduler jobs e procedures, que serão usados no processo de export e por questões administrativas, não é legal que estes objetos sejam armazenados no schema do usuário sys ou system.

Agora vamos começar o mão na massa para criar o processo de export dinâmico:

Passo 1: Criação do usuário que conterá os objetos e as permissões necessárias para executar o jobs
     Caso já exista um usuário que possa ser usado (Ex.: rman), pule para o passo 2, senão, execute o script abaixo para criar o usuário userbkp:

CREATE USER userbkp
IDENTIFIED BY userbkp
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
/

     Nas máquinas alvo dos exports crie o mesmo usuário com os grants que são mostrados no passo 4.
  
  
Passo 2: Criar o diretório onde ficarão os arquivos de dump gerados
     Caso já exista um diretório que pode ser usado, passe ao passo 3. Antes de criar o objeto Directory no banco, pode ser interessante criar uma área de armazenamento na máquina para receber os dumps.

     Execute o comando abaixo para criar o diretório no Linux:
       mkdir -p /oradump

     Se não estiver usando o usuário oracle, o leitor precisará mudar as atribuições da pasta criada, mudando o usuário e o grupo donos da mesma e as permissões para os usuários. Para isso execute o comando abaixo para mudar as permissões no Linux:
        chown oracle:oinstall
        chmod 777 /oradump

     Agora é possível criar o objeto directory no banco. Execute o script abaixo para criar o diretório no BD:
       CREATE DIRECTORY ORADUMP AS '/oradump';

Passo 3: Concedendo privilégios de leitura e gravação para o usuário
     Depois da criação do diretório, devemos dar as permissões de leitura e gravação para o usuário userbkp. Execute o script abaixo para conceder as permissões ao diretório:
        GRANT READ, WRITE ON DIRECTORY ORADUMP TO  userbkp;

Passo 4: Concendendo privilégios de BD ao usuário
     As permissões abaixo precisam ser dadas ao usuário apenas na máquina de onde será disparado o export. Execute o script abaixo para conceder as permissões ao usuário userbkp:

GRANT ALTER ANY PROCEDURE TO USERBKP;
GRANT CREATE ANY JOB TO USERBKP;
GRANT CREATE DATABASE LINK TO USERBKP;
GRANT CREATE EXTERNAL JOB TO USERBKP;
GRANT CREATE JOB TO USERBKP;
GRANT CREATE PROCEDURE TO USERBKP;
GRANT CREATE SESSION TO USERBKP;
GRANT CREATE TABLE TO USERBKP;
GRANT DEBUG ANY PROCEDURE TO USERBKP;
GRANT EXECUTE ANY CLASS TO USERBKP;
GRANT EXECUTE ANY PROCEDURE TO USERBKP;
GRANT EXECUTE ANY PROGRAM TO USERBKP;
GRANT EXEMPT ACCESS POLICY TO USERBKP;
GRANT EXPORT FULL DATABASE TO USERBKP;
GRANT IMPORT FULL DATABASE TO USERBKP;
GRANT MANAGE SCHEDULER TO USERBKP;
GRANT SELECT ANY TABLE TO USERBKP;
GRANT UNLIMITED TABLESPACE TO USERBKP;
GRANT CONNECT TO USERBKP;
GRANT DBA TO USERBKP;
GRANT EXECUTE_CATALOG_ROLE TO USERBKP;
GRANT EXP_FULL_DATABASE TO USERBKP;
GRANT IMP_FULL_DATABASE TO USERBKP;
GRANT RECOVERY_CATALOG_OWNER TO USERBKP;
GRANT RESOURCE TO USERBKP;
GRANT SCHEDULER_ADMIN TO USERBKP;
ALTER USER RMAN DEFAULT ROLE ALL;
GRANT SELECT ON SYS.DBA_DATAPUMP_JOBS TO USERBKP;
GRANT SELECT ON SYS.DBA_DB_LINKS TO USERBKP;
GRANT SELECT ON SYS.DBA_SCHEDULER_JOBS TO USERBKP;
GRANT EXECUTE ON SYS.DBMS_DATAPUMP TO USERBKP;
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO USERBKP;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO USERBKP;
GRANT EXECUTE ON SYS.DBMS_OUTPUT TO USERBKP;
GRANT EXECUTE ON SYS.DBMS_RLS TO USERBKP;
GRANT EXECUTE ON SYS.DBMS_SQL TO USERBKP;
GRANT EXECUTE ON SYS.DBMS_SYS_ERROR TO USERBKP;
GRANT EXECUTE ON SYS.DBMS_SYS_SQL TO USERBKP;
GRANT EXECUTE ON SYS.UTL_RECOMP TO USERBKP;
GRANT SELECT ON SYS.V_$INSTANCE TO USERBKP;
GRANT CREATE TRIGGER TO USERBKP;
GRANT CREATE ANY TRIGGER TO USERBKP;

Passo 5: Verificando os sinônimos
     Verifique se existem os sinônimos para os Types abaixo:
sys.ku$_Status1010
     sys.ku$_Status1020

     Esses Types são objetos que serão utilizados pelo pacote DBMS_DATAPUMP, para recuperar mensagens de erro e status dos jobs.


CREATE TYPE sys.ku$_Status1010 AS OBJECT
(
mask NUMBER,         /* Indicates which status types are present*/
wip ku$_LogEntry1010,  /* Work-In-Progress: std. exp/imp msgs */
job_description    ku$_JobDesc1010,   /* Complete job description */
job_status         ku$_JobStatus1010, /* Detailed job status + per-worker sts */
error              ku$_LogEntry1010   /* Multi-level contextual errors */
)
CREATE OR REPLACE PUBLIC SYNONYM ku$_Status1010 FOR sys.ku$_Status1010;

CREATE TYPE sys.ku$_Status1020 IS OBJECT
        (
               mask             NUMBER,           
-- Status types present
               wip              ku$_LogEntry1010, 
-- Work in progress
               job_description  ku$_JobDesc1020,  
-- Complete job description
               job_status       ku$_JobStatus1020,
-- Detailed job status
               error            ku$_LogEntry1010  
-- Multi-level context errors
        )
CREATE OR REPLACE PUBLIC SYNONYM ku$_Status1020 FOR sys.ku$_Status1020;
CREATE OR REPLACE PUBLIC SYNONYM ku$_Status FOR ku$_Status1020;


     As procedures que automatizam os export farão as criações dos objetos anteriores dinamicamente.

Passo 6: Gerenciando as triggers de logon TR_LOGON, e de logoff, TR_LOGOFF

        Em ambientes onde o parâmetro REMOTE_DEPENDENCIES_MODE esteja configurado para TIMESTAMP, erros ORA-04026 ocorrerão. Para driblar o problema, mudaremos o parâmetro em tempo de execução para SIGNATURE, ou seja, mesmo que o horário dos objetos estejam diferentes, mas as suas assinaturas sejam as mesmas, o comando abaixo é executado:


     execute immediate 'begin alter session set    REMOTE_DEPENDENCIES_MODE=SIGNATURE; end';

        A trigger de logon sera criada em todas as máquinas. A trigger de logoff, precisará ser criada apenas no servidor do backupSeguem abaixo os códigos:


/* Esta trigger será criada dinamicamente nas demais máquinas, mas deve ser criada no servidor. */
create or replace
TRIGGER RMAN.TR_LOGON after logon on database
                                            WHEN ( USER = 'USERBKP' )
                                            begin
                                                  execute immediate   'alter session set REMOTE_DEPENDENCIES_MODE=SIGNATURE';
                                            end;

/* Esta trigger deve ser criada no servidor. */ 
create or replace
TRIGGER RMAN.TR_LOGOFF before logoff on database
WHEN ( USER = 'USERBKP' )
begin
      execute immediate   'begin alter session set REMOTE_DEPENDENCIES_MODE=TIMESTAMP; end;';
end;

Passo 7: Criando a procedure a ser executada pelo job
     Lembrando que precisamos de uma tabela com as informações do banco. Aqui usamos a tabela RMAN.NODE que já tem os registros dos bancos que são backupeados e deixa o procedimento ainda mais automatizado. Caso queira usar outra tabela, crie-a com pelo menos um campo que deve ter o nome de db_unique_name, ou outro nome desde que e o leitor lembre-se de trocar o nome da variável nos códigos que vierem a seguir.
     O arquivo TNSNAMES.ORA também deve conter a entrada para o novo banco. Sempre que pensamos em automatizar algum procedimento temos que padronizar as rotinas. Para que o procedimento desse artigo funcione bem, temos que registrar o banco no RMAN, incluir uma entrada no TNSNAMES.ORA com o mesmo nome do host, por exemplo, o banco prod, deve ter uma entrada prod no tnsnames, se for prd, o procedimento dará errado.
     Onde o RMAN não é utilizado (lembrando que o RMAN sempre é instalado automaticamente com o BD), sugiro a criação de uma tabela externa que leia as informações do tnsnames, porém, os DBAs já sabem que o arquivo TNSNAMES.ORA, precisa de um tratamento para que todas as informações sejam retidas do mesmo, ou por uma função, procedure ou edição do tnsnames.

create or replace
PROCEDURE      SP_Executa_Export
AS
  comando  varchar2(32000):='';
 
begin
 
  dbms_output.enable(NULL);
  for l in
        (select db_unique_name
         from rman.node
         where bcr_in_use = 'YES')
loop
                                        
    comando := 'DECLARE
               
                  cursor_name INTEGER;
                  ret INTEGER;
               BEGIN
                    cursor_name := DBMS_SQL.OPEN_CURSOR@DB_BKP_'||l.db_unique_name||';
                    DBMS_SQL.PARSE@DB_BKP_'||l.db_unique_name||'(cursor_name, ''
                                            create or replace
                                            TRIGGER RMAN.TR_LOGON after logon on database
                                            WHEN ( USER = ''''USEBKP'''' )
                                            begin
                                                  execute immediate   ''''alter session set REMOTE_DEPENDENCIES_MODE=SIGNATURE'''';
                                            end;'', DBMS_SQL.NATIVE); 
                    ret := DBMS_SQL.EXECUTE@DB_BKP_'||l.db_unique_name||'(cursor_name);
                    DBMS_SQL.CLOSE_CURSOR@DB_BKP_'||l.db_unique_name||'(cursor_name);
              END;';
     
     dbms_output.put_line(comando);
   
     execute immediate comando;

  comando := 'DECLARE
                  v_Instrucao  varchar2(32000):='''';
                  cursor_name INTEGER;
                  ret INTEGER;
              BEGIN
                    SELECT  replace(replace(DBMS_METADATA.get_ddl(''PROCEDURE'', ''SP_TESTA_EXPORTS_BKP'', ''USEBKP''),''"'',''''),''/'','''')
                      INTO    v_Instrucao
                    FROM    Dual;
                    cursor_name := DBMS_SQL.OPEN_CURSOR@DB_BKP_'||l.db_unique_name||';
                    DBMS_SQL.PARSE@DB_BKP_'||l.db_unique_name||'(cursor_name, v_Instrucao, DBMS_SQL.NATIVE); 
                    ret := DBMS_SQL.EXECUTE@DB_BKP_'||l.db_unique_name||'(cursor_name);
                    DBMS_SQL.CLOSE_CURSOR@DB_BKP_'||l.db_unique_name||'(cursor_name);
                    sys.UTL_RECOMP.recomp_parallel(4,''USEBKP'');
                    sys.UTL_RECOMP.recomp_parallel@DB_BKP_'||l.db_unique_name||'(4,''USEBKP'');
                    execute immediate ''begin RMAN.SP_TESTA_EXPORTS_BKP@DB_BKP_'||l.db_unique_name||'; end;'';
                    END;';
                    execute immediate comando;
                                      
end loop;
end;

Passo 8: Criação da procedure  a ser replicada
     O Oracle não permite a execução de export remotamente por meio de Databases Links de tabelas que possuam campos lobs (Large Objects). Se tal procedimento fosse possível facilitaria muito a execução dos backups lógicos com a inclusão do parâmetro abaixo na package DBMS_DATAPUMP:
      remote_link => 'DB_BKP_'||l.db_unique_name||'.WORLD', -- uso do db_link criado acima, que garante o disparo remoto do export

     Como isso não é possível, desenvolvemos uma procedure que será replicada aos demais bancos sempre que necessário. O script abaixo cria a procedure que executa a package DBMS_DATAPUMP com os parâmetros necessário para execução de um export full:

create or replace
PROCEDURE      SP_EXECUTA_EXPORT
AS
    hand NUMBER;
    contar NUMBER;
    v_data VARCHAR2(16);
    db_unique_name VARCHAR2(16); 
    v_status number;
    v_tb VARCHAR2(35); 
    sts sys.ku$_Status1010;  
    createCommand varchar2(32000);
  begin
       
       SELECT COUNT(1) into contar FROM all_objects WHERE OBJECT_NAME  LIKE UPPER('KU$_STATUS%') AND OWNER = 'PUBLIC';
       if (contar < 3) then
       createCommand := '
                                         CREATE TYPE sys.ku$_Status1010 AS OBJECT
                              (
                              mask NUMBER,         * Indicates which status types are present*
                              wip ku$_LogEntry1010,  * Work-In-Progress: std. expimp msgs *
                              job_description    ku$_JobDesc1010,   * Complete job description *
                              job_status         ku$_JobStatus1010, * Detailed job status + per-worker sts *
                              error              ku$_LogEntry1010   * Multi-level contextual errors *
                              )
                              CREATE OR REPLACE PUBLIC SYNONYM ku$_Status1010 FOR sys.ku$_Status1010;
                              
                              CREATE TYPE sys.ku$_Status1020 IS OBJECT
                                      (
                                             mask             NUMBER,            -- Status types present
                                             wip              ku$_LogEntry1010,  -- Work in progress
                                             job_description  ku$_JobDesc1020,   -- Complete job description
                                             job_status       ku$_JobStatus1020, -- Detailed job status
                                             error            ku$_LogEntry1010   -- Multi-level context errors
                                      )
                              CREATE OR REPLACE PUBLIC SYNONYM ku$_Status1020 FOR sys.ku$_Status1020;
                              CREATE OR REPLACE PUBLIC SYNONYM ku$_Status FOR ku$_Status1020;' ;
           dbms_output.put_line(createCommand);
           execute immediate createCommand;
        end if;
  
        select instance_name,  TO_CHAR(sysdate,'YYYYMMDDhh24miss') into db_unique_name, v_data from v$instance;
       
       
        hand :=  Dbms_DataPump.Open(operation=>'EXPORT',
                            job_mode=>'FULL',
                            job_name => 'EXPDP_FULL_'||v_data,
--remote_link=> 'DB_BKP_'||l.db_unique_name||'.WORLD', -- uso do db_link criado acima, que garante o disparo remoto do export
                            version=>'COMPATIBLE');
                  Dbms_DataPump.Add_File(handle=>hand,
                            filename=>'expdp_full_'||lower(db_unique_name)||'_'||v_data||'.log',
                            directory=>'ORADUMP',
                            filetype=>3);
                  Dbms_DataPump.Add_File(handle=>hand,
                            filename=>'expdp_full_'||lower(db_unique_name)||'_'||v_data||'.dmp',
                            directory=>'ORADUMP',
                            filetype=>1);
                  Dbms_DataPump.Set_Parameter(handle=>hand,
                            name=>'ESTIMATE',
                            value=>'STATISTICS');
                  DBMS_DATAPUMP.SET_PARALLEL(handle=>hand,  
                            degree=>4); --ok
                  Dbms_DataPump.Start_Job(hand);
                  Dbms_Datapump.detach(hand);
                 dbms_output.put_line(dbms_datapump.ku$_status_job_error);
                 v_tb := 'EXPDP_'||db_unique_name||'_'||v_data;
                 v_status := 1;
end;

Passo 9: Criação do Scheduler Job
     O Agendador serve para executarmos a procedure acima em um determinado horário. Segue o script para criação do agendamento:
BEGIN
      DBMS_SCHEDULER.CREATE_JOB(
        job_name=>'USERBKP.JOB_EXECUTA_EXPORT',
        JOB_TYPE=>'PLSQL_BLOCK',
        JOB_ACTION=>'BEGIN USERBKP.SP_EXECUTA_EXPORT();  END;',
        START_DATE=>TO_TIMESTAMP_TZ('03/02/2012 21:00:00 -02:00','dd/mm/yyyy hh24:mi:ss TZR'),
        repeat_interval=>'FREQ=DAILY;BYHOUR=21',
        ENABLED=>TRUE,
        COMMENTS=>'Job para executar EXPORT.');
END;

     Para testar se o Scheduler Job está funcionando, execute o script abaixo:
begin
 DBMS_SCHEDULER.RUN_JOB(job_name=>'USERBKP.JOB_EXECUTA_EXPORT'); end;

Se durante os testes, o procedimento falhar, é prudente apagar as sessões ativas para evitar erro posterior na execução dos jobs. Execute o script abaixo e rode o resultado, com CUIDADO, para não apagar as sessões de outros usuários que executam o export.
Scripts para identificação e deleção de sessões travadas:

SELECT DISTINCT 'ALTER system KILL SESSION '''||sid||', '||serial#||''' IMMEDIATE;'
FROM v$session
where USERNAME LIKE '%USERBKP%'

ALTER system KILL SESSION '267, 68' IMMEDIATE;
ALTER system KILL SESSION '329, 11346' IMMEDIATE;

Passo 10: Monitoramento
    Select  de algumas visões úteis para monitorar o procedimento:
select * from user_scheduler_job_run_details;
select * from dba_scheduler_jobs;
SELECT * FROM v$session where USERNAME LIKE '%USERBKP%'
  
     Nesse artigo apresentei uma forma mais dinâmica para execução dos exports, que dependerá de menos intervenções dos DBAs. É possível, porém, identificar alguns mais alguns procedimentos que podem ser automatizados, como por exemplo o envio de mensagens em caso de erros na compactação dos arquivos gerados. Tais procedimentos podem ser implementados para facilitar a administração dos exports dos bancos. Fica o desafio para os leitores!


     Agradeço ao Fábio por ceder um espaço em seu blog para que eu pudesse compartilhar o artigo e ao nosso amigo de trabalho Igor T. Ribeiro, que me ajudou na criação e testes do procedimento.


Até mais.

Luciano Barros Pires
Graduado pela Fatec-SP e IFSP
Pós-Graduado pelo IBTA
Mestrando (especial) na UFABC

0 comments:

Postar um comentário

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)