Wednesday, January 18, 2012

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

After doing some intensive database simulations I decided to do some SQL tuning. So I went to EM and selected SQL tuning advisor. Since I did not have pre-configured SQL tuning sets, I selected a suitable time window in SQL top activity pane. Everything  looked fine until I clicked on submit button.
Bad luck.

There was a problem creating a SQL tuning task. ORA-01187: cannot read from file because it failed verification tests ORA-01110: data file 201: '/u01/app/oracle/oradata/testdb/temp01.dbf' ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 7718 ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 275 ORA-06512: at "SYS.DBMS_SQLTUNE", line 674 ORA-06512: at line 1


As you can see, error was ridiculously  long. Look, something is wrong with the temp file. So first thing I did was to check if the temp file really exists!

SQL> !ls /u01/app/oracle/oradata/testdb/temp01.dbf
/u01/app/oracle/oradata/testdb/temp01.dbf
It does exist. Now what? I checked dba_temp_files to check if it is listed there.


SQL> show user
USER is "SAJEEVA"
SQL> conn / as sysdba
Connected.
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: '/u01/app/oracle/oradata/testdb/temp01.dbf'

Something terribly wrong with the temp file. So here is how I solved it.

First I added another temp file to temp tablespace.

SQL> alter tablespace temp add tempfile;

Tablespace altered.
OMF was enabled, So no need to give the absolute path. 
Then I deleted the currupted (lets say currupted)


SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/testdb/temp01.dbf';

Tablespace altered.

Problem solved.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TESTDB2/datafile/o1_mf_temp_7kfxh4jq_.tmp

No comments:

Post a Comment