Home » RDBMS Server » Server Utilities » Very long import running time
Very long import running time [message #73733] Mon, 28 June 2004 03:00 Go to next message
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
Member
I've run a test export on a database which took about 2 Hrs 20 mins, this created an export file with a size of 20Gig (instance size around 36Gig). I used direct=y and compress=n in the parfile.              

I've then been trying to run an import into another instance using the export dump file but this is taking around 24 Hrs (and this is next to useless). In my test script i'm running the first import with commit=y, ignore=n,indexes=n,analyze=n,buffer=500000000. The second import (which runs after the first has finished) runs with commit=y, ignore=n,indexes=y,indexfile=indexesfile,analze=n,buffer=500000000. 

I've even set "alter tablespace mp_space nologging;" and "alter tablespace mp_idx nologging;"  but this makes no diffrence (the tablespaces that hold the tables and indexes). The alert log of the instance i'm importing into show no problems (the redo logs are switching ok with no warnings reguarding the redo logs). I've not tried running parallel import jobs using the same export file (would this quicken things?). The cpu usage on the Unix system is fine.

Oracle 8i on a unix Sun platform (Solaris 8)
Re: Very long import running time [message #73736 is a reply to message #73733] Mon, 28 June 2004 03:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
DO NOT USE COMMIT=y.
>>The second import (which runs after the first has finished) runs with commit=y, ignore=n,indexes=y,indexfile=indexesfile,analze=n,buffer=500000000
this will NOT do any actual import at all.
u are using indexfile parameter.
importing with indexfile=indexfile produces an indexfile.sql which contains the ddl of all the objects found in the .dmp file.
Re: Very long import running time [message #73738 is a reply to message #73736] Mon, 28 June 2004 22:52 Go to previous messageGo to next message
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
Member
Hello Mahesh, Thanks for your reply and comments. I'll try another export with commit=n. I'll let you know the outcome when i get around to testing the import again. Thanks.
Re: Very long import running time [message #73741 is a reply to message #73738] Tue, 29 June 2004 03:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
try import with commit=n NOT the export
Re: Very long import running time [message #73744 is a reply to message #73741] Wed, 30 June 2004 01:52 Go to previous messageGo to next message
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
Member
Hello Mahesh,
I kicked off an import job yesterday at 15:00 but its still running now at 10:45 (19 3/4 Hours taken so far). There's still some large tables its not imported yet. Here's the import i'm running (from the Unix command line):-

imp parfile=impfile

impfile contains :-

userid=mpdba/password@accedb
file='/backups/proddb/exp_proddb.dmp'
log='imp_accedb.log'
fromuser=mpdba
touser=mpdba
buffer=500000000
commit=n
ignore=n
indexes=n
analyze=n

I'll let the import finish, just noticed the file impfile the import is using may not be using my amended one with the details above. I'll let you know.
Re: Very long import running time [message #73746 is a reply to message #73744] Wed, 30 June 2004 02:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
since you are trying to import large volume of data,
first drop the indexes in target tables, load or import the data, rebuild the indexes.
Re: Very long import running time [message #73752 is a reply to message #73746] Wed, 30 June 2004 23:39 Go to previous messageGo to next message
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
Member
Hello Mahesh,
My import ran with the wrong parfile values. I kicked off another import last night but its still running slow (i deleted the destination user first along with all its indexes/tables etc). I've totally ran out of ideas on how to speed up the import. I was hoping to cut the import time from 24 Hrs down to say 6 Hours but i think this is unlikely (i also looked for any other tips on the web but i've tried most things). What i'm going to look at now is database cloning, i've got instructions on how to clone a database from prod into test. I'll give this a go and let you know in a few days how it goes.
Thanks again for your help.
Re: Very long import running time [message #73771 is a reply to message #73746] Fri, 09 July 2004 01:03 Go to previous message
James Briar
Messages: 72
Registered: January 2002
Location: Morden (South London)
Member
Hello Mahesh, Just to let you know that i've successfully used Rman to duplicate a database rather than use export/import. The export/import was taking 24 to 26 Hours to run. Using Rman to duplicate a database instead only takes 30 to 35 mins.
Previous Topic: import data from 8.1.6 to ora 9.2
Next Topic: installation of 10g server and client
Goto Forum:
  


Current Time: Wed Dec 25 07:49:38 CST 2024