• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 51
  • Last Modified:

Speed Up Large Oracle Database Export

Preparing Database Upgrade 11G, 11.2.0.3 to 12c, 12.1.0.1. 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
0
sdruss
Asked:
sdruss
  • 2
  • 2
  • 2
1 Solution
 
schwertnerCommented:
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:
1. ONLINE
2. PARALLEL

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!
0
 
sdrussAuthor Commented:
schwertner:  Any issue with setting DataPump parameter, parallel so big, i.e. parallel=48?  But will it actually help?
0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdrussAuthor Commented:
Can't use parallel and estimate_only together?
0
 
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.
0
 
schwertnerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now