Case 2: Recover from corrupted or missing data file (When database already open)

This scenario deal with a situation where data file has gone missing or corrupted and databases already open.

If the database is already open when datafile corruption is detected, you can recover the datafile without shutting down the database. The only additional step is to take the relevant tablespace offline before starting recovery. In this case you would perform recovery at the tablespace level. The commands are:

C:\>rman target /

Recovery Manager: Release 9.2.0.4.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)

--offline affected datafile
RMAN> sql 'alter database datafile 4 offline';

--recover offlined tablespace

RMAN> recover datafile 4;

--online recovered tablespace

RMAN> sql 'alter database datafile 4 online';

RMAN>

Here we have used the SQL command, which allows us to execute arbitrary SQL from within RMAN.