This post will be about Cross-platform transportable tablespace. Cross-platform transportable tablespace is a technique where you can copy a tablespace from a database running in one platform to another platform. Also Cross-platform transportable tablespace isn’t the same as Cross-platform transportable database. In this instance the whole database is copied including the SYSTEM tablespace. In this post we are going to copy a tablespace from one database to another database. Then the first thing you have to know is the internal names for each platform that supports cross-platform data transport and this information is found in V$TRANSPORTABLE_PLATFORM view as below:
SQL> select PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little
20 rows selected.
After you will need to run the SQL below to get the platform name of your current source server and later this information will be used to convert the files of the tablespace afterwards. In this post the source will have the platform from ‘Solaris Operating System (x86-64)’ as you can see:
SQL> SELECT PLATFORM_NAME
FROM V$TRANSPORTABLE_PLATFORM
WHERE PLATFORM_ID =
( SELECT PLATFORM_ID FROM V$DATABASE ); 2 3 4
PLATFORM_NAME
-----------------------------------------------------------------------------------------------------
Solaris Operating System (x86-64)
Place the tablespaces to be transported in read-only mode as below:
Command: alter tablespace <tablespace name> read only; SQL> alter tablespace TBS_DATABASKET read only; Tablespace altered.
Create the directory where the dump will be generated:
SQL> CREATE OR REPLACE DIRECTORY TBSTECHDIR AS '/datapump/tbs/'; Directory created. SQL> grant read, write on directory TBSTECHDIR to public;
Ok, now we have all the information that we need to perform the export of the tablespace and create the dump and all required datafiles to perform the transport of the tablespace TBS_DATABASKET running on the Solaris Operating System (x86-64) to a Linux x86 64-bit system. So I have executed this command:
expdp br/br@dbtech directory=TBSTECHDIR transport_tablespaces=TBS_DATABASKET
Following by the output:
Export: Release 12.1.0.2.0 - Production on Sun May 24 14:48:32 2020 Copyright (c) 1982, 2014, Oracle and/or its affiliates. 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 Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** transport_tablespaces=TBS_DATABASKET directory=TBSTECHDIR dumpfile=TBSTECHDIRBR.dmp logfile=TBSTECHDIRBR.log 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/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS 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 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /datapump/tbs/TBSTECHDIRBR.dmp ****************************************************************************** Datafiles required for transportable tablespace TESTE_OCM: /u01/app/oracle/oradata/PROD/tbstechdata01.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun May 24 14:49:48 2020 elapsed 0 00:00:16
So I copied the datafile and the dump to the directory /techdatabask02/server02/tbs in the target server:
Datafile: /u01/app/oracle/oradata/PROD/tbstechdata01.dbf Dump: /datapump/tbs/TBSTECHDIRBR.dmp Directory in the target server: /techdatabask02/server02/tbs
Now I am going to connect to the database PRDTECH and convert the datafile:
Recovery Manager: Release 12.1.0.2.0 - Production on Sun May 24 15:07:46 2020 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. RMAN> connect target / connected to target database: PRDTECH (DBID=1951929193) RMAN> connect catalog <rmanuser>/<rmanpassword>@rman connected to recovery catalog database RMAN> CONVERT DATAFILE ' /techdatabask02/server02/tbs/tbstechdata01.dbf' DB_FILE_NAME_CONVERT '/techdatabask02/server02/tbs/', '/u01/app/oracle/oradata/PROD/' FROM PLATFORM 'Solaris Operating System (x86-64)';2> 3> Starting conversion at target at 24-MAY-20 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 channel ORA_DISK_1: starting datafile conversion input file name=/techdatabask02/server02/tbs/tbstechdata01.dbf converted datafile=/u01/app/oracle/oradata/PROD/tbstechdata01.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 Finished conversion at target at 24-MAY-20
Let’s import the tablespace TBS_DATABASKET into the PRDTECH database:
- Change the and with the right values.
impdp <user>/<password> dumpfile=TBSTECHDIRBR.dmp directory=TBSTECHDIR transport_datafiles=/u01/app/oracle/oradata/PROD/tbstechdata01.dbf
The output:
Import: Release 12.1.0.2.0 - Production on Sun May 24 15:29:50 2020 Copyright (c) 1982, 2014, Oracle and/or its affiliates. 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 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Source time zone is +02:00 and target time zone is +00:00. Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=TBSTECHDIRBR.dmp directory=TBSTECHDIR transport_datafiles=/u01/app/oracle/oradata/PROD/tbstechdata01.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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sun May 24 15:29:34 2020 elapsed 0 00:00:44
Change the status of the new tablespace in the new database:
SQL> Alter tablespace TBS_DATABASKET read write;
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)