• Home
  • About
  • Log in
  • Register
Follow Us
KTEXPERTS.COM
  Upcoming Sessions  
  • Oracle
  • Oracle Apps
  • SQL Server
  • Cassandra
  • Hadoop
  • Teradata
  • DevOps
  • MySQL
  • UNIX
  • Index
    • Public_Articles
    • Private_Articles
mm

Ajay Kumar

This is Ajay Kumar having around 3+ years of experience as Oracle DBA having exposure to SQL,PL*SQL, RAC,Dataguard, RMAN,GoldenGate,MySQL DBA and Amazon Web Services. Share your knowledge. It’s a way to achieve immortality.

Follow

Share this post

Tags

  • T-ts between two pluggable databases
  • transportable tablespace in container db

Transportable Tablespace between two pluggable database(PDB’s).

Posted on July 10, 2018 in 12c New Features, Oracle Backups, Oracle DBA, ORACLE EXPORTS, Oracle Metadata
Post Views: 668

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.

DATAPUMP IN ORACLE 12c

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.
1 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 5.00 out of 5)
Loading...

You might also like...

  • RENAMING INSTANCE

  • Renaming the Oracle Database.

  • Controlfile Multiplexing and De-Multiplexing In Oracle12c

    Controlfile Multiplexing and De-Multiplexing In Oracle12c

Previous Post: Performance Tuning in Oracle – Part2
Next Post: Performance Tuning in Oracle – Part3

One thought on “Transportable Tablespace between two pluggable database(PDB’s).”

  1. mm

    Ajay Kumar

    July 10, 2018 at 7:22 am

    Reply

    If you are interested to know more details about future session please join below telegram group :
    https://t.me/joinchat/JFVAtAv1TE9DGHLbJ6rZbw

Add Comment Cancel reply

Telegram Group

Please join our Telegram group for future session .

Click here to join Telegram .

Video

Advertisement

SUBSCRIBE NOW


Get Tech articles and other opportunities all into your inbox.

  • Popular
  • Latest
  • Tags
  • DevOps : Maven introduction

    DevOps : Maven introduction

    September 18, 2017
  • Hadoop : What is BIG DATA?

    Hadoop : What is BIG DATA?

    September 25, 2017
  • Oracle : REDOLOG FILE MANAGEMENT

    Oracle : REDOLOG FILE MANAGEMENT

    October 16, 2017
  • Oracle_Apps: Oracle E-Business Suite Architecture Overview for Apps DBA’s

    November 17, 2016
  • Oracle : DATA MASKING (12c)

    Oracle : DATA MASKING (12c)

    September 19, 2017
  • RENAMING INSTANCE

    February 15, 2019
  • Renaming the Oracle Database.

    February 15, 2019
  • Controlfile Multiplexing and De-Multiplexing In Oracle12c

    Controlfile Multiplexing and De-Multiplexing In Oracle12c

    February 15, 2019
  • Different Block Size of ORACLE

    February 13, 2019
  • Storage Management in Oracle -3

    Storage Management in Oracle -3

    February 12, 2019

Tags

12c top n queries about MySQL Adding ASM Disks bigdata big data cassandra cassandra key components cassandra key structures clonning user in oracle DATA DISTRIBUTION AND PRIMARY INDEX Devops Maven Introduction dmts featured Gossip in Cassandra hadoop hadoop what is big data How To Kill runaway processes After Terminating Concurrent Request information_schema InnoDB Invisible columns in oracle lmts Multitenant Database MyISAM MySQL NoSQL oracle 12c masking oracle database link Oracle Database Parameter Oracle Data Guard Oracle Dataguard oracle dba oracle history commands oracle performance tuning Oracle Server architecture shell scripting for DBA SQL caching Steps before patching GI and RDBMS home storage engines in mYSql Teradata Architecture TERADATA BASIC TERMINOLOGIES AND SPACE ALLOCATION teradata TYPES OF PRIMARY INDEXES Transportable tablespace undo UNIX vprocs and the number of AMPs

