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
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:
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 backup. Seguem 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;
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;
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.
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
Nenhum comentário:
Postar um comentário