Move Tablespace by RMAN
No like we move individual data file to another place by RMAN, changing the location of a tablespace by RMAN is actually moving all data files in that tablespace in one step. In this case, we'd like to change the location of a whole tablespace of a pluggable database (PDB).
1. Switch Container to PDB
SQL> alter session set container=ORCLPDB;
Session altered.
2. 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
---------- ------------------------------------------------------------
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
3. Alter Tablespace Offline
We take the tablespace offline.
SQL> alter tablespace example offline;
Tablespace altered.
4. Backup as Copy DB_FILE_NAME_CONVERT Tablespace
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.
5. Switch Tablespace To Copy
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"
6. Alter Tablespace Online
SQL> alter tablespace example online;
Tablespace altered.
7. Check Data Files in 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.
RAC Database
For RAC database, it's nothing different from changing the location of a tablespace in a single-instance database.
1. Switch Container to PDB
SQL> alter session set container=ORCLPDB;
Session altered.
2. 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
3. Alter Tablespace Offline
SQL> alter tablespace example offline;
Tablespace altered.
Please don't exit SQL*Plus session, we still need the session for later operations.
4. Backup as Copy DB_FILE_NAME_CONVERT Tablespace
[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.
5. List Copy of 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
6. Switch Tablespace To Copy
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"
7. Alter Tablespace Online
SQL> alter tablespace example online;
Tablespace altered.
8. 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