Very bad ORACLE recovery scenario .....

[Volker Borowski]

Hi,

here is the plot:

My customer has lost (means overwritten) some tapes from his backup software (Omniback). Oracle is running in SAP environment 8.0.5 64 Bit. Total size is around 250 GB. Backup is going to two tapes, protection for the tapes is 10 days (ya, they are going to change that now....).

Now the problem:
- due to programming error we need to recover to 2001/12/31 06:00
- due to overwritten tapes, we need to restore from several diffrent backups, but we will not be able to restore ALL files.
- redologs are available as far as backups go back
- I have a binary backup of the controlfile from 2001/12/30 14:00
- when all is going well, I will have in the end 9 files missing, of which I can create 4 with CREATE DATAFILE, as they are quite new.

Basicly I need some data from two tables, both located in the same
tablespace. The tablespace consists of 30 2GB files. I miss file #3 and file #13 of this tablespace. The tables are unlikey to have major updates, and esp. the rows I need are suspected to be in datafiles bigger than #20 (this is gambling, but anyway...)
Now I tracked some queries, to extract the data I need from a sample database from a tablespace with an offline datafile. Did work quite ok.

When my restore is finished, I hopefully will have:
- all datafiles of the system tablespace
- all datafiles of the index tablespace corresponding to the tablespace with two missing datafiles

My approach will be to set the missing datafiles offline, recover the database (... using backup controlfile until 'timestamp').
I tend to comment out all rollback-segments from the init.ora, because one of the missing datafiles belongs to the rollback-TS.
With this, I'd like to "open resetlogs" the database.

### Is there a chance that this will work ??? ###

When I am that far, I am pretty sure, I'll manage to get some data, but I have two big question marks:
- "recover database" with offline datafiles
- "open reset logs" with offline datafiles

I know this is far beyond any standard recovery, but will the be a chance (with luck of course) to access some data from those tables ?

Any feedback welcome,
even wishes for luck :-)
Volker

[Andreas D. Skjervold]

Hi

Haven't been down that road (yet), but as far as I know this is the case:

As long as you have some version of all your old datafiles and ALL archived redologs since, the recover database command should succeed.

But;
if you have recreated some of the tablespaces due to the lack of backupfiles, these would not be able to recieve data from the redologs, as these are NEW datafiles as far as Oracle knows (I think).

Good luck!
Sorry I can't be of much help!

Andreas

[Alexander M. Ermes]

Hi there.
Volker, you have a good chance, if your database is in archivelog mode.
If you restore the database, you should have the offline redo log files available from that date on.
Restore only the datafiles you need ( no logfiles, no controlfiles ), then do the recover until time. That should restore all the data before the moment of the crash.
If you overwrite the controlfile, you might loose the correct timestamp.
If you need more help, contact me.

alexander_ermes@web.de
Rgds
Alexander M. Ermes

[Volker Borowski]

Thanks for comments so far.

The real problem is:
- since I need to do a point in time recovery, I need to restore ALL datafiles (but I do not have them all!)
- there is no problem to recover the database with a backup controlfile, even without having some datafiles. These can be created with CREATE DATAFILE, if the controlfile knows them already and you have all redologs (been there, done that).

The problem is, I do not have ALL files and for the files missing, I do not have all redologs. I need to do the recovery with some files offline and then "open resetlos" the database.

Regards
Volker

[Printaporn_1]

I think it not possible to open database in this situation.

[Andreas D. Skjervold]

Hi

There is a parameter you can set in the init.ora file to force Oracle to startup even tough files are missing / need recovery.

This will give a inconsistant database, but it will anyway be more than you have today.

Unfortunately I haven't been able to recall or lookup this parameter but will continue to search my mind and notes for it.

Anyone else know this?

Andreas

[Andreas D. Skjervold]

Found it:

_allow_corruption
_offline_rollback_segments, and _corrupted_rollback_segments

From Oracle Note: 94114.1 (Metalink)

h) Recovery with corrupted/missing rollback segments.
If a rollback segment is missing or corrupted, you will not be able to open the
database. The first step is to find out what object is causing the rollback to
appear corrupted. If we can determine that, we can drop that object.
If we can't we will need to use the hidden parameters _allow_corruption.

