ORA-01516 when trying to recreate a datafile
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ORA-01516 when trying to recreate a datafile

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    149
    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

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    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.
    Code:
    drop tablespace (give_tablespace_name) including contents;
    2. create new tablespace.
    Code:
    create tablespace (give_tablespace_name) datafile 'G:\Oracle\oradata\mydb\users01.dbf' size 20m
    extent management local
    uniform size 64k;
    HTH.

  3. #3
    Join Date
    Mar 2001
    Posts
    149
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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.

    -nagarjuna

  5. #5
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    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.

  6. #6
    Join Date
    Aug 2002
    Posts
    17
    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.




  7. #7
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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
    -nagarjuna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width