Solved

how do i improve performance during data pump import

Posted on 2014-09-22
25
1,767 Views
Last Modified: 2014-10-07
I am importing data from tables that have a huge amount of partitions from 200 to over 500 and growing. It is terabytes of data and I need to migrate the database from a 10g RAC database to a 11g RAC database in a virtual environment. I used datapump to export the tables which took several hours. However, when I tried to import a table it had taken 5 days and then before it could complete it was killed in ordered to shutdown the server for another reason. I need to resume the import as well as import the other similar partitioned tables. I don't know if there is anything i can do to improve the amount of time for the imports. Networks links are not possible. I have multiple dump files for each table. 1 table has a minimum of 12 dump files and the largest table has 28 dump files. I am using the parallel=4 parameter. Is there anything else I can do that would improve the amount of time it takes to import?
0
Comment
Question by:sikyala
  • 8
  • 5
  • 4
  • +2
25 Comments
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 100 total points
ID: 40337331
If you wanted to use expdp/impdp then try using parallel clause to improve the speed.

But its not a recommended practice to use exp/imp when you're dealing with the multi terabyte database.

You can consider rman clone then upgrade to 11g, or try using transportable tablespace.

Thanks,
sve.
0
 

Author Comment

by:sikyala
ID: 40337348
I am using datapump and the parallel clause. Are there any initialization parameters that I could change?
0
 
LVL 18

Expert Comment

by:sventhan
ID: 40337405
Nope.

Can you post your import script with the parfiles?

how big the table is?  What is your CPU usage when your run the import? Is there anything else running on this server?

Find out what its waiting when you are doing the import.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sikyala
ID: 40337504
I execute command line

impdp <username>/>password> directory=weekly dumpfile=amfer_data_%u.dmp parallel=4 tables=amfer_data content=data_only logfile=logs:amfer_data.log
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 200 total points
ID: 40337669
Have you tried doing it using transportable tablespaces? or RMAN?

Or, clone the  database and upgrade.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 40337832
Some things to look at:
try two imports:
first with no indexes then import the indexes.

Is the database in archive log mode?  I wonder if the long delays is caused by excessive log archiving.

Were the tablespaces pre-created with appropriate sizing?  If they were pre-created small and set to auto-extend, this could cause problems.  Extending a tablespace/datafile is a very expensive process.
0
 

Assisted Solution

by:HNC24
HNC24 earned 100 total points
ID: 40339041
As above suggested you have to do two import,

Import dump with parameter EXCLUDE=INDEX,STATISTICS (Statistics will need to be Re-Analyzed)

Create Index definition by using INDEXFILE to a filename and import, so no data will be imported,
but you must have edit this file and have to provide password on CONNECT statements

Use parameter ANALYZE=N , which will ignore analyze statements

Use parameter BUFFER to a large value

You can try with hidden parameter _disable_logging = true to which can reduce the redo (not advisable)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40339160
>>You can try with hidden parameter _disable_logging = true to which can reduce the redo (not advisable)

DO NOT DO THIS!!!

You can corrupt the database.  Tom Kyte has an extensive thread on this:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3387504900346765531

In that same thread he also points out why there isn't much a performance gain using it.
0
 

Expert Comment

by:HNC24
ID: 40340799
Yes he should not use _disable_logging on production DB.
But you can test on test db you can see the difference :).
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40341432
>>But you can test on test db you can see the difference :).

To what end?  Just to prove what Tom Kyte has already stated:  You will only get a SMALL performance gain.

I have no desire to potentially corrupt even a TEST database.

Besides, it is wasted effort.

This is along the same lines of:
I wonder if I delete my largest datafile, if backups will be a lot faster.  Let's try it on TEST to confirm it.  Hey, it worked.  It's a shame I cannot do this in production.  Time to rebuild the test database.
0
 

Expert Comment

by:HNC24
ID: 40341585
As i said use that parameter is not advisable .. still stick to it.

