Online rename and relocation of an online data file on DG environment.

 Now no need to perform number of steps to move or renaming of datafile. with 12c you can move the datafile online simply using ALTER DATABASE MOVE DATAFILE command ,while the data is being transferred, the user can perform DML and DDL tasks and you may can do migration from non-ASM to ASM and vice versa.  

Rename a data file:

SQL> select banner from v$version where rownum<=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

//Select the DB role

SQL> select database_role  from v$database;

DATABASE_ROLE

----------------

PRIMARY

//get the file location which you want to move as in my e.g i am going to perform on users datafile .

SQL> select name from  v$datafile where file#=6;

NAME

--------------------------------------------------

C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

//Check the max sequence number of primary DB

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

            22

// connect to standby 

QL> conn sys/xxxx@standby as sysdba

Connected.

//Check the applied sequence number .

SQL> select max(sequence#),applied from v$archived_log group by applied;

MAX(SEQUENCE#) APPLIED

-------------- ---------

            22 YES

//check the file location of db file 6 as in my case its on same path.

SQL> select name from  v$datafile where file#=6;

NAME

--------------------------------------------------

C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

// now login on primary and perform the movement operation.

SQL> conn sys/xxxx@primary as sysdba

Connected.

// performning he movement command .

SQL> alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF

' to 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf';

Database altered.

//as we can see file got change to its parent folder from oradata/orcl to oradata.

SQL> select name from  v$datafile where file#=6;

NAME

--------------------------------------------------

C:\APP\ADMINISTRATOR\ORADATA\USERS01.DBF

//Check the max sequence number of primary DB

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

           22

// not switch the logfile to check the synch status.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

            23

//login to standby and see the synch status and file path at standby server .

SQL> conn sys/xxxx@standby as sysdba

Connected.

SQL> select name from  v$datafile where file#=6;

NAME

--------------------------------------------------

C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

SQL> select max(sequence#),applied from v$archived_log group by applied;

MAX(SEQUENCE#) APPLIED

-------------- ---------

            23 NO

            22 YES

SQL> select max(sequence#),applied from v$archived_log group by applied;

MAX(SEQUENCE#) APPLIED

-------------- ---------

            23 YES

//As we can see DB got synced but file path is same this is new things in 12c, physical file movement wont go from redo you have to perform MOVE DATAFILE command at standby also. 

SQL> select name from  v$datafile where file#=6;

NAME

--------------------------------------------------

C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

SQL>

Now if you want to move the file at standby also need to perform move datafile command at standby db.

========================Moving the file at stanbdy====================

SQL> alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF

 ' to 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf';

alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF ' to

 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf'

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

//As MRP is running need to stop the same.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database move datafile 'C:\app\Administrator\oradata\orcl\USERS01.DBF

 ' to 'C:\APP\ADMINISTRATOR\ORADATA\users01.dbf';

Database altered.

// now users db file on both site are on same path.

//Below few more task(12c New feature ) you may can perform on-line if required  

Migrate a data file from non-ASM to ASM:

SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '+DG_DATA';

Migrate a data file from one ASM disk group to another:

SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_NEW';

Overwrite the data file with the same name, if it exists at the new location:

SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '/u01/data_new/users_01.dbf' REUSE;

Copy the file to a new location whilst retaining the old copy in the old location:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;

You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view. Additionally, you can also refer the alert.log of the database where Oracle writes the details about action being taken place.