If in fact the rollback segment itself is corrupted, we should see if we can
restore from a backup. However, that isn't always possible, there may not be a
recent backup etc. In this case, we have to force the database open with the
unsupported, hidden parameters
_offline_rollback_segments, and _corrupted_rollback_segments.
Please note, that this is potentially dangerous!


Andreas

[Krishna Prasad]

Whan is the date of the last good backup that you have?

And how far back to have archives files available?

If you have missing files and you create empty files you will have to have the archives as far back as the first time the file was created to even have a chance.

If they are only keeping the backup for 10 days how long are they keeping the archives?

[Thierry Poels_1]

Hi,

isn't it possible for you to recover (part of) the database to another location/server. As maybe 99.9% of the current DB is still OK, it would be pitty to loose it.
Afterwards you can compare and/or copy data from the backup with the current DB.
The bad thing about this is that you need diskspace.

I once used this trick to get some table data from an older backup after "one" table got corrupted. I was able to restore system, temp, rollback and the one datafile (was whole tablespace) I needed, and ignored all other datafiles (did not have space for them anyway), and was able to export/import the required data to the production DB.

good luck,
Thierry.

[Volker Borowski]

Luck, luck, luck, ....

right now we managed to get nearly all the files. Right now, we only miss one datafile of an index tablespace, which does not contain any data we need to access.

I am pretty confident now, that we will be able to get everything we need. Nevertheless, if I have some spare time, I'll try this in a test environment (with a small database), as this scenario seems extremly interesting.

Right now recovery is on the way.
I'll let you know later how it turned out.

Thanks
Volker

PS:

Andreas: yes, I found those parameters also. I am not sure, if there would be need to uses them. May be it would have been necessary to use them, if a file of the rollback tablespace is missing (which in the begining was the case).

Ron: They use 10 days protection in Omniback. Since Omniback releases the tapinformation when the tape is overwritten, it may be, that you can go back further that the protection period.

Thierry: This is in fact what we are doing.

[Carlos Fernandez Riera]

Well ... you will say i am mad...

Once upon a time my DBA changed something inside a datafile ( i supose SCN) using a hex editor on the header of a datafile ( maybe adb should be used).

If you are pretty sure that database is not enough corrupted yo you this would be a way...

I can tell any more....

Best of lucks.

[Volker Borowski]

Hi Carlos,

hopefully I will never be that desperate in any case :-)

Update: Recovery ist finished !

Oracle claimed two aspects:
- database needed to be "open resetlogs"
- datafile "..the one missing...." will be lost because offline
No suprise so far.

Since very recent, we got the information, that there might be a chance that we need to recover until 12:00, we did not open the database yet, but take an additional filesystem backup now.

Open Resetlogs tomorrow !

Volker

[Carlos Fernandez Riera]

The only good thing of this type of work is all the knowledge you get.

Very good work, Volker.. Congrats..

And good luck again,


[N/A] Please.

[Krishna Prasad]

If you are only missing a index you should be able to recreate it later.

Would you mind detaling what exact steps you took to do your recovery?

I wouldn't mind having in my oh-#$%# file.

[Volker Borowski]

Of course friends,

when I am through this, I'll do a detailed documentation of this job, be sure :-)

I am not so sure, if all Oracle-guys will be able to use this, because some steps might sound Omniback or SAP specific (I'll do my best to translate these steps to Oracle-language :-).

So long
Volker

[Krishna Prasad]

Great, my environment is SAP/Oracle

[Volker Borowski]

I am going to have a big scotch tonight :-)

- Recovery OK
- OPEN RESETLOGS did work after drop of missing file
- Required data was accessible
( 500K compressed export *smile* )
- Rebuild of index tablespace is on the way

I guess I am a very happy fellow today :-)
I hope I can do a documentation next week.

Have a nice weekend everyone
Volker

[Volker Borowski]

Hi friends,

this is the final message, closing this thread.
As an attachment, I put a short documentation, of what was done to recover this situation.

In addition to the header of the document, I have to repeat here, that the SQL-statements of that document are specific to the recovery-situation. Executing these statements against a database may lead to inconsistencies or loss of data if not used in the proper context.
Of course I take no warranty for anything in this document.

If anyone is in a similar situation, I am available for consulting :-)
Volker