Datapump/Performance : Exportando grandes tabelas LOB de maneira rápida e eficiente

Recentemente me deparei com a necessidade de realizar um export de algumas tabela com mais de 500GBs de tamanho, cerca de 2TBs no total. Se tratava de um backup pontual executado todo mês para fins de fechamento. O mesmo em sua ultima execução havia demorado cerca de 6 horas para ser exportado e como havia a necessidade de exportar os dados do schema para só então o fechamento ser iniciado o mesmo era feito em duas partes, primeiro era feito o backup sem as tabelas detentoras dos LOBS e no final de semana seguinte era feito backup apenas das tabelas detentoras dos LOBS. Não era a melhor opção mas era oque já vinha sendo feito a um bom tempo. Dessa forma de maneira a melhorar esse processo para que o export do schema fosse feito de uma só vez sem a necessidade de horas extras no final de semana comecei a procurar alternativas de exportar as tabelas com LOBS de maneira mais performática já que o parallel como parâmetro no export é ignorado uma vez que estamos realizando um export de tabelas com LOBs.

Dessa forma a maneira ideal é executar o expdp usando chunks por rowid, que consiste em separar a tabela em pedaços usando o rowid como referência e realizar o export desses pedaços separadamente em exports diferentes, se trata de um paralelismo DIY, criando N processos expdp para aumentar a eficiência da atividade levando em consideração os recursos computacionais disponíveis e a massa de dados a ser exportada.

Depois de conversar com algumas pessoas da área varias ideias surgiram e entre elas a idea dos chunks mas havia a necessidade de testar antes. Dessa forma cheguei a esse script Perl onde realizei diversos testes em tabelas LOB menores e até maiores e se mostrou muito consistente tanto o export como o import. Se trata de um script perl onde você indica quantos processos expdp você deseja para exportar a tabela. Em alguns de meus testes cheguei usar 11 processos expdp para uma tabela de 700GBs levando em média 15 minutos para realizar o export. Abaixo o script usado:

Script Export em perl:

#! /bin/perl

use strict;

my $PARALLEL=4;

my $PFILE="/u01/dump/TESTE_lob.par";

my @row;

my $sp_flash_time=`sqlplus -S / as sysdba <<EOF

set pages 9999 lines 172

col x format a172

set trimout on

set trimspool on

select 'row:' || TO_CHAR(SYSDATE, 'dd/mon/yyyy hh24:mi:ss') x from dual

/

EOF`;

my $rc = $?;

if ($rc) {

print "sqlplus failed - exiting";

exit $?;

}

my $flash_time;

foreach ( split (/\n/, $sp_flash_time) ) {

chomp;

if ( /^row:/ ) {

s/^row://g;

$flash_time = $_;

}

}

if ( !defined ($flash_time) ) {

print "Unable to get database time - exiting";

}

print "Using flash time of: " . $flash_time . "\n";

open(PARFILE, ">", $PFILE);

print PARFILE "flashback_time=\"to_timestamp('${flash_time}', 'dd/mon/yyyy hh24:mi:ss')\"\n";

print PARFILE "parallel=1\n";

print PARFILE "exclude=statistics\n";

close(PARFILE);

$SIG{CHLD} = 'IGNORE';

my $shard = 0;

my $cmd;

foreach ($shard = 0 ; $shard < $PARALLEL ; $shard++) {

$cmd = "expdp \\\'/ as sysdba\\\' tables=TESTE.TABELA_TESTE job_name=JOB_EXPORT_TESTE_${shard} dumpfile=TESTE:EXPDP_TESTE_${shard}_%U.dmp logfile=TESTE:EXPDP_TESTE_${shard}.log";

$cmd .= " parfile=${PFILE}";

$cmd .= " query=TESTE.TABELA_TESTE:\'\"where mod(dbms_rowid.rowid_block_number(rowid), ${PARALLEL}) = " . $shard . "\"\'";

$cmd .= " &";

print "Starting: $cmd\n";

my $cpid = system($cmd);

} 

[oracle@ol07db01 dump]$ 

O campo "my $PARALLEL=" é onde voce indica a quantidade de processo a serem abertos e a parte mais a banco onde ele passa a parametrização para o expdp deve ser alterada como de costume, informando o logfile, dumpfile e etc.

Outro ponto importante, o perl deve estar instalado e configurado no servidor para que o mesmo funcione. É necessária a alteração do cabeçalho para que o SO possa buscar as libs do perl e interpretar o script. Desse forma onde de costume temos "#!/usr/bin/bash", devemos alterar para o valor retornado através do comando "which perl", o mesmo vai nos mostrar onde constam as bibliotecas para que o script seja interpretado e executado.

Conclusão

De fato esse problema foi bastante desafiador pois o tempo era realmente crucial para o fechamento que era feito mensalmente e não ter o backup up to date gerava um insegurança.

Abaixo alguns artigos que serviram como ponto de partida para minha análise antes de chegar a solução:

Primary Note for Data Pump - My Oracle Support[ ID 1264715.1]

Checklist For Slow Performance Of DataPump Export [ID 453895.1]

Parallel Capabilities of Oracle Data Pump [ID 365459.1]  

https://jensenmo.blogspot.com/2012/10/optimising-data-pump-export-and-import.html