DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: ORA-01113: file 2 needs media ercovery

  1. #1
    Join Date
    Dec 2002
    Location
    Chennai, India
    Posts
    104

    ORA-01113: file 2 needs media ercovery

    Hi All,
    I am getting the following error
    ORA-01113: file 2 needs media ercovery
    ORA-01110: data file 2: 'H:\db\acc\acc.dbf'

    Problem Description
    ==================
    I have two system in which two database is running one system acts as back up. In our sync process I need to copy the database files to backup. For this my perl script do the following.
    1. Stop active and backup systems Oracle Services(using net stop...)
    2. Copies all the files from active to local using xmit util
    3. Start Oracle Service in both the system.
    But I'm getting this error on back up. Once if I manually copies the file acc.dbf from active side to local manually then every thing works fine.
    Could any one help me out pls.

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    sudheer_nm,

    Let me see if I'm reading this right....
    You have an active database and you have some sort of make shift standby database created using perl scripts? The copy commands that you are using to get the datafiles across sound like to be the problem.

    If this is the case, why don't you just set up an actual Oracle standby database? Sounds to me that would be the way to go (You know, less moving parts the better). Besides, if you're running 9i the features that you could use for your standby are awesome.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Dec 2002
    Location
    Chennai, India
    Posts
    104
    Hi,

    I have created databases on each system but when ever one system goes down I need to revert back with updated data from the other system and for this the perl utility copies the database files from other system

  4. #4
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Exactly!

    That's why I'm saying your situation is ideal for a standby database. They're not hard to do and depending on how important it is that your data be "up to the minute", you could have your standby database available at anytime.

    Read up on standby databases and if you have trouble finding documentation come back here and I'll help you out.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  5. #5
    Join Date
    Dec 2002
    Location
    Chennai, India
    Posts
    104
    Hi,
    I won't be able to change the present design and there by standby database.
    Another interesting thing I'd observed is that I am not getting this file corruption when I'd added a delay of 10 sec.
    That is

    Stop local & remote Oracle Service
    sleep for 10 sec.
    delete old files in local and copies latest from remote
    Start local & remote Oracle Services

    Here everything works fine and I wonder why it happens bcoz other than 10 sec delay my stop script contains a loop of delay to verify whether the service was being stopped.

  6. #6
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    it's been a while since I used Oracle on Windows but I vaguely remember that there are two steps when shutting down the Oracle service --

    1. The service stops
    2. The database instance shutsdown (if that's how the service was setup with ORADIM)

    So the fact that the Oracle service has stopped does not gurantee the database instance is down (and so you may be copying the datafiles that are still open). The 10 seconds delay takes care of that problem.

    In Unix it's easy to chec k(by grepping for the processes like SMON) but in windows I guess the only way would be to somehow read the last line of the alert log to decipher if the database stopped.
    Rajeev Suri

  7. #7
    Join Date
    Dec 2002
    Location
    Chennai, India
    Posts
    104
    Hi
    I have seen that before copying the files it executes only 'net stop OracleServiceXXX on both the system and it does not do any shutdown on the database itself. If database shutdown was the real problem then it should seen in other copy also because in this process I am copying three databases to local and this problem is seen only for one database data file. Also this problem couldn't be reproduced in our test environment but seen on customer side.

    Should I need to shutown the database also before this copy like
    1. net stop OracleServiceXXX
    2. sqlplus /nolog
    set instance XXX
    connect /as sysdba
    shutown immediate

    Any thoughts.....

  8. #8
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    you shouldn't copy datafiles when the database is open (until unless the database is backed up HOT and the tablespaces are put in backup mode)

    Check the Registry and look at the Oracle Service to see if all three databases' registry Services are setup the same way; they should be setup to do both the following --
    1. Stop/Start the Service
    2. Stop/Start the database instance

    Another way to test will be to stop the service and check the alert log to see if it actually shutdown the database; again compare all three databases with this test.
    Rajeev Suri

  9. #9
    Join Date
    Dec 2002
    Location
    Chennai, India
    Posts
    104
    Hi
    How can I figure out whether database service has been set for
    Stop/Start the Service and Stop/Start the database instance
    because I've seen 'start as 2', 'type as 10' in the registry for oracle service.

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: ORA-01113: file 2 needs media ercovery

    Originally posted by sudheer_nm
    Hi All,
    I am getting the following error
    ORA-01113: file 2 needs media ercovery
    ORA-01110: data file 2: 'H:\db\acc\acc.dbf'

    So recover it.
    Jeff Hunter

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