| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rebuilding indexes
Richard,
Thanks! Now let me know if the book is actually useful...
I think you are going to have to bite the bullet and do the rebuild with logging. And, since this is something you may have to do over and over again, I'd create that extra tablespace and datafile and leave them there on both the primary and standby databases, as long as you have the disk space to do so.
I see why you don't want to have to add the datafile to the standby database, as you want to end up with the index back where it was. BUT... nologging operations don't carry over properly, you can end up needing to do recovery and have corrupted blocks.
I did a quick check on metalink, advanced search looking for standby database nologging:
Doc ID Note:126041.1
Clause
this was last updated March 6, 2001 so it's pretty current:
<snip>
"In some SQL statements, you have the option of specifying the NOLOGGING
clause, which indicates that the database operation is not logged in the
redo log file. Even though you specify the NOLOGGING clause, a redo log
record is still written to the redo log. However, when the redo log file is
transferred to the standby site and applied to the standby database, a
portion of the datafile is unusable and marked unrecoverable. "
and from one of the forums:
"Please note that the object is created and logged, however the population of the object is not and after a recovery operation the blocks occupied by the object are marked as corrupted. Attempts to access the object should encounter an error.
The behavior with regards to a standby database is not really any different than a recovery operation on the primary database.
NOLOGGING operations are discussed in the Oracle8i Standby Database White Paper which is available from the Oracle Server Enterprise Edition Generic Technical Library under White Papers -> Center of Expertise Articles
Oracle8i Standby Database
Note:76451.1 "
Sorry
hth
Rachel
>From: Richard Huntley <rhuntley_at_mindleaders.com>
>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 10:21:00 -0800
>
>Rachel,
>
>First, let me say that I just bought your book, so I definitely appreciate
>any comments coming from you!! :) I'm simply trying to defrag the indexes
>tablespace of the primary database, which I've done in the past as I
>described below. Now that I have a standby database out there, I just want
>to make the rebuild as efficient as possible without a bunch of added
>steps,
>if I can avoid them, so I thought I'd ask for suggestions for better ways
>of
>doing this.
>
>At the moment, however, it's looking like I'll be forced to add the
>datafile
>to the standby database, even though I'll use NOLOGGING to rebuild the
>indexes. The additional tablespace/datafile, I'll build on the primary and
>do the rebuild to the new tablespace then back to the old after I drop and
>recreate the old. The problem comes in with the redo generated from the
>creation of the new tablespace being carried to the standby database. I
>was
>hoping I wouldn't have to add the datafile to the standby database. With
>the standby database in mind, would you suggest that I do the index rebuild
>with or without logging?
>
>TIA,
>
>Richard Huntley
>E-mail: rhuntley_at_mindleaders.com
>
>
>-----Original Message-----
>Sent: Thursday, May 31, 2001 12:16 PM
>To: Multiple recipients of list ORACLE-L
>
>
>there IS no way to do that with a standby database. Once you open it for
>anything other than read-only (8i) you invalidate the standby status.
>
>
>
>
> >From: Richard Huntley <rhuntley_at_mindleaders.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Rebuilding indexes
> >Date: Thu, 31 May 2001 06:00:56 -0800
> >
> >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.
> >
> >TIA,
> >Richard Huntley
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Richard Huntley
> > INET: rhuntley_at_mindleaders.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).
>
>_________________________________________________________________
>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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Richard Huntley
> INET: rhuntley_at_mindleaders.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).
-- 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 - 14:39:09 CDT
 |
 |