Re: Create Index with nologging?

Hardcore importing - we once exported and imported a TB DB in 10hours with 120 simultaneous imports AND exports
with redologs (10x256MB) AND rollback in RAMDISK (it's on unsupported on AIX but who cares) IT IS FAST :-))

"Fraser McCallum" <fmcc_at_NOSPAModbaguru.com> wrote in message news:h%m77.10768$H11.112582_at_NewsReader...
> Peter
>
> The quick answer is that no this will not affect your logging. Also except
> for the stuff I talk about below with regards to speeding it up there is
> nothing speical you need to do to turn loggin back on.
>
> The long answer: The nologging option in the index creation statement
> applies to the creation of the index only. Depending on what you mean by
> logging, either archive/redo or transaction monitoring, the same level
 will
> continue to apply to the index and table associated with the index once
 you
> have created it. With regards to the backup, you are correct you need to
> backup after the table imports/index builds if you are using archive
 logging
> as otherwise you will have no point to roll forward from in event of a
> disaster.
>
> As to speeding it all up a couple of ideas that I have found work.
>
> If you are going to do an offline backup at the end of this operation and
> during the import/index build the users are not using the database....
 turn
> off archiving, alter the temporary sort / retained size to be much larger,
> turn off any tracing you may have on (Most of this is a change to your
> init.ora and involves a restart of the dB, take a backup of the init.ora).
> Remember to put it all back to normal before letting the users in (by
> replacing your new init.ora with your backup and restarting the dB). If
 you
> are doing this and want to prevent users connecting either restrict your
 dB
> or if necessary shutdown the listener.
>
> Split your index builds into different files for different groups of
 tables,
> as many files as you have CPU's (assuming only the one dB on this machine)
> and kick them off in parallel.
>
> Regards
>
> Fraser McCallum
> MVP Oracle
> www.brainbench.com
>
> "Peter Stryjewski" <pstryjew_at_worldnet.att.net> wrote in message
> news:3B5DED3B.F4F5365D_at_worldnet.att.net...
> > Hello,
> >
> > I have searched the Deja (now Google Groups) archives and the Ask Tom
> > page (how often does Tom take questions?) and found conflicting answers.
> >
> > Scenario, Oracle 8.1.x:
> >
> > Export DB from original system (with direct option for speed)
> > Move export file to new system
> > Use "indexfile" to get table create and index create statements
> > Create tables
> > Import Data without index build
> > ...
> > Now create indexes. This I would like to speed up!
> >
> > If I modify the index create statements, created by the "indexfile" and
> > add nologging (or is it norecovery?). This should speed things up.
> >
> > But what happens after the Create Index? I know I should take a
> > backup. But will all subsequent operations on the index be logged? Do
> > I have to ALTER the index, for this to happen? This is the portion with
> > a lot of conflicting information floating around.
> >
> > Does anyone have any good information on achieving the fastest import
> > and index build.
> >
> > Pete
>
>
>
>
Received on Wed Jul 25 2001 - 16:01:18 CDT