Thursday, August 29, 2013

ORA-25152: TEMPFILE cannot be dropped at this time during rconfig

Scenario:

Conversion of single instance database in shared NFS storage to RAC/ASM using "rconfig" command. Database version is 11.2.0.3.0

Received following error in the "Setting TEMP tablespace" step.

<ErrorDetails>
             oracle.sysman.assistants.rconfig.engine.StorageCreationException: o
racle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-25152: TEMPFILE cannot be dropped at this time
Operation Failed. Refer logs at /u01/app/oracle/cfgtoollogs/rconfig/rconfig_05_14_13_21_18_17.log for more details.
</ErrorDetails>

Verification:

The database had 4 temp files out of which 2 were succesfully moved to ASM. The other two files had issues.

Normally this error is noticed while trying to drop a temporary tablespace which is used by users at the execution of the command.

In this case on RAC conversion there was not much chance to identify and kill the process using temp file.

Solution:

a) Drop the tempfiles before trying rconfig. 
b) Then restart the RAC conversion using rconfig. 
c) After RAC conversion add the temp files which was dropped earlier.

Use the following command to drop temp files.

alter database tempfile '<PATH>/temp02.dbf' drop including datafiles;


I dropped both the problematic tempfiles and then removed the files physically from ASM. Later I reinitiated the rconfig command and the conversion happened.

After RAC conversion add both tempfiles, which were dropped earlier.

alter tablespace temp2 add tempfile size 524288000; 

2 comments:

  1. I was facing the same issue and after killing the sessions using temp I was able to drop the temp files.

    ReplyDelete
  2. I was facing the same issue and after killing the sessions using temp I was able to drop the temp files.

    ReplyDelete