DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: ora-01187 : datafile failed verification test

  1. #1
    Join Date
    Sep 2001
    Posts
    34

    Exclamation

    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" ?

  2. #2
    Join Date
    Nov 2000
    Posts
    56

    Smile

    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.
    Rama

  3. #3
    Join Date
    Sep 2001
    Posts
    34

    Lightbulb

    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.

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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';

  5. #5
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    An excerpt:

    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.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  6. #6
    Join Date
    Sep 2001
    Posts
    34

    Talking

    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 ?

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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.

  8. #8
    Join Date
    Sep 2001
    Posts
    34

    Thumbs up

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

  9. #9
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763

    Talking

    Hi,

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

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width