Friday, October 14, 2011

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

You may receive this error under below circumstances.
1. when your temp file drive failed
2. when you duplicate your database.
3. When you trying to take backup controlfile to trace….etc

ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/u03/oradata/TEST/temp01.dbf'


The temp data file didn’t not pass the checks to insure it is part of the database and reads are not allowed until it is verified.

You will see the status as “ONLINE” when you check the status of the temp file using v$tempfile. But when you query DBA_TEMP_FILES’, you will receive the error.

SQL> SELECT FILE_ID,FILE_NAME, STATUS FROM DBA_TEMP_FILES;
ERROR at line 1:
ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/u03/oradata/AGX/temp01.dbf'

To resolve this issue first run below command and query DBA_TEMP_FILES.
SQL> ALTER SYSTEM CHECK DATAFILES

If you still have the issues then add a tempfile and drop the file which has issues.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘'/u03/oradata/TEST/temp02.dbf' SIZE 100M;
SQL> ALTER DATABASE TEMPFILE TEMPFILE ‘'/u03/oradata/TEST/temp01.dbf' DROP;

This should resolve your issue.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

4 comments:

  1. nice one and helpful.


    Thanks&Regards
    Muhammad Abdul Halim
    http://halimdba.blogspot.com/

    ReplyDelete
  2. Hello Satishbabu,

    Thank you for sharing this information. It will help database administrator to resolve problems.

    Regards,
    Mark Willium
    http://oraclerecovery.blogspot.com/

    ReplyDelete
  3. Thanks , remove one 'TEMPFILE' from last statement.

    SQL> ALTER DATABASE TEMPFILE TEMPFILE ‘'/u03/oradata/TEST/temp01.dbf' DROP;

    ReplyDelete