DB - Transportable Tablespaces x-Platforms
In this post i'm sharing how to copy data /tablespaces between databases in different platforms
as I've no HP-UX here at home .. I will have to convert a tablespace to HP-UX, then to convert the datafile back to Linux
I will execute the next step to get this done:
1 - Source setup - create a tablespace, user and a table with data
2 - Export the tablespace
3 - Convert the tablespace to 'HP-UX IA (64-bit)'
4 - Remove the Tablespace from the source
5 - Prepare the import
6 - Convert datafile from 'HP-UX IA (64-bit)' > 'Linux IA (32-bit)'
7 - Import the tablespace in the destination
8 - Validate the data
1 - Source setup - create a tablespace, user and a table with data
Criar Tablespace : xpto : Data File : xpto.bdf
Criar user teste : xpto
Tabela : xpto.users
SQL> create tablespace xpto datafile 'xpto.bdf' size 10m autoextend on next 10m maxsize 100m;
Tablespace created.
SQL> create user xpto identified by xpto default tablespace xpto quota unlimited on xpto;
User created.
SQL> create table xpto.users as select * from dba_users;
Table created.
2 - Export the tablespace
Standard expdp - create a directory + expdp ( the TS needs to be read only)
SQL> create directory DIR_TTS as '/home/oracle';
Directory created.
SQL> alter tablespace xpto read only;
Tablespace altered.
SQL> !expdp DIRECTORY=DIR_TTS TRANSPORT_TABLESPACES='XPTO' DUMPFILE=tts_xpto.dmp
Export: Release 11.2.0.1.0 - Production on Fri Feb 16 16:32:50 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA DIRECTORY=DIR_TTS TRANSPORT_TABLESPACES=XPTO DUMPFILE=tts_xpto.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tts_xpto.dmp
******************************************************************************
Datafiles required for transportable tablespace XPTO:
/u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:33:36
3 - Convert the tablespace to 'HP-UX IA (64-bit)'
Sofar - as the same as copy between same platforms
Now start the diferences, to carry one lets generate a data file with RMAN for 'HP-UX IA (64-bit)'
RMAN> CONVERT TABLESPACE xpto TO PLATFORM 'HP-UX IA (64-bit)' FORMAT '/home/oracle/xpto_%U';
Starting conversion at source at 16-FEB-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00006 name=/home/oracle/xpto.bdf
converted datafile=/home/oracle/xpto_data_D-xTTS_I-2336971890_TS-XPTO_FNO-6_03srcukf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 16-FEB-18
4 - Remove the Tablespace from the source
As we have tablespace backup, and to let remove the tablespace from the source / and load it again from the 'HP-UX IA (64-bit)' data file,
SQL> drop tablespace xpto including contents and datafiles;
Tablespace dropped.
5 - Prepare the import
Is need to copy the data files and and dump files to destionation
it can be done using standard commands scp / ftp .. or usubf a share FS.
As this is case is the same box .. I will skip it
6 - Convert datafile from 'HP-UX IA (64-bit)' > 'Linux IA (32-bit)'
Now let's convert the datafile from ( step 3) from 'HP-UX IA (64-bit)' > 'Linux IA (32-bit)'
SQL> SELECT PLATFORM_NAME FROM V$TRANSPORTABLE_PLATFORM
WHERE PLATFORM_ID = ( SELECT PLATFORM_ID FROM V$DATABASE );
PLATFORM_NAME
-----------------------------------------------------------------------------------------------------
Linux IA (32-bit)
RMAN> CONVERT FROM PLATFORM 'HP-UX IA (64-bit)'
DATAFILE '/home/oracle/xpto_data_D-xTTS_I-2336971890_TS-XPTO_FNO-6_03srcukf'
FORMAT '/home/oracle/XPTO_CONVERTED_FROM_HP_2_LINUX-32b';
Starting conversion at target at 16-FEB-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/xpto_data_D-xTTS_I-2336971890_TS-XPTO_FNO-6_03srcukf
converted datafile=/home/oracle/XPTO_CONVERTED_FROM_HP_2_LINUX-32b
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 16-FEB-18
7 - Import the tablespace in the destination
Almost done - now is only run the impdp : the dmp file from ( step - 2) and the datafile from (step - 6)
$ impdp DIRECTORY=DIR_TTS DUMPFILE='tts_xpto.dmp' TRANSPORT_DATAFILES='/home/oracle/XPTO_CONVERTED_FROM_HP_2_LINUX-32b'
Import: Release 11.2.0.1.0 - Production on Fri Feb 16 17:35:14 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA DIRECTORY=DIR_TTS DUMPFILE=tts_xpto.dmp TRANSPORT_DATAFILES=/home/oracle/XPTO_CONVERTED_FROM_HP_2_LINUX-32b
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 17:35:19
8 - 8 - Validate the data
After the impdp is complete we can connect and validate the data
And change the tablespace to "read write"
[oracle@ocm ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 16 17:35:26 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(0) from xpto.users;
COUNT(0)
----------
37
SQL> alter tablespace xpto read write;
Tablespace altered.
SQL> col FILE_NAME form a100
SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='XPTO';
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------------------------------------- ------------------------------
/home/oracle/XPTO_CONVERTED_FROM_HP_2_LINUX-32b XPTO
Thank you for reading, hope this post was helpful.
Rogerio
Comments