This is a followup to my previous post about using a Datapump export to ASM on an Exadata system. I’m finalizing my backup of a large table that I’m modifying this weekend. I’ve been comparing notes with another team member on how this table has been backed up in the past. At one point in the past this table was backed up in 2.5 hours with a parallel 16 insert into an uncompressed table with parallel DML enabled. But, I don’t want to create another copy of this huge table just to back it up, so that is why I’m looking at datapump. But, my first test took 5.25 hours which seemed slower than the parallel insert. My test was parallel=8 so it was half the parallelism of the previous parallel insert, but I don’t expect twice the parallelism to equal half the runtime so datapump still seems slower than the parallel insert.
Yesterday I remembered that there was a parameter you could stick in the datapump export parfile that would force all of the backup processes on a parallel export to stay on the node you ran datapump on. In my previous test I was puzzled when I ran “top” during the export to find only four backup processes on node 1 where I ran my test. Yesterday I ran an AWR report for both nodes covering the time frame of my datapump test and found both nodes with datapump activity. So, the light clicked on and I found/remembered the cluster=no datapump parfile parameter setting. I added this setting to my parfile, re-ran an export of the same table, and it ran in 4 hours instead of 5.25. When I ran top on node 1 this time I saw 8 export processes running instead of four.
Here is my new parameter file, with the table name removed:
DIRECTORY=asm_dump JOB_NAME=datapumptoasm_export2 DUMPFILE=datapumptoasm2%u.dmp LOGFILE=asm_dump_log:expdp_datapumptoasm2.log COMPRESSION=NONE TABLES=(...put your table name here...) PARALLEL=8 cluster=no
So, if you are doing parallel datapump export on a RAC or Exadata system you might try cluster=no to see if it speeds your export. It appears to make the parallel uncompressed datapump export to ASM about the same speed as a parallel insert into an uncompressed table.
Well, the production backup this weekend took about 6 hours. In the past the same table was backed up using an uncompressed parallel 16 insert in 2.5 hours. Could be the amount of data isn’t the same but it wouldn’t be half the current size. The datapump backup was fast enough for my purpose but it appears the insert is still roughly twice as fast.