| 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
I bow to the uber geek :)
but putting either of those bumper stickers on my car in NY could get me seriously hurt!
>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: Fri, 01 Jun 2001 09:47:15 -0800
>
>On Fri, 1 Jun 2001, Hatzistavrou Giannis wrote:
>
> > I don;t quite understand you quoting
> > "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. "
> > Is primary a typo error (i.e. standby)?
>
>Argh! It was a typo. Of course, after doing many unrecoverable
>operations, you
>should refresh the affected datafiles from the primary to the standby.
>
>Vadim Gorbounov mentioned the column 'unrecoverable_change#' in v$datafile.
>This looks like an SCN, not a count of unrecoverable changes. Either way,
>it
>seems like a useful way to decide if a datafile shoule be refreshed onto
>the
>standby. I can even imagine improving a standby log applier that could be
>made
>to refresh such files from the primary automatically.
>
>On another topic, I believe I qualify for some sort of award as uber geek.
>This week I obtained the Washington State license plate "ORA DBA" for my
>'74
>Volvo.
>
>It got me thinking that it would be fun to have some bumper stickers made
>up for
>members of this list. Some ideas I had:
>
>"ARE YOU AN IDIOT?"
>
>or
>
>"SHUTDOWN ABORT"
>
>--
>Jeremiah Wilton
>http://www.speakeasy.net/~jwilton
>
>
> > > -----Original Message-----
> > > From: Jeremiah Wilton [SMTP:jwilton_at_speakeasy.net]
> > > Sent: Thursday, May 31, 2001 22:15
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: RE: Rebuilding indexes
> > >
> > > 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).
-- 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 Fri Jun 01 2001 - 14:15:20 CDT
 |
 |