David Marcos' Blog

ORA-01187: cannot read from file because it failed verification tests + ORA-01110 on tempfiles 5 October 2010

Today I had a call from a Java developer asking me to help him with an error from our applications. The error being reported was:

java.sql.SQLException: ORA-01187: cannot read from file because it failed verification tests ORA-01110: data file 206: '/u03/oradata/xxx/tempapp22.dbf'

so I did have a look.

The problem:

ORA-01187 + ORA-01110 on the application logs. This part of the application runs on an Active Standby database.

This database has two temporary tablespaces called TEMPAPP and TEMPUSER. We assign TEMPAPP to application users and TEMPUSER to physical users.

The solution:

The first thing I did was to check the existence of the tempfile on the filesystem:

[oracle@sssss ~]$ ls -lrt /u03/oradata/xxx/tempapp22.dbf
-rw-r--r-- 1 oracle oinstall 26843553792 Oct 2 11:26 /u03/oradata/xxx/tempapp22.dbf

ok, this is a step forward as the file exists on the server. What did not look ok was the date of the file (October 2nd) and today is October 5th… maybe there is not much activity on the database?

Then I did have a look on the database:

SQL> select file_name from dba_temp_files;
ERROR:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 205: '/u03/oradata/xxx/tempapp11.dbf'

This did not look good, specially because the tempfile being returned was a different one from the application. After some trial and error I decided the following strategy:

1.- Drop the tempfile from the database:
SQL> alter database tempfile '/u03/oradata/xxx/tempapp11.dbf' drop;

2.- Create a new tempfile for the temporary tablespace:
alter tablespace TEMPAPP1 add tempfile ‘/u03/oradata/xxx/tempapp11.dbf’ SIZE 25600M REUSE;

I did have to drop all tempfiles (from TEMPAPP and TEMPUSER) as all of them were giving problems when selecting from dba_temp_files

Once I did drop all tempfiles and created them again, the query did return the expected rows:

SQL> select file_name from dbA_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/u03/oradata/xxx/tempapp12.dbf
/u03/oradata/xxx/tempapp21.dbf
/u03/oradata/xxx/tempapp11.dbf
/u03/oradata/xxx/tempapp22.dbf
/u03/oradata/xxx/tempusers11.dbf
/u03/oradata/xxx/tempusers21.dbf

As always, comments are welcome.