RECOVER DATAFILE WHEN YOU LEFT WITH NO BACKUP

Sometimes you are in a situation where u have no backup at all but have all archive redo-logs intact.So in this situation you can easily recover your datafile without actually use any backup.

Here is the magic starts..........

alter tablespace USERS add datafile ‘/u02/oradata/mydatabase/users02.dbf’ size 10M;

here you see the list of datafiles....

select FILE_ID, FILE_NAME, ONLINE_STATUS, STATUS from dba_data_files;

FILE_ID FILE_NAME

———- ————————————————–

1 /u02/oradata/mydatabase/system01.dbf

2 /u02/oradata/mydatabase/undotbs01.dbf

3 /u02/oradata/mydatabase/sysaux01.dbf

4 /u02/oradata/mydatabase/users01.dbf

5 /u02/oradata/mydatabase/users02.dbf

Now we remove the recently added file i.e.users02.dbf

SQL> startup mount

SQL> alter database datafile 5 offline;

SQL> alter database open;

SQL> alter database create datafile ‘/mydatabase/u02/oradata/mydatabase/users02.dbf’;

SQL> recover datafile ‘/mydatabase/u02/oradata/mydatabase/users02.dbf’;

Type:

auto

SQL> alter database datafile ‘/mydatabase/u02/oradata/mydatabase/users02.dbf’ online;

:-) Try this one out..