Restore datafile without backup and then recover: CREATE DATAFILE of ALTER DATABASE command

Restore datafile without backup and then recover it

While in cafetria some admins were discussing that how to recover the lost datafile which was created after last backup. It might be created as part of any tablespace. I overheard of using CREATE DATAFILE clause of ALTER DATABASE command. fortunately I never cam across this situation. I thought let me test this whether, It can successfuly done or not. Here I am presenting the recover of a datafile whose backup is not there provided database is running in ARCHIVELOG mode.

Before doing any test lets confirm db should be running in archivelog mode.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9

Lets create a tablespace:

SQL> create tablespace myts datafile ‘d:\myts01.dbf’ size 10m;

Tablespace created.

Lets checkout file# of newly created datafile of tablespace:

SQL> select file#,name from v$datafile;

FILE#
———-
NAME
——————————————————————————–
1
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

2
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

3
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

FILE#
———-
NAME
——————————————————————————–
4
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

5
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF

6
D:\MYTS01.DBF

6 rows selected.

Now populate tablespace / datafile with some sample data:

SQL> create table tt tablespace myts as select * from dual;

Table created.

SQL> select * from tt;

D
–
X
Now rename the file. On UNIX platform this can be done but on windows it does not permit to do so. For this let take the datafile offline and rename it.
SQL> alter database datafile 6 offline;

Database altered.

Try to check whether database accept it reversal command after we renamed the file…..ahhh

SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 – see DBWR trace file
ORA-01110: data file 6: ‘D:\MYTS01.DBF’

DB complains that there is no such file. Now create a file physically by using following command. why? because we do not have backup of this file. This is a unique situation where following command is playing an important role.

SQL> alter database create datafile 6 as ‘d:\myts01_new.dbf’;

Database altered.
Test without doing recovery, what happend when we take datafile online. Obviously DB will say that datafile requires recover.

SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: ‘D:\MYTS01_NEW.DBF’

Here it recongnize that file is there but it not in sync with latest data. We know that we just created the file with only have header and no data. Lets recover the datafile. While recovering the datafile DB will read all the changes done in datafile from redo and archive log.
SQL> alter database recover datafile 6;

Database altered.

Recovery done. Take the datafile online and test whether we got our sample data back or not.

SQL> alter database datafile 6 online;

Database altered.

SQL> select * from tt;

D
–
X

Enjoy!!!!!!!!