jump to navigation

Restore datafile without backup and then recover: CREATE DATAFILE of ALTER DATABASE command March 10, 2010

Posted by sendtoshailesh in Uncategorized.

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;







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;


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;






1. computer hardware support - August 6, 2014

I like the helpful information you provide in your articles.
I’ll bookmark your blog and check again here frequently.
I’m quite certain I will learn many new stuff right here! Best
of luck for the next!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: