Anurag Varma wrote:
>
> 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
Thanks. Anurag. I have such a database, in which one of the user owns
couple tables that store > 8 Mil records. Becasue the source and the
format of the records change time from time, it took 48 hour to load the
records into the database from a VB interface. every couple months when
the supplier change the format or increase the number of records, we
have to reload them again. So we construct two database, one back-up,
one production. Every month when we have to reload the new records, we
like the records been on the production in a very short downtime.
Therefore we need to transfer the tables from backup to production ASAP
after we load the new records,possible new format of some tables into
backup. So I guess to answer your question, it should be close 2.
C Chang
Received on Tue Jan 28 2003 - 20:30:07 CST
