ORA-01113: file 97 needs media recovery

[DnD_1]

Hi guys,

pls help..

today our server hangs, after reboot it, and when i tried to start the oracle db,

i got this error msg,

ERROR at line 1:
ORA-01113: file 97 needs media recovery
ORA-01110: data file 97: '/elr/db1/oradata/ELR/mdsidx.dbf'

so i tried to recover it, but go the following errors.

SQL> recover datafile '/elr/db1/oradata/ELR/mdsidx.dbf';
ORA-00279: change 30637558718 generated at 11/03/2008 16:11:58 needed for
thread 1
ORA-00289: suggestion : /elr/oracle/product/9.2.0/dbs/arch1_62801.dbf
ORA-00280: change 30637558718 for thread 1 is in sequence #62801


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/elr/oracle/product/9.2.0/dbs/arch1_62801.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/elr/oracle/product/9.2.0/dbs/arch1_62801.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3

anyone pls help ?

[Steven E. Protter · Accepted solution]

Shalom,

File is corrupt.

Restore from last backup.

If possible, use redo logs to roll forward and lose as little data as possible.

SEP

[DnD_1]

Hi steve,

you mean whole backup or just a file backup ?

how do i use the redo log to roll forward ? i have very basic idea about db :(

many thanks !

[DnD_1]

hi steve

i just remember that we dont have the backup :(

[Volker Borowski]

Hum,
a simple crash should do no corruption to the database.

As you already did a reboot, you might try to

shutdown the database

startup mount

use

select * from v$backup

to check if any files are still in backup mode (status='ACTIVE' ?), if yes you can try to

alter datafile '......' end backup;

after no more file in backup mode, you should be able to open the db.

Volker

[DnD_1]

hi volker...

there's no active backup....

and still got the same error..

ORA-01113 and ORA-01110

:(

[Patrick Wallek]

>>there's no active backup....

Ummm......That's not good.

If you have a support contract with Oracle, I would call them and see if they have any advice.

[DnD_1]

what a disaster we have....

[Volker Borowski]

Not good...

Since the datafilename suggests the tablespace it belongs to only contains indexes (idx). You might be able to rebuild these.

But that is complicate stuff.

Before you do anything else...
Shut down every piece of software beside the OS and do a backup.
[I hope there is no second database on this box running from raw devices.]

Off we go:

STARTUP MOUNT the database

Check v$recover_file if this is the only file, that needs recovery.

If not, post results.

If yes, you might try (but to be honest, this should be done by someone who is more in detail with this sort of stuff)

alter datafile '/elr/db1/oradata/ELR/mdsidx.dbf' offline;
alter database open;

If this gives any errors, post results.

If this works, check in dba_data_files to which tablespace the file belongs.
select tablespace_name from dba_data_files where file_name ='/elr/db1/oradata/ELR/mdsidx.dbf';

For the tablespace reported, check dba_segments for the distinct segment_types located in this tablespace.

select distinct segment_type from dba_segments where tablespace_name='MDSIDX';
(or whatever the tablespace name has been reported)

If only INDEX is reported, you might end up lucky. If the database is small, you could try to do an export and import it to a newly created database, or you might be able to extract the indexdefinitions, drop the tablespace and create everything new.

If other segments are located inside, we'll have do further analysis.

Best luck, and again, this is no situation to do "try and error" without knowing exactly.

Volker

BTW: do you have a second box with a testsystem on it, where you could try to do some stuff without destroying the original?

[Volker Borowski]

Just forgot:

Some sort of logs would be nice in addition.
The last section of the alert log would be of great help as a starter.

Volker