RE: Change index from nologging to logging
Date: Sat, 21 Mar 2009 12:41:20 -0400
The "Alter index index_name rebuild online;" will rebuild the index which will take a while depending on the number of rows in the table and is only necessary when you want to move it from one tablespace to another or if something has happened to the index to make it corrupt, or perform poorly. The second option (alter index index_name logging;) just switches the flag on index and is very fast, which is what you need. The drop and create are a third option which I don't recommend as if something goes wrong during the script you may end up without one or many indexes.
As long as the indexes are in logging mode immediately before and during a hot backup they will be refreshed properly.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Kumar Madduri
Sent: Saturday, March 21, 2009 11:48 AM
Subject: Change index from nologging to logging
Some indexes were build using the nologging option. This creates corruption when the instance is refreshed. Now they need to be converted to logging option. I have two options to do this.
Alter index index_name rebuild online;
alter index index_name logging;
drop index index_name
create index index_name with logging option;
I was going with the first option. Is there a difference between doing 1 and 2.
Thank you for your time
KumarReceived on Sat Mar 21 2009 - 11:41:20 CDT