ORA-01113: file # needs media recovery ORA-01110

Question

I have mistakenly added datafile to the tablespace... And was trying to drop it. I run the following:

alter database datafile 'datafile_name'  OFFLINE;

now when I run

alter database datafile 'datafile_name'  ONLINE;

it shows

ORA-01113: file 30 needs media recovery
ORA-01110: data file 30: 'datafile_name'

also status of the file is RECOVER... What can I do? I cant restart database....

Answer 1

As soon as you add a datafile to an existing tablespace, a segment inside this tablespace that needs space may allocate an extent within the new datafile.

It will not wait until the other datafile(s) are full.

This is actually a feature (called extent striping) that was introduced to spread I/O across multiple devices, should datafiles reside on different drives. It is standard behavior since I don't know when - probably from the very start :-)

Answer 2

Found the answer:

SQL> recover datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF';

alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF';

alter  TABLESPACE EXAMPLE  drop datafile  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF' 

But for production DB it needs a lot of time to recover... Can't I drop that file without recovering it?

One more question... Another datafile was available during adding that file.. is there any chance that newly created datafile was used for storing data? I think oracle uses datafile until it will not exceed its maximum size and then switching to another available file... is not it so?

I am asking this because I want to be sure that newly created file doesn't contain any necessary data...