Recover a datafile which is never backed up using RMAN

Using RMAN you can able to recover a datafile which is never backed up, the recovery will come from the combination of online redo and available archived logs.

Before you do the test make sure that no backups are running. Login into the database and create table privileges to do the test with. The database must be in archivelog mode.

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 26 12:32:43 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------- ----------- -----------------------------
db_create_file_dest string /db1/oradata
SQL> create tablespace test_tbs;
SQL> create user testuser identified by test124 default tablespace tbs_tbs;

Connect to "TESTUSER" and create a "test_table" for testing
SQL> connect testuser/xxxxxx
SQL> create table test_table as select * from user_objects;

Now you can take the newly created tablespace offline as "SYS" user and check the status. Please note that the database should be in archivelog mode to be able to accomplish the recovery.

SQL> alter system switch logfile;
SQL> alter tablespace test_tbs offline immediate;
SQL> select tablespace_name, status from dba_tablespaces;

SQL>> select file_name,status from dba_data_files;
/db1/oradata/ORCL/01_tbs_1_snz4161s_.dbf RECOVER

Remove the datafile from the operating system location.

Remember that there is no backup of this new tablespace at this point in time. Start an RMAN session.

$ rman TARGET / CATALOG rman/rman123@rmancat
RMAN> restore tablespace test_tbs

At this restore point, the OS level the datafile still doesn't exist.

RMAN searches for redo and/or archive log during the recovery command. During this particular restore session no archive logs were applied. so it actually restored the missing data file from online redo.

RMAN> recover tablespace tbs_from_online;

After recover bring the tablespace online and and check the status.

$ sqlplus / as sysdba
SQL> alter tablespace tbs_test online;
SQL>select tablespace_name, status from dba_tablespaces;

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com