Subscribe YouTube Channel For More Live Tutorials
How to fix error ORA-01187: cannot read from file because it failed verification tests
Problem Summary :
SQL> select * from DBA_TEMP_FILES;
select * from DBA_TEMP_FILES
*
ERROR at line 1:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '/oradata/prod/temp01.dbf'
Issue Occurred :
Commonly occurs when manual refresh activity using rman from production to non production.
Before reset logs ,which is after restore and recover , we need to add temp files with reuse command..
eg: We learn’t recovering control files using backup script with no reset log option , these we see commands to reuse temp files after control files created with recover database option.
you can try creating backup script for control file and read the file to recover control files in second option
Same here we need to add temp files after recover using rman in target database after refresh with reuse command and verify.
If we miss to reuse and reset the logs then after we receive this error.
So add commands before reset logs.
Solution:
- add all listed physical tempfiles to the existing temp tablespace with reuse command and verify.
- If required can drop and recreate temp tablespace.
$cd /oradata/prod
$ls -ltr temp*
SQL> alter tablespace TEMP add tempfile '/oradata/prod/temp01.DBF' reuse;If multiple files found in list then add those all tempfiles with above command changing file name/location.
sql>select * from dba_temp_files;
will list the temp files wtihout error.
Note: May have multiple temp tablespaces in some databases, then we need to do the same above steps for all temp tablespaces.
