Oracle EXPDP - IMPDP

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!
ralph_reaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
ralph_reaAuthor Commented:
yes, but impdp write to the different disks
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
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
 
ralph_reaAuthor Commented:
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
 
DavidSenior Oracle Database AdministratorCommented:
certainly, and good catch :)
0
 
Geert GOracle dbaCommented:
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
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.

All Courses

From novice to tech pro — start learning today.