Wednesday, November 14, 2012

Temporary tablespace tempfile moved accidently at OS level

I saw this issue when the tempfile was moved accidently at the operating system level.It was throwing following error in the alert log.


data file 202: '/udb/u02/ORACLE/shas/temp2.dbf'
ORA-27041: unable to open file
ORA-12012: error on auto execute of job 449748
ORA-01116: error in opening database file ORA-01116: error in opening database file 202
ORA-01110: data file 202: '/udb/u02/ORACLE/shas/temp2.dbf'
ORA-27041: unable to open file
ORA-06512:


The resolution is to drop and add the tempfile but when I tried to drop I recieved the  following error.

ORA-25152: TEMPFILE cannot be dropped at this time

I used the following steps to identify the SID using the tempfile .After killing the SID I was able to drop and recreate the tempfile.

1)  SELECT s.sid, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks  
FROM v$session s, v$sort_usage u  
WHERE s.saddr=u.session_addr  
ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks; 
 

2)  select sid, username, osuser, program, machine from v$session where sid in   
(); 
 






No comments: