jump to navigation

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

Posted by David Alejo Marcos in Oracle 11.2, Standby.
Tags: ,

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;
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;

As always, comments are welcome.



1. Ian - 2 December 2010

Just a quick thank you. I was performing a dataguard switchover this morning, and ran into this problem on the new primary.

While waiting for MyOracle Support to load, I decided to check Google and yours came up in the top few, but most relevant to my predicament.

Needless to say, it saved me a huge amount of time and allowed me to restore service sooner rather than later!

Keep it up!!!

2. Willy - 22 December 2010

thanks! it worked great for the temp files that I was getting an error.

3. vijay Kumar - 5 December 2011

Thank you so much David !!! It worked.

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 )


Connecting to %s

%d bloggers like this: