Skip to content

Switch Tablespace To Copy

  • by

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

Leave a Reply

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