Categories

  • Big Data Hadoop (4)
  • Cassandra (12)
    • Cassandra Architecture (6)
    • NoSQL (5)
  • DevOps (3)
    • Maven (1)
  • MySQL (30)
  • MySQL DBA (21)
    • MySQL Architecture (8)
  • Oracle Apps DBA (8)
  • Oracle DBA (132)
    • 12c New Features (13)
    • Goldengate Oracle (1)
    • Load Test (2)
    • Oracle Adhoc SQL Scripts (5)
    • Oracle Architecture (9)
    • Oracle Archivelogs (2)
    • ORACLE ASM (4)
    • Oracle Auditing (2)
    • Oracle Backups (5)
    • Oracle Cloud (2)
    • Oracle Dataguard (9)
    • ORACLE EXPORTS (5)
    • Oracle Fragmentation (1)
    • Oracle Metadata (6)
    • Oracle Networking (1)
    • Oracle Partitioning (3)
    • ORACLE RAC (3)
    • Oracle SQL Loader (1)
    • Oracle_Flashback (1)
    • Oracle_Stats (1)
    • Oracle_Tablespace (5)
    • OS Commands for DBAs (3)
    • Patching (1)
    • Performance Monitoring (9)
    • PFILE AND SPFILE (1)
    • Production Support (14)
    • User Management (1)
  • Public_Articles (9)
  • SQL Server DBA (19)
    • SQL Server – Production Support (16)
    • SQL Server Performance Tuning (2)
    • SQL Server Troubleshooting (2)
  • Teradata (11)
  • Teradata DBA (9)
    • Teradata Architecture (6)
  • Uncategorized (37)
  • UNIX (18)

Recent Comments

  • Ajay Kumar on Oracle installation on AWS Cloud
  • ajay001 on ORACLE DATABASE CREATION (MANUAL)
  • Ajay Kumar on ORACLE DATABASE CREATION (MANUAL)
  • Ajay Kumar on Startup and Shutdown modes in Oracle
  • Swetha on Teradata : TYPES OF PRIMARY INDEXES (UPI AND NUPI)

Archives

  • February 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016

Tags

12c top n queries about MySQL Adding ASM Disks bigdata big data cassandra cassandra key components cassandra key structures clonning user in oracle DATA DISTRIBUTION AND PRIMARY INDEX Devops Maven Introduction dmts featured Gossip in Cassandra hadoop hadoop what is big data How To Kill runaway processes After Terminating Concurrent Request information_schema InnoDB Invisible columns in oracle lmts Multitenant Database MyISAM MySQL NoSQL oracle 12c masking oracle database link Oracle Database Parameter Oracle Data Guard Oracle Dataguard oracle dba oracle history commands oracle performance tuning Oracle Server architecture shell scripting for DBA SQL caching Steps before patching GI and RDBMS home storage engines in mYSql Teradata Architecture TERADATA BASIC TERMINOLOGIES AND SPACE ALLOCATION teradata TYPES OF PRIMARY INDEXES Transportable tablespace undo UNIX vprocs and the number of AMPs

About

This is Vinod Kumar having around 14+ years of experience as Oracle DBA having exposure to RAC,Dataguard,RMAN,GoldenGate,DB Upgrades,Hadoop. Looking forward to sharpen my skill set and adapt new things. Worked for various industries like Banking, financial, Telecom, Product based companies and retail etc.

  • I believe that Knowledge sharing is Knowledge Gaining.
  • Working Hard for Something We Don't Care about is called STRESS.
  • Working Hard for Something we LOVE is called PASSION.

Recent Posts

  • RENAMING INSTANCE

  • Renaming the Oracle Database.

  • Controlfile Multiplexing and De-Multiplexing In Oracle12c

    Controlfile Multiplexing and De-Multiplexing In Oracle12c

  • Different Block Size of ORACLE

  • Storage Management in Oracle -3

    Storage Management in Oracle -3

Archives

  • February 2019 (11)
  • January 2019 (18)
  • December 2018 (16)
  • November 2018 (11)
  • October 2018 (19)
  • September 2018 (14)
  • August 2018 (7)
  • July 2018 (9)
  • June 2018 (11)
  • May 2018 (1)
  • April 2018 (1)
  • March 2018 (5)
  • February 2018 (10)
  • January 2018 (5)
  • December 2017 (4)
  • November 2017 (4)
  • October 2017 (7)
  • September 2017 (5)
  • August 2017 (7)
  • July 2017 (7)
  • June 2017 (1)
  • May 2017 (1)
  • March 2017 (6)
  • February 2017 (3)
  • January 2017 (2)
  • December 2016 (8)
  • November 2016 (35)
  • October 2016 (31)

Get Updates

Subscribe to our newsletter to receive breaking news by email.

Follow us

Website Visitors


Locations of visitors to this page

  • About
  • Privacy Statement
COPYRIGHT © 2017 - KTEXPERTS.COM Back To Top