Easy way to perform Transportable tablespace across platforms.(Video included)

Share via:

Post Views: 0

Easy way to perform Transportable tablespace across platforms.

Transportable Tablespace feature was introduced in Oracle 8i to transfer large amount of data between the oracle databases.

In Oracle 10g this useful feature was enhanced with cross-platform support which allowed a tablespace, or set of tablespaces, to be transported between databases deployed on different hardware platforms .

Even we can perform T-Ts which platforms have different Endian format.

This article will help you to perform T-Ts from RHEL 7.5 to Windows 7 operating system and Vice Versa.

Source database server details :

Operating Version : RHEL 7.5 -64 bit

Database version : Oracle 12cR1

Type of DB : Container DB

ENDIAN FORMAT : Little

target database server details :

Operating Version : Windows 7 x86-64 bit

Database version : Oracle 12cR1

Type of DB : Non –Container DB

ENDIAN FORMAT : Little

Check the source database(LINUX) information.

SYS>>select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
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 - 12-JUL-2018 19:52:53 - PRIMARY-YES
SYS>>show pdbs
    CON_ID         CON_NAME              OPEN MODE      RESTRICTED
    ----------   ----------------------- ----------     ----------
         2           PDB$SEED             READ ONLY         NO
         3           PLUG4                MOUNTED
         4           PLUG2                READ WRITE        NO
         5           PLUG5                MOUNTED

SYS>>select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE 12.1.0.2.0 Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

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 - 12-JUL-2018 19:52:53 - PRIMARY-YES

SYS>>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ----------------------- ---------- ----------

2 PDB$SEED READ ONLY NO

3 PLUG4 MOUNTED

4 PLUG2 READ WRITE NO

5 PLUG5 MOUNTED

Here Plug2 pluggable database is in READ WRITE mode.

Now connect to plug2 PDB and create a new tablespace.

SYS>> alter session set container=plug2
Session altered.
SYS>>show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PLUG2                          READ WRITE NO
SYS>>create tablespace  trans_cp_ts datafile '/u01/oradata/microcdb/plug2/trans_cp_ts.dbf' size 10m;
Tablespace created.

SYS>> alter session set container=plug2

Session altered.

SYS>>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

4 PLUG2 READ WRITE NO

SYS>>create tablespace trans_cp_ts datafile '/u01/oradata/microcdb/plug2/trans_cp_ts.dbf' size 10m;

Tablespace created.

Create a schema with required privileges and assign the user to above created tablespace.

SYS>>grant dba to trans_cp_us identified by trans_cp_us;
Grant succeeded.
SYS>>alter user trans_cp_us default tablespace trans_cp_ts;
User altered.

SYS>>grant dba to trans_cp_us identified by trans_cp_us;

Grant succeeded.

SYS>>alter user trans_cp_us default tablespace trans_cp_ts;

User altered.

Connect to Schema and create a table.

SYS>>conn trans_cp_us/trans_cp_us@to_plug2
Connected.
TRANS_CP_US>>create table xxtag (sno number);
Table created.
TRANS_CP_US>>insert into xxtag values(&sno);
Enter value for sno: 10
old   1: insert into xxtag values(&sno)
new   1: insert into xxtag values(10)
1 row created.
TRANS_CP_US>>/
Enter value for sno: 20
old   1: insert into xxtag values(&sno)
new   1: insert into xxtag values(20)
1 row created.
TRANS_CP_US>>/
Enter value for sno: 30
old   1: insert into xxtag values(&sno)
new   1: insert into xxtag values(30)
1 row created.
TRANS_CP_US>>/
Enter value for sno: 40
old   1: insert into xxtag values(&sno)
new   1: insert into xxtag values(40)
1 row created.
TRANS_CP_US>>/     
Enter value for sno: 50
old   1: insert into xxtag values(&sno)
new   1: insert into xxtag values(50)
1 row created.
TRANS_CP_US>>commit;
Commit complete.

SYS>>conn trans_cp_us/trans_cp_us@to_plug2

Connected.

TRANS_CP_US>>create table xxtag (sno number);

Table created.

TRANS_CP_US>>insert into xxtag values(&sno);

Enter value for sno: 10