How to Move DataFile Location in Oracle

  • by

ALTER DATABASE MOVE DATAFILE

After changing SPFILE location, changing control file location and changing redo logs location, we are going to move or rename data files.

For you reference, the procedures to move a whole database offline can also be found.

In this post, I will introduce some ways to move data files in single-instance databases and RAC databases.

  1. Move Data File Location of a Single-Instance Database
  2. Move Data File Location of a RAC Database

A. Single-Instance Database

There're 3 scenarios of changing data file location in a single-instance database.

  1. Move All Data Files Location of a single-instance database
  2. Move Tablespace Location of a single-instance database
  3. Move Some Data Files Location of a single-instance database

1. Move All Data Files Location

Make some necessary directories in the new locations.

[root@test ~]# mkdir -p /oradata/ORCLCDB/ORCLPDB
[root@test ~]# mkdir -p /oradata/ORCLCDB/pdbseed
[root@test ~]# chown -R oracle:oinstall /oradata/

Check current data files location.

SQL> set pagesize 100;
SQL> select name from v$datafile order by 1;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/sysaux01.dbf
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/system01.dbf
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/undotbs01.dbf
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/users01.dbf
/u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
/u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
/u01/app/oracle/oradata/ORCLCDB/system01.dbf
/u01/app/oracle/oradata/ORCLCDB/undotbs01.dbf
/u01/app/oracle/oradata/ORCLCDB/users01.dbf

12 rows selected.

Restart the database to MOUNT.

RMAN> shutdown immediate; RMAN> startup mount;

Copy all of data files to the new location.

RMAN> backup as copy db_file_name_convert ('/u01/app/oracle/oradata/ORCLCDB/','/oradata/ORCLCDB/') database;

Starting backup at 14-JAN-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCLCDB/system01.dbf
output file name=/oradata/ORCLCDB/system01.dbf tag=TAG20210114T015142 RECID=9 STAMP=1061776313
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
output file name=/oradata/ORCLCDB/sysaux01.dbf tag=TAG20210114T015142 RECID=10 STAMP=1061776325
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/sysaux01.dbf
output file name=/oradata/ORCLCDB/ORCLPDB/sysaux01.dbf tag=TAG20210114T015142 RECID=11 STAMP=1061776336
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
output file name=/oradata/ORCLCDB/pdbseed/sysaux01.dbf tag=TAG20210114T015142 RECID=12 STAMP=1061776344
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/system01.dbf
output file name=/oradata/ORCLCDB/ORCLPDB/system01.dbf tag=TAG20210114T015142 RECID=13 STAMP=1061776351
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
output file name=/oradata/ORCLCDB/pdbseed/system01.dbf tag=TAG20210114T015142 RECID=14 STAMP=1061776358
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCLCDB/undotbs01.dbf
output file name=/oradata/ORCLCDB/undotbs01.dbf tag=TAG20210114T015142 RECID=15 STAMP=1061776364
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf
output file name=/oradata/ORCLCDB/ORCLPDB/example01.dbf tag=TAG20210114T015142 RECID=16 STAMP=1061776366
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/undotbs01.dbf
output file name=/oradata/ORCLCDB/ORCLPDB/undotbs01.dbf tag=TAG20210114T015142 RECID=17 STAMP=1061776369
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
output file name=/oradata/ORCLCDB/pdbseed/undotbs01.dbf tag=TAG20210114T015142 RECID=18 STAMP=1061776372
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCLCDB/users01.dbf
output file name=/oradata/ORCLCDB/users01.dbf tag=TAG20210114T015142 RECID=19 STAMP=1061776374
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/users01.dbf
output file name=/oradata/ORCLCDB/ORCLPDB/users01.dbf tag=TAG20210114T015142 RECID=20 STAMP=1061776375
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-JAN-21

Starting Control File and SPFILE Autobackup at 14-JAN-21
piece handle=/u01/app/oracle/fast_recovery_area/ORCLCDB/autobackup/2021_01_14/o1_mf_s_1061775991_hzztl8sw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-JAN-21

We use DB_FILE_NAME_CONVERT to change the location, but with the same file name.

RMAN> list copy of database;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time        Sparse
------- ---- - --------------- ---------- --------------- ------
9       1    A 14-JAN-21       2922895    14-JAN-21       NO
        Name: /oradata/ORCLCDB/system01.dbf
        Tag: TAG20210114T015142

10      3    A 14-JAN-21       2922895    14-JAN-21       NO
        Name: /oradata/ORCLCDB/sysaux01.dbf
        Tag: TAG20210114T015142

