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

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

ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: ‘E:\ORACLE\ORADATA\IC\TEMP01.DBF’

Check the following error occurred during time of dba_temp_files view query

SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
*
ERROR at line 1:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: 'E:\ORACLE\ORADATA\IC\TEMP01.DBF'

Solution in two ways:
First way to add and remove only the tempfile. First add new tempfile then remove old.
Second way to remove complete temp tablespace and created new tablespace.

First Way
1. Check the table space name

select * from v$tablespace;

2. Add the Tempfile with new name into temp tablespace

alter tablespace TEMP add tempfile 'E:\ORACLE\ORADATA\IC\TEMP_01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M REUSE;

3. Drop the tempfile from the Database causing problem:

alter database tempfile 'E:\ORACLE\ORADATA\IC\TEMP01.DBF' drop;

4. Delete the temp file from file system.
 
Second Way
 
1. Check default tablespace

select * from database_properties where property_name like 'DEFAULT%TABLESPACE';

2. Steps to clear the temporary tablespace:
Note: Used only when load on server is very less because it may cause the server in hang state if large no of transaction is going in the database.
Check the usage of Temporary tablespace;

select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks,
a.sid, a.serial#, a.username, a.osuser, a.status
from v$session a, v$sort_usage srt
where a.saddr = srt.session_addr
order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks;

3. Find the location of temp files:

select * from v$tempfiles;

4. Create another Temporary table space in database;

Create temporary tablespace TEMP2 tempfile 'E:\ORACLE\ORADATA\IC\TEMP01.DBF'  size 500m;

5. Make the second temporary tablespace as default.

Alter database default temporary tablespace temp2;

6. Drop the old temporary tablespace.

Drop tablespace temp;

7. Need to add more temp files to second temporary files.

Alter database tempfile 'E:\ORACLE\ORADATA\IC\TEMP01.DBF' autoextend on next 100m;

8. Check the tablespace view to verify:

select * from v$tablespace;
select property_name, property_value from database_properties;

9. Manually remove the temp files from oradata folder of old temp tablespace. Do it carefully.
 

Advertisements

One thought on “ORA-01187: cannot read from file because it failed verification tests

  1. Pingback: Issue during datapatch verbose command in 12c patching (ORA-20001) | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s