Database Recovery Part 2

Consider  while working with Production environment due to media failure, one of the data file is corrupted. But all other data files are working fine. As the DBA restarted the database, one of data file is starts complaining. Then the DBA decided to recover that particular datafile.

SQL> startup;
ORACLE instance started.
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: ‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF'

He made the corrupted data file OFFLINE and opened the database, so users can use the database while recovering that particular data file (but in case of system01.dbf you need sufficient downtime to recover).

SQL> alter database datafile ‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF' OFFLINE;

Database altered.
SQL> alter database open;
Database altered.

SQL> Select file_id from dba_data_files where file_name = ‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF';
FILE_ID
----------
6
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
Now connect the RMAN with catalog and restore and recover the datafile 6
C:\>rman target sys/****@mujazhr.world catalog catalog/catalog@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 25 14:30:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MUJAZORC (DBID=1165034825)
connected to recovery catalog database
RMAN>run
    {
    restore datafile 6;
    recover datafile 6;
    }
Starting restore at 25-JUN-12
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
creating datafile fno=6 name=D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF
restore not done; all files readonly, offline, or already restored
Finished restore at 25-JUN-12
Starting recover at 25-JUN-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-JUN-12
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 25 14:34:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database datafile ‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF' ONLINE;
Database altered.

SQL> Select distinct status from dba_data_files;
STATUS
---------
AVAILABLE