1748224 Members
4505 Online
108759 Solutions
New Discussion юеВ

how to spped up import?

 
faust2004
Regular Advisor

how to spped up import?

Hi, experts,

We have to migarate a oracle databaser from dec box( oracle7.3.4 )to 2 hp box ( oracle 8.1.7 ops n4000, 12G mem), importing the 50G exportfile took about 10 hr. how to speed up import?
the redo log is 100M , rollback is 400M. is using ops import help?
I attched th init file .

Thanks
Sunny

12 REPLIES 12
harry d brown jr
Honored Contributor

Re: how to spped up import?

sunny,

You are doing a little more than migrating, you are converting, and depending upon which DEC box you have and which DEC OS you are using, you could be UN-"BYTE SWAPPING".

Oracle is a pig, it's just something we have to live with, and every year, with evry release, it's only going to get worse.

5GB an hour isn't really bad, but I agree it should be better. On the HP side, make sure you have done all of the performance tuning.

One thing you left out, is HOW you are getting the DATA (exportfile) from the DEC over to the HP. And how long was this process?

live free or die
harry
Live Free or Die
faust2004
Regular Advisor

Re: how to spped up import?

the dec box is tru 5.1, hp box is 11.0
geting the 50G file from dec to hp took 1.5 hr.

Thanks
Sunny
Tom Geudens
Honored Contributor

Re: how to spped up import?

Hi,
You might want to consider to run the import with INDEXES=N and build the indexes afterwards. That way you'll definitely speed up the import (and your indexes will be a lot "cleaner" afterwards).

Hope this helps,
Tom
A life ? Cool ! Where can I download one of those from ?
Alexander M. Ermes
Honored Contributor

Re: how to spped up import?

Hi there.
Some more hints :

- do a no-rows export for getting the structure of the database only

- run this as an import on the new database without creating indexes

- import the data without creating indexes

while running this import you should permanently keep compiling invalid objects, because your import might get stuck if you don't. You can do this by running the scripts through cron. There are scripts available like compile.sh or so from Oracle Support.


- run a now-rows import of the data after that.
this will create missing objects and build up the indexes.

Hope i could help.
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Dragan Krnic_2
Advisor

Re: how to spped up import?

I had a similar case with a 70 GB Oracle DB porting it from HP-UX to Linux. Significant speed up can be achieved by putting redo-logs into RAM, spreading tablespaces and indices over as many spindles as possible (TEMPORARY tablespace MUST be on a separate volume). Don't do indices and use commit=y. After data import create indices.

The recipee is more complicated in detail. To really do it right you need to do some raw editing of the dry dump (rows=n) to create all objects minus indices and constraints ahead of import. After data import use the same dry dump to import indices and constraints.

I let all tablespaces grow naturally starting with a reasonably small size and autoextending by reasonable increments. Same for tables and indices. A dump gives you a good overview of how much space the database really needs. In the end the new DB was less than 30 GB and ran significantly better than the original.
no quote
Carlos Fernandez Riera
Honored Contributor

Re: how to spped up import?

First thing to do is open database as not archive log.

On your 8.1.7 you can alter all tablespaces as no loggin.


import with rows=n indexes=n;


run an export with rows=n indexes=yes.

run imp indexfile=yes


split this indexfile.sql in some files and then you can run all them at a time. You alse can add unrecoverable to each create index.


While running sqls for create index set the multi_read_count=32 in init.ora.


Just ideas.


unsupported
Kerry_2
New Member

Re: how to spped up import?

I think you'll see the biggest improvement by making sure that you bump up your sort memory before starting the import. You can do this by logging in as SYSTEM and typing (for example): 'ALTER SESSION SET SORT_AREA_SIZE=100000000 DEFERRED;'

This will help you sort in memory instead of using the database TEMP space on disk. Make sure to reset this sort_area_size when the import completes, or each user will grab this amount when sorts are required.
Sandip Ghosh
Honored Contributor

Re: how to spped up import?

Just have a look at the lan setting also. Make sure your lan is set 100 mbps full duplex. I don't think that your old DEC box was having 100 Mbps card. That is why it has taken 1.5 Hr for transferring the file.

Sandip
Good Luck!!!
pap
Respected Contributor

Re: how to spped up import?

Hi If you are importing the data using Oracle utilities or by some other means over the network , I will suggest you to check your network settings and NFS settings(in case you use NFS mounting).
Also check the nslookup takes how much time to resolve DEC machines name on your HP box. Normally nfs mounting transfers take more time.
I will suggest you to transfer Oracle database through ftp, it will be very fast. I have done that thing few days back and transfered 2GB file within 5 minutes.

Hopethis helps.

-pap
"Winners don't do different things , they do things differently"