Manage Transport of tablespaces across platforms

Transporting Tablespace is fast way to transport tablespaces to different platform, it requires only few steps.

Before starting with transport you have to check if you are going to transport the tablespace between systems with different endian format. You can view the platforms and their endian formats with V$TRANSPORTABLE_PLATFORM view.

select  platform_name, endian_format
from    v$transportable_platform;

I will create one tablespace (demotbs) , create table and insert one row into the table. Later I will transport the demotbs tablespace on other host.
Create the tablespace.

SQL>
SQL> create tablespace demotbs datafile '/u01/app/oracle/oradata/prmy01/demotbs01.dbf' size 10M autoextend on next 20M extent management local segment space management auto;

Tablespace created.

SQL>
SQL> create user iarsov identified by iarsov default tablespace demotbs quota unlimited on demotbs;

User created.

SQL> grant connect, resource to iarsov;

Grant succeeded.

SQL>
SQL> conn iarsov/iarsov
Connected.
SQL>
SQL> create table test(id number,descr varchar2(255));

Table created.

SQL> insert into test values(1,'This is demo for transporting tablespace');

1 row created.

SQL> commit;

Commit complete.

SQL>

Next, check if the tablespace that you want to transport is “self-contained”.

SQL>
SQL> exec dbms_tts.transport_set_check('demotbs',TRUE);

PL/SQL procedure successfully completed.

Check if there are errors, if there are any you’ll see them in V$TRANSPORT_SET_VIOLATIONS view.

SQL> select * from transport_set_violations;

no rows selected

SQL>

After you’ve fixed all errors you can continue and create transport self-contained set of tablespaces.
first, put the tablespace in read only mode.

SQL> alter tablespace demotbs read only;

Tablespace altered.

SQL>

With Data Pump export the metadata about the tablespaces included in the set.

[oracle@xsrv ttsdir]$
[oracle@xsrv ttsdir]$ expdp system@prmy01 directory=ttsdir dumpfile=expdp_tts.dmp transport_tablespaces=demotbs logfile=expdp_tts.log

Export: Release 11.2.0.3.0 - Production on Mon Mar 11 22:32:16 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/********@prmy01 directory=ttsdir dumpfile=expdp_tts.dmp transport_tablespaces=demotbs logfile=expdp_tts.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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:
  /home/oracle/ttsdir/expdp_tts.dmp
******************************************************************************
Datafiles required for transportable tablespace DEMOTBS:
  /u01/app/oracle/oradata/prmy01/demotbs01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 22:33:13

[oracle@xsrv ttsdir]$

Now, If you transport the tablespace between platforms with different endian format, you can perform datafile conversion.

Next, copy dump file and datafiles (you can get datafile names from the log file). If you use endian conversion transfer the converted datafile to destination.

Datafiles required for transportable tablespace USERS:
  /u01/app/oracle/oradata/prmy01/demotbs01.dbf

In this case it is users01.dbf.
Now you can put the tablespace in read write mode.

SQL> alter tablespace demotbs read write;

Tablespace altered.

SQL>

Create the user on destination database.
I’ve created the user because I didn’t use REMAP_SCHEMA attribute, so the user must already exist on destination database.

SQL> create user iarsov identified by iarsov;

User created.

SQL> grant connect, resource to iarsov;

Grant succeeded.

SQL>

If you use endian conversion and you didn’t convert the datafiles on the source database, you can convert them on destination database with rman.
Import the metadata with Data Pump.

[oracle@xsrv ttsdir]$
[oracle@xsrv ttsdir]$ impdp system@prmy01 directory=ttsdir dumpfile=expdp_tts.dmp transport_datafiles=/u01/app/oracle/oradata/prmy01/demotbs01.dbf logfile=impdp_tts.log

Import: Release 11.2.0.3.0 - Production on Mon Mar 11 22:47:25 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.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/********@prmy01 directory=ttsdir dumpfile=expdp_tts.dmp transport_datafiles=/u01/app/oracle/oradata/prmy01/demotbs01.dbf logfile=impdp_tts.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:47:29

[oracle@xsrv ttsdir]$

Confirm that you have successfully transported the tablespace set.

SQL> column descr format a50
SQL> select * from test;

        ID DESCR
---------- --------------------------------------------------
         1 This is demo for transporting tablespace

SQL>

Leave a Reply