RMAN can be used to relocate a datafile.
I start with the datafile present in /home/oracle/app/oracle/oradata/orcl/
SQL> select tablespace_name, file_id, file_name
2 from dba_data_files
3 where file_name like '%add%';
TABLESPACE_NAME FILE_ID
------------------------------ ----------
FILE_NAME
--------------------------------------------------------------------------------
ADD_TBS 14
/home/oracle/app/oracle/oradata/orcl/add_tbs.dbf
SQL>
I would now like to relocate this to /oradata/orcl. I use RMAN thus :
RMAN> sql 'alter tablespace add_tbs offline';
using target database control file instead of recovery catalog
sql statement: alter tablespace add_tbs offline
RMAN>
RMAN> copy datafile 14 to '/oradata/orcl/add_tbs_01.dbf';
Starting backup at 24-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/home/oracle/app/oracle/oradata/orcl/add_tbs.dbf
output file name=/oradata/orcl/add_tbs_01.dbf tag=TAG20120324T001024 RECID=3 STAMP=778723826
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 24-MAR-12
RMAN>
RMAN> switch datafile 14 to copy;
datafile 14 switched to datafile copy "/oradata/orcl/add_tbs_01.dbf"
RMAN> recover tablespace add_tbs;
Starting recover at 24-MAR-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-MAR-12
RMAN> sql 'alter tablespace add_tbs online';
sql statement: alter tablespace add_tbs online
RMAN>
That was easy -- so far.
Now ... the question is : If I do a LIST BACKUP, what appears as the location of the datafile ? Is it /home/oacle/app/oracle/oradata/orcl ? OR is it /oradata/orcl ?
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Let's see ...........
.
.
.
.
.
.
.
.
.
.
RMAN> list backup of datafile 14;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 487.60M DISK 00:01:12 01-JAN-12
BP Key: 8 Status: AVAILABLE Compressed: YES Tag: TAG20120101T112516
Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
14 Full 4956816 01-JAN-12 /oradata/orcl/add_tbs_01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 530.30M DISK 00:01:44 23-MAR-12
BP Key: 18 Status: AVAILABLE Compressed: YES Tag: TAG20120323T231454
Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
14 Full 5192430 23-MAR-12 /oradata/orcl/add_tbs_01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 530.70M DISK 00:01:23 23-MAR-12
BP Key: 21 Status: AVAILABLE Compressed: YES Tag: TAG20120323T235528
Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T235528_7ps77jf2_.bkp
List of Datafiles in backup set 21
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
14 Full 5194695 23-MAR-12 /oradata/orcl/add_tbs_01.dbf
RMAN>
Apparently, Oracle now shows the *new* location of the datafile even for backups taken earlier !! How cool is that ?!
If I take a fresh backup ...
RMAN> backup datafile 14;
Starting backup at 24-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/oradata/orcl/add_tbs_01.dbf
channel ORA_DISK_1: starting piece 1 at 24-MAR-12
channel ORA_DISK_1: finished piece 1 at 24-MAR-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp tag=TAG20120324T001527 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-MAR-12
RMAN> list backup of datafile 14;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 487.60M DISK 00:01:12 01-JAN-12
BP Key: 8 Status: AVAILABLE Compressed: YES Tag: TAG20120101T112516
Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
14 Full 4956816 01-JAN-12 /oradata/orcl/add_tbs_01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 530.30M DISK 00:01:44 23-MAR-12
BP Key: 18 Status: AVAILABLE Compressed: YES Tag: TAG20120323T231454
Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
14 Full 5192430 23-MAR-12 /oradata/orcl/add_tbs_01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 530.70M DISK 00:01:23 23-MAR-12
BP Key: 21 Status: AVAILABLE Compressed: YES Tag: TAG20120323T235528
Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T235528_7ps77jf2_.bkp
List of Datafiles in backup set 21
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
14 Full 5194695 23-MAR-12 /oradata/orcl/add_tbs_01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 1.23M DISK 00:00:00 24-MAR-12
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20120324T001527
Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
14 Full 5196666 24-MAR-12 /oradata/orcl/add_tbs_01.dbf
RMAN>
Yes,
BackupSet Key 24 has the latest backup, created today.
Today's question :
What is the information that Oracle stores ? If the location of datafile 14 was under /home/oracle/app/oracle/oradata/orcl on 01-Jan and 23-Mar, why isn't that location presented ?Is it not stored in the repository ? Yes, the location of the BackupPiece is evident, but the location of the datafile is "current" even when the backup was taken when the datafile was elsewhere !
.
.
.
