Saturday, April 28, 2007

Brought back deleted datafile

It is possible to brought back deleted datafile. To see how :

SQL> CREATE TABLESPACE test DATAFILE '/backup2/test_01.dbf'
SIZE 10M;

SQL> ALTER TABLESPACE test ADD DATAFILE '/backup2/test_02.dbf'
SIZE 800M ;

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST';

TABLESPACE_NAME FILE_NAME
------------------------------ -----------------------------------------
TEST /backup2/test_01.dbf

TEST /backup2/test_02.dbf

SQL> alter tablespace test drop datafile '/backup2/test_02.dbf';
Tablespace altered.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST';

TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEST /backup2/test_01.dbf

SQL> ALTER TABLESPACE test ADD DATAFILE '/backup2/test_02.dbf'
SIZE 800M REUSE;

Tablespace altered.


SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST';

TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEST /backup2/test_01.dbf
TEST /backup2/test_02.dbf

** there is no guaranty that the object stored on the deleted datafile are available when it brought back :)
so when such thing happen the ideal way to use backup dump or other database backup system ...

No comments: