Enviar e-mail utilizando PL/SQL dentro do Oracle é realmente uma tarefa razoavelmente simples com o UTL_SMTP ou mesmo com o UTL_TCP. O que não aparece de forma simples na documentação é como mandar um e-mail com usuário e senha autenticados.

Bom, não é algo do outro mundo, mas segue aqui um pequeno script que mostra como fazer isso. Você só precisa editar os parâmetros iniciais e só chamando ele, já temos um teste. Depois é só passar o assunto e mensagem como parâmetro na procedure e você já pode enviar seus alertas para todo mundo.

Segue abaixo o script:

 
CREATE OR REPLACE PROCEDURE envia_email(
  p_assunto          VARCHAR2      := 'Teste de envio',
  p_mensagem         CLOB          := 'Teste'
) IS
  v_remetente        VARCHAR2(50)  :='fabio.telles@midstorm.org';  -- Remetente
  v_destinatario     VARCHAR2(50)  :='cliente@midstorm.org';       -- Destinatário
  v_destinatario_cc  VARCHAR2(50)  :='dba@savepoint.org';          -- Com cópia
  v_smtp             VARCHAR2(20)  := '192.168.0.1';               -- Endereco IP do SMTP
  v_smtp_user        VARCHAR2(50)  := 'dba@midstorm.org';          -- Usuário autenticado no SMTP
  v_smtp_user_pass   VARCHAR2(50)  := 'sua_senha';                 -- Senha do usuário SMTP
  v_conn             utl_smtp.connection;                          -- Abre conexão SMTP e HTTP
BEGIN
  -- Abre conexão com um Servidor SMTP, porta padrão é 25
  v_conn := utl_smtp.open_connection (v_smtp,25);
  utl_smtp.helo (v_conn, v_smtp);
  utl_smtp.command (v_conn, 'AUTH LOGIN');
  utl_smtp.command (v_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(
    utl_raw.cast_to_raw((v_smtp_user)))));
  utl_smtp.command (v_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(
    utl_raw.cast_to_raw((v_smtp_user_pass)))));
  utl_smtp.mail (v_conn, ('<' || v_remetente || '>'));
  utl_smtp.rcpt (v_conn, ('<' || v_destinatario || '>'));
  utl_smtp.rcpt (v_conn, ('<' || v_destinatario_cc || '>'));
 
  utl_smtp.open_data(v_conn);
  utl_smtp.write_raw_data(v_conn, utl_raw.cast_to_raw('From:'    || v_remetente  
    || utl_tcp.crlf));
  utl_smtp.write_raw_data(v_conn, utl_raw.cast_to_raw('To:'      || v_destinatario 
    || utl_tcp.crlf));
  utl_smtp.write_raw_data(v_conn, utl_raw.cast_to_raw('Cc:'      || v_destinatario_cc 
    || utl_tcp.crlf));
  utl_smtp.write_raw_data(v_conn, utl_raw.cast_to_raw('Subject:' || p_assunto 		
    || utl_tcp.crlf)); 
  utl_smtp.write_raw_data(v_conn, utl_raw.cast_to_raw(' '        || utl_tcp.crlf));
  utl_smtp.write_raw_data(v_conn, utl_raw.cast_to_raw(utl_tcp.crlf || p_mensagem)); 
 
  utl_smtp.CLOSE_DATA(v_conn);
  utl_smtp.QUIT (v_conn);
 
EXCEPTION
  WHEN OTHERS THEN
    utl_smtp.quit (v_conn);
    RAISE_APPLICATION_ERROR(-20011,'Não foi possível enviar o e-mail devido ao seguinte erro: ' 
      || sqlerrm);
END ENVIA_EMAIL;

Rápido e rasteiro:

Vira e mexe você precisa dar uma geral num banco de dados que você nunca viu antes. Bom, então segue abaixo um pequeno script para lhe ajudar na tarefa. Eu, sei, podia melhorar muito, aceito sugestões, claro. Em todo caso, já dá para começar:

SET serveroutput ON SIZE 1000000 FORMAT WRAPPED
SET autotrace    OFF
SET feedback     OFF
SET wrap         OFF
SET trimspool    ON
SET pagesize     100
SET linesize     200
 
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YY';
 
DECLARE
  v_media_archive   number;
  v_spfile          varchar(10);
  v_version         v$version.banner%TYPE;
  v_host            v$instance.host_name%TYPE;
  v_instance        v$instance.instance_name%TYPE;
  v_thread          v$instance.thread#%TYPE;
  v_startup_time    v$instance.startup_time%TYPE;
  v_name            v$database.name%TYPE;
  v_created         v$database.created%TYPE;
  v_resetlogs_time  v$database.resetlogs_time%TYPE;
  v_log_mode        v$database.log_mode%TYPE;
  v_open_mode       v$database.open_mode%TYPE;
