Cross Platform Transportable Tablespace
Transportable Tablespace is used to move a large amount of data between databases where the tablespace is copied from source database to the target database.
A Tablespace can be Transport between databases on the same Platform from version 8i. From the version 10g, a Tablespace can be Transported between databases on Different Platforms.
In this article, we will see transportable tablespace on Cross platform.
The tablespace that is created in Database which is on the Solaris will be migrated into Linux environment and then into Windows.
Source Database Platform Solaris:
- Login to the database that is running on Solaris OS, and create a tablespace that will be transferred to the target server. Then create a user and an object on that tablespace.
Connecting to the database
|
1 2 3 |
[oracle@localhost ~]$ export ORACLE_SID=SOLARISDB [oracle@localhost ~]$ sqlplus "/ as sysdba" |
Creating the Tablespace
|
1 2 3 |
SQL> create tablespace tbs1 datafile '/export/home/oracle/oradata/SOLARISDB/tbs101.dbf' size 1m; Tablespace created. |
Creating the user with Some privileges and assigning the tablespace.
|
1 2 3 4 5 6 7 |
SQL>grant dba to test identified by test; Grant succeeded. SQL> alter user test default tablespace tbs1; User altered |
Connecting to the database as test user and creating the Database Objects.
|
1 2 3 4 5 6 7 |
SQL> conn test/test Connected. SQL> create table tbl_so (id number) tablespace tbs1; Table created. |
- Verify the self-contained status of the tablespace, place it in read-only mode, create the necessary directory object and use Data Pump to export the metadata of the objects of that tablespace.
Verify the self-contained status of the tablespace.
|
1 2 3 4 5 6 7 |
SQL> execute sys.dbms_tts.transport_set_check('tbs1',true); PL/SQL procedure successfully completed. SQL> select * from sys.transport_set_violations; no rows selecte |
Make the tablespace read-only.
|
1 2 3 |
SQL> alter tablespace tbs1 read only; Tablespace altered. |
creating the directory for Data Pump Operations.
|
1 2 3 4 5 |
SQL> create directory ora_dump as '/export/home/oracle'; Directory created. SQL> exit |
Export the metadata of the objects using Data Pump Utility.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
bash ~$ expdp test/test dumpfile=tbs1.dmp directory=ora_dump transport_tablespaces=tbs1 Starting "test"."sys_export_transportable_01": test/******** dumpfile=tbs1.dmp directory=ora_dump transport_tablespaces=tbs1 processing object type transportable_export/plugts_blk processing object type transportable_export/table processing object type transportable_export/post_instance/plugts_blk master table "test"."sys_export_transportable_01" successfully loaded/unloaded ********************************************************************** Dump file set for test.sys_export_transportable_01 is: /export/home/oracle/tbs1.dmp job "test"."sys_export_transportable_01" successfully completed at 15:38:37 bash ~$ |
Note: We are performing the transportable tablespace on Cross platform so we cannot send the datafiles directly.
- In this step, use the convert tablespace command with the platform name where the conversion should be performed. Use Linux IA (32-bit).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
RMAN> convert tablespace tbs1 2> to platform 'linux ia (32-bit)' 3> db_file_name_convert '/export/home/oracle/oradata/SOLARISDB' '/export/home/oracle'; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=141 devtype=DISK channel ORA_DISK_1: starting datafile conversion input datafile fno=00005 name=/export/home/ oradata/SOLARISDB/tbs101.dbf converted datafile=/export/home/oracle/tbs101.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 Place the tablespace to read/write mode, copy the dump file and convert datafiles to the target server. SQL> alter tablespace tbs1 read write; Tablespace altered. |
Target Database Platform Linux.
- Login to the database that is running on Linux OS, Create a user and directory object on the target database and use Data Pump to import the tablespace to the target database.
Connecting to the database
|
1 2 3 |
[oracle@localhost ~]$ export ORACLE_SID= LINUXDB [oracle@localhost ~]$ sqlplus "/ as sysdba" |
Creating the user with Some privileges.
|
1 2 3 |
SQL> grant dba to test identified by test; Grant succeeded. |
creating the directory for Data Pump Operations.
|
1 2 3 4 5 |
SQL> create or replace directory ora_dump as '/home/oracle/ora_dump'; Directory created. $ mkdir -p /home/oracle/ora_dump |
Import the Tablespace using Data Pump Utility.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[oracle@localhost ~]$ impdp test/test dumpfile=tbs1.dmp directory=ora_dump transport_datafiles='/home/oracle/ora_dump/tbs101.dbf' Processing object type transportable_export/plugts_blk processing object type transportable_export/table processing object type transportable_export/post_instance/plugts_blk job "test"."sys_import_transportable_01" successfully completed at 11:47:23 SQL> select tablespace_name, plugged_in from dba_tablespaces; TABLESPACE_NAME PLUGGED_IN ------------------------------ ------------------------------- system NO undotbs1 NO sysaux NO users NO temp NO tbs1 YES 6 rows selected. |
Connect the test user and check the database object.
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> conn test/test Connected. SQL> desc tbl_sol; Name Null? Type --------------------------------- -------- ------------------------- ID NUMBER |
The tablespace is successfully converted and created in the second database that is running on the Linux platform.
If the size of the tablespace is huge and there is more than one datafile in the tablespace, then add parallelism and format options to run the process in any parallel degree that is desired and generate user defined file names. In the following command, convert the tablespace tbs1_two, which has three datafiles, into five parallel sessions and user-defined filenames:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SQL> select tablespace_name, name from v$datafile_header where tablespace_name='tbs1_two'; TABLESPACE_NAME NAME ----------------------------------- -------------------------------- tbs1_two /export/home/oracle/oradata/SOLARISDB/tbs1_1.dbf tbs1_two /export/home/oracle/oradata/SOLARISDB/tbs1_2.dbf TBS1_TWO /expo rt/home/oracle/oradata/SOLARISDB/tbs1_3.dbf RMAN> convert tablespace tbs1_two 2> to platform 'linux ia (32-bit)' 3> parallelism 5 4> format '/tmp/converted/file_%N_%f'; Starting backup at 12-APR-10 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 using channel ORA_DISK_5 channel ORA_DISK_1: starting datafile conversion input datafile fno=00006 name=/export/home/oracle/ oradata/SOLARISDB/tbs1_1.dbfchannel ORA_DISK_2: starting datafile conversion input datafile fno=00007 name=/export/home/oracle/ oradata/SOLARISDB/tbs1_2.dbfchannel ORA_DISK_3: starting datafile conversion input datafile fno=00008 name=/export/home/oracle/ oradata/SOLARISDB/tbs1_3.dbf converted datafile=/tmp/converted/file_TBS1_TWO_6 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:00 converted datafile=/tmp/converted/file_TBS1_TWO_7 channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:00 converted datafile=/tmp/converted/file_TBS1_TWO_8 channel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:01 Finished backup at 12-APR-10 bash ~$ ls -ltr total 6192 -rw-r----- 1 oracle dba 1056768 apr 12 09:49 file_tbs1_two_6 -rw-r----- 1 oracle dba 1056768 apr 12 09:49 file_tbs1_two_7 -rw-r----- 1 oracle dba 1056768 apr 12 09:49 file_tbs1_two_8 |
Now we are using the convert datafile Command on the Destination Host to Convert the Datafile from the Linux Platform to the Windows OS.
In case a single datafile or set of datafiles needs to be converted to any platform that RMAN supports, use the convert datafile to platform command on the destination host. As all the steps are the same as in the previous scenario, only the RMAN command that will be used on the destination host (Windows OS) to convert the datafile that was copied from the source host (Linux OS) will be shown.
Use platform command and mention the platform name from which the conversion is being performed:
|
1 2 3 4 5 |
RMAN> convert datafile 'c:\test\tbs_lin01.dbf' 2> from platform 'linux ia (32-bit)' 3> format 'c:\tmp\file_%U'; |
Loading...
