Transportable tablespace in oracle 11g

Oracle Version : oracle 11g R2

What is Transportable Tablespaces(TTS)?
Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles.Oracle 8i supports tablespace
transportation between databases that run on same OS platforms and use the same database block size. But in oracle 9i TTS(Transportable Tablespaces) technology was enhanced and was enabled
to use multiple block size which removed this restriction.In Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces
between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats.


Limitations on Transportable Tablespace :-
1. The source and target database must use the same character set and national character set.
2.Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects
are in the tablespace set.
3. You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Thismeans that you cannot use TTS for PL/SQL, triggers, or views. These would have to be
moved with export.
4.You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or
the destination tablespace before the transport operation.
5. You cannot transport an encrypted tablespace to a platform with different endianness.

Steps to transport tablespace from one database to another:-
1.Check endian format of both platforms,

Source DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
-------------------------------------------------
Linux x86 64-bit

SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;

PLATFORM_ID PLATFORM_NAME                      ENDIAN_FORMAT
----------- ---------------------------------- --------------
         13 Linux x86 64-bit                   Little

Destination DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
----------------------------------------------------------
Linux x86 64-bit

SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;

PLATFORM_ID PLATFORM_NAME                                ENDIAN_FORMAT
----------- -------------------------------------------- --------------
         13 Linux x86 64-bit                             Little


Now let me create a tablespace to demonstrate the feature.

Source DB:-
[oracle@server1 ~]$ sqlplus / as sysdba

SQL> CREATE TABLESPACE tts DATAFILE '/u01/app/oracle/oradata/prim/tts_data01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M;
Tablespace created.

SQL> CREATE USER tts_user IDENTIFIED BY tts_user  DEFAULT TABLESPACE tts  TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tts;

User created.

SQL> grant connect , resource , create table to tts_user;

Grant succeeded.

SQL> CREATE TABLE test_tbl (id NUMBER, description VARCHAR2(50),CONSTRAINT test_tbl_pk PRIMARY KEY (id) );

SQL> INSERT INTO test_tbl (id, description) SELECT level, 'Description for ' || level FROM   dual CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.
SQL> select count(*) from test_tbl;  

  COUNT(*)
----------
     10000

Now For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.

SQL> CONN / AS SYSDBA
Connected.
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'tts' , incl_constraints => TRUE);

PL/SQL procedure successfully completed.

P.S. You can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the setof tablespaces is self-contained, this view is empty.
SQL> select * from tRANSPORT_SET_VIOLATIONS;

no rows selected

Now , put the tablespace in read only mode  which we are going to move.

SQL> alter tablespace tts read only;

Tablespace altered.

Now , Export the tablespace metadata using expdp utility.

[oracle@server1 ~]$ expdp system/system dumpfile=tts.dmp directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES =tts
Export: Release 11.2.0.1.0 - Production on Thu Jan 21 14:59:52 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=ttss.dmp TRANSPORT_TABLESPACES=tts directory=DATA_PUMP_DIR
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/prim/dpdump/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
  /u01/app/oracle/oradata/prim/tts_data01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:00:57


Now transfer the datafile of the tablespace along with the dumpfile into the destination server. We will transfer it using scp .
[oracle@server1 prim]$ scp t/u01/app/oracle/oradata/prim/tts_data01.dbf oracle@192.168.2.104:/u01/app/oracle/oradata/prim/
oracle@192.168.2.104's password:
ts_data01.dbf                                                      100%   10MB  10.0MB/s   00:00

[oracle@server1 prim]$ scp /u01/app/oracle/admin/prim/dpdump/tts.dmp oracle@192.168.2.104:/u01/app/oracle/admin/prim/dpdump/
tts.dmp                                                            100%   96KB  96.0KB/s   00:00


Now the source tablespace can be switched  back to read write mode.
SQL> alter tablespace tts read write;

Tablespace altered.


At Destination DB:-

Destination Database

Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.

SQL> CONN / AS SYSDBA

SQL> CREATE USER tts_user IDENTIFIED BY tts_user;

User created.

SQL> GRANT connect , resource, create table TO tts_user;

Grant succeeded.
Import the dump file of transportable tablespace .
[oracle@server2 prim]$ impdp system/system directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/prim/tts_data01.dbf'

Import: Release 11.2.0.1.0 - Production on Thu Jan 8 23:56:26 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles=/u01/app/oracle/oradata/prim/tts_data01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 23:56:31


SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TTS                            READ ONLY

SQL> alter tablespace tts read write;

Tablespace altered.


SQL> select TABLESPACE_NAME,PLUGGED_IN,status from dba_tablespaces where tablespace_name='TTS';

TABLESPACE_NAME                PLU STATUS
------------------------------ --- ---------
TTS                            YES ONLINE

SQL> conn tts_user/tts_user
Connected.
SQL> select count(*) from test_tbl;  

  COUNT(*)
----------
     10000

No comments:

Post a Comment