ORA-01157, ORA-01110 Errors, When Query DBA_TEMP_FILES

Last morning I was migrating a database from filesystem to ASM, when I was quering the dba_temp_files get the next error.

 

 
SQL> SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FILES
              *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/pludev/pludev_temporal_01.dbf'

Ok, the CF file reference old temporary files on filesystem. You can check this by the next query (I have added news tempfiles on ASM);

 
SQL> select ts#, name from v$tempfile;

       TS# NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         3 /pludev/pludev_temporal_01.dbf
         5 /pludev/pludev_temporal_users01.dbf
         3 /pludev/pludev_temporal_02.dbf
         3 +PLU_DEV_DAT_DG/pludeV/tempfile/temp.298.955652617
         5 +PLU_DEV_DAT_DG/pludeV/tempfile/temp_users.299.943353021
         3 +PLU_DEV_DAT_DG/pludeV/tempfile/temp.300.945664717

6 rows selected.

In this case the easy way is drop old tempfiles (in filesystem format) as show in the next script;

 
alter tablespace temp1 drop tempfile '/pludev/pludev_temporal_01.dbf'; 
alter tablespace temp2 drop tempfile '/pludev/pludev_temporal_users01.dbf'; 
alter tablespace temp3 drop tempfile '/pludev/pludev_temporal_02.dbf'; 

After that, if you repeat the query on dba_temp_files;

 
SQL> select file_name from dba_temp_files;

FILE_NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+PLU_DEV_DAT_DG/pludeV/tempfile/temp.298.955652617
+PLU_DEV_DAT_DG/pludeV/tempfile/temp_users.299.943353021
+PLU_DEV_DAT_DG/pludeV/tempfile/temp.300.945664717

HTH – Antonio NAVARRO

 

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s