Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rebuilding indexes

RE: Rebuilding indexes

From: Rachel Carmichael <carmichr_at_hotmail.com>
Date: Thu, 31 May 2001 13:52:56 -0700
Message-ID: <F001.00316E3B.20010531132641@fatcity.com>

Jeremiah,

Thanks -- I knew someone would know better than me how to do this! Another post to save for when I get to have a standby db again :)

Rachel

>From: Jeremiah Wilton <jwilton_at_speakeasy.net>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Rebuilding indexes
>Date: Thu, 31 May 2001 12:15:25 -0800
>
>No need to copy the whole file to the standby when adding a datafile! Just
>wait
>for recovery to fail on the new file (ORA-01670), then type on the standby
>(in
>'mount standby database' mode):
>
>SQL> alter database create datafile '<filename>' as '<filename>';
>
>As long as you are using identical paths on both primary and standby, both
>filenames are the same. Our standby log applier does this automatically,
>so no
>manual intervention is required.
>
>If you use logical volumes or raw, just make sure the volume is where the
>system
>expects it to be on the standby.
>
>If the original poster wants to create a scratch tablespace where the
>indexes
>will be initially rebuilt, then move the indexes back into the "real"
>tablespace, he can probably just 'offline drop' the datafiles for the
>scratch
>space on the standby when recovery encounters them. Once the indexes have
>all
>been rebuilt nologging into the original tablespace, all that's left is to
>refresh the datafiles for that tablespace onto the primary. When the
>scratch
>tablespace is dropped on the primary, the offline dropped datafile records
>in
>the standby's controlfile will also disappear.
>
>With regard to logging/nologging, with index rebuilds, I would rebuild the
>indexes NOLOGGING, and when you are all done, refresh the datafiles for the
>newly reorganized tablespace onto the standby. That saves on space,
>operations,
>time and reduces the logs you have to back up.
>
>Nologging operations are not harmful to the standby - you just have to keep
>track of nologging operations and refresh datafiles on the standby
>accordingly.
>
>--
>Jeremiah Wilton
>http://www.speakeasy.net/~jwilton
>
>On Thu, 31 May 2001, Rachel Carmichael wrote:
>
> > It adds the datafile NAME only, not the file itself. And if the physical
> > file has not been moved before the archived log that has that redo in it
>is
> > applied, the recovery dies.
> >
> > >From: Hatzistavrou Giannis <j.hatzistavrou_at_telesoft.gr>
> > >
> > >You might be mistaken, since Oracle Manual (Oracle 8.1.5 Backup and
> > >Recovery) , chapter 16 -- Managing a standby database --- Adding
>Datafiles
> > >states the following:
> > >
> > >"Adding a datafile to your primary database generates redo data that,
>when
> > >applied at your standby, automatically adds the datafile name to the
> > >standby
> > >control file....."
> > >There is also a detailed example on how to do it
> > >
> > > > -----Original Message-----
> > > > From: Richard Huntley [SMTP:rhuntley_at_mindleaders.com]
> > > >
> > > > For those of you that have implemented a standby database, what
>method
> > >do
> > > > you use to rebuild your indexes tablespace.
> > > > My plan was to create a new tablespace and rebuild the indexes into
>the
> > > > new
> > > > tablespace and then reverse the process to move
> > > > back to the original tablespace after I drop and recreate it in
>order to
> > > > get
> > > > rid of fragmentation in the originally indexes tablespace.
> > > > However, creating the new datafile associated with the new
>tablespace,
> > > > cancels media recovery associated with the standby database...just
> > > > wondered
> > > > if anyone has a better method of defragging an index tablespace when
> > >there
> > > > is a standby database catching the redo it generates.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Jeremiah Wilton
> INET: jwilton_at_speakeasy.net
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).



Get your FREE download of MSN Explorer at http://explorer.msn.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: carmichr_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu May 31 2001 - 15:52:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US