Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

RMAN Cross platform Transportable Tablespaces

Expert Oracle Database Tips by Donald BurlesonMarch 25, 2015

RMAN - Transporting the Tablespace across Different Platforms which have a different endian format

  • Scenario 2:  Bob gets a call from his IT Manager, Hi Bob. We need to plug the tablespace from the production database that runs on Solaris OS into the data warehouse system that runs on Linux OS (Solaris->Linux).  We also need to get the tablespace from the warehouse system and plug it into the test server that runs on Windows OS for testing purposes (Linux->Windows).  Please try to finish this request by the end of the day.?

Having received these requests, Bob decides to use the Cross-Platform Tablespace Transportation method to transport the whole tablespace from one platform to another and vice versa. When the tablespace is transporting to a different platform, it should be converted to match the destination platform type.  RMAN provides two commands for the conversion of the tablespaces:

  • Convert tablespace:  Used to convert the whole tablespace (with all its datafiles) on the source host

  • Convert datafile:   Used to convert specific datafiles on the destination host

  • In this section, both methods will be tried as follows:

  • Transporting the tablespace from Solaris to Linux using the first method (convert tablespace)

  • Transporting the tablespace from Linux to Windows using the second method (convert datafile)

This is further illustrated in Figure 6.1 on the following page:


Figure 6.1:  Transporting Tablespaces from Solaris to Linux and from Linux to Windows


In the following sections, three different databases will be used:

  • SOLDB:  Production database that runs on Solaris OS

  • LINDB:  DataWarehouse database that runs on Linux OS

  • WINDB:  Test database that runs on Windows OS

Using the convert tablespace command on the source host to transport the whole tablespace (with all its datafiles) from Solaris OS to the Linux OS

The following scenario involves transporting the tablespace from the Solaris OS to the Linux OS.  All steps will be the same as in the previous scenario where a manual transport of the tablespace was done with live datafiles.

  1. 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:

[oracle@localhost ~]$ export ORACLE_SID=SOLDB
[oracle@localhost ~]$ sqlplus "/ as sysdba"
SQL>
create tablespace
 tbs_sol datafile '/export/home/oracle//oradata/soldb/tbs_sol01.dbf' size 1m;
Tablespace created.
 
SQL>
grant dba to
 test
identified by
 test;
Grant succeeded.
 
SQL>
conn
 test/test
Connected.
 
SQL>
create table
 tbl_sol (id number) tablespace tbs_sol;
Table created.
SQL>

  1. Verify the self-contained status of the tablespace, place it in read-only mode, create the necessary directory object and use Data Pumpto export the metadata of the objects of that tablespace:

SQL>
execute
 sys.dbms_tts.transport_set_check('tbs_sol',true);
PL/SQL procedure successfully completed.
 
SQL>
elect * from
 sys.transport_set_violations;
no rows selected
 
SQL>
alter tablespace
 tbs_sol read only;
Tablespace altered.
 
SQL>
create directory
 ora_dump as '/export/home/oracle';
Directory created.
 
