Oracle Database: Transportable Tablespace – across platform (Windows to AIX)

Hello everyone!!

I am a little absent, on writing articles at the blog, because I am working on many projects (sleeping less too) with very tight deadlines (Typical life of DBA). Anyway…
Some days ago one friend called for help to make a transportable tablespace  I will post this activities here from Windows to AIX (differents endian formats).

A transportable tablespace set is a self-contained group of tablespaces that encapsulate the
objects that you wish to transport from one database to another.

To check which plaform you can transport, run this query:
SQL> select * from v$transportable_platform;

You can run this query too:

set lines 200
set pages 200
COL “Source” FORM a32
COL “Compatible Targets” FORM a40
BREAK ON “Source”
select d.platform_name “Source”, t.platform_name “Compatible Targets”, endian_format
from v$transportable_platform t, v$database d where t.endian_format =
(select endian_format from v$transportable_platform t,
v$database d where d.platform_name = t.platform_name)
order by “Compatible Targets”;

For check, which platform are compatible with the your source database.

In order for tablespace set to be transportable, there are some basic rules:

The tablespace set must contain all of the partitions of a partitioned table if any of the table’s partitions are included in the tablespace set.
If an index is included in a tablespace set, its corresponding table must also be included in the tablespace set.
If you choose to include referential integrity constraints in the tablespace set, then all tablespaces required to support the constraints must be included in the set.
If you have tables with LOB columns in the set, the tablespace that contains the LOBs must be included.
For check the tablespace dependeces you can the DBMS.TSS.TRANSPORT_SET_CHECK:

SQL> create table HR.CLIENTS (ID NUMBER, NAME varchar2(20)) tablespace USR_DATA;
Table created.

SQL> create index HR.ID_indx on HR.CLIENTS (ID) tablespace IDX_USR;
Index created.

SQL> exec dbms_tts.transport_set_check (‘IDX_USR’);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
—————————————————————————————————-

ORA-39907: Index HR.ID_INDX in tablespace IDX_USR points to table HR.CLIENTS in tablespace USR_DATA.

With this simple verification showed to us that our tablespace have a dependence. The HR.ID INDEX is an index that correspond with the HR.CLIENTS table which is on tablespace USR_DATA. On the transportable you must include the IDX_USR tablespace.

First, place the tablespaces in the transportable set in read-only mode:

SQL> ALTER TABLESPACE USR_DATA READ ONLY;
Tablespace altered.

SQL> ALTER TABLESPACE IDX_USR READ ONLY;
Tablespace altered.

Now execute the data pump export command:

C:> expdp hr/oracle dumpfile=hrtbs.dmp directory=data_pump_dir transport_tablespaces=usr_data, idx_usr

Export: Release 11.2.0.4.0 – Production on Sun Nov 22 15:50:19 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “HR”.”SYS_EXPORT_TRANSPORTABLE_01″: hr/******** dumpfile=hrtbs.dmp directory=data_pump_dir transport_tablespaces=usr_data, idx_usr
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “HR”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TRANSPORTABLE_01 is:
D:\ORACLE\ADMIN\DB11G\DPDUMP\HRTBS.DMP
******************************************************************************
Datafiles required for transportable tablespace IDX_USR:
D:\ORACLE\ORADATA\DB11G\IDX_USR01.DBF
Datafiles required for transportable tablespace USR_DATA:
D:\ORACLE\ORADATA\DB11G\USRDATA01.DBF
Job “HR”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at Sun Nov 22 15:51:07 2015 elapsed 0 00:00:47

In this case an endian converion is require than we will invoke RMAN to do this.

rman target /

RMAN> run
2> {
3> convert tablespace usr_data, idx_usr to platform ‘AIX-Based Systems (64-bit)’ format ‘D:\tbs_convertion\%U’;
4> }

Starting conversion at source at 22-NOV-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00006 name=D:\ORACLE\ORADATA\DB11G\USRDATA01.DBF
converted datafile=D:\TBS_CONVERTION\DATA_D-DB11G_I-365959502_TS-USR_DATA_FNO-6_01QMTMRJ
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00007 name=D:\ORACLE\ORADATA\DB11G\IDX_USR01.DBF
converted datafile=D:\TBS_CONVERTION\DATA_D-DB11G_I-365959502_TS-IDX_USR_FNO-7_02QMTMRQ
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished conversion at source at 22-NOV-15

Now copy all this files to the host destination, the dump file and the datafiles converted.

On the destination host run this:

RMAN> run
{
CONVERT DATAFILE ‘/u02/oradata/dbaix/datafile/DATA_D-DB11G_I-365959502_TS-USR_DATA_FNO-6_01QMTMRJ’,
‘/u02/oradata/dbaix/datafile/DATA_D-DB11G_I-365959502_TS-IDX_USR_FNO-7_02QMTMRQ’
FROM PLATFORM “AIX-Based Systems (64-bit)” DB_FILE_NAME_CONVERT (‘/u02/oradata/dbaix/datafile/’, ‘/u02/oradata/dbaix/’);
}2> 3> 4> 5> 6>

Starting conversion at target at 22-NOV-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/u02/oradata/dbaix/datafile/DATA_D-DB11G_I-365959502_TS-USR_DATA_FNO-6_01QMTMRJ
converted datafile=/u02/oradata/dbaixDATA_D-DB11G_I-365959502_TS-USR_DATA_FNO-6_01QMTMRJ
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input file name=/u02/oradata/dbaix/datafile/DATA_D-DB11G_I-365959502_TS-IDX_USR_FNO-7_02QMTMRQ
converted datafile=/u02/oradata/dbaixDATA_D-DB11G_I-365959502_TS-IDX_USR_FNO-7_02QMTMRQ
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 22-NOV-15

Now run the import from data pump utility to import the dump files:

oracle@srvdb /u02/oradata >impdp hr/oracle dumpfile=HRTBS.DMP directory=data_pump_dir \
> TRANSPORT_DATAFILES=’/u02/oradata/dbaixDATA_D-DB11G_I-365959502_TS-USR_DATA_FNO-6_01QMTMRJ’, ‘/u02/oradata/dbaixDATA_D-DB11G_I-365959502_TS-IDX_USR_FNO-7_02QMTMRQ’

Import: Release 11.2.0.4.0 – Production on Sun Nov 22 20:32:02 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “HR”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
Source time zone is +00:00 and target time zone is -03:00.
Starting “HR”.”SYS_IMPORT_TRANSPORTABLE_01″: hr/******** dumpfile=HRTBS.DMP directory=data_pump_dir TRANSPORT_DATAFILES=/u02/oradata/dbaix/DATA_D-DB11G_I-365959502_TS-USR_DATA_FNO-6_01QMTMRJ, /u02/oradata/dbaix/DATA_D-DB11G_I-365959502_TS-IDX_USR_FNO-7_02QMTMRQ
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job “HR”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at Sun Nov 22 20:32:08 2015 elapsed 0 00:00:04

Now go to sqlplus and make the tablespaces read write:

oracle@srvdb /u02/oradata >sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 22 20:33:10 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace usr_data read write;

Tablespace altered.

SQL> alter tablespace idx_usr read write;

Tablespace altered.


And so did the trick!

I hope that you enjoy, cheers!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s