RECOVERY!!! URGENT!!! TAMIL,PANDO and EVERYBODY join !!!

[vinodkmg]

HI All,

VERSION 8.0.6 oracle running in Archive Mode.

Its Urgent my production system has some probs.....I will tell the the situation,,,first my junior dba added a datafile to a tablespace and which lead to the status of the datafile in RECOVER Mode....So what he did is ,,he gave a statement to Recover the datafile

Recover datafile '/path'

While it what recovering he cancled it seems...

So after that he called me and now i used the give the RECOVER DATAFILE '/path' which leads to error saying cancled during Recovery and its no not recovering it.

and what i didnt is gave alter database datafile 'path' offline drop.

and checked the status in the v$datafile but still it is RECOVER mode.

So i took the control file back and removed the entry of the newly added datafile and RECREATE the control file with RESETLOGS.

Then now i checked the status of the datafile and found its '/path/Missing00066'

Then i gave alter database RENAME file '/path/MISSING00066' to the old '/path'

But the entry in the datafile got changed from MISSING00066 to the path and the file name i gave.....but still the datafile is in RECOVER mode...Please Let me know the solution...ITS VERY VERY URGENT.

Thanks & Regards,
Vinod Kumar.

[carp]

Vinod -
This is a long shot, but have you tried
ALTER DATABASE RECOVER CANCEL;

This might kill all recovery operations and allow you to drop the file.

[vinodkmg]

Hi,
No i havent tried that....Since the control file has been recreated with RESETLOGS.I cant use that command.

RECOVER DATABASE UNTIL CANCEL.

[Kishore]

Best Way is if you can down the database, restore the previous day's backup. Recover it and apply all redo logs till just before the new datafile is added.

I had the same situation and nothing worked and I finally had to restore the last night's database and aply the archive log file.

[pando]

this is tricky, it hasnt happened to me in a real database but it did happen with a test database although the situation was a bit different, the problem I had was I changed the database structure by adding a datafile then the database crashed Since I didnt have any backup of the new datafile I came across with this problem of datafile being recover and missing00xx. The only way I could recover it was going back 1 day and applied the logs until just before I added the datafile with a backup controlfile as well.
I conclude that whenever i change the database structure immediately I should make a cold backup...

[vinodkmg]

Hi All,
See we have O/S backup of all the filesystem..Say if iam going to restore all the datafiles,controlfiles,Logfiles,Archives of the previous days backup...and if i start the database..will it come without any probs..May be i may lose todays transaction...but will i get at least data from yesterday nights.We have O/S backup of 29th night.

Thanks and Regards.

[marist89]

Depends on if your backup was done with the database up or down.

If the database was down, everything should come up OK.

If the database was up, you still have a chance to restore.

Either way, you can recover using a backup control file:
1. copy the restored control file to the locations your original control files were located.
2. start the database in mount mode
3. recover the database using the backup controlfile (alter database recover until cancel using backup controlfile).
4. keep applying archived redologs until just before the problem.
5. alter database open resetlogs.

(This procedure is outlined in note 69468.1 on metalink.oracle.com.)

[tamilselvan]

If nothing is stored in the datafile, try to recreate the data file and do the recovery .

After the control file was recreated, while opening the data base, did you get error message?

[abhijit]

If you can effort to loose data after last full backup(i.e 29 th nite backup), then you should restoreandrecover your database from that backup as mentioned by Kishore. Your DB will come without anyprobs.

If you want to restore your DB just before the new data file, restore all the data files from 29th backup and recover the DB with current control file (not the control file that you created after the crash) with until cancel time(I hope you have all the archive logs) option.

Good luck

abhijit

[pando]

You cannot use the current controlfile since the structure of the database changed from last backup.