Friday, February 18, 2011

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..

1 comment:

Anonymous said...

Hello,

Please mention that to recover that datafile we need archived redologs generated since the creation of DB.