Speed Up Large Oracle Database Export

Preparing Database Upgrade 11G, to 12c, Our first planned activity is to perform a DataPump export.  In the past the export has taken almost 24 hours, and the import has taken over 48 hours.  Needless to say this is unacceptable to our customer.
Should mention we have a 2 node RAC Database, that has 24 cores.  Plan to use parameter I haven’t used before.  Database is reasonable large (~12TB)

Anyone have experience setting these parameters cluster=Y Parallel=48?

$expdp glass/GlassPass-1111 directory=dumpdir  dumpfile=bigTest%U.dmp  parallel=48  job_name=bigTest  cluster=y  estimate_only=y
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you have RAC then of course you have to do 2 important things:
1. To make a file share and to make it visible on all RAC instances.
2. Use CLUSTER=Y or do not use it at all (in the last case the all RAC instances will be involved)
3. Set large_pool parameter on a big amount of RAM.

If possible try to "import" the indexes in another step. Indexes are not imported, DDL CREATE INDEX will be used instead.

I have in the past created the DDLs from the export and extracted the DDLs for index creation. Next step was to add two parameters:

This is not always possible (so many indexes) but gives the possibility to add the indexes in next step without locking the base tables (ONLINE option).

Good luck!
sdrussAuthor Commented:
schwertner:  Any issue with setting DataPump parameter, parallel so big, i.e. parallel=48?  But will it actually help?
Mark GeerlingsDatabase AdministratorCommented:
I've done lots of exports and imports, and yes, the imports usually take longer than the exports.  But no, I haven't ever used the "parallel" option with DataPump.  With a 2-node RAC, I would recommend dividing up the export by schema(s), if possible, or by tables, so each node can work independently and not cause inter-node traffic.  So, maybe setting parallel=24 then for each of the two export jobs would work well.

I think there is also a RAC setting or parameter something like "force local" that can be used to avoid causing inter-node traffic that may not help this process.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdrussAuthor Commented:
Can't use parallel and estimate_only together?
Mark GeerlingsDatabase AdministratorCommented:
“Estimate only”?  That only affects the time to calculate statistics during the import.  That may help a bit.  I don’t know if that is compatible with “parallel” or not.
The bottleneck in the import with DP are the indexes. This is so because in fact the indexes are not exported, instead CREATE INDEX statements are generated by export and executed by import. PARALLEL will work fine by export and by import of the tables. The prerequisite to use RAC is to made a share that is writable from all nodes of the RAC.

The major problems arises by the index creation part of the Import. This is the reason why the shops are trying to divide data and index import. Burleson Consulting has good articles about this.

In Oracle 11g I have faced an unpleasant problem.
1. The CREATE INDEX statements are with NO parallel parameter at all (unexpected!)
2. The CREATE INDEX statements do no use ONLINE parameter (is logical!)

My solutions was to divide the indexes in 2 major parts:
1. Ordinary B-tree indexes
2. Domain Indexes (Oracle Text and other applications).

I have excluded from the import thr Domain Indexes and imported only the ordinary indexes.
Before this I have extracted the CREATE INDEX part of the import (there is a special parameter or technology to do this).
Manually I have added PARALLEL and ONLINE option to the CREATE INDEX statements (the domain indexes were only 16!).
After that I have run the script with CREATE INDEX parameter separately.

Speaking shortly:
You have not many tool to influence the Import of the Data (PARALLEL and multimode processing are the only). Of course do not try to compress the export files - it is paid option and also it speeds down the import.

Analyze the indexes:
- count 1500 or 150,000
-type - normal B-Tree and Domain

Pay close attention on the Import (in fact CREATE INDEX part)

The speculation here is that after importing the data you can start the Data Base. It will work slowly (of course the ordinary indexes has to be created!). Try to create the Domain Indexes (if any) in ONLINE mode.

Of course my experience is not the best advice, so everybody can add his thoughts.

Also be aware that the Import is dependent on the RAM - SGA, SHARED_POOL, LARGE_POOL,
Index creation uses intensively SORT operations  - so the big PGA area is a good thing.
Also the TEMP tablespace should be big enough.

But the big amount of RAM is very important. Also outside the OS and the instance. Unix has native mechanism to use this space to speed up the disk read/write operations.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.