But i do kind of testing when i get time .. to check out what kind of errors come and so.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 40341786
>> need to migrate the database from a 10g RAC database to a 11g RAC

If its a migration effort, I would say use the Transportable Tablespace method. It would take around 3 hours for a 1TB of data in a decent hardware.

The export/import is not a prefer method.

Oracle support has a step by step document on this.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40341806
Finally someone agrees with me...Thanks.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 40341870
Mike -

That's what I've been saying since the beginning (my first post). At the same time I'm in the process of migrating 40TB database on 11g on Linux 6.

Thanks!
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40341920
Oh, yeah! now I noticed.

Last year I did several "migrations" that included upgrade from 10 to 11 and I found the fastest method was either transportable tablespaces or by "cloning" the old-fashion way: copying the dbf files.
If you do it right, you don't even need 10g installed on the target server for the upgrade.

Fortunately it was same OS (AIX) higher version.
Good luck!
0
 

Author Comment

by:sikyala
ID: 40342338
>> Have you tried doing it using transportable tablespaces? or RMAN? Or, clone the  database and upgrade.

Unfortunately due to restrictions and the instability of production I am unable to do any of these options
0
 

Author Comment

by:sikyala
ID: 40342349
>> Is the database in archive log mode? precreate tablespaces....

No I disabled archivelog mode 2 weeks ago in order to do the imports. The tablespaces were created with more than enough disk space before the import. I imported the schema and all of the db users objects without the data first. Then I expdp/impdp the data only for the partitioned table.
0
 

Author Comment

by:sikyala
ID: 40342356
>> Create Index definition by using INDEXFILE to a filename and import, so no data will be imported, but you must have edit this file and have to provide password on CONNECT statements

I am not familiar with this option. Would I need to modify the export parameters to include only index information or how would I import just the indexes then just the data if I am understanding what you are suggesting?
0
 

Author Comment

by:sikyala
ID: 40342360
>> Yes he should not use _disable_logging on production DB. But you can test on test db you can see the difference :).

Yes I am trying to import production data into our test environment
0
 

Author Comment

by:sikyala
ID: 40342370
>> If its a migration effort, I would say use the Transportable Tablespace method. It would take around 3 hours for a 1TB of data in a decent hardware.

The production database is 9 terabytes. The hardware is not decent. It is old and is past warranty on parts. In fact there is nowhere to store a full backup of the database due to lack of disk space even in ASM. The data in the database was analyzed to identify the critical data that would need to be restored in case the production database becomes unavailable. I don't know how I could specify only that data in a transportable tablespace export. Is that possible?
0
 

Expert Comment

by:HNC24
ID: 40343651
If you can not use transportable tablespace or RMAN,

You must have to use datapump utility  (EXPDP/IMPDP ) , It is easy to use same as export/import.
check this link for the same EXPDP IMPDP

Datapump is a fast & flexible compared to older one and specially on bigger job.
Datapump VS Exp/Imp
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40343756
>>You must have to use datapump utility  (EXPDP/IMPDP )

They are already using datapump.  The original question is about improving the response time of a datapump import.
0
 

Expert Comment

by:HNC24
ID: 40343813
Its my mistake .. Wrong place
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 200 total points
ID: 40350050
Yes I am trying to import production data into our test environment
I have similar situation where I have to create/maintain a QA and Training environments in 10% of the production space.

Solution:

1) Pre-create tables
2) Full copy of all non-transacional (support) tables like codes, activities, products, etc...
3) Using the datapump "query" clause choose a sub-set of the data (for example in my case only some company "locations" are selected).
4)  Use datapump over network link to avoid physical backup files.

HTH

PS: Do not try to kill two birds with same stone...
a) Create test non-rac
b) Upgrade to 11g
c) Then convert to rac
0
 

Author Closing Comment

by:sikyala
ID: 40366125
Thanks. I will try clone and transportable tablespace when i migrate from test to production in the virtual environment
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to update  and reset admin password for Linux 5 55
exp/imp 25 75
winscp 000webhost.com 6 53
error log using ftp 7 41
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question