15      4    A 14-JAN-21       2922895    14-JAN-21       NO
        Name: /oradata/ORCLCDB/undotbs01.dbf
        Tag: TAG20210114T015142

14      5    A 14-JAN-21       2914979    16-DEC-20       NO
        Name: /oradata/ORCLCDB/pdbseed/system01.dbf
        Tag: TAG20210114T015142
        Container ID: 2, PDB Name: PDB$SEED

12      6    A 14-JAN-21       2914979    16-DEC-20       NO
        Name: /oradata/ORCLCDB/pdbseed/sysaux01.dbf
        Tag: TAG20210114T015142
        Container ID: 2, PDB Name: PDB$SEED

19      7    A 14-JAN-21       2922895    14-JAN-21       NO
        Name: /oradata/ORCLCDB/users01.dbf
        Tag: TAG20210114T015142

18      8    A 14-JAN-21       2914979    16-DEC-20       NO
        Name: /oradata/ORCLCDB/pdbseed/undotbs01.dbf
        Tag: TAG20210114T015142
        Container ID: 2, PDB Name: PDB$SEED

13      9    A 14-JAN-21       2918247    16-DEC-20       NO
        Name: /oradata/ORCLCDB/ORCLPDB/system01.dbf
        Tag: TAG20210114T015142
        Container ID: 3, PDB Name: ORCLPDB

11      10   A 14-JAN-21       2918247    16-DEC-20       NO
        Name: /oradata/ORCLCDB/ORCLPDB/sysaux01.dbf
        Tag: TAG20210114T015142
        Container ID: 3, PDB Name: ORCLPDB

17      11   A 14-JAN-21       2918247    16-DEC-20       NO
        Name: /oradata/ORCLCDB/ORCLPDB/undotbs01.dbf
        Tag: TAG20210114T015142
        Container ID: 3, PDB Name: ORCLPDB

20      12   A 14-JAN-21       2918247    16-DEC-20       NO
        Name: /oradata/ORCLCDB/ORCLPDB/users01.dbf
        Tag: TAG20210114T015142
        Container ID: 3, PDB Name: ORCLPDB

16      13   A 14-JAN-21       2918247    16-DEC-20       NO
        Name: /oradata/ORCLCDB/ORCLPDB/example01.dbf
        Tag: TAG20210114T015142
        Container ID: 3, PDB Name: ORCLPDB

Switch all data files to the new location.

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/oradata/ORCLCDB/system01.dbf"
datafile 3 switched to datafile copy "/oradata/ORCLCDB/sysaux01.dbf"
datafile 4 switched to datafile copy "/oradata/ORCLCDB/undotbs01.dbf"
datafile 5 switched to datafile copy "/oradata/ORCLCDB/pdbseed/system01.dbf"
datafile 6 switched to datafile copy "/oradata/ORCLCDB/pdbseed/sysaux01.dbf"
datafile 7 switched to datafile copy "/oradata/ORCLCDB/users01.dbf"
datafile 8 switched to datafile copy "/oradata/ORCLCDB/pdbseed/undotbs01.dbf"
datafile 9 switched to datafile copy "/oradata/ORCLCDB/ORCLPDB/system01.dbf"
datafile 10 switched to datafile copy "/oradata/ORCLCDB/ORCLPDB/sysaux01.dbf"
datafile 11 switched to datafile copy "/oradata/ORCLCDB/ORCLPDB/undotbs01.dbf"
datafile 12 switched to datafile copy "/oradata/ORCLCDB/ORCLPDB/users01.dbf"
datafile 13 switched to datafile copy "/oradata/ORCLCDB/ORCLPDB/example01.dbf"

Open database.

RMAN> alter database open;

Statement processed

Check current data files location again.

SQL> select name from v$datafile order by 1;

NAME
--------------------------------------------------------------------------------
/oradata/ORCLCDB/ORCLPDB/example01.dbf
/oradata/ORCLCDB/ORCLPDB/sysaux01.dbf
/oradata/ORCLCDB/ORCLPDB/system01.dbf
/oradata/ORCLCDB/ORCLPDB/undotbs01.dbf
/oradata/ORCLCDB/ORCLPDB/users01.dbf
/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/oradata/ORCLCDB/pdbseed/system01.dbf
/oradata/ORCLCDB/pdbseed/undotbs01.dbf
/oradata/ORCLCDB/sysaux01.dbf
/oradata/ORCLCDB/system01.dbf
/oradata/ORCLCDB/undotbs01.dbf
/oradata/ORCLCDB/users01.dbf

12 rows selected.

2. Move Tablespace Location

