Moving DATAFILE(S) with the Database OPEN in 10.2.0.3

A datafile has been created in a wrong location by mistake, so it has to be moved to a valid location without down time. Its a 10.2.0.3 production environment.

Renaming or Moving DATAFILE(S) with the Database OPEN
  
1. Check how many datafiles are associated with the tablespace.

  
SQL>SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TS_BCH_DE';  

FILE_NAME                                  STATUS
----------------------------------------------------------------------------

F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_01.DBF                                               AVAILABLE
F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_02.DBF                                               AVAILABLE
F:\ORACLE10G\STT\ORADATA\TS_BCH_DE_03.DBF                                                      AVAILABLE

2. Make sure that all datafiles returned have the status AVAILABLE.

3. Make the tablespace is read only.

     
SQL> ALTER TABLESPACE TS_BCH_DE READ ONLY;

Tablespace altered.


4. Make sure that the tablespace is defined as read only in the data dictionary.
 
SQL>  SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TS_BCH_DE';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TS_BCH_DE                    READ ONLY

SQL>


5. Copy the datafile(s) to the new location using the operating system copy command.Once the datafile(s) have been copied to the new location compare the sizes of the datafiles.Make sure that the sizes match.

6. Once the datafiles have been copied to the new location alter the tablespace offline.


SQL> ALTER TABLESPACE TS_BCH_DE OFFLINE;

Tablespace altered.


7. Once the tablespace is offline you will need to rename the datafile(s) to the new location.This updates the entry for the datafile(s) in the controlfile.

     
SQL> ALTER DATABASE RENAME FILE
  2                   'F:\ORACLE10G\STT\ORADATA\TS_BCH_DE_03.DBF'
  3                   TO
  4                   'F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_03.DBF';

Database altered.


8. Once the alter database statement has been processed for the datafile(s) you can bring the tablespace online.

      
SQL> ALTER TABLESPACE TS_BCH_DE ONLINE;

Tablespace altered.


9. After you bring the tablespace back online you can make the tablespace read/write again.

      
SQL> ALTER TABLESPACE TS_BCH_DE READ WRITE;

Tablespace altered.

10. You can check the status of the tablespace to make sure it is read/write.You can also verify that the controlfile has been updated by doing the following:

        > ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

the above command creates the readable format of the content of the controlfile, check whether the datafile location is updated

         or

Optionally, we can query V$DATAFILE, which gets information from the controlfile as well.


SQL> select file#,status,enabled,name from v$datafile where name like '%TS_BCH_DE%';

     FILE# STATUS  ENABLED    NAME
---------- ------- ---------- ---------------------------------------------------------------------
        28 ONLINE  READ WRITE F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_01.DBF
        85 ONLINE  READ WRITE F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_02.DBF
        90 ONLINE  READ WRITE F:\ORACLE10G\STT\ORADATA\BCH_DE\TS_BCH_DE_03.DBF



11. Remove the datafile(s) from the old location at the O/S level.

Regards,

Navaneeth