Datapump to RECO ASM Diskgroup on Exadata

I have a large sub-partitioned table on our Exadata system that I need to backup prior to a change.  In the past we have done backups of this large table to another table using inserts and parallel DML,  but it just adds more data in our regular RMAN backups to have a copy of the original table sitting around as a backup.  It dawned on me that our Exadata systems have an empty RECO diskgroup in ASM.  We used to use RECO for RMAN backups to disk.  But now our RMAN backups are done another way and RECO sits empty.  Eventually we will probably reclaim the space in RECO by putting datafiles there.  But for now it is an opportunity.  Another factor is that we haven’t had any luck doing datapump backups to DBFS.  We have a large DBFS filesystem that we use to hold flat files for data loads but when we try to use it as a dumping ground for datapump we get wierd internal errors – probably some bug.  But, we haven’t tried datapumping straight to ASM, so that’s what I decided to try.  I found a nice blog post describing how to do this:

http://www.gloworld.co.uk/Home/datapump/datapump-notes/usingasmwithdatapump

I did essentially all the steps in that post with minor name changes and RECO as the diskgroup.  I also did a parallel export and just one big table.

Here was what I did in asmcmd:

ls
cd reco
mkdir dpdump

Here are my commands connected as SYSDBA:

create or replace directory ASM_DUMP as '+RECO/dpdump';
grant read,write on directory asm_dump to system;
create or replace directory ASM_DUMP_LOG as 
  '/home/oracle/datapumptoasm';
grant read,write on directory asm_dump_log to system;

Prior to running these I created the unix directory /home/oracle/datapumptoasm. Here is the parfile:

DIRECTORY=asm_dump
JOB_NAME=datapumptoasm_export
DUMPFILE=datapumptoasm%u.dmp
LOGFILE=asm_dump_log:expdp_datapumptoasm.log
COMPRESSION=NONE
TABLES=(...put your own table name here...)
PARALLEL=8

expdp system parfile=expdp_asm.par

It exported 1.7 terabytes in about 5.25 hours.  This was on a quarter rack Exadata V2.  Here is the end of the output log:

Master table "SYSTEM"."DATAPUMPTOASM_EXPORT" successfully 
  loaded/unloaded
***********************************************************
Dump file set for SYSTEM.DATAPUMPTOASM_EXPORT is:
  +RECO/dpdump/datapumptoasm01.dmp
  +RECO/dpdump/datapumptoasm02.dmp
  +RECO/dpdump/datapumptoasm03.dmp
  +RECO/dpdump/datapumptoasm04.dmp
  +RECO/dpdump/datapumptoasm05.dmp
  +RECO/dpdump/datapumptoasm06.dmp
  +RECO/dpdump/datapumptoasm07.dmp
  +RECO/dpdump/datapumptoasm08.dmp
Job "SYSTEM"."DATAPUMPTOASM_EXPORT" successfully completed 
  at 15:19:13

This might be worth a try if you have a bunch of free space in ASM as we do.

– Bobby

 

 

 

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Datapump to RECO ASM Diskgroup on Exadata

  1. Pingback: cluster=no to speed Exadata datapump to ASM | Bobby Durrett's DBA Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.