Changing the location of a tablespace is actually changing the locations of data files of that tablespace. In this case, we'd like to change the location of a whole tablespace of a PDB. First of all, let's see all data files in the tablespace.

SQL> alter session set container=ORCLPDB;

Session altered.

SQL> column file_name format a60;
SQL> select file_id, file_name from dba_data_files where tablespace_name = 'EXAMPLE';

   FILE_ID FILE_NAME
---------- ------------------------------------------------------------
        13 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf
        14 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example02.dbf
        15 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example03.dbf
        16 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example04.dbf

We take the tablespace offline.

SQL> alter tablespace example offline;

Tablespace altered.

We copy all data files in the tablespace to another place.

RMAN> backup as copy db_file_name_convert ('/u01/app/oracle/oradata/ORCLCDB/ORCLPDB','/u01/app/oracle/oradata/ORCLCDB/TESTPDB') tablespace orclpdb:example;

Starting backup at 28-JAN-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf
output file name=/u01/app/oracle/oradata/ORCLCDB/TESTPDB/example01.dbf tag=TAG20210128T034445 RECID=4 STAMP=1062992688
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example02.dbf
output file name=/u01/app/oracle/oradata/ORCLCDB/TESTPDB/example02.dbf tag=TAG20210128T034445 RECID=5 STAMP=1062992688
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example03.dbf
output file name=/u01/app/oracle/oradata/ORCLCDB/TESTPDB/example03.dbf tag=TAG20210128T034445 RECID=6 STAMP=1062992689
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example04.dbf
output file name=/u01/app/oracle/oradata/ORCLCDB/TESTPDB/example04.dbf tag=TAG20210128T034445 RECID=7 STAMP=1062992690
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-JAN-21

Starting Control File and SPFILE Autobackup at 28-JAN-21
piece handle=/u01/app/oracle/fast_recovery_area/ORCLCDB/autobackup/2021_01_28/o1_mf_s_1062992692_j14yd469_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-JAN-21

Beside prefixing the PDB name, there're other ways to backup tablespaces from PDB by RMAN.

Switch all data files to copy ones.

RMAN> switch tablespace orclpdb:example to copy;

datafile 13 switched to datafile copy "/u01/app/oracle/oradata/ORCLCDB/TESTPDB/example01.dbf"
datafile 14 switched to datafile copy "/u01/app/oracle/oradata/ORCLCDB/TESTPDB/example02.dbf"
datafile 15 switched to datafile copy "/u01/app/oracle/oradata/ORCLCDB/TESTPDB/example03.dbf"
datafile 16 switched to datafile copy "/u01/app/oracle/oradata/ORCLCDB/TESTPDB/example04.dbf"

Take the tablespace online.

SQL> alter tablespace example online;

Tablespace altered.

Check the current locations of all data file of the tablespace.

SQL> select file_id, file_name from dba_data_files where tablespace_name = 'EXAMPLE';

   FILE_ID FILE_NAME
---------- ------------------------------------------------------------
        13 /u01/app/oracle/oradata/ORCLCDB/TESTPDB/example01.dbf
        14 /u01/app/oracle/oradata/ORCLCDB/TESTPDB/example02.dbf
        15 /u01/app/oracle/oradata/ORCLCDB/TESTPDB/example03.dbf
        16 /u01/app/oracle/oradata/ORCLCDB/TESTPDB/example04.dbf

We have changed the location of all data files in the tablespace.

3. Move Some Data Files Location

Moving data files from a mount point with nearly full space to other mount points which could have more space to accommodate growing data files seems a common job for DBA. Here I simplify the scenario to a basic procedure that can be easy to follow.

  • Some data files will be moved to the new location.
  • Their tablespaces will remain unchanged.

Determine what datafiles are going to move

For example, we would like to move two data files below:

  • datafile_123.dbf
  • datafile_456.dbf

See what tablespaces will be affected

SQL> column datafile_name format a40;
SQL> column tablespace_name format a20;
SQL> select d.name datafile_name, t.name tablespace_name from v$datafile d inner join v$tablespace t on d.ts# = t.ts# where d.name in ('/oradata1/ORCL/datafile_123.dbf', '/oradata1/ORCL/datafile_456.dbf');

DATAFILE_NAME                            TABLESPACE_NAME
---------------------------------------- --------------------
/oradata1/ORCL/datafile_456.dbf          TBS_1
/oradata1/ORCL/datafile_123.dbf          TBS_4

Although the two data files are going to move to another place, their tablespace belonging to will remain unchanged in our case.

Take these tablespaces offline

SQL> alter tablespace tbs_1 offline;
SQL> alter tablespace tbs_4 offline;