BEGIN
  SELECT DECODE(COUNT(*),0,'PFILE','SPFILE') INTO v_spfile FROM v$spparameter
    WHERE isspecified != 'FALSE';
  SELECT banner INTO v_version FROM v$version  WHERE ROWNUM < 2;
  SELECT UPPER(host_name), UPPER(instance_name), thread#, startup_time
    INTO v_host, v_instance, v_thread, v_startup_time
    FROM v$instance
  ;
  SELECT name, created, resetlogs_time, log_mode, open_mode
    INTO v_name, v_created, v_resetlogs_time, v_log_mode, v_open_mode
    FROM v$database
  ;
 
  dbms_output.put_line('');
  dbms_output.put_line('Informações de ' || v_name);
  dbms_output.put_line('========================');
  dbms_output.put_line('');
  dbms_output.put_line('Informações Gerais');
  dbms_output.put_line('------------------');
  dbms_output.put_line('Servidor..............: ' || v_host);
  dbms_output.put_line('Versão................: ' || v_version);
  dbms_output.put_line('Instância / Thread....: ' || v_instance || ' / ' || v_thread);
  dbms_output.put_line('Criado em.............: ' || to_char(v_created,'DD/MM/YYYY'));
  dbms_output.put_line('Último RESETLOG.......: ' || to_char(v_resetlogs_time,'DD/MM/YYYY'));
  dbms_output.put_line('Última inicialização..: ' || to_char(v_startup_time,'DD/MM/YYYY'));
  dbms_output.put_line('Inicializado com......: ' || v_spfile);
  dbms_output.put_line('Modo ARCHIVE..........: ' || v_log_mode);
  dbms_output.put_line('Status................: ' || v_open_mode);
  dbms_output.put_line('');
 
  dbms_output.put_line('Localização');
  dbms_output.put_line('===========');
  dbms_output.put_line('Parâmetro            | Valor');
  dbms_output.put_line('---------------------|------');
  FOR nls IN (
    SELECT RPAD(description,20) parameter, property_value
      FROM database_properties
      WHERE property_name IN ('NLS_CHARACTERSET', 'NLS_DATE_FORMAT','NLS_LANGUAGE',
        'NLS_NUMERIC_CHARACTERS', 'NLS_TERRITORY', 'DBTIMEZONE')
  ) LOOP
    dbms_output.put_line(nls.parameter || ' | ' || nls.property_value);
  END LOOP;
  dbms_output.put_line('');
 
  dbms_output.put_line('Memória');
  dbms_output.put_line('=======');
  dbms_output.put_line('Parâmetro                 | Valor(MB)');
  dbms_output.put_line('--------------------------|----------');
  FOR mem IN (
    SELECT RPAD(name,25) parameter, ROUND(value/1024/1024) valor_mb
      FROM v$parameter
      WHERE name IN ('db_cache_size','large_pool_size','java_pool_size',
        'sga_max_size','shared_pool_size','pga_aggregate_target')
      ORDER BY name
  ) LOOP
    dbms_output.put_line(mem.parameter || ' | ' || mem.valor_mb);
  END LOOP;
  dbms_output.put_line('');
 
  dbms_output.put_line('REDO');
  dbms_output.put_line('====');
  dbms_output.put_line('Grupo | Tamanho | Arquivo');
  dbms_output.put_line('------|---------|--------');
  FOR log IN (
    SELECT f.GROUP# AS grupo, ROUND(l.bytes/1024/1024) AS tamanho, f.member AS arquivo
      FROM v$logfile f, v$log l
      WHERE f.GROUP# = l.group#) LOOP
    dbms_output.put_line(lpad( log.grupo,5) || ' | ' || lpad(log.tamanho,7) ||
      ' | ' || log.arquivo);
  END LOOP;
  dbms_output.put_line('');
 
  dbms_output.put_line('Control File');
  dbms_output.put_line('============');
  FOR control IN ( SELECT name FROM v$controlfile) LOOP
    dbms_output.put_line(control.name);
  END LOOP;
  dbms_output.put_line('');
 
  IF v_log_mode = 'ARCHIVELOG' THEN
    SELECT ROUND(SUM(blocks * block_size) / to_number( MAX(first_time) -
      MIN(first_time)) /1024/1024) media
      INTO v_media_archive
      FROM V$ARCHIVED_LOG; 
 
    dbms_output.put_line('ARCHIVE');
    dbms_output.put_line('=======');
    dbms_output.put_line('Quantidade média de archive gerado: ' || v_media_archive || 
      'MB / dia');
    dbms_output.put_line('');
    dbms_output.put_line('ID | Status     | Tipo       | Destino    | Arquivo');
    dbms_output.put_line('---|------------|------------|------------|--------');
 
    FOR arch IN (
      SELECT RPAD(dest_id, 2) id, RPAD(STATUS,10) STATUS, RPAD(binding,10) tipo,
        RPAD(target,10) destino, destination arquivo
        FROM v$archive_dest WHERE destination IS NOT NULL) LOOP
      dbms_output.put_line(arch.id || ' | ' || arch.STATUS || ' | ' || arch.tipo ||
        ' | ' || arch.destino || ' | ' || arch.arquivo);
    END LOOP;
    dbms_output.put_line('');
  END IF;
 
  dbms_output.put_line('Logs do grupo ADMIN');
  dbms_output.put_line('===================');
  dbms_output.put_line('Nome            | Diretório');
  dbms_output.put_line('----------------|----------');
  FOR admin IN (
    SELECT RPAD(name,15) log, value
      FROM v$parameter
      WHERE name IN ('audit_file_dest', 'background_dump_dest', 'core_dump_dest',
        'user_dump_dest')
      ORDER BY NAME) LOOP
    dbms_output.put_line(admin.log || ' | ' || admin.value);
  END LOOP;
  dbms_output.put_line('');
 
  dbms_output.put_line('Configurações de auditoria e segurança');
  dbms_output.put_line('======================================');
  dbms_output.put_line('Parâmetro       | Valor');
  dbms_output.put_line('----------------|------');
  FOR security IN (
    SELECT RPAD(name,15) log, value
      FROM v$parameter
      WHERE name IN ('audit_trail', 'os_authent_prefix', 'remote_os_authent',
        'remote_login_passwordfile', 'utl_file_dir')
      ORDER BY NAME) LOOP
    dbms_output.put_line(security.log || ' | ' || security.value);
  END LOOP;
  dbms_output.put_line('');
 
END;
/
 
SET serveroutput OFF
SET serveroutput ON SIZE 1000000  FORMAT WRAPPED
 
BEGIN
  dbms_output.put_line('JOBs');
  dbms_output.put_line('====');
  dbms_output.put_line('    Nº | Esquema         | Dur.(min) | BK| Intervalo    ' ||
    '                       | SQL');
  dbms_output.put_line('-------|-----------------|-----------|---|--------------' ||
    '-----------------------|----');
  FOR job IN (
    SELECT LPAD(job,6) id, RPAD(schema_user,15) esquema, RPAD(TRUNC(total_time/60),9) dur_mi,
      broken, RPAD(interval,35) interval, what
      FROM dba_jobs
      WHERE interval !='null'
  ) LOOP
    dbms_output.put_line(job.id || ' | ' || job.esquema || ' | ' || job.dur_mi || ' | '
      || job.broken || ' | ' || job.interval || ' | ' || job.what);
  END LOOP;
  dbms_output.put_line('');
END;
/
 
SET serveroutput OFF
SET serveroutput ON SIZE 1000000  FORMAT WRAPPED
 
BEGIN
  dbms_output.put_line('Segmentos por esquema, tablespace e tipo');
  dbms_output.put_line('========================================');
  dbms_output.put_line('Esquema         | Tablespace      | Tipo de Objeto  | QT   | Tam(MB)');
  dbms_output.put_line('----------------|-----------------|-----------------|------|--------');
  FOR schema IN (
    SELECT 
      RPAD(owner,15) schema, 
      RPAD(tablespace_name, 15) tablespace, 
      RPAD(segment_type,15) type, 
      LPAD(count(*),4) qt, LPAD(ROUND(SUM(bytes)/1024/1024),6) mb 
        FROM dba_segments
        WHERE OWNER NOT IN ('SYS','OUTLN','SYSTEM','WMSYS','XDB','SISMON')
        GROUP BY owner, tablespace_name, segment_type
        ORDER BY owner, tablespace_name, segment_type) LOOP
    dbms_output.put_line(schema.schema || ' | ' || schema.tablespace || ' | '
      || schema.type || ' | ' || schema.qt || ' | ' || schema.mb);
  END LOOP;
  dbms_output.put_line('');
END;
/
 
SET serveroutput OFF
SET serveroutput ON SIZE 1000000  FORMAT WRAPPED
 
BEGIN
  dbms_output.put_line('Objetos inválidos por esquema e tipo');
  dbms_output.put_line('====================================');
  dbms_output.put_line('Esquema         | Tipo de Objeto  |  QT');
  dbms_output.put_line('----------------|-----------------|----');
  FOR invalid IN (
    SELECT RPAD(owner,15) AS esquema, RPAD(object_type,15) tipo, LPAD(COUNT(*),4) qt
      FROM dba_objects
      WHERE STATUS != 'VALID'
      GROUP BY owner, object_type) LOOP
    dbms_output.put_line(invalid.esquema || ' | ' || invalid.tipo || ' | ' || invalid.qt);
  END LOOP;
  dbms_output.put_line('');
END;
/
 
SET serveroutput OFF
SET serveroutput ON SIZE 1000000 FORMAT WRAPPED FORMAT WRAPPED
 
BEGIN
  dbms_output.put_line('Database Links');
  dbms_output.put_line('==============');
  dbms_output.put_line('Esquema         | Nome            | Criacao    |' ||
    ' Esquema remoto  | Host remoto');
  dbms_output.put_line('----------------|-----------------|------------|' ||
    '-----------------|------------');
  FOR dblink IN (
    SELECT 
      RPAD(owner,15) AS esquema, 
      RPAD(db_link,15) nome,
      RPAD(username,15) esquema_destino, 
      host host_destino, 
      to_char(created,'DD-MM-YYYY') criacao
        FROM dba_db_links ORDER BY host, owner) LOOP
    dbms_output.put_line(dblink.esquema || ' | ' || dblink.nome || ' | ' ||
      dblink.criacao || ' | ' || dblink.esquema_destino || ' | ' || dblink.host_destino);
  END LOOP;
  dbms_output.put_line('');
 
  dbms_output.put_line('Visões Materializadas');
  dbms_output.put_line('=====================');
  dbms_output.put_line('Esquema         | Nome            | Q Len | Atualiz. | DBLink');
  dbms_output.put_line('----------------|-----------------|-------|----------|-------');
  FOR mview IN (
    SELECT RPAD(owner,15) esquema, RPAD(mview_name,15) nome, RPAD(master_link,15) link,
      LPAD(query_len,5) len, last_refresh_date FROM dba_mviews
  ) LOOP
    dbms_output.put_line(mview.esquema || ' | ' || mview.nome || ' | ' || mview.len ||
      ' | ' || mview.last_refresh_date || ' | ' || mview.link);
  END LOOP;
  dbms_output.put_line('');
 
END;
/
SET feedback ON
SET LINESIZE  120

Atualização

Se inscreva no site do evento, vagas limitadas.

Sim,  este ano tem Copa do mundo, eleição, e claro PGCon e PGDay.

O blog podia estar parado, mas a comunidade brasileira de PostgreSQL continua a todo o vapor. Já tivemos o PGDay BA, em Ilhéus, e em breve deverá sair a data do PGDay RJ. O próximo será o PGDay DF, em 31/05 no SERPRO. Então fiquem antenados para as inscrições no evento.

Palestrantes confirmados:

• Euler Taveira
• Fábio Telles
• Fernando Ike
• Leonardo César
• João Cosme

Nesse eu vou!

Bom, fui convidado para o evento e se nada der errado (fui escalado para uma migração na data do PGDay BA) estarei lá. Nada, já fiz uma mega amarração no trabalho, o meu chefe vai me substituir! Nesse eu vou mesmo.

Me ajude a escolher o tema para a palestra no PGDay DF!

Ocorre que o pessoal me deixou a vontade para escolher o tema da palestra. Eu que sou preguiçoso tenho uma forte tendência a reciclar artigos e palestras anteriores. Claro que para o PGCon Brasil, a gente sempre tras coisa nova, mas com menos de 20 dias para preparar a palestra (é claro que não estar na organização do evento já ajuda muito) a chance de eu dar uma atualizada em uma palestra antiga aumenta.

Então gostaria de saber se alguém tem alguma sugestão para o PGDay DF. Pode ser um artigo aqui do blog, pode ser uma palestra minha no Slide Share, ou pode ser um tema novo. Comentários com sugestões aqui no blog, até o final da semana (que é quando eu vou começar a mexer nisso) são bem vindos. É claro que se eu não mandar logo a descrição da palestra para a organização o povo lá me mata também!

Não, não é. Pode chorar, pode xingar mas não é.

No ano passado fiz uma pesquisa aqui no blog sobre as rotinas de backup utilizados no PostgreSQL. O resultado foi que a maioria esmagadora só utiliza o pg_dump. Veja, eu não tenho absolutamente nada contra o pg_dump. Mas antes de me explicar, vamos pensar… PARA QUE SERVEM OS BACKUPS MESMO?

Se eu fizer uma pesquisa, 10 entre 10 irão dizer: para se recuperar em caso de desastres! Simples assim, eu faço uma cópia dos dados e caso tudo dê errado eu restauro a minha cópia e volto a trabalhar.

Simples não?

NÃO.

Não tem nada de simples nisso. Vamos avaliar melhor o caso. Primeiro temos que entender o que é “tudo dê errado”. Podem acontecer várias coisas diferentes…

Quanto tudo dá errado!

1. Desastre natural
   O CPD pegou fogo, inundou (eu já vi acontecer), teve um terremoto, jogaram um avião no prédio… bom, nestes casos só um stand by em outro prédio (ou outra cidade, outro país) resolve a situação. Mas você bem que poderia guardar os seus backups fora do prédio onde está o CPD, não?
2. Falha de hardware (exceto os discos)
   Um problema no hardware do servidor, que não seja o disco. Neste caso, basta substituir a peça com defeito e voltar a trabalhar, certo? Errado. Quem disse que você tem pentes de memória, fontes, processadores ou mesmo uma placa-mãe sobressalente para substituir naquele servidor. E no caso de você trocar algo como uma placa-mãe, provavelmente vai trocar por outro modelo, o SO pode se desentender com ele e pode exigir algum tipo de ajuste no SO.
   Isto leva tempo, mas não exige que se mexa com backup, certo? Errado. Se você não pode esperar até uma nova peça chegar, você vai querer subir o seu banco de dados em outro servidor o mais rápido possível, e aí sim você vai precisar do seu backup.
3. Falha nos discos
   Um problema físico num disco ou no seu storage. Bom, se você utiliza RAID (RAID 0 a princípio não é RAID e se você utiliza RAID 0 para guardar seus dados, saiba que existe um lugar especial no inferno reservado para você) e tem um Hot Spare configurado, você não precisa fazer nada naquele momento. Todo Storage descente não permite a criação de RAID sem o Hot Spare.
   Se você tem um storage e paga o seu caríssimo contrato de manutenção, tudo que você terá de fazer e dizer para o técnico do storage entrar e substituir o disco. Em geral quem vai lhe avisar que um disco está com problema é o técnico que chegou para trocar o disco. Os storages devem ser monitorados remotamente pelo fornecedor. Em caso de defeito eles mandam um técnico para resolver o problema ANTES de você notar que perdeu um disco.
   Mas, a vida não é perfeita, storages são caros e os contratos de manutenção também. Se você tem um RAID na sua controladora local e teve uma falha em um disco, provavelmente a vida vai continuar e você não vai nem perceber. É aí que mora o perígo. Alguém tem que monitorar os logs do SO para saber que um disco apresentou problema, apesar de tudo continuar funcionando. Se você não perceber isso, então a próxima falha no próximo disco (como os discos são comprados em geral do mesmo lote, eles tem esse péssimo hábito que queimar mais ou menos na mesma época…) vai gerar uma paralisação total dos banco. Aí você volta para o item 1 e com certeza vai ter de utilizar o seu backup para restaurar os dados. Isso se você não tiver aquela idéia maravilhosa de guardar os seus backups justamente naquele disco / RAID que deu problema. Assim fica a dica: monitore os logs do seu SO e nunca guarde o seu backup no mesmo disco que os seus dados.
4. Ok, o hardware está ótimo, mas o SO egripou do nada
   Pegou um vírus (sem comentários), deu uma tela azul (pode escolher outra cor, o efeito é o mesmo) da morte ou um mesmo um kernel panic. Uma atualização do SO fez com que tudo o mais travasse. O banco de dados não sobe mais. Você pode até tentar reinstalar o SO, limpar o vírus, etc e tal. Mas se isso demorar, você vai querer voltar o backup em outro servidor;
5. O sistema de arquivos corrompeu
   Após uma falta de energia, quando o luz voltou você teve uma grata surpresa: justamente a partição onde os dados estavam se corrompeu. Não vou entrar no mérito sobre qual sistema de arquivos é mais seguro agora. Ok, já vi gente que queria rodar um SGDB num Pen Drive… FAT não deveria servir para guardar nem base em .mdb! NTFS corrompe muito, mas EXT3, EXT4, ReiserFS, XFS, JFS, UFS, ZFS e outros também corrompem. Se você inventar de fazer um “ajuste de desempenho” no ser sistema de arquivos então…
   Bom, se a ferramenta do seu sistema de arquivos não conseguir restaurar os dados em 100%, já viu… Formate tudo e volte o backup;
6. Um DBA ou sysadmin desastrado foi lá e apagou/alterou/moveu um arquivo do banco de dados
   Vai corromper na hora. Volte o seu backup… e dê uma bronca ou PNB* no funcionário que fez esta besteira. Se foi o seu estagiário que fez isso, então peça demissão. Quem mandou deixar o estagiário chegar perto do seu banco de dados?
7. O banco de dados corrompeu por uma falha do próprio banco de dados
   É raro mas acontece. Você vai ter que verificar os logs do SO e do banco para entender o que aconteceu. Pode ser preciso aplicar um patch no servidor ou implementar um “workarround”, mais conhecido como gambiarra. Depois de entender minimamente a causa do problema e tomar providências para que isso não ocorra novamente, você vai ter de voltar o seu backup;
8. Um usuário desastrado apagou uma tabela ou outro objeto do banco de dados
   Primeiro você vai se perguntar: quem deu permissão para o usuário apagar alguma coisa no banco de dados? Se o usuário for um desenvolvedor, ídem. Se for o sysadmin, ídem. Se for o DBA… bom, sobra a bronca ou PNB*. E você vai ter que voltar o seu backup para o momento anterior ao desastre. É um detalhe importante. Tem gente que só nota a besteira dias depois de feita a besteira. Se você voltar o backup feito depois da besteira, vai continuar com o mesmo problema. Identifique quando o problema ocorreu antes de voltar o backup.

Outros usos para o backup

Você pode utilizar o seu backup para outras coisas além de recuperação de desastres:

1. Mover dados entre bases. Particularmente atualizar dados de homologação com os dados da produção. Operação bastante comum em que se utiliza o backup. Sempre que você quer fazer um teste de performance ou validar uma atualização da aplicação, alguém solicita uma atualização deste tipo.
2. Auditoria. Você pode guardar backups realizados após fechamentos cíclicos (como mensal ou anual) para efeito de auditoria. Estes backups são em geral guardados por vários anos, pelo menos 5 anos em geral.
3. Criação de novos ambientes de homologação / teste. É muito comum projetos específicos exigirem análises destrutivas dos dados e para isso utilizarem uma base/esquema separados para isso.

O tal do SLA

Antes de definir a sua estratégia de backup, você deve determinar qual o seu SLA ou Service Level Agreement. Seja pessimista e defina o quanto você pode suportar em caso de desastre. Não adianta dizer que você quer 100% de alguma coisa. 100% não existe e os famosos “five nines” ou 99,999% custam uma verdadeira fortuna. O cofre não pode ser mais caro que o ouro que você guarda. Então pense com realismo (ou pessimismo, como você preferir) nos seguintes limites:

• Quanto tempo você pode ficar com o banco de dados indisponível em caso de parada não prevista em horário comercial? E se for em horário não comercial?
• Quanto tempo você pode ficar com o banco de dados indisponível em caso de uma parada prevista (uma manutenção) em horário comercial? E se for em horário não comercial?
• Respire fundo…. você pode perder dados relativos a quanto tempo de operação?

Ok, a resposta para as 3 perguntas sempre deveria ser ZERO. Mas como eu já disse, não existe como garantir isso, e chegar perto disso custa muuuito caro. Cada uma das suas respostas vai te levar para uma estratégia de backup diferente.

Tempo necessário para restaurar um backup em caso de desastre.

Este é um ponto crítico que poucos sabem responder. Então vejamos como calcular isso. O tempo vai variar muito conforme alguns fatores:

• Você já testou uma restauração completa do backup?
• Você tem equipe disponível no local para fazer a restauração?
• Você tem equipe disponível para fazer a restauração fora do horário comercial?
• As pessoas disponíveis para fazer a restauração fora do horário comercial tem acesso remoto ao servidor?
• Você tem o procedimento de restauração documentado em papel?
• A documentação está atualizada?
• Você tem hardware de contingência disponível?

Bom, supondo que você tenha uma base de até 1GB, sem muitas demandas de desempenho, tudo fica simples. Você pode instalar o PostgreSQL em qualquer servidor com um pouco de folga no desempenho e alguns GB de espaço em disco sobrando em subir um dump guardado em outro servidor. Pode não levar mais do que uns 30 minutos para fazer tudo. Neste caso o pg_dump parece fazer sentido.

Agora se você precisar subir um novo servidor dedicado para abrigar uma base com 100GB, então você terá problemas com o pg_dump. Importar um dump para criar uma base de 100GB deve levar várias horas. Se a base tiver alguma tabela muito grande, você poderá precisar fazer ajustes específicos para conseguir recriar seus índices. Se a base tiver algo em torno de 500GB por exemplo, isso pode levar dias. Mesmo com a paralelização do processo, se você souber fazer isso com cuidado.

Como evitar a perda de dados?

Outro problema com o pg_dump. Quanto tempo de operação em produção você está disposto a perder? Suponha que você faça o backup toda madrugada, e seu desastre ocorra no final da tarde. Se você voltar o seu backup, você vai perder todas as alterações realizadas ao longo do dia. Isso é muito grave e intolerável para a maioria dos ambientes OLTP. É claro que você pode realizar o pg_dump mais de uma vez por dia, mas fazer isso em horário comercial costuma ter um impacto muito negativo na performance. Quais as soluções para contornar este problema?

• Replicação: existem N ferramentas de replicação. Algumas síncronas, outras assíncronas. Existe uma excelente documentação sobre os principais tipos de replicação para PostgreSQL. De qualquer forma, podemos categoriza-las em:
  • As ferramentas síncronas garantem a perda ZERO de dados. Cada vez que um COMMIT é realizado no servidor de produção, outro COMMIT é realizado na réplica. Somente após o COMMIT ser realizado com sucesso na base de produção e na réplica é que a operação prossegue. Resultado: ZERO em perda de dados e um gargalo de performance terrível. Para conseguir ter uma performance razoável com replicação síncrona, você vai ter que gastar muito em hardware e em ajustes de performance.
  • As ferramentas assíncronas não garantem ZERO de perda de dados, todos possuem algum atraso na sincronia entre o servidor de produção e a réplica.
• Point In Time Recovery: é uma técnica que consistem em fazer uma cópia do WAL (Write Ahead Log) do PostgreSQL e utilizar estas cópias para atualizar um backup até um horário específico. O PITR é uma técnica consagrada em todos os SGDBs sérios do mercado. Você não precisa instalar nenhum software extra para faze-lo funcionar. O PITR também não penaliza o desempenho do seu servidor, uma vez que o WAL já é gerado normalmente. O único overhead é a cópia deles. Você também pode ajustar o intervalo máximo entre as cópias do WAL, o que lhe permite um ajuste do período máximo de perda de dados que você terá com o PITR (ele é assíncrono então não garante perda ZERO de dados).

Se você escolher logo uma estratégia de replicação, pense em primeiro lugar no Stand by. Com o lançamento da versão 9.0 o Stand by sofreu melhorias importantes e tudo indica que a próxima versão terá mais melhorias ainda. Combinar um Stand By com outras técnicas de backup é a melhor forma de se proteger contra a perda de dados e o downtime. Você estará utilizando uma solução nativa, relativamente simples, bem testada e documentada, com baixo impacto em performance e com um custo realmente baixo em comparação com outras alternativas.

Backup físico, o primeiro passo.

É claro que apesar de recomendar o uso do stand by, ele não é algo tão simples e barato assim. Você precisa de um servidor com capacidade semelhante ao de produção para suportar a mesma carga, lembre-se disto.  Novamente, existe excelente documentação sobre como criar um standby. Você também precisa aprender com perfeição a fazer backups físicos e saber restaurá-los de forma adequada.

Existem dois tipos de backup físico, o frio e o quente. O frio é feito com o banco de dados desativado. Você copia todos os arquivos do banco (inclusive os de configuração) e depois reativa o banco de dados. Este método é muito simples e seguro, mas tem aquele problema básico: você precisa tirar o banco de dados do ar. Se você não tem janela para isto ou o seu sistema é 24X7, então você terá que recorrer ao backup quente. O quente é um pouco mais complicado,  pois exige que você avise o PostgreSQL quando vai começar a copiar os arquivos e quando terminou. Ele também EXIGE que você faça a cópia dos logs do WAL, utilizando o parâmetro “archive_command“. Não vou me alongar sobre o assunto aqui. Leia a documentação atentamente sobre isso.

Depois que você se tornar um expert em fazer backups e recuperações de backups físicos quentes, já estará de quebra fazendo o PITR de forma natural e estará pronto para fazer o seu Stand By, que utiliza o backup quente como princípio para a sua criação.

Então eu não preciso mais do pg_dump?

Precisa sim. Não para a recuperação de desastres, mas para os outros casos mencionados. Suponhamos que você precisa restaurar um backup feito há 8 anos atrás. Provavelmente o servidor onde este backup foi gerado não existe mais. Talvez  o DBA que criou o procedimento  de backup não trabalhe mais na empresa. Certamente o SO e a versão do SGDB mudaram, pode ser que você não tenha mais versões compatíveis disponíveis. Então o backup físico não é indicado para estas situações. Ele é adequado para recuperação de desastres, mas não para reter backups por um longo período. Com um backup feito pelo pg_dump, você pode migrar os dados para outra versão do PostgreSQL ou até mesmo para outro SGDB.

E tem mais um detalhe, backups físicos são monolíticos. Você precisa restaurar o banco de dados no mesmo SO, com a mesma versão do PostgreSQL, com a mesma estrutura de diretórios, o mesmo sistema de arquivos e além de tudo precisa restaurar o banco de dados inteiro. Suponhamos que você só precise de algumas tabelas, o pg_dump oferece todo o tipo de flexibilidade para restaurar apenas uma parte dos dados.

Estratégia de backup.

Ok, o pg_dump não é O backup, ele é parte de uma estratégia de backup. Você precisa do pg_dump para gerar backups esporádicos e guarda-los por um longo período. Para a recuperação de desastres você deve ter um backup físico (quente ou frio), cópia dos logs do WAL e cópia dos arquivos de configuração. Você não precisa guardar o backup físico pro muito tempo, alguns dias é razoável. Depende de quanto você deseja voltar no tempo se necessário, e de quanto você confia na sua mídia de armazenamento. Lembre-se da máxima: quem tem um backup não tem nada. Faça o backup físico uma vez por dia e não sobreescreva o backup do dia anterior. Você pode fazer o backup físico para o disco em outro servidor e gravar este backup em fita diferentes todos os dias por exemplo.  Já a cópia do WAL é feita automaticamente, mas deve ser copiada para fora do servidor também, se não imediatamente no comando do “archive_command”, faça isso em intervalos curtos para evitar a perda de dados em caso de desastres.

Conclusões :

(atualizado em 17/05/2010)

• Backup não é simples, mesmo em bancos de dados pequenos;
• Backup exige estudo, dedicação e disciplina;
• Backup não deve ser responsabilidade de estagiários, deve ser criado e verificado diariamente por pessoas qualificadas e de confiança;
• Backup é caro: os recursos de hardware são caros e os recursos humanos também;
• Nunca diga que você está 100% seguro, você não está;
• Você pode e deve estimar o tempo para se recuperar de desastres e qual o SLA que você pode entregar com os recursos disponíveis. Documente isso e protocole a entrega deste documento junto ao seu superior. Sim, isto pode salvar o seu emprego e de toda a sua equipe junto. Sua família agradece.

Atualização:

Em tempo, existe uma nova ferramenta para backup incremental chamada pg_rman. Sim, é um clone do RMAN do Oracle para o PostgreSQL. A ferramenta é relativamente nova, mas parece que o projeto está bastante ativo. Alguém aí já testou?

OBS: Gostaria de dedicar este humilde texto à todos aqueles que me contrataram para recuperar seus bancos de dados (desculpe, somente àqueles que não me deram calote depois, os que não pagaram vão para o mesmo lugar daqueles que utilizam RAID 0). Àqueles que sofreram durante madrugadas intermináveis com a pressão de não saber se a empresa voltaria a funcionar no dia seguinte e todas as conseqüências que daí decorrem.

* PNB = Pé Na Bunda, demissão mesmo.

Há tempos atrás eu postei este script tosco para extrair usuários com suas respectivas senhas no Oracle. Eu sei, no PostgreSQL, basta um ‘pg_dumpall -r’ e tudo se resolve, mas no Oracle, você tem um pouco mais de trabalho, ainda mais se não quiser utilizar o ultra-super-mega-boga-hyper-duper-master-blaster-heavy-metal Database Control, também conhecido como Enterprise Manager e mais uma dúzia de apelidos.

Bom, o script a seguir cria um arquivo com os usuários, grupos (ok, o nome certo é ‘roles’), permissões em tabelas, privilégios de sistema, roles, quotas de tablespace e mais alguma coisa. Se você precisa atualizar bases entre versões diferentes (como do 9i para o 10g ou para o 11g), ou entre Sistemas Operacionais diferentes (se você tem o Oracle Enterprise dá para usar o conceito de tablespace transportável).

Enfim, todo DBA sabe utilizar as ferramentas de geração de Dump (exp ou expdp) e importação de Dump (imp, impdp). E quase todos costumam importar os dados por esquema e não tudo de uma vez (full=y).  E como sempre, antes de importar o esquema, o usuário deve existir da base de destino.

Chega de blá, blá, blá. Segue o script:

--
-- mov_user.sql
--
-- Exporta usuários roles e privilegios.
--
--
-- Criado por Fábio Telles Rodriguez
--
-- Última atualização: 05/05/2010
--
--
 
-- Ajusta parâmetros de visualização do SQL*Plus
SET DEFINE OFF
SET PAGESIZE 0
SET WRAP      ON
SET TRIMSPOOL ON
SET SERVEROUTPUT ON
SET VERIFY OFF
SET FEED OFF
SET HEADING OFF
SET LINESIZE  120
 
-- Gera arquivo em mov_user.sql
SPOOL mov_user_.sql
 
-- Verifica se está conectado com o usuário SYS
WHENEVER SQLERROR EXIT;
 
DECLARE
  v_user varchar(100);
BEGIN
  SELECT user INTO v_user FROM dual WHERE user = 'SYS';
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20000, 
        'ERRO! Você deve estar conectado como SYS para rodar este script!!!');
