Re: Recover index tablespace

From: Fabrice <>
Date: Tue, 20 Oct 2009 07:08:23 +0200
Message-ID: <4add45c7$0$992$>


Thanks for your answer.
today, It happens nothing. It just a question for me. I had to restore my datafiles (index and data) there was some days. Problem with hardware. And now I'm looking for answers to somme questions. And what about corrupt or loss of the index tablespace (for example hardware failure or datafile corruption).

And I down't know the best way to resolve this issue :

  • Can I just restore index datafile from my last cold backup and replay redolog archivied to get all my data and indexes consistents. (recover tablespace "tbs_index";)
  • Or do I have to recreate (by adding a new datafile) my index tablespace from nothing and launch a global rebuild of all the indexes. Can we do thant in production ?

I would like to test the issue.
Thanks for your help.

ddf wrote:

> Comments embedded.
> On Oct 19, 2:00 pm, Fabrice <> wrote:

>> Hello
>> How to recover index tablespace ?

> What has happened to the index tablespace? Are there any non-index
> objects in this tablespace?
>> I have A tablespace for the data and an onother for indexes.
>> . Can I restore the datafile of my index tablespace and do an un recover
>> tablespace. The redologs archivied will be replayed ?
>> and Do my base will become consistent ?

> If all objects in that tablespace are indexes you could simply build a
> new tablespace and rebuild the indexes into that new tablespace.
>> . Or do I add a new datafile in my tablespace, drop the old one and
>> rebuild all the indexes ?

> What happened to this datafile that raises this question?
>> How to rebuild all the indexes ?

> Query the data dictionary for all indexes in that tablespace and
> generate the rebuild statements:
> select 'alter index '||owner||'.'||index_name||' rebuild;'
> from dba_indexes
> where tablespace_name = '.....';
> Replace '.....' with your index tablespace name.
>> thanks a lot
>> fabrice

> David Fitzjarrell
Received on Tue Oct 20 2009 - 00:08:23 CDT

Original text of this message