We have to take their tablespaces offline first for further operations, but the result will affect online users who are using them. Therefore, you should notify online users before taking any actions.

Alternatively, you can take data files offline instead of tablespaces, if the database is in ARCHIVELOG mode. You don't have to know the tablespaces that the data files belong to.

SQL> alter database datafile '/oradata1/ORCL/datafile_123.dbf' offline;
SQL> alter database datafile '/oradata1/ORCL/datafile_456.dbf' offline;

Copy the data files to the new location on OS-level

[oracle@test ~]$ cp -ip /oradata1/ORCL/datafile_123.dbf /oradata2/ORCL/datafile_123.dbf
[oracle@test ~]$ cp -ip /oradata1/ORCL/datafile_456.dbf /oradata2/ORCL/datafile_456.dbf

For protecting the data files with same names in the new location from overwriting, we copy the data files with interactive mode "-i" for safety. Further more, we remain the meta data of source files.

Change the location of the data files to the new path on database-level

SQL> alter database rename file '/oradata1/ORCL/datafile_123.dbf' to '/oradata2/ORCL/datafile_123.dbf';
SQL> alter database rename file '/oradata1/ORCL/datafile_456.dbf' to '/oradata2/ORCL/datafile_456.dbf';

As you can see, the renaming process is actually an operation of changing the absolute path of data files.

Alternatively, you can do renaming on tablespace-level.

SQL> alter tablespace tbs_1 rename datafile '/oradata1/ORCL/datafile_123.dbf' to '/oradata2/ORCL/datafile_123.dbf';
SQL> alter tablespace tbs_4 rename datafile '/oradata1/ORCL/datafile_456.dbf' to '/oradata2/ORCL/datafile_456.dbf';

I still think you should always operate data files on database level, if there's nothing to do with the tablespace throughout your plan.

Bring the tablespaces online

SQL> alter tablespace tbs_1 online;
SQL> alter tablespace tbs_4 online;

Or do this:

SQL> recover datafile '/oradata2/ORCL/datafile_123.dbf';
SQL> recover datafile '/oradata2/ORCL/datafile_456.dbf';
SQL> alter database datafile '/oradata2/ORCL/datafile_123.dbf' online;
SQL> alter database datafile '/oradata2/ORCL/datafile_456.dbf' online;

As you can see, I recover the two data files before bringing them online. Don't forget that, this is only for ARCHIVELOG mode.

Now, data files are now ready for service.

Remove the unused data files

For reclaiming the disk space, you have to remove the source data files.

[oracle@test ~]$ rm -i /oradata1/ORCL/datafile_123.dbf
[oracle@test ~]$ rm -i /oradata1/ORCL/datafile_456.dbf

After that, you may check the disk space for sure.

B. RAC Database

There're 2 scenarios of changing data file location in a RAC database.

  1. Move all data files location of a RAC database
  2. Move Tablespace Location of a RAC database
  3. Move only 1 data file location of a RAC database

1. Move All Data Files Location

Make some necessary directories in the new locations.

[grid@primary01 ~]$ asmcmd mkdir +DATA/TESTCDB
[grid@primary01 ~]$ asmcmd mkdir +DATA/TESTCDB/ORCLPDB
[grid@primary01 ~]$ asmcmd mkdir +DATA/TESTCDB/pdbseed

Check current data files location.

SQL> set pagesize 100;
SQL> select name from v$datafile order by 1;

NAME
--------------------------------------------------------------------------------
+DATA/ORCLCDB/ORCLPDB/sysaux01.dbf
+DATA/ORCLCDB/ORCLPDB/system01.dbf
+DATA/ORCLCDB/ORCLPDB/system01_i2_undo.dbf
+DATA/ORCLCDB/ORCLPDB/undotbs01.dbf
+DATA/ORCLCDB/ORCLPDB/users01.dbf
+DATA/ORCLCDB/erptbs_1.dbf
+DATA/ORCLCDB/erptbs_2.dbf
+DATA/ORCLCDB/pdbseed/sysaux01.dbf
+DATA/ORCLCDB/pdbseed/system01.dbf
+DATA/ORCLCDB/pdbseed/undotbs01.dbf
+DATA/ORCLCDB/sysaux01.dbf
+DATA/ORCLCDB/system01.dbf
+DATA/ORCLCDB/undotbs01.dbf
+DATA/ORCLCDB/undotbs02.dbf
+DATA/ORCLCDB/users01.dbf

15 rows selected.

Restart the database to MOUNT.

[oracle@primary01 ~]$ srvctl stop database -d testcdb
[oracle@primary01 ~]$ srvctl start database -d testcdb -o mount

