ora-01187 : datafile failed verification test

Hello,

Today , i'm very sad.

This morning, when i went at office, I saw that backup didn't work this night (since 3 days). When I search why it didn't work in the log file, I read in it an error ora-01187 and ora-01110. They mean that a datafile called TOTO_TEMP2.ORA failed verification tests.

But unfortunatelly, I don't find the reason of this problem.
The files is on the disk, at the good folder, with the good name.

Can anyone help me.

Can I create a new tablespace which could replace this one. If yes, could you explain me risks there are to move users from one tablespace to the new tablespace. How can I drop the "bad tablespace" ?

Hi,

This error generally occurs when the database does not recognise the datafile. Could you tell me the conf. of the Database and O/s u r using.
Make a normal shutdown and open it and let me know.

Thanx,
I've already try to shutdown and restart the DB but nothing ....

The db has 50 M of SGA, archive log Mode is not activate, the instance has been created in 8.1.5 version, the db block size is set to 2048, the parameter mts_server in the init is set to 1, and time_Statistic is set to TRUE.

The OS i'm using is NT4.0 with sp4 SPack. I've remarqued that when I execute this request :

select * from v$datafile;

the tablespace with the file which is bad does't appear.

Oracle Docs says you have to issue ALTER DATABASE CHECK.
But I'm not sure if this can solve the problem. Was your database restored from backup datafile?

TOTO_TEMP2.ORA
Is this a datafile of a TEMPORARY TABLESPACE, if it is, then you can recreate this. Issue the command to verify:
select file_name, status from dba_data_files
where tablespace_name='TEMP';

An excerpt:

Quote:

---

ORA-01187 cannot read from file string because it failed verification tests

Cause: The datafile did not pass the checks to insure it is part of the database. Reads are not allowed until it is verified.

Action: Make the correct file available to the database. Then, either open the database, or execute ALTER SYSTEM CHECK DATAFILES

---

I think your datafile is corrupted.
You can make new users and new tablespace but if those users have created any objects in the corrupt tablespace, I am afraid, I dont think they can be recovered.
You can drop the bad tablespace after taking it offline.

I thank you all for your precious recommandation...

The datafile which is corrupted is in a TEMPORARY TABLESPACE. What do you think about the creation of a new Tablespace before drop the current Tablespace which contains the corrupted datafile ?

PWC.... you're LUCKY

You can just recreate the tablespace TEMPORARY

when no more user logged in to your db(or you can bounce your db and login with restrict mode).

alter tablespace TEMPORARY offline;
drop tablespace TEMPORARY;
create tablespace TEMPORARY datafile 'C:\ORANT\ORADATA\PRD\TOTO_TEMP1.ORA' size100M reuse online temporary;
alter tablespace TEMPORARY add datafile 'C:\ORANT\ORADATA\PRD\TOTO_TEMP2.ORA' size 100M reuse;

Did I miss something guys? Hope not.

good luck.

Excellent I thank you for your help...
I'll try this evening your solution... it's 12 h 00 AM here ...

Hi,

A good company in this cases is Rama Velpuri's Backup & Recovery handbook.

Cheers