Oracle Recovery after "Open DB reset logs" !!!!!!

[mk83]

Hi Guys,

We have a massive problem, I really would appreciate any help you may be able to offer, Two weeks ago a process was begun on a box to move the oracle database to another box. In order to keep the system up for as long as possible, while the online was being restored to the new box, the old system was kept running. during this time two new datafiles were added to the productive system, they were not backed up!

Once the restore was complete, the archives were copied across and the old database was shutdown, the new one was restarted and a recovery until cancel was run, resulting in the two files being listed as MISSING00256 and MISSING00257 on the new database. The database was then opened with the command "open database resetlogs". This box was then allowed to be worked on, but no backups were run on it!

The Database then crashed, the only backup we have is the one originally used to copy the database across, We have the logs that were run until the "open database resetlogs" command, and all logs after that!

Is there anyway to recover the data using the logs we have, i.e. is there anyway to run a recovery over a "open database resetlogs" command (The official oracle statement is 'no', but we are not looking for a supported procedure, we just need a procedure that works, any procedure, we are really desperate)


Please help,
Thanks in advance,
MK83

 

[alexhu]

In my experience (UK-based) if Oracle say this will not work - then it will not work.

I know they can be very 'picky' with 'normal' problems, but they will try any method to ressurect a down database, supported procedure or not.

Can you be a little more clear on the sequence of events ?

You added datafiles during the backup ?

You allowed users on the box, even though you had missing files reported ?

Alex

 

[mk83]

Hi Alex,

The biggest problem is the need we have to roll forward on logs until the point in time that the "Open database resetlogs" command was run, after that point we need to roll forward on the logs created after!

They added datafiles after the backup, but we seem to have found the correction for that, by running "Create datafile 'file1' as 'file2'" as soon as the rollforward reports the error!

The sequence of events is too embarrasing to ever totally be brought out into the open, (Luckly I was not involved in the creation of the problem! But rather in the fixing up )

Yes the box was running the whole time, reference above ^

Luckily I can report that you first statement has proved to be wrong, we found a procedure in one of our Oracle Manuals and when we asked oracle again, they "found" that they had a fuller set of documentation for the procedure, that had slipped thier minds yesterday! Funnily we were so desperate Last Night we phoned Oracle S.A, UK and America, none of them even hinted at a solution!

Thanks for the help, I will post back here if we manage to come right, and hopefully be able to leave a procedure that others can follow!

Regards,
Robert

 

[sybaseguru]

mk83,

Good luck with all these problems. So in the nutshell what caused the problem was the fact that someone made structural changes to the source database (adding new datafiles) without replicating it at the target database
You started using the new database, it crashed and you did not have a backup. You tried to recover it by using the old archive log from the source database but there was incompatibility because of those two new datafiles!