Copy all of data files to the new location.

For speeding up the copying, we can raise the degree of parallelism of RMAN channels. This is an optional step.

RMAN> configure device type disk parallelism 4;

Then we take a copy of all data files by RMAN.

RMAN> backup as copy db_file_name_convert ('+DATA/ORCLCDB','+DATA/TESTCDB') database;

Starting backup at 20-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 instance=TESTCDB1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=63 instance=TESTCDB1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=58 instance=TESTCDB1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=51 instance=TESTCDB1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/ORCLCDB/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=+DATA/ORCLCDB/sysaux01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00011 name=+DATA/ORCLCDB/ORCLPDB/sysaux01.dbf
channel ORA_DISK_4: starting datafile copy
input datafile file number=00006 name=+DATA/ORCLCDB/pdbseed/sysaux01.dbf
output file name=+DATA/TESTCDB/orclpdb/sysaux01.dbf tag=TAG20210120T131015 RECID                                                            =4 STAMP=1062335752
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:05:41
channel ORA_DISK_3: starting datafile copy
input datafile file number=00004 name=+DATA/ORCLCDB/undotbs01.dbf
output file name=+DATA/TESTCDB/pdbseed/sysaux01.dbf tag=TAG20210120T131015 RECID                                                            =5 STAMP=1062335802
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:06:21
channel ORA_DISK_4: starting datafile copy
input datafile file number=00005 name=+DATA/ORCLCDB/pdbseed/system01.dbf
output file name=+DATA/TESTCDB/pdbseed/system01.dbf tag=TAG20210120T131015 RECID=6 STAMP=1062336153
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:05:56
channel ORA_DISK_4: starting datafile copy
input datafile file number=00010 name=+DATA/ORCLCDB/ORCLPDB/system01.dbf
output file name=+DATA/TESTCDB/sysaux01.dbf tag=TAG20210120T131015 RECID=8 STAMP=1062336248
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:13:59
channel ORA_DISK_2: starting datafile copy
input datafile file number=00008 name=+DATA/ORCLCDB/pdbseed/undotbs01.dbf
output file name=+DATA/TESTCDB/undotbs01.dbf tag=TAG20210120T131015 RECID=7 STAMP=1062336246
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:08:07
channel ORA_DISK_3: starting datafile copy
input datafile file number=00012 name=+DATA/ORCLCDB/ORCLPDB/undotbs01.dbf
output file name=+DATA/TESTCDB/pdbseed/undotbs01.dbf tag=TAG20210120T131015 RECID=9 STAMP=1062336406
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:02:33
channel ORA_DISK_2: starting datafile copy
input datafile file number=00013 name=+DATA/ORCLCDB/ORCLPDB/system01_i2_undo.dbf
output file name=+DATA/TESTCDB/orclpdb/undotbs01.dbf tag=TAG20210120T131015 RECID=10 STAMP=1062336422
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:02:39
channel ORA_DISK_3: starting datafile copy
input datafile file number=00009 name=+DATA/ORCLCDB/undotbs02.dbf
output file name=+DATA/TESTCDB/orclpdb/system01_i2_undo.dbf tag=TAG20210120T131015 RECID=13 STAMP=1062336493
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:20
channel ORA_DISK_2: starting datafile copy
input datafile file number=00015 name=+DATA/ORCLCDB/erptbs_1.dbf
output file name=+DATA/TESTCDB/undotbs02.dbf tag=TAG20210120T131015 RECID=11 STAMP=1062336489
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:01:07
channel ORA_DISK_3: starting datafile copy
input datafile file number=00016 name=+DATA/ORCLCDB/erptbs_2.dbf
output file name=+DATA/TESTCDB/orclpdb/system01.dbf tag=TAG20210120T131015 RECID=12 STAMP=1062336493
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:05:28
channel ORA_DISK_4: starting datafile copy
input datafile file number=00007 name=+DATA/ORCLCDB/users01.dbf
output file name=+DATA/TESTCDB/erptbs_1.dbf tag=TAG20210120T131015 RECID=14 STAMP=1062336502
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:09
channel ORA_DISK_2: starting datafile copy
input datafile file number=00014 name=+DATA/ORCLCDB/ORCLPDB/users01.dbf
output file name=+DATA/TESTCDB/erptbs_2.dbf tag=TAG20210120T131015 RECID=15 STAMP=1062336502
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:09
output file name=+DATA/TESTCDB/users01.dbf tag=TAG20210120T131015 RECID=16 STAMP=1062336505
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:08
output file name=+DATA/TESTCDB/orclpdb/users01.dbf tag=TAG20210120T131015 RECID=17 STAMP=1062336511
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:07
output file name=+DATA/TESTCDB/system01.dbf tag=TAG20210120T131015 RECID=18 STAMP=1062336552
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:18:56
Finished backup at 20-JAN-21

