Expdp e Impdp dicas: Criar diretórios de backup e importação no Oracle

Expdp e Impdp dicas

Criar o diretório do backup via expdp com o usuário do backup

export ORACLE_SID= <instance_name>
sqlplus /nolog
connect / as sysdba

create or replace directory EXP_DIR_FULL as '/bkp_servidor/<instance_name>/';
grant read  on  directory EXP_DIR_FULL  to backup;
grant write on  directory EXP_DIR_FULL  to backup;
exit

criar o parfile

usando parfile ficar escondido o usuário e a senha

cat <instance_name>.par
=================
userid=BACKUP/<senha> directory=exp_dir_full FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')" PARALLEL=4

expdp parfile=<instance_name>.par full=Y  dumpfile=<instance_name>%u.dmp logfile=<instance_name>.log

Podemos querer voltar um backup …temos que criar imp_dir que poderá ou não ser igual ao exp_dir

export ORACLE_SID=<instance_name>
sqlplus /nolog
connect / as sysdba
create or replace directory imp_DIR as '/bkp_servidor/<instance_name>/';
grant read  on  directory imp_dir  to backup;
grant write on  directory imp_dir  to backup;
exit

Obs. Eu nunca uso @base, fica mais lento porque usa a rede.

setar ORACLE_SID antes ficar melhor e mais rápido.

export ORACLE_SID=base

impdp backup/<senha> directory=imp_DIR dumpfile=<arquivo>. dmp logfile=owner_dest.imp remap_schema=owner:owner_dest tables=owner.tabela
impdp backup/<senha> directory=IMP_dir dumpfile=spo_01.dmp logfile= spo_backup_20120724.imp remap_schema= spo:spo_backup_20120724
impdp backup/<senha> directory=IMP_DIR dumpfile= pwda_%u.dmp logfile= pwda.impdp schemas=pwda

usando parfile

cat <instance_name>_imp.par

userid=BACKUP/<senha> directory=imp_dir

comando

impdp parfile=<instance_name>_imp.par dumpfile=<arquivo>. dmp logfile= owner_dest.imp remap_schema=owner:owner_dest tables= owner.tabela
impdp parfile=<instance_name>_imp.par dumpfile=spo_01.dmp logfile= spo_backup_20120724.imp remap_schema=  spo:spo_backup_20120724
impdp parfile=<instance_name>_imp.par dumpfile= pwda_%u.dmp logfile= pwda.impdp schemas=pwda

usando a rede o comando completo ficaria assim

impdp backup/<senha>@base directory=imp_DIR dumpfile =<arquivo>.dmp logfile= owner_dest.imp remap_schema=owner:owner_dest tables= owner.tabela
impdp backup/<senha>@base directory=IMP_dir dumpfile= spo_01.dmp logfile= spo_backup_20120724.imp remap_schema= spo:spo_backup_20120724
impdp backup/<senha>@base directory=IMP_DIR dumpfile= pwda_%u.dmp logfile= pwda.impdp schemas=pwda