how to spped up import?

[faust2004]

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

[harry d brown jr]

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

[faust2004]

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]

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

[Alexander M. Ermes]

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

[Dragan Krnic_2]

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.

[Carlos Fernandez Riera]

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.

[Kerry_2]

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]

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

[pap]

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

[faust2004]

Dragan,
how to putting redo-logs into RAM, make the log buffer large enough?
I wonder why oracle not turn off redo log
and rollback to speed up import?

Thanks all
Sunny

[Dragan Krnic_2]

RAM volume is a poorly documented feature of the HP-UX system. You've got to include ram or ramdisc driver in the kernel and configure the ram volume(s) by using specific (again poorly documented) mksf options.

Fortunately there is a company which does all the dirty work for you for a fair price. Their ramdisc sw can automate autosave of ram volume data at shutdown and autocopy at bootup. It can also mirror the data on a terrestrial pv. It lets you create up to 31 ram volumes each up to 1GB in size.

You can download it for a 30-day free trial at http://www.jtlltd.com/ramdsk.html . I found it very useful.

[The Old Man]

Hello,
Just an update on RamDisc Software:
JTL no longer sells/supports RamDisc. The author and technical staff have formed a new company, AppMate Performance Software, and continue to add to the RamDisc family of products.

Info, demos:

www.ramdisc.com

Best Regards,

Tom