END;
/
 
 
--Cria usuários
SELECT
    'CREATE USER ' || LOWER(username) || 
    DECODE (password, 
            'EXTERNAL',' IDENTIFIED EXTERNALLY',
            'GLOBAL',' IDENTIFIED GLOBALLY AS ''' || external_name,
            ' IDENTIFIED BY VALUES ''' || password || '''') || CHR(10) ||
    '    DEFAULT TABLESPACE ' || default_tablespace || CHR(10) ||
    '    TEMPORARY TABLESPACE ' || temporary_tablespace || CHR(10) ||
    '    PROFILE ' || profile || CHR(10) ||
    DECODE (account_status,
        'OPEN',                           '    ACCOUNT UNLOCK;',
        'LOCKED',                         '    ACCOUNT LOCK;',
        'LOCKED(TIMED)',                  '    ACCOUNT LOCK;',
        'EXPIRED & LOCKED',               '    ACCOUNT LOCK PASSWORD EXPIRE;',
        'EXPIRED & LOCKED(TIMED)',        '    ACCOUNT LOCK PASSWORD EXPIRE;',
        'EXPIRED(GRACE) & LOCKED',        '    ACCOUNT LOCK PASSWORD EXPIRE;',
        'EXPIRED(GRACE) & LOCKED(TIMED)', '    ACCOUNT LOCK PASSWORD EXPIRE;',
        'EXPIRED',                        '    ACCOUNT UNLOCK PASSWORD EXPIRE;',
        'EXPIRED(GRACE)',                 '    ACCOUNT UNLOCK PASSWORD EXPIRE;') mov_user
  FROM dba_users
  WHERE
    default_tablespace NOT IN ('EXAMPLE','DRSYS','CWMLITE','ODM','XDB','SYSTEM','SYSAUX')
  ORDER BY default_tablespace, username
;
 
-- Concede quotas para usuários
SELECT 
    'ALTER USER ' || LOWER(username) || 
    ' QUOTA UNLIMITED ON ' || LOWER(tablespace_name) || ';' 
  FROM dba_ts_quotas;
 
-- Especifica planos de recursos para usuários
SELECT 
    'EXEC DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING' || CHR(10) ||
    '(DBMS_RESOURCE_MANAGER.ORACLE_USER, ''' || LOWER(username) || 
    ''', ''' || LOWER (INITIAL_RSRC_CONSUMER_GROUP) || ''');'
  FROM dba_users
  WHERE
    default_tablespace NOT IN ('EXAMPLE','DRSYS','CWMLITE','ODM','XDB','SYSTEM','SYSAUX')
  ORDER BY default_tablespace, username
;
 
-- Cria ROLEs
SELECT 
    'CREATE ROLE ' || LOWER(name) || ' ' ||
    DECODE (password, 
        NULL,'NOT IDENTIFIED;',
        'EXTERNAL',' IDENTIFIED EXTERNALLY;',
        'GLOBAL',' IDENTIFIED GLOBALLY',
        ' IDENTIFIED BY VALUES ''' || password || ''';')
  FROM sys.user$
  WHERE 
    type# = 0 AND 
    name NOT IN ('PUBLIC', '_NEXT_USER', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE',
      'AUTHENTICATEDUSER', 'CONNECT', 'CTXAPP', 'DBA', 'DELETE_CATALOG_ROLE', 'EJBCLIENT',
      'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS',
      'GLOBAL_AQ_USER_ROLE', 'HS_ADMIN_ROLE', 'IMP_FULL_DATABASE', 'JAVADEBUGPRIV',
      'JAVAIDPRIV', 'JAVASYSPRIV', 'JAVAUSERPRIV', 'JAVA_ADMIN', 'JAVA_DEPLOY',
      'LOGSTDBY_ADMINISTRATOR', 'MGMT_USER', 'OEM_ADVISOR', 'OEM_MONITOR',
      'PLUSTRACE', 'RECOVERY_CATALOG_OWNER', 'RESOURCE',  'SCHEDULER_ADMIN',
      'SELECT_CATALOG_ROLE', 'SNMPAGENT','WM_ADMIN_ROLE', 'XDBADMIN', 'XDBWEBSERVICES')
;
 
-- Gera GRANTs em objetos
SELECT 
    'GRANT ' || LOWER(privilege) ||
    ' ON ' || LOWER(owner) || '.' || LOWER(table_name) ||
    ' TO ' || LOWER(grantee) || 
    DECODE(grantable,'YES','WITH GRANT OPTION',' ') ||
    DECODE(hierarchy,'YES', 'WITH HIERARCHY OPTION;',';')
  FROM dba_tab_privs p, dba_users u
  WHERE
    u.username = p.grantee AND
    u.default_tablespace NOT IN ('EXAMPLE','DRSYS','CWMLITE','ODM','XDB','SYSTEM','SYSAUX')
;
 
-- Gera GRANTs em colunas
SELECT 
    'GRANT ' || LOWER(privilege) || '(' || LOWER(column_name) || ')' ||
    ' ON ' || LOWER(owner) || '.' || LOWER(table_name) || 
    ' TO ' || LOWER(grantee) || 
    DECODE(grantable,'YES','WITH GRANT OPTION;',';')
  FROM dba_col_privs;
 
-- Gera GRANTs em roles
SELECT 
    'GRANT ' || LOWER(granted_role) || ' TO ' || LOWER(grantee) || 
        DECODE(admin_option,'YES','WITH ADMIN OPTION;',';')
  FROM dba_role_privs p, dba_users u
  WHERE
    u.username = p.grantee AND
    u.default_tablespace NOT IN ('EXAMPLE','DRSYS','CWMLITE','ODM','XDB','SYSTEM','SYSAUX')
;
 
-- Altera roles que não são padrão
DECLARE
  v_non_default   NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_non_default 
        FROM dba_role_privs WHERE default_role = 'NO';
  IF v_non_default > 0 THEN
    FOR u IN (SELECT DISTINCT grantee 
                FROM dba_role_privs 
        WHERE default_role = 'NO' 
        ORDER BY grantee) 
    LOOP
      DBMS_OUTPUT.PUT('ALTER USER ' || LOWER(u.grantee) || ' DEFAULT ROLE ALL EXCEPT ');
      SELECT count(*) INTO v_non_default 
        FROM dba_role_privs 
        WHERE grantee = u.grantee AND default_role = 'NO';
      FOR r IN (SELECT rownum, granted_role 
                  FROM dba_role_privs 
                  WHERE grantee = u.grantee AND default_role = 'NO') 
      LOOP
        DBMS_OUTPUT.PUT(LOWER(r.granted_role));
        IF r.rownum != v_non_default THEN 
          DBMS_OUTPUT.PUT(',');
        END IF;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(';');
    END LOOP;
  END IF;
END;
/
 
-- Gera GRANTs em privilégios de sistema
SELECT 
    'GRANT ' || LOWER(privilege) || ' TO ' || LOWER(grantee) || 
        DECODE(admin_option,'YES','WITH ADMIN OPTION;',';')
  FROM dba_sys_privs p, dba_users u
  WHERE
    u.username = p.grantee AND
    u.default_tablespace NOT IN ('EXAMPLE','DRSYS','CWMLITE','ODM','XDB','SYSTEM','SYSAUX')
;
 
SPOOL OFF

Sugestões de melhoria são bem vindas.