Starting Control File and SPFILE Autobackup at 20-JAN-21
piece handle=+DATA/TESTCDB/AUTOBACKUP/2021_01_20/s_1062334847.365.1062336555 comment=NONE
Finished Control File and SPFILE Autobackup at 20-JAN-21

We use DB_FILE_NAME_CONVERT to change the location, but with the same file name.

RMAN> list copy of database;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time        Sparse
------- ---- - --------------- ---------- --------------- ------
18      1    A 20-JAN-21       3603337    20-JAN-21       NO
        Name: +DATA/TESTCDB/system01.dbf
        Tag: TAG20210120T131015

8       3    A 20-JAN-21       3603337    20-JAN-21       NO
        Name: +DATA/TESTCDB/sysaux01.dbf
        Tag: TAG20210120T131015

7       4    A 20-JAN-21       3603337    20-JAN-21       NO
        Name: +DATA/TESTCDB/undotbs01.dbf
        Tag: TAG20210120T131015

6       5    A 20-JAN-21       2161167    14-OCT-20       NO
        Name: +DATA/TESTCDB/pdbseed/system01.dbf
        Tag: TAG20210120T131015
        Container ID: 2, PDB Name: PDB$SEED

5       6    A 20-JAN-21       2161167    14-OCT-20       NO
        Name: +DATA/TESTCDB/pdbseed/sysaux01.dbf
        Tag: TAG20210120T131015
        Container ID: 2, PDB Name: PDB$SEED

16      7    A 20-JAN-21       3603337    20-JAN-21       NO
        Name: +DATA/TESTCDB/users01.dbf
        Tag: TAG20210120T131015

9       8    A 20-JAN-21       2161167    14-OCT-20       NO
        Name: +DATA/TESTCDB/pdbseed/undotbs01.dbf
        Tag: TAG20210120T131015
        Container ID: 2, PDB Name: PDB$SEED

11      9    A 20-JAN-21       3603337    20-JAN-21       NO
        Name: +DATA/TESTCDB/undotbs02.dbf
        Tag: TAG20210120T131015

12      10   A 20-JAN-21       3603205    20-JAN-21       NO
        Name: +DATA/TESTCDB/orclpdb/system01.dbf
        Tag: TAG20210120T131015
        Container ID: 3, PDB Name: ORCLPDB

4       11   A 20-JAN-21       3603205    20-JAN-21       NO
        Name: +DATA/TESTCDB/orclpdb/sysaux01.dbf
        Tag: TAG20210120T131015
        Container ID: 3, PDB Name: ORCLPDB

10      12   A 20-JAN-21       3603205    20-JAN-21       NO
        Name: +DATA/TESTCDB/orclpdb/undotbs01.dbf
        Tag: TAG20210120T131015
        Container ID: 3, PDB Name: ORCLPDB

13      13   A 20-JAN-21       3603205    20-JAN-21       NO
        Name: +DATA/TESTCDB/orclpdb/system01_i2_undo.dbf
        Tag: TAG20210120T131015
        Container ID: 3, PDB Name: ORCLPDB

17      14   A 20-JAN-21       3603205    20-JAN-21       NO
        Name: +DATA/TESTCDB/orclpdb/users01.dbf
        Tag: TAG20210120T131015
        Container ID: 3, PDB Name: ORCLPDB

14      15   A 20-JAN-21       3603337    20-JAN-21       NO
        Name: +DATA/TESTCDB/erptbs_1.dbf
        Tag: TAG20210120T131015

15      16   A 20-JAN-21       3603337    20-JAN-21       NO
        Name: +DATA/TESTCDB/erptbs_2.dbf
        Tag: TAG20210120T131015

