ORA-01516 when trying to recreate a datafile

[newbie]

Hi all, I'm wondering what are the requirements before one can issue alter database create datafile command. I've lost an datafile and wanted to recreated in a different location, when I issued alter database command:

SVRMGR> alter database create datafile 'G:\Oracle\oradata\mydb\users01.dbf' as
'F:\ORACLE\ORADATA\MYDB\USERS01.DBF';
alter database create datafile 'G:\Oracle\oradata\mydb\users01.dbf' as
'F:\ORACLE\ORADATA\MYDB\USERS01.DBF';
*
ORA-01516: nonexistent log file, datafile or tempfile 'G:\Oracle\oradata\mydb\users01.dbf'


I'm not sure how to fix this, please help me . Thank you

[ggnanaraj]

Hi

The error you get is because your syntax is wrong...Check this out...for your error message
http://technet.oracle.com/docs/produ...00.htm#1003914

Do the following...IF YOU DON'T WANT THE CONTENTS OF THE DATAFILE YOU HAVE LOST to create a new tablespace

1. drop the tablespace.

drop tablespace (give_tablespace_name) including contents;

2. create new tablespace.

create tablespace (give_tablespace_name) datafile 'G:\Oracle\oradata\mydb\users01.dbf' size 20m
extent management local
uniform size 64k;

HTH.

[newbie]

Hi, thanks for the tips..actually I was trying to recreate a file that I don't have a backup for it. I read the Oracle B&R Guide and it suggested that we should use ..alter database create datafile... to recreate the file. Any ideas how to make this work?? Thank a whole bunch

[nagarjuna]

Originally posted by newbie
Hi, thanks for the tips..actually I was trying to recreate a file that I don't have a backup for it. I read the Oracle B&R Guide and it suggested that we should use ..alter database create datafile... to recreate the file. Any ideas how to make this work?? Thank a whole bunch

Try

alter database create datafile 'G:\Oracle\oradata\mydb\users01.dbf' as
'F:\ORACLE\ORADATA\MYDB\USERS01.DBF' size ;

if this does not work, then check whether the datafile is in locally managed temporary tablespace.. if it so, the use the following syntex...

alter database create tempfile 'G:\Oracle\oradata\mydb\users01.dbf' as
'F:\ORACLE\ORADATA\MYDB\USERS01.DBF' size ;


But, I suggest you to recreate your tablespace in the later case.

One more point... You should have all the redologs genarated after the creation of the concerned datafile, then only you can use the above method to recreate the datafile.

[ggnanaraj]

Originally posted by newbie
..alter database create datafile... to recreate the file. Any ideas how to make this work??

Check out this doc in Metalink...Note:29430.1

Cheers.

[YellowLark]

Reading your posts, where you mention "recreate" and not having a backup file, I'm wondering if you are trying to recover your data, not just to allocate a new datafile.

If you are trying to get that data back, you may be out of luck without any backup available.

[nagarjuna]

Originally posted by YellowLark
Reading your posts, where you mention "recreate" and not having a backup file, I'm wondering if you are trying to recover your data, not just to allocate a new datafile.

If you are trying to get that data back, you may be out of luck without any backup available.

Recreating is different from restoring.. Recreating is done when no backup is available and all the archivelogs are available from the time of datafile creation. Restoration is only possible with backup sets available.. Recovery is possibly only when back up of datafiles and archivelogs are available