Joe's Oracle

Recover Index Datafile

Problem Description:
====================

This is a recovery scenario in which a datafile in an index tablespace has been lost or damaged to a point that Oracle cannot recognize it anymore. Trying to startup the database will result in ORA-1157, ORA-1110, and possibly an operating system level error such as ORA-7360. Trying to shut down the database in normal or immediate mode will result in ORA-1116, ORA-1110, and possibly an operating system level error such as ORA-7368.

This entry contains the solution for this problem.

Solution Description:
=====================

The simplest solution in this scenario is to offline drop the datafile in the index tablespace, drop the tablespace itself, and then recreate it. Other approaches are possible if recreating the indexes is too laborious.

Solution Explanation:
=====================

There are two possible scenarios here:

I. THE INDEX TABLESPACE CAN BE EASILY RECREATED
------------------------------------------------

If the indexes in the affected tablespace can be easily recreated by running a script or by manually issuing CREATE INDEX statements, the best solution is to offline drop the lost datafile, drop the index tablespace, recreate it, and then recreate all the indexes in it. The steps for that are:

1. If the database is down, mount it.

3. Offline drop the datafile.

ALTER DATABASE DATAFILE '' OFFLINE DROP;

4. If the database is at mount point, open it.

5. Drop the index tablespace.

DROP TABLESPACE INCLUDING CONTENTS;

6. Recreate the index tablespace.

7. Recreate all the previously existing indexes in the tablespace.
This can be done via existing scripts, an indexfile script created from an export or an indexfile script created from a test or copy database.
If a copy database exists an indexfile script could be created by exporting with rows=n and then using the import indexfile=y parameter.

II. THE INDEX TABLESPACE CANNOT BE EASILY RECREATED
----------------------------------------------------

Oftentimes, the index tablespace comprises hundreds or even thousands of indexes on very large tables. In such cases, rebuilding the index tablespace can be too laborious or time-consuming. As an alternative approach, you may restore a backup of the missing datafile and apply media recovery on it to bring it in synch with the rest of the database. If the database is in NOARCHIVELOG mode, you will only succeed in recovering the datafile if the redo to be applied is within the range of your online logs.

These are the steps:

1. Restore the lost file from a backup.

2. If the database is down, mount it.

3. Issue the following query:

SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

This will list all your online redolog files and their respective sequence and first change numbers.

4. If the database is in NOARCHIVELOG mode, issue the query:

SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs, the datafile can be recovered. Just keep in mind that all the logs to be applied will be online logs, and move on to step 5.

If the CHANGE# is LESSER than the minimum FIRST_CHANGE# of your logs, the file cannot be recovered. Your options at this point would be to restore the most recent full backup (and thus lose all changes to the database since) OR recreate the index tablespace as explained in scenario I.

5. Recover the datafile:

RECOVER DATAFILE ''

6. Confirm each of the logs that you are prompted for until you receive the message "Media recovery complete". If you are prompted for a non-existing archived log, Oracle probably needs one or more of the online logs to proceed with the recovery. Compare the sequence number referenced in the ORA-280 message with the sequence numbers of your online logs. Then enter the full path name of one of the members of the redo group whose sequence number matches the one you are being asked for. Keep entering online logs as requested until you receive the message "Media recovery complete".

7. If the database in not already open, open it.