Oracle 23ai — Transport Tablespace over Network

In Oracle version 23ai, the Transport Tablespace feature can now be implemented over the network. To achieve this, you must use the RMAN tool, which transfers the datafiles of the desired tablespace from the source database to the destination database.

This process is done by executing the RESTORE FOREIGN TABLESPACE command in the destination database. With this command, even the metadata of the tablespace is transferred to the destination via a generated dump file and is automatically restored in the target database.

Executing the RESTORE command requires some preparations, which are explained below.

We intend to transfer a tablespace named TBS1 from APDB to BPDB:

Source: APDB
Destination: BPDB

Step 1: Set the tablespace to READ ONLY

--source
SQL> alter session set container=APDB;
Session altered.
SQL> ALTER TABLESPACE tbs1 READ ONLY;
Tablespace altered.

Step 2: Create a user with the same name and password on both source and destination

-- source + destination
SQL> create user c##vahid identified by a;
User created.

Grant the following privileges to this user (on both databases):

SQL> grant sysbackup to c##vahid container=all;
Grant succeeded.

SQL> grant EXP_FULL_DATABASE to c##vahid container=all;
Grant succeeded.

You can now connect to the source database in RMAN using this user:

RMAN> connect target "c##vahid/a@APDB AS SYSDBA"
connect target *
connected to target database: FREE:APDB (DBID=1145898896)
RMAN>

Step 3: Create net service names on both sides

APDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.42)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apdb)
    )
  )

BPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BPDB)
    )
  )

Step 4: Create a PUBLIC DATABASE LINK in the destination database to export metadata

--source
SQL> alter session set container=apdb;
Session altered.
SQL> create user vahid identified by a;
User created.
SQL> grant dba to vahid;
Grant succeeded.

--destination
SQL> alter session set container=bpdb;
Session altered.
SQL> SQL> CREATE PUBLIC DATABASE LINK linkMetDataDump CONNECT TO vahid IDENTIFIED BY a USING 'APDB';
Database link created.

Step 5: Run the RESTORE command in the destination database

FOREIGN TABLESPACE: Name of the tablespace to be transferred
FROM SERVICE: Service name of the source database
PLUGIN DBLINK: Name of the database link created in step 4
PLUGIN FILE: Path to store the exported metadata dump

RMAN> connect target "c##vahid/a@BPDB AS SYSBACKUP"
connect target *
connected to target database: FREE:BPDB (DBID=4273248419)

RMAN> RESTORE
FOREIGN TABLESPACE tbs1 
FORMAT 'tbs1%f.dbf'
FROM SERVICE 'APDB'
PLUGIN FILE '/dump/tbs1_meta.dmp'
PLUGIN DBLINK 'linkMetDataDump';

Starting restore at 13-FEB-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=283 device type=DISK
Performing export of metadata for specified tablespaces...
   EXPDP> Starting "SYSBACKUP"."NET_EXP_?d_fjod":
   EXPDP> Master table "SYSBACKUP"."NET_EXP_?d_fjod" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYSBACKUP.NET_EXP_?d_fjod is:
   EXPDP>   /dump/tbs1_meta.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TBS1:
   EXPDP>   /opt/oracle/oradata/FREE/1147EA9E8D360F5CE0630100007F17F4/datafile/o1_mf_tbs1_lwq                              bmrl1_.dbf
   EXPDP>   /opt/oracle/oradata/FREE/1147EA9E8D360F5CE0630100007F17F4/datafile/o1_mf_tbs1_lwq                              bn2x5_.dbf
   EXPDP>   /opt/oracle/oradata/FREE/1147EA9E8D360F5CE0630100007F17F4/datafile/o1_mf_tbs1_lwq                              bn8fm_.dbf
   EXPDP> Job "SYSBACKUP"."NET_EXP_?d_fjod" successfully completed at Tue Feb 13 19:15:41 2024 elapsed 0 00:00:17
Export completed
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service APDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring foreign file 26 to /opt/oracle/product/23c/dbhomeFree/dbs/tbs126.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service APDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring foreign file 27 to /opt/oracle/product/23c/dbhomeFree/dbs/tbs127.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service APDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring foreign file 28 to /opt/oracle/product/23c/dbhomeFree/dbs/tbs128.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Performing import of metadata...
   IMPDP> Master table "SYSBACKUP"."TSPITR_IMP_FREE_DydF" successfully loaded/unloaded
   IMPDP> Starting "SYSBACKUP"."TSPITR_IMP_FREE_DydF":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYSBACKUP"."TSPITR_IMP_FREE_DydF" successfully completed at Tue Feb 13 19:15:57 2024 elapsed 0 00:00:05
Import completed
Finished restore at 13-FEB-24

After this, a new tablespace named TBS1 will be created in the BPDB database:

SQL> alter session set container=bpdb;
Session altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where TABLESPACE_NAME='TBS1';
TABLESPACE_NAME                STATUS
------------------------------ ---------
TBS1                           READ ONLY

Finally, set the tablespace to READ WRITE mode on both databases:

SQL> alter tablespace TBS1 READ WRITE;
Tablespace altered.