This discussion is archived
12 Replies Latest reply: May 4, 2013 2:03 AM by 998588 RSS

gone datafile

998588 Newbie
Currently Being Moderated
Hi,
we are working on a oracle 11g and one of the datafiles has been moved to another directory by someone who didnt know what he is doing :)
now we moved the dbf file back to the appropriate location.
DB is down and when trying to open its giving the error that recovery is needed...

Unfortunately no arc file exist and there is no backup as well.

can anyone suggest what is the best solution in this case?


Thanks!
  • 1. Re: gone datafile
    %bala% Explorer
    Currently Being Moderated
    Please post what error you are getting on command prompt when you are opening database and also last 50 lines of your alert log file

    database version and os too.
  • 2. Re: gone datafile
    EdStevens Guru
    Currently Being Moderated
    fcbman1899 wrote:
    Hi,
    we are working on a oracle 11g and one of the datafiles has been moved to another directory by someone who didnt know what he is doing :)
    now we moved the dbf file back to the appropriate location.
    DB is down and when trying to open its giving the error that recovery is needed...

    Unfortunately no arc file exist and there is no backup as well.

    can anyone suggest what is the best solution in this case?


    Thanks!
    The first rule when one finds oneself in a hole is to quit digging.
    Before doing anything else, with the database down take a complete backup of all files involved ... datafiles, redo logs, spfiles, control files .... put them in a safe place where you can copy them back to their original locations as many times as you need.
  • 3. Re: gone datafile
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    fcbman1899 wrote:
    Hi,
    we are working on a oracle 11g and one of the datafiles has been moved to another directory by someone who didnt know what he is doing :)
    now we moved the dbf file back to the appropriate location.
    DB is down and when trying to open its giving the error that recovery is needed...

    Unfortunately no arc file exist and there is no backup as well.

    can anyone suggest what is the best solution in this case?


    Thanks!
    is this normal in the company that anyone can access to the server and miss around in your database ?
  • 4. Re: gone datafile
    998588 Newbie
    Currently Being Moderated
    Hi,
    I thought the error is obvious...

    ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
    ORA-01110: data file 3: '/opt/db/data/dbf/main3.dbf'


    I already copied all we have... but that doesnt help for now :)
    We are trying to find the best solution for now :)


    Regarding the last question if its normal that people can mess around with the database - this is a applicable DB...
    hence everyone has access to it...
    If I was more aggresive I would also say what I think about the person who moved it:)

    Thanks!
  • 5. Re: gone datafile
    EdStevens Guru
    Currently Being Moderated
    fcbman1899 wrote:
    Hi,
    I thought the error is obvious...

    ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
    ORA-01110: data file 3: '/opt/db/data/dbf/main3.dbf'


    I already copied all we have... but that doesnt help for now :)
    We are trying to find the best solution for now :)


    Regarding the last question if its normal that people can mess around with the database - this is a applicable DB...
    hence everyone has access to it...
    Having application access to the DB does not mean having direct access to the underlying data files ....

    Hence, the only people who should even be able to do this are DBA's and SA's.
    If I was more aggresive I would also say what I think about the person who moved it:)
    Self inflicted wound?

    >
    Thanks!
  • 6. Re: gone datafile
    998588 Newbie
    Currently Being Moderated
    the DB is embedded and is part of the application.
    this is a result of a stupid customer...

    engineers or DBAs in my company has nothing to do with it :)

    Now...
    instead of sending 10 messages "why someone did it" or "why they have access"...
    Is there anyone that has the idea what is the best solution?
  • 7. Re: gone datafile
    jgarry Guru
    Currently Being Moderated
    Well, if you had done nothing when the problem was discovered, you might have been able to put the file back, depending on your OS.

    So:

    Tell us your OS
    Tell us your exact Oracle version
    Show us the last 50 lines of your alert log (text file if on 11g)
    Tell us if you are in archivelog mode.

    It doesn't matter if a db is embedded, it still needs a backup scheme appropriate to usage, including user errors. You may be TSOL, maybe not, you haven't given us enough to work with. Usually if you have a problem with media (like a data file disappearing) you need media recovery (RMAN stands for Recovery Manager, get it?), but there are some exceptions.

    Logical backups are one easy way to deal with user errors. Do you have any (exports)?
  • 8. Re: gone datafile
    998588 Newbie
    Currently Being Moderated
    Did you read the thread?
    as stated in the beginning of the thread...
    We dont have any arc file nor backups.
    no exports as well.
    also mentioned - we tried to use the file back but oracle says a recover is needed (which we cannot do since we dont have arc files)
  • 9. Re: gone datafile
    %bala% Explorer
    Currently Being Moderated
    would you like to find a solution for your problem or not?

    Paste your results for the below mentioned questions first
    >
    Tell us your OS
    Tell us your exact Oracle version
    Show us the last 50 lines of your alert log (text file if on 11g)
  • 10. Re: gone datafile
    EdStevens Guru
    Currently Being Moderated
    fcbman1899 wrote:
    the DB is embedded and is part of the application.
    this is a result of a stupid customer...

    engineers or DBAs in my company has nothing to do with it :)
    OK. I interpreted your earlier smilies to be a "sheepish grin", indicating you did it to yourself.
    Now...
    instead of sending 10 messages "why someone did it" or "why they have access"...
    Is there anyone that has the idea what is the best solution?
  • 11. Re: gone datafile
    jgarry Guru
    Currently Being Moderated
    fcbman1899 wrote:
    Did you read the thread?
    as stated in the beginning of the thread...
    We dont have any arc file nor backups.
    OK, that does not really answer the question of whether you are in archivelog mode. It is entirely possible to have not switched through all redo since the data file was created, with the information you have given.
    no exports as well.
    also mentioned - we tried to use the file back but oracle says a recover is needed (which we cannot do since we dont have arc files)
    I don't know what "we tried to use the file back" means. Did you copy something from somewhere?

    Please copy and paste the commands you used and the response you received when you tried to recover. Please show the alert log from when you first got an error through your recovery attempt.

    We're quite willing to help fix ridiculous problems like this which stem from below grade administration. It's an interesting challenge when it looks completely hopeless. But you need to help us help you. When we ask for information, you need to give it if you want help.
  • 12. Re: gone datafile
    998588 Newbie
    Currently Being Moderated
    Hi,
    I'm trying to get all the information, but as mentioned - this is customer env and I dont have access.

    what I do know...
    OS is linux RedHat5
    logs switched over.
    oracle is 11.2.0.1.0 - 64bit
    DB is in archivelog but as mentioned there are no arc file existing :(
    moving the file means we moved the dbf to the location where it should have been. (where it actually was before customer moved it)
    then when opening oracle doesnt say any more it cannot find the file, but instead we get this:

    ORA-01113: file 5 needs media recovery
    ORA-01110: data file 5: ....

    trying to recover the file (or the database) gives the error that arc files cannot be found.


    Unfortunately I dont have access to get the complete alert.log file.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points