RMAN corrupt data file recovery

As we have situation like our production db files are corrupted more than 90%.Initially we thought of to go full recovery but then after we decided to go as table space level.

RMAN: Recovering from corrupteddatafile

Any kind of recovery needs a few very basic steps:

1) Checking the availability of valid backup
2) Deciding upon the recovery procedure
3) Actual recovery

When I know the table space name and datfiles are corrupted ,then check as below:

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME

FROM V$DATAFILE_HEADER

WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

RMAN> restore datafile 4 preview;

RMAN> restore datafile 4 preview;

SQL> select * from v$log;

The Procedure
Recovery can be complete or incomplete. A complete recovery has no data loss whereas an incomplete recovery has some data loss. An incomplete recovery is accompanied by RESETLOGS. An incomplete recovery is necessary when some or all of the data is unavailable to recover the object from its restore point to the point of dataloss. An object here could be a datafile, tablespace, or database. Restore point here is the point until where the DB has been restored to using the backup, and to make the DB current until the point of object loss we will need recovery to be performed. Recovery requires availability of archived and online redo logs. If the archived logs or redo logs are missing, then incomplete recovery is the only option. We may have to use RECOVER…UNTIL TIME option for point-in-time recovery of the missing object. (more on the point-in-time recovery in future posts).

In our case, since we have all the required archives we will perform a complete recovery. There is no data loss in this recovery procedure. The DB can remain in MOUNT or OPEN state during this procedure. Only the datafile will be made offline during the restore/recovery operation if the DB is in OPEN state.

The Recovery Itself
Since all the backups are available, the actual recovery procedure is quite simple.

Connect to RMAN and issue the below statements:


RUN {
SQL ‘ALTER TABLESPACE users OFFLINE IMMEDIATE’;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
SQL ‘ALTER TABLESPACE users ONLINE’;
}


The snapshot of the same, in my DB, is below. In my snapshot, the ALTER TABLESPACE users ONLINE statement is missing. I wanted to confirm that the RECOVER TABLESPACE command runs successfully before bringing the tablespace online. I ran the ONLINE tablespace command later at the end.
Also note that this RMAN command OFFLINE’s and ONLINE’s the entire tablespace. Since in our case this tablespace had only one datafile (which was missing), it wont much matter OFFLINE’ing the tablespace or datafile. If we had more than one datafile in this tablespace and one among those datafiles was missing or corrupt, we would OFFLINE only that datafile instead of the entire tablespace. The command is ‘ALTER DATABASE DATAFILE  OFFLINE’.



Once you recover all the Tablespace has been recovered.System table space we can't take offline when the DB is in online.

Take the DB is Offline mode and startup in nomount state.


RUN {

RESTORE TABLESPACE SYSTEM;

RECOVER TABLESPACE  SYSTEM;

}



Once SYSTEM TS recovery is done.start the DB as "alter database open".



Note: Please make sure RMAN backup location and archive logs need to be accessible all the time where you want to recovery.