Move or Rename the Tempfile in Oracle ASM

Move or Rename the Tempfile in Oracle
==================================


Step1:-  Login as sysdba

Step2: Check Tempfile status corresponding to tablepace  using below query

 SQL> SELECT v.file#, t.file_name, v.status
   FROM dba_temp_files t, v$tempfile v
   WHERE t.file_id = v.file#
   and t.TABLESPACE_NAME='TEMP1';
    
     FILE# FILE_NAME                                STATUS
---------- ---------------------------------------- -------
         2 +DATA2/orcl/tempfile/temp1.269.935535665 ONLINE

SQL>

Step3: Make offline file which want to move using below query

SQL>
SQL> alter database tempfile '+DATA2/orcl/tempfile/temp1.269.935535665' offline;

Database altered.

SQL>
SQL>

Step 4: Check status of file

SQL> SELECT v.file#, t.file_name, v.status
   FROM dba_temp_files t, v$tempfile v
   WHERE t.file_id = v.file#
   and t.TABLESPACE_NAME='TEMP1';  2    3    4

     FILE# FILE_NAME                                STATUS
---------- ---------------------------------------- -------
         2 +DATA2/orcl/tempfile/temp1.269.935535665 OFFLINE



Step 5:- login  in asm instance and copy file to  one diskgroup to another diskgroup


[grid@rac1 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/grid
[grid@rac1 ~]$
[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA1/
DATA2/
ASMCMD>
ASMCMD>
ASMCMD> cp +DATA2/orcl/tempfile/temp1.269.935535665 +DATA1/ORCL/DATAFILE/temp1_new
copying +DATA2/orcl/tempfile/temp1.269.935535665 -> +DATA1/ORCL/DATAFILE/temp1_new
ASMCMD>
ASMCMD>
ASMCMD> cd +DATA1/ORCL/DATAFILE/
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            N    temp1_new => +DATA1/ASM/TEMPFILE/temp1_new.256.935624893

Step 6:  Rename file to new location  using below command 

SQL> alter database rename file '+DATA2/orcl/tempfile/temp1.269.935535665' to '+DATA1/ASM/TEMPFILE/temp1_new.256.935624893';

Database altered.

SQL>

Step 7: Make online file using below command 

SQL> alter database tempfile '+DATA1/asm/tempfile/temp1_new.256.935624893' online;

Database altered.

SQL>
SQL>  SELECT v.file#, t.file_name, v.status
   FROM dba_temp_files t, v$tempfile v
   WHERE t.file_id = v.file#
   and t.TABLESPACE_NAME='TEMP1';  2    3    4

     FILE# FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ -------
         2 +DATA1/asm/tempfile/temp1_new.256.935624893                  ONLINE

SQL>



=======
Make sure file is offline otherwise get below error
----------------------------------------------------------------
SQL> Alter database rename file '+DATA2/orcl/tempfile/temp1.269.935535665' to '+DATA1/ASM/TEMPFILE/temp1_new.256.935624893';
alter database rename file '+DATA2/orcl/tempfile/temp1.269.935535665' to '+DATA1/ASM/TEMPFILE/temp1_new.256.935624893';
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 202 - file is in use or recovery
ORA-01110: data file 202: '+DATA2/orcl/tempfile/temp1.269.935535665'