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.

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.

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

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.

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.

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.

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.....

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.

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.

Quote:

---

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.

I couldn't able to connect to this database due to this error so recovering is not possible.

Connect locally using svrmgrl (or sqlplus 9i). Mount the database. Recover it.

I won't be able to connect using sqlplus and it always says 'oracle shutdown in progress' and when I did the following I got 'File 2 needs media recovery error'.

net start OracleServiceSID /y
sqlplus /nolog
set instance SID
connect /as sysdba
startup pfile'/xx/init.ora'

yes that's where you have to recover the datafile. I hope you have the archive logs to recover.

Quote:

---

Originally posted by SANJAY_G
Connect locally using svrmgrl (or sqlplus 9i). Mount the database. Recover it.

---

My database is in NOARCHIEVELOG mode and more over I am doing cold backup.

You can still try to recover. If you are lucky the file can be recovered from online redo logs. If not, your only option is to restore everything from your last cold backup.

Quote:

---

Originally posted by sudheer_nm
I won't be able to connect using sqlplus and it always says 'oracle shutdown in progress' and when I did the following I got 'File 2 needs media recovery error'.

---

You all missed an important point.. Sudheer_nm since it says "oracle shutdown in progress" abort the database. Make sure all processes connected with the database are down.

Your database is in No archive log mode so here is what you have to do.
If you've backed up your control file to trace we can get this thing recovered (if your datafile is not phyically corrupted).
Lets try this the easy way first and hopefully that will work.
1.start up the database (yea you'll get the .dbf needs recovery that's ok.
2. issue recover database
3. It's going to ask you for an archive log file. Give it the path of your first redo log file. If it doesn't work give it the next redo log file until there are no log files or your database recovers itself.

More than likely your datafile(s) have gotten out of sync and he's looking for a sequence #.

If this doesn't work then we'll have to get down and dirty with it the most important thing is if you have a control file backed up to trace we can get it recovered.