Solved

Oracle EXPDP - IMPDP

Posted on 2013-01-20
7
1,290 Views
Last Modified: 2013-01-23
Hi,
Oracle version: 10g
O.S: Windows 2003

I've 2 oracle instance: ORCL1 (schema TOM) on machine1 and ORCL2 (schema SAM) on machine2
I'd like to import the schema TOM into schema SAM.
TOM schema size is 300 GB

I exported TOM with following parfile:

SCHEMAS=TOM
DIRECTORY=TOM_DIR
DUMPFILE=exp_tom_%U.dmp
LOGFILE=exp_tom.log 
filesize=30GB
JOB_NAME=EXP_JOB

Open in new window

and created about 10 dump files in more of 30 hours

Now I need import into schema SAM, but I'd like to import faster

Which parameter can I add at following imp parfile to speed up the import?

schemas=TOM
remap_schema=TOM:SAM
logfile=imp_sam.log
directory=SAM_DIR
DUMPFILE=exp_tom_%U.dmp
exclude=statistics
job_name=IMP_JOB

Open in new window


Thanks in advance!
0
Comment
Question by:ralph_rea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38798953
30 hours for 300GB ... sounds like you have much too slow hardware, and I don't think you will be able to import "faster" than you export.
does the expdp write to the same disks as where the database files reside?
0
 

Author Comment

by:ralph_rea
ID: 38798988
yes, but impdp write to the different disks
0
 
LVL 23

Assisted Solution

by:David
David earned 200 total points
ID: 38799325
I might alter the default sort_area_size to something larger than its default; also, to log the database in noarchivelog mode.

Thirdly, defer your index creation.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ralph_rea
ID: 38799331
my oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0

in impdp I used FILESIZE parameter. Can I use PARALLEL parameter for expdp speed?


schemas=TOM
remap_schema=TOM:SAM
logfile=imp_sam.log
directory=SAM_DIR
DUMPFILE=exp_tom_%U.dmp
exclude=statistics
job_name=IMP_JOB
PARALLEL=4
0
 
LVL 23

Expert Comment

by:David
ID: 38799389
certainly, and good catch :)
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 38800038
just to do the math: 30GB/h => 512MB/m => 8MB/s
The write of modern hard drives are in the 50-150MB/s range.
and if you are on SAN disk arrays, the "end-user" speed is even higher, as the writes are spread over several disks.
so, if you have a backup speed of 8MB, you have a hardware issue.
note: backup => READ from Database files (disk) + WRITE to file (backup file)
restore => READ from backup file + WRITE to database files.
so, in the end, it does not matter if you refer to backup/expdp/restore/impdp ...
your disk system has some bottleneck(s), check out that issue, and your backup time shall go down to +- 5 hours, if not less. the restore time will be hence faster also.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38800195
what is your SGA set to in the init.ora file ?
select name, value, display_value from v$parameter
where name in ('sga_max_size', 'sga_target');

how much physical memory do you have on your server ?

if your sga_size is very close or bigger than your physical memory then your server will be paging all the time >> slow performance
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EF5: Update Model from Database not working 3 83
SQL Server 2014 Express vs Full - Service Pack question 5 47
oracle query 4 31
Importing from CSV to SSMS 2 14
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

751 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