Switch all data files to the new location.

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/TESTCDB/system01.dbf"
datafile 3 switched to datafile copy "+DATA/TESTCDB/sysaux01.dbf"
datafile 4 switched to datafile copy "+DATA/TESTCDB/undotbs01.dbf"
datafile 5 switched to datafile copy "+DATA/TESTCDB/pdbseed/system01.dbf"
datafile 6 switched to datafile copy "+DATA/TESTCDB/pdbseed/sysaux01.dbf"
datafile 7 switched to datafile copy "+DATA/TESTCDB/users01.dbf"
datafile 8 switched to datafile copy "+DATA/TESTCDB/pdbseed/undotbs01.dbf"
datafile 9 switched to datafile copy "+DATA/TESTCDB/undotbs02.dbf"
datafile 10 switched to datafile copy "+DATA/TESTCDB/orclpdb/system01.dbf"
datafile 11 switched to datafile copy "+DATA/TESTCDB/orclpdb/sysaux01.dbf"
datafile 12 switched to datafile copy "+DATA/TESTCDB/orclpdb/undotbs01.dbf"
datafile 13 switched to datafile copy "+DATA/TESTCDB/orclpdb/system01_i2_undo.dbf"
datafile 14 switched to datafile copy "+DATA/TESTCDB/orclpdb/users01.dbf"
datafile 15 switched to datafile copy "+DATA/TESTCDB/erptbs_1.dbf"
datafile 16 switched to datafile copy "+DATA/TESTCDB/erptbs_2.dbf"

Open database.

Node 1
RMAN> alter database open;

Statement processed
Node 2
RMAN> alter database open;

Statement processed

Check current data files location again.

SQL> select name from v$datafile order by 1;

NAME
--------------------------------------------------------------------------------
+DATA/TESTCDB/erptbs_1.dbf
+DATA/TESTCDB/erptbs_2.dbf
+DATA/TESTCDB/orclpdb/sysaux01.dbf
+DATA/TESTCDB/orclpdb/system01.dbf
+DATA/TESTCDB/orclpdb/system01_i2_undo.dbf
+DATA/TESTCDB/orclpdb/undotbs01.dbf
+DATA/TESTCDB/orclpdb/users01.dbf
+DATA/TESTCDB/pdbseed/sysaux01.dbf
+DATA/TESTCDB/pdbseed/system01.dbf
+DATA/TESTCDB/pdbseed/undotbs01.dbf
+DATA/TESTCDB/sysaux01.dbf
+DATA/TESTCDB/system01.dbf
+DATA/TESTCDB/undotbs01.dbf
+DATA/TESTCDB/undotbs02.dbf
+DATA/TESTCDB/users01.dbf

15 rows selected.

As we can see, all data files are switched to the new destination.

2. Move Tablespace Location

Changing the location of a tablespace in a RAC database is nothing different from changing the location of a tablespace in a single-instance database.

Switch container to the PDB

SQL> alter session set container=ORCLPDB;

Session altered.

Check data files in the tablespace

SQL> column file_name format a60;
SQL> select file_id, file_name from dba_data_files where tablespace_name = 'EXAMPLE';

   FILE_ID FILE_NAME
---------- ------------------------------------------------------------
        15 +DATA/ORCLCDB/ORCLPDB/example01.dbf
        16 +DATA/ORCLCDB/ORCLPDB/example02.dbf
        17 +DATA/ORCLCDB/ORCLPDB/example03.dbf
        18 +DATA/ORCLCDB/ORCLPDB/example04.dbf

Take the tablespace offline

SQL> alter tablespace example offline;

Tablespace altered.

Please don't exit SQL*Plus session, we still need the session for later operations.

Copy data files to the new location by RMAN

[oracle@primary01 ~]$ rman target /
...
RMAN> backup as copy db_file_name_convert ('+DATA/ORCLCDB/ORCLPDB','+DATA/TESTCDB/ORCLPDB') tablespace orclpdb:example;

Starting backup at 01-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=+DATA/ORCLCDB/ORCLPDB/example01.dbf
output file name=+DATA/TESTCDB/ORCLPDB/example01.dbf tag=TAG20210201T143953 RECID=4 STAMP=1063377638
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=+DATA/ORCLCDB/ORCLPDB/example02.dbf
output file name=+DATA/TESTCDB/ORCLPDB/example02.dbf tag=TAG20210201T143953 RECID=5 STAMP=1063377642
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=+DATA/ORCLCDB/ORCLPDB/example03.dbf
output file name=+DATA/TESTCDB/ORCLPDB/example03.dbf tag=TAG20210201T143953 RECID=6 STAMP=1063377644
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=+DATA/ORCLCDB/ORCLPDB/example04.dbf
output file name=+DATA/TESTCDB/ORCLPDB/example04.dbf tag=TAG20210201T143953 RECID=7 STAMP=1063377645
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 01-FEB-21

Starting Control File and SPFILE Autobackup at 01-FEB-21
piece handle=+DATA/ORCLCDB/AUTOBACKUP/2021_02_01/s_1063377648.326.1063377649 comment=NONE
Finished Control File and SPFILE Autobackup at 01-FEB-21

We used DB_FILE_NAME_CONVERT to replace the directory with a new one but keep the filename.

List the copies of the tablespace