SQL>
exit
bash-3.00$ expdp test/test dumpfile=tbs_sol.dmp directory=ora_dump
transport_tablespaces=tbs_sol
Starting "test"."sys_export_transportable_01":  test/********
dumpfile=tbs_sol.dmp directory=ora_dump transport_tablespaces=tbs_sol
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/tbs_sol.dmp
job "test"."sys_export_transportable_01" successfully completed at 15:38:37
bash-3.00$

  1. In this step, use the convert tablespace commandwith the necessary platform name where the conversion should be performed. Use Linux IA (32-bit) platform name in this conversion process:

RMAN> convert tablespace tbs_sol
2> to platform 'linux ia (32-bit)'
3> db_file_name_convert '/export/home/oracle/oradata/soldb'
'/export/home/oracle';
 
Starting backup at 11-APR-10
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/soldb/tbs_sol01.dbf
converted datafile=/export/home/oracle/tbs_sol01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 11-APR-10
RMAN>

  1. Place the tablespace to read/write mode, copy the dump file and convert datafiles to the target server.  Then create a user and directory object on the target database and use Data Pumpto import the tablespace to the target database:

SQL>
alter tablespace
 tbs_sol read write;
Tablespace altered.
SQL>
-- Perform the following steps on the target database after copying the
converted datafiles and export dump file to it
[oracle@localhost ~]$ export ORACLE_SID=LINDB
[oracle@localhost ~]$ sqlplus "/ as sysdba"
SQL>
grant dba to
 test
identified by
 test;
Grant succeeded.
 
SQL>
create or replace directory
 ora_dump as '/home/oracle/ora_dump';
Directory created.
 
[oracle@localhost ~]$ impdp test/test dumpfile=tbs_sol.dmp
directory=ora_dump
transport_datafiles='/home/oracle/ora_dump/tbs_sol01.dbf'
<......output omitted ......>
<......output omitted ......>
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
 name
from
 v$tablespace;
 
NAME
------------------------------
system
undotbs1
sysaux
users
temp
tbs_sol
6 rows selected.

SQL>
conn
 test/test
Connected.
 
SQL>
desc
 tbl_sol;
 
Name                              Null?    Type
--------------------------------- -------- -------------------------
 ID                                                 NUMBER
SQL>

The tablespace was successfully converted and plugged into the second database that is running on the different 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 tbs_sol_two, which has three datafiles, into five parallel sessions and user-defined filenames:

SQL>
select
 tablespace_name, name
from
 v$datafile_header
where
 tablespace_name='tbs_sol_two';
 
TABLESPACE_NAME                     NAME
----------------------------------- --------------------------------
tbs_sol_two                         /export/home/oracle/
oradata/soldb/tbs_sol_1.dbf
 
tbs_sol_two                         /export/home/oracle/
oradata/soldb/tbs_sol_2.dbf
 
TBS_SOL_TWO                         /export/home/oracle/
oradata/soldb/tbs_sol_3.dbf
SQL>
 
RMAN> convert tablespace tbs_sol_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/soldb/tbs_sol_1.dbfchannel ORA_DISK_2: starting datafile conversion
input datafile fno=00007 name=/export/home/oracle/
oradata/soldb/tbs_sol_2.dbfchannel ORA_DISK_3: starting datafile conversion
input datafile fno=00008 name=/export/home/oracle/
oradata/soldb/tbs_sol_3.dbfconverted
datafile=/tmp/converted/file_TBS_SOL_TWO_6
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:00
converted datafile=/tmp/converted/file_TBS_SOL_TWO_7
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:00
converted datafile=/tmp/converted/file_TBS_SOL_TWO_8
channel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 12-APR-10
RMAN>
 
bash-3.00$ ls -ltr
total 6192
 
-rw-r-----   1 oracle   dba    1056768 apr 12 09:49 file_tbs_sol_two_6
-rw-r-----   1 oracle   dba    1056768 apr 12 09:49 file_tbs_sol_two_7
-rw-r-----   1 oracle   dba    1056768 apr 12 09:49 file_tbs_sol_two_8

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.  Here are two options:

One is to use the to platform command and mention the platform name to which the conversion is going to be performed:

RMAN> convert datafile 'c:\test\tbs_lin01.dbf'
2> to platform 'microsoft windows ia (32-bit)'
3> format 'c:\tmp\file_%U';

The other is to use the from platform command and mention the platform name from which the conversion is being performed:

RMAN> convert datafile 'c:\test\tbs_lin01.dbf'
2> from platform 'linux ia (32-bit)'
3> format 'c:\tmp\file_%U';

If the datafile is attempted to be converted on the source host (Linux OS) using the TO PLATFORM clause to the Windows OS, the following error will occur:

RMAN> convert datafile '/u01/oracle/product/10.2.0/db_1/oradata/lindb/tbs_lin01.dbf'
2> to platform 'microsoft windows ia (32-bit)'
3> format '/tmp/converted_to_win_%U';
 
Starting backup at 07-APR-10
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/07/2010 16:45:41
RMAN-06595: platform name 'Microsoft Windows IA (32-bit)' does not matchdatabase platform name 'Linux IA (32-bit)'

 

 

 
 
 
Get the Complete
Oracle Backup & Recovery Details 

The landmark book "Oracle Backup & Recovery: Expert secrets for using RMAN and Data Pump " provides real world advice for resolving the most difficult Oracle performance and recovery issues. Buy it for 40% off directly from the publisher.
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster