Re: More efficient way to exp / imp

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Tue, 28 Jan 2003 00:55:19 -0500
Message-ID: <v3c6q9d96kc3ab@corp.supernews.com>

Chang,

There are ways to speed up exp and ways to speed up import.  However I think what Daniel meant by his question was: What exactly are you trying to achieve?

1. Are you trying to make a duplicate copy of your whole database?
2. Are you trying to copy a tablespace into another database?
3. .. or are you planning to use exp imp for copying user data or subset of tables or reorganize data (for some sick twisted reasons :) etc ...?

If you are trying 1 ... then database cloning can actually be a very simple and fast way. You can read about it in the backup manual.
If you are trying 2 ... then the transportable tablespaces feature of exp imp can be a fast way. You can read about it also in the admin manual.

If you still want exp imp .. and want it to be done fast, you could try couple of things in the order listed ( I have skipped some methods that have already been suggested to you .. like creating indexes later ..)... For faster exports:
1. use direct=y option. This option has its limitations which you can read in the admin guide.

For faster imports
1. set sort_area_size big .. alter system set sort_area_size = 100M; (reset it after you are done) 2. increase the buffer parameter to something like 100M or 200M and set commit = y.

3. You could try creating the indexes separate from the data. For which You would first create an indexfile

     using the indexfile option. After that run imp with rows=y indexes=n option. After that is complete ...
     create the indexes using the indexfile (make sort_area_size for the system big ... like 10-100M).

4. Make sure that redo logs are large enough to not switch too frequently. v$log, v$logfile views are your friends in determining this.

    make redo logs bigger .. in case redo logs are switching more frequently than 30mins (I'm willing to bet that your redo logs are switching

    at the rate of 4-20 per min ..)

1, 2 and 4 options are the ones which I usually make sure. Also make sure that your sga is sized properly (i.e. isn't too huge ... so that its
creating problems on its own)

But frankly ... TTS or database cloning might be worth looking for you. Especially because they are relatively easy to follow/implement.

Anurag

"C Chang" <cschang_at_maxinter.net> wrote in message news:3E34BB5D.519A_at_maxinter.net...
> DA Morgan wrote:
> >
> > C Chang wrote:
> >
> > > I have a 3 G database on a NT 4 (P-550) box installed with 8.1.6
> > > oracle. There are 5 X 16 G HD and 1 G RAM. I tried to export and
> > > import the DB and found that my current methods are not faster enough.
> > > What i did were either to use Full=Y or fromuser=.. in export and import
> > > back accordingly. But both way are too slow and took at least 48 hours
> > > to finish. I examined my system and found that the only full tablespace
> > > is one of the tablespaces for data, rest of tablespace is quite empty.
> > > Therefore I can not use the imp commit=N, which could not extend the
> > > tablespace during the import. I also read the articles from other
> > > reference (One-on-One ) and websites for clues. I am wondering that
> > > dividing the DB to several export files will speed up? or export/import
> > > the DB table by table, then indexed. I have never done those. Can
> > > anyone have done either one sharing their experience? Thanks.
> > >
> > > C Chang
> >
> > What are you going to do with the export? That might help us make a
> > suggestion.
> >
> > Daniel Morgan
> Thanks everyone, especial Daniel. I have seen your name in a lot of
> response. Great help. Let me say first that I am not DBA or either SQL
> programmer in our project, I am part time web programmer, part time
> tester, part time Web Administor..., You got my point. Therefor you
> have to keep patient with my inability to major ORACLE stuffs. I
> basically followed our old DBA instructions for a Full Export. I
> believe something like:
> exp sys/password file=E:\oracle_dump\myexp.dmp full=y indexes=yes
> consistent=yes compress=n log=E:\oracle_dump\expToday.log

>

> Then I used imp sys/password file=I:\oracle_dump\myexp
> fromuser=SpecificUser touser=SpecificUser commit=y log=impToday.log
> , Which tooks 40 hours to finish. I tried to use commit=n, but it
> generated error about the tablespace and extension.
> Then I also tried to import only with the table as
> imp sys/password file=I:\oracle_dump\myexp fromuser=SpecificUser
> touser=SpecificUser tables=(oneOftable) log=impToday.log
> But it also failed with a reason ( I forgot to copy down and I will
> bring next post)
> I also tried with import withn indexfile=theIndexFile.sql, but it
> creates something scripts that does not mean anything. As result, I
> have been running out of my skills. I hope anyone that has more
> experience can threw in some tips. Great appreciate.
>
> C Chang
Received on Mon Jan 27 2003 - 23:55:19 CST