flashback database point in time recovery where is my relocated datafile available in 12c

Its new location

Documentaion says

“A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one location to another and later flash back the database to a point in time before the move, then the data file remains in the new location, but the contents of the data file are changed to the contents at the time specified in the flashback.”

Let see the example :

Step 1:

22:18:14 SQL> select current_scn from v$database;

CURRENT_SCN
———–
6270275

22:18:26 SQL> create table emp(empno number) tablespace users;

Table created.

22:18:55 SQL> insert into emp values(000001);

1 row created.

22:19:11 SQL> commit;

Commit complete.

22:19:14 SQL> select current_scn from v$database;

CURRENT_SCN
———–
6270313

Step 2:

22:19:51 SQL> select name from v$datafile;

NAME
——————————————————————————–
/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/oradata/ora12c/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf

Step 3:

22:19:59 SQL> alter database move datafile ‘/premia/app/12c/oracle/oradata/ora12c/users01.dbf’ to ‘/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf’;

Database altered.

Step 4:

22:20:29 SQL> select name from v$datafile;

NAME
——————————————————————————–
/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf

Step 5:

22:20:39 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
22:22:57 SQL> startup mount
ORACLE instance started.

Total System Global Area 5016387584 bytes
Fixed Size 5293992 bytes
Variable Size 1258293336 bytes
Database Buffers 3741319168 bytes
Redo Buffers 11481088 bytes
Database mounted.
22:23:09 SQL> !rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Sep 26 22:23:23 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12C (DBID=264834751, not open)

RMAN> FLASHBACK DATABASE TO SCN 6270275;

 

RMAN> alter database open resetlogs;

RMAN> select name from v$datafile;

NAME
——————————————————————————–

/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf