Transportable Tablespace between two pluggable database(PDB’s).
This article discusses simple example of performing TTS (Transportable Tablespace) across two pluggable databases in a same container.
Transportable tablespaces(T-Ts) is used to move the data from one database to another database in NON CDB environment,where as in container database we can move the data across pluggable databases using T-Ts.
Moving data using Transportable tablespace is much faster than performing either an export/import or unload/load of the same data.
Database Type : Container database
Database name : microcdb
Pluggable databases : plug2 & plug4.
Connect to Container database and check required information.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[oracle@dba20 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 6 21:51:52 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS>>select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role||'-'||CDB from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE||'-'||CDB ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- MICROCDB - microcdb@dba20.ord.com - microcdb - 12.1.0.2.0 - READ WRITE - 07-JUL-2018 20:26:32 - PRIMARY-YES SYS>>sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ---------- ------------- ---------- 2 PDB$SEED READ ONLY NO 3 PLUG4 READ WRITE NO 4 PLUG2 READ WRITE NO 5 PLUG5 READ WRITE NO |
Connect to pluggable database -PLUG2
|
1 2 3 4 5 6 |
SYS>>alter session set container=plug2; Session altered. SYS>>sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ------- --------- ---------- ---------- 4 PLUG2 READ WRITE NO |
Creating a tablespace .
|
1 2 |
SYS>>create tablespace trans_ts datafile '/u01/oradata/microcdb/plug2/trans_ts.dbf' size 10m; Tablespace created. |
Creating a user with required privileges in PLUG2 pluggable database and assign user to a particular tablespace.
|
1 2 3 4 |
SYS>>grant dba to trans_us identified by trans_us; Grant succeeded. SYS>>alter user trans_us default tablespace trans_ts; User altered. |
Connect to user and create a object.
|
1 2 3 4 |
SYS>>conn trans_us/trans_us@to_plug2 connected. TRANS_US>>create table t1(sno number); Table created. |
Perform some transaction under T1 object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
TRANS_US>>insert into t1 values(&sno); Enter value for sno: 10 old 1: insert into t1 values(&sno) new 1: insert into t1 values(10) 1 row created. TRANS_US>>/ Enter value for sno: 20 old 1: insert into t1 values(&sno) new 1: insert into t1 values(20) 1 row created. TRANS_US>>/ Enter value for sno: 30 old 1: insert into t1 values(&sno) new 1: insert into t1 values(30) 1 row created. TRANS_US>>/ Enter value for sno: 40 old 1: insert into t1 values(&sno) new 1: insert into t1 values(40) 1 row created. TRANS_US>>/ Enter value for sno: 50 old 1: insert into t1 values(&sno) new 1: insert into t1 values(50) 1 row created. TRANS_US>>commit; Commit complete. |
Check T1 segment is created under TRANS_TS tablespace or not.
|
1 2 3 4 |
TRANS_US>>select table_name,tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME ---------- ------------------------------ T1 TRANS_TS |
Connect to SYS user in PLUG2 pluggable database and verify the self-contained status of the tablespace.
|
1 2 3 4 5 |
TRANS_US>>conn sys/sys@to_plug2 as sysdba SYS>>execute dbms_tts.transport_set_check('trans_ts'); PL/SQL procedure successfully completed. SYS> select * from sys.transport_set_violations; no rows selected. |
We need to keep tablespace read-only while performing Transportable tablespace.
|
1 2 |
SYS>>alter tablespace trans_ts read only; Tablespace altered. |
Check the requirements to take backup of objects using datapump utility by using below link.
Export the metadata of the objects using Data Pump Utility by using a parameter called transport_tablespace.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[oracle@dba20 dbs]$ expdp directory=dir_plug2 dumpfile=trans_ts.dmp nologfile transport_tablespace=y tablespaces=trans_ts job_name=trans_ts1 Export: Release 12.1.0.2.0 - Production on Fri Jul 6 22:07:21 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: sys/sys@to_plug2 as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, Replaced with: "transport_tablespaces=trans_ts" Legacy Mode has set reuse_dumpfiles=true parameter. Starting "SYS"."TRANS_TS1": sys/********@to_plug2 AS SYSDBA directory=dir_plug2 dumpfile=trans_ts.dmp nologfile tablespaces=trans_ts job_name=trans_ts1 reuse_dumpfiles=y Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYS"."TRANS_TS1" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.TRANS_TS1 is: /u01/oradata/microcdb/plug2/dir_plug2/trans_ts.dmp ****************************************************************************** Datafiles required for transportable tablespace TRANS_TS: /u01/oradata/microcdb/plug2/trans_ts.dbf Job "SYS"."TRANS_TS1" successfully completed at Fri Jul 6 22:09:19 2018 elapsed 0 00:01:36 |
To perform an import operation into plug 4 initially we need to copy dumpfile and physical datafile to plug4 location.
|
1 2 3 4 |
[oracle@dba20 ~]$ cd /u01/oradata/microcdb/plug2 [oracle@dba20 plug2 ]$ cp trans_ts.dbf /u01/oradata/microcdb/plug4 [oracle@dba20 plug2 ]$ cd dir_plug2 [oracle@dba20 dir_plug2 ]$ cp trans_ts.dmp /u01/oradata/microcdb/plug4/dir_plug4 |
Connect to plug pluggable database and create required users.
|
1 2 3 4 5 6 7 8 9 10 11 |
[oracle@dba20 ~]$ sqlplus sys/sys@to_plug4 as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 6 22:18:02 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS>>grant dba to trans_us identified by trans_us; Grant succeeded. SYS>>exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options |
Now import the dumpfile into plug4 pluggable database using datafiles parameter.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
oracle@dba20 ~]$ impdp directory=dir_plug4 dumpfile=trans_ts.dmp job_name=imp_trans_ts transport_tablespace=y datafiles='/u01/oradata/microcdb/plug4/trans_ts.dbf' Import: Release 12.1.0.2.0 - Production on Fri Jul 6 22:18:29 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: sys/sys@to_plug4 as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "datafiles=/u01/oradata/microcdb/plug4/trans_ts.dbf" Location: Command Line, Replaced with: "transport_datafiles=/u01/oradata/microcdb/plug4/trans_ts.dbf" Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, ignored. Master table "SYS"."IMP_TRANS_TS" successfully loaded/unloaded Starting "SYS"."IMP_TRANS_TS": sys/********@to_plug4 AS SYSDBA directory=dir_plug4 dumpfile=trans_ts.dmp job_name=imp_trans_ts transport_datafiles=/u01/oradata/microcdb/plug4/trans_ts.dbf Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYS"."IMP_TRANS_TS" successfully completed at Fri Jul 6 22:20:27 2018 elapsed 0 00:01:33 |
Then connect to plug4 pluggable database and check the data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[oracle@dba20 ~]$ sqlplus sys/sys@to_plug4 as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 6 22:20:45 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS>>select * from trans_us.t1; SNO ---------- 10 20 30 40 50 |
Note :
Whenever we exported the tablespace across pluggable database or noncdb environment it should be in read only mode.
Manually we need to convert it into Read write mode to perform the transactions.
Thank you……
Note: Please test scripts in Non Prod before trying in Production.
Loading...
Ajay Kumar
If you are interested to know more details about future session please join below telegram group :
https://t.me/joinchat/JFVAtAv1TE9DGHLbJ6rZbw