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:
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.