RMAN> list copy of tablespace orclpdb:example;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time        Sparse
------- ---- - --------------- ---------- --------------- ------
4       15   A 01-FEB-21       3470721    01-FEB-21       NO
        Name: +DATA/TESTCDB/ORCLPDB/example01.dbf
        Tag: TAG20210201T143953

5       16   A 01-FEB-21       3470721    01-FEB-21       NO
        Name: +DATA/TESTCDB/ORCLPDB/example02.dbf
        Tag: TAG20210201T143953

6       17   A 01-FEB-21       3470721    01-FEB-21       NO
        Name: +DATA/TESTCDB/ORCLPDB/example03.dbf
        Tag: TAG20210201T143953

7       18   A 01-FEB-21       3470721    01-FEB-21       NO
        Name: +DATA/TESTCDB/ORCLPDB/example04.dbf
        Tag: TAG20210201T143953

Switch current data files to copies

RMAN> switch tablespace orclpdb:example to copy;

datafile 15 switched to datafile copy "+DATA/TESTCDB/ORCLPDB/example01.dbf"
datafile 16 switched to datafile copy "+DATA/TESTCDB/ORCLPDB/example02.dbf"
datafile 17 switched to datafile copy "+DATA/TESTCDB/ORCLPDB/example03.dbf"
datafile 18 switched to datafile copy "+DATA/TESTCDB/ORCLPDB/example04.dbf"

Take the tablespace online

SQL> alter tablespace example online;

Tablespace altered.

Check data files in tablespace

SQL> select file_id, file_name from dba_data_files where tablespace_name = 'EXAMPLE';

   FILE_ID FILE_NAME
---------- ------------------------------------------------------------
        15 +DATA/TESTCDB/ORCLPDB/example01.dbf
        16 +DATA/TESTCDB/ORCLPDB/example02.dbf
        17 +DATA/TESTCDB/ORCLPDB/example03.dbf
        18 +DATA/TESTCDB/ORCLPDB/example04.dbf

3. Move Only 1 Data File Location

To move only one or some data files, it depends on what release we are using.

For 12c and Later Releases

Since 12c, we can move data files online simply by SQL statement.

Get into the correct container.

SQL> alter session set container=ERPAPP3;

Session altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ERPAPP3                        READ WRITE NO
SQL> show con_name;

CON_NAME
------------------------------
ERPAPP3

Then we move it by ALTER DATABASE MOVE DATAFILE statement.

SQL> alter database move datafile '+DATA/ERPCDB3/ERPAPP3EXT/CIDATA_6_14.dbf' to '+DATA/ERPCDB3/ERPAPP3/FIDATA_6_14.dbf' reuse;

Database altered.

Not only location, but we can also move the data file from one disk group to another by this way.

For 11g and Earlier Releases

As usual, we use RMAN to change the location of a data file.

Check the file number.

SQL> select file# from v$datafile where name = '+DATA/ORCLCDB/ORCLPDB/example02.dbf';

     FILE#
----------
        18

Copy the file online.

RMAN> backup as copy datafile 18 format '+DATA/TESTCDB/ORCLPDB/example02.dbf';

Starting backup at 27-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=120 instance=TESTCDB1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=+DATA/ORCLCDB/ORCLPDB/example02.dbf
output file name=+DATA/TESTCDB/ORCLPDB/example02.dbf tag=TAG20210127T162803 RECID=6 STAMP=1062952085
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 27-JAN-21

Starting Control File and SPFILE Autobackup at 27-JAN-21
piece handle=+DATA/TESTCDB/AUTOBACKUP/2021_01_27/s_1062952087.364.1062952089 comment=NONE
Finished Control File and SPFILE Autobackup at 27-JAN-21

List the copy of the datafile.

RMAN> list copy of datafile 18;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time        Sparse
------- ---- - --------------- ---------- --------------- ------
6       18   A 27-JAN-21       3616028    27-JAN-21       NO
        Name: +DATA/TESTCDB/ORCLPDB/example02.dbf
        Tag: TAG20210127T162803

Take the original file offline.

SQL> alter database datafile 18 offline;

Database altered.

Switch to new location.

RMAN> switch datafile 18 to copy;
datafile 18 switched to datafile copy "+DATA/TESTCDB/ORCLPDB/example02.dbf"

Recover the new file.

SQL> recover datafile 18;
Media recovery complete.

Take the new file online.

SQL> alter database datafile 18 online;

Database altered.

Basically, if you'd like to move only 1 data file, the steps are the same, no matter what type of database you're using.

Leave a Reply

Your email address will not be published. Required fields are marked *