Re: Reading corrupted Datafiles offline

From: <jost_at_unitrade.com>
Date: Tue, 6 Sep 2005 11:59:41 +0200
Message-Id: <200509061159.41539.jost@unitrade.com>

On Tuesday 06 September 2005 08:42, Parker, Matthew wrote:
> As Jim mentioned there is the DUL (Data Unloader ) tool that Oracle
> consulting can use for you. The normal stated accuracy is 99% depending
on
> your system. It never understood how to follow chained rows. You are
lucky
> to be on 8.1.6 as they never updated the tool to 9i/10g according to the
> developer, ( a project sometime in the future for him).
>
> Give us so more details about what exactly happened and what you have of
> your database, then we may be able to help you.

hi,

here first the short version of what was happend.

First off all, the database belongs to a small customer of our company. This
customer doesn't take care of the backup what we have learned the hard way.

After a harddisk failure, which was not recoverable, some datafiles get lost,
other datafile get corrupted and some are totally ok.

Controlfiles are lost
System - Datafile is good
Rollback is totally lost
Redo Logs are good
Temp is good
User_Data was a bunch of 6 Files
File 1 is good

File 2 is corrupted (4 Blocks including Block# 1)
File 3 is corrupted (1 Block)
File 4 is corrupted (2 Blocks including Block# 1)
File 5 is corrupted (48 Blocks including Block# 1)

File 6 is missing

A Problem is, that we are not able to determine the right filename for some of
the files. The corrupted ones are the result of the recover of the bad hard -
disk. The original filename was lost, so we are unable to get the right filename. Unfortunatly dbv is not giving the filename or filenumber as a result of the offline check.

My intention is now to read or even export the data, which is included in the
datafiles of the tablespace USER_DATA.

Because of the missing controlfiles, i try to create new ones with the following SQL:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE
SET DATABASE SCHU
RESETLOGS
NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 254
    MAXINSTANCES 8
    MAXLOGHISTORY 907
LOGFILE

  GROUP 1 '/pro1/oradata/SCHU/redo01.log'  SIZE 10M,
  GROUP 2 '/pro1/oradata/SCHU/redo02.log'  SIZE 10M,
  GROUP 3 '/pro1/oradata/SCHU/redo03.log'  SIZE 10M

DATAFILE

  '/pro1/oradata/SCHU/system01.dbf',
  '/pro1/oradata/SCHU/temp01.dbf',
  '/pro1/oradata/SCHU/usr1SCHU.dbf',
  '/pro1/oradata/SCHU/usr2SCHU.ora',
  '/pro1/oradata/SCHU/usr3SCHU.ora',
  '/pro1/oradata/SCHU/usr4SCHU.ora',
  '/pro1/oradata/SCHU/usr5SCHU.ora'

CHARACTER SET WE8PC850
;

The init.ora includes the parameter _allow_resetlogs_corruption=true

The above mentioned SQL fails with the following error:

ORA-01503: CREATE CONTROLFILE gescheitert
ORA-01210: Datendatei-Header hat physikalischen Fehler
ORA-01110: Datendatei : '/pro1/oradata/SCHU/usr2SCHU.ora'

sorry for german, here the english translation:

ORA-01503 CREATE CONTROLFILE failed
ORA-01210 data file header is media corrupt
ORA-01110 data file '/pro1/oradata/SCHU/usr2SCHU.ora'

This is my point now. I have to restore the header of the files with a corrupt
block# 1 i think. But i don't know how.

So i thought about an offline datafile reader like dbv, which is able to get
the data out of the datafiles.

I will also start to discuss this with the Oracle support as well. But if someone of you have any ideas, i will of course appreciate this.

Thx in advance.

Jörg

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 06 2005 - 05:02:13 CDT