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

[Steven E. Protter]

Shalom,

Sorry I did not respond to your follow up question. I was probably sleeping or something. Time zones.

Post Disaster.

If you were lucky, there was no activity on the database since the last checkpoint was written. If so, and the disk is not bad, you should be able with the help of Oracle support perform a fuzzy recovery. I did that once when my backup script failed to execute once the night before a disk failure.

Either way you need to pick a backup method.

Oracle provides tools, they are obtuse, and sometimes difficult to use.

If you can afford an hour or so of downtime (amount depends on time), you can simply bring the database down and take an OS copy of the tablespaces.

This works surprisingly well and was the backup of choice when I worked in the US. My DBA didn't like to fiddle around with the Oracle tools.

Rolling the database forward may be in issue in that scenarios.

Most backup software vendors for an additional fee can provide a software add in that will let your Legato,Netbackup client
perform hot or cold backups any time you like.

It is possible with the help of http://technet.oracle.com or the help of a halfway decent DBA to write a shell or perl script that performs the backup that puts the database in backup mode, which writes a checkpoint, also known as a restore point prior to taking a backup.

You may think you are a systems administrator but when the DBA has a problem, you are really kind of a DBA. So you are responsible to either build a decent backup method or push your DBA/development team to do it for you.

Last: Test restoration in the lab where you can afford to have it fail.

SEP