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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 GruwezOracle 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
All Courses

From novice to tech pro — start learning today.