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 


 

 

 


 

 

 
 

Transportable Tablespaces Tips

Expert Oracle Tips by Burleson Consulting


Question:  Can you please explain how Transportable tablespaces work.  I want to use Transportable tablespaces and I need to see how to use Transportable tablespaces across platforms.

Answer:  Oracle Transportable tablespaces are a useful tool because all of the metadata for the tablespace is self-contained, making it easy to copy the tablespace from one database to another database.

Transportable Tablespace gives the grid users a very fast and efficient mechanism to move large data from one database system to another. Another feature that is particularly useful for large enterprises is the facility of mounting read-only tablespace by two or more databases.
With 10g release, it becomes possible to move the transportable tablespace across different platforms or operating systems. Another new feature is the provision of big tablespaces, which support ultra-large tablespaces to store the objects.

A transportable tablespace allows the quick movements of a subset of an Oracle database from one Oracle database to another.  However, in the previous release of Oracle server, it was only possible to move a tablespace across Oracle databases within the same platform. Oracle 10g is going one step further by allowing the movement of tablespace across different platforms. This feature becomes particularly significant in the Grid framework where many different operating systems coexist and conduct data movements and data sharing.

The limitation requiring transportable tablespaces to be transferred to the same operating system has been removed. However, to transport tablespaces across different platforms, both the source and target databases must be at least on Oracle Database 10g, be on at least version 10.0.1, and have the compatible initialization parameter set to 10.0. Using the new cross-platform transportable tablespace method to move data is more efficient than the traditional method of export and import.

Oracle Database 10g supports many platforms for transportable tablespace. A new data dictionary view, v$transportable_platform, lists all the supported platforms, along with platform ID and endian format.

The v$database data dictionary view also adds two columns, platform ID and platform name, both for transportable tablespaces:

select
   name,
   platform_id,
   platform_name
from
   v$database;

NAME      PLATFORM_ID     PLATFORM_NAME
-------   -----------     -----------------------
GRID                2     Solaris[tm] OE (64-bit)

For example, it is not necessary to convert the data file to transport a tablespace from an AIX-based platform to a Sun platform, since both platforms use a big endian format. However, to transport a tablespace from a Sun Platform (big endian) to a Linux Platform (little endian), the CONVERT command in the RMAN utility should be used to convert the byte ordering.  This can be done on either the source platform or the target platform.

If you are staying on the same server, you can upgrade from an older to a new release with about 5 minutes of downtime with transportable tablespaces.  To do this, you will be using transportable tablespaces, and the only requirement is that all your tablespaces are locally managed.

1) Install the new release to a separate Oracle Home on the same server

2) Create the new release with only the base tablespaces: SYSTEM, SYSAUX, UNDO, and TEMP

3) On the old database, put all your tablespaces into read only mode (write downtime begins)

4) Perform a transportable tablespace export of all non-system tablespaces (as a sysdba user)

5) Shut down the old database (true downtime begins)

6) Start up the new database

7) Perform a transportable tablespace import into the new database (end true downtime)

8) Make all your tablespaces read/write (end write downtime)

See these related notes on transportable tablespaces:


Transportable Tablespaces

by Mike Ault

A transportable tablespace allows you to quickly move a subset of an Oracle database from one Oracle database to another. However, in the previous release of Oracle server, you can only move a tablespace across Oracle databases within the same platform.

Oracle 10g is going one step further by allowing you to move tablespace across different platforms.

Benefits

One of the major benefits for organizations that hosts Oracle databases on different platforms is that data can now be moved between databases quickly, across different platforms. Using the new cross-platform transportable tablespaces method to move data is more efficient than the traditional method of export and import.

Supported Platforms and New Data Dictionary Views

Oracle Database 10g supports nine platforms for transportable tablespace.

A new data dictionary view, v$transportable_platform, lists all nine supported platforms, along with platform ID and endian format.

PLATFORM_ID

PLATFORM_NAME

ENDIAN_FORMAT

1

Solaris[tm] OE (32-bit)

Big

2

Solaris[tm] OE (64-bit)

Big

3

HP-UX (64-bit)

Big

4

HP-UX IA (64-bit)

Big

5

HP Tru64 UNIX

Little

6

AIX-Based Systems (64-bit)

Big

7

Microsoft Windows NT

Little

8

Linux IA (32-bit)

Little

9

Linux IA (64-bit)

Little

Table 3.3: Supported platforms for transportable tablespaces.

The v$database data dictionary view also adds two columns, platform ID and platform name:

SQL> select name, platform_id,platform_name
   2 from   v$database;
NAME      PLATFORM_ID     PLATFORM_NAME

-------   -----------     -----------------------
GRID                    2     Solaris[tm] OE (64-bit)

To transport a tablespace from one platform to another, datafiles on different platforms must be in the same endian format (byte ordering).

The pattern for byte ordering in native types is called endianness. There are only two main patterns, big endian and little endian. Big endian means the most significant byte comes first, and little endian means the least significant byte comes first. If the source platform and the target platform are of different endianness, then an additional step must be taken on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.

Be aware of the following limitations as you plan for transportable tablespace use:

  • The source and target database must use the same character set and national character set.
  • You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
  • The set should be self-containing

Convert Datafiles using RMAN

You do not need to convert the datafile to transport a tablespace from an AIX-based platform to a Sun platform, since both platforms use a big endian.

However, to transport a tablespace from a Sun platform (big endian) to a Linux platform (little endian), you need to use the CONVERT command in the RMAN utility to convert the byte ordering. This can be done on either the source platform or the target platform.

RMAN> CONVERT TABLESPACE ‘USERS'
      TO PLATFORM = ‘Linux IA (32-bit)'
      DB_FILE_NAME_CONVERT = ‘/u02/oradata/grid/users01.dbf', ‘/dba/recovery_area/transport_linux'

The limitation requiring transportable tablespaces to be transferred to the same operating system has been removed. However, to transport tablespaces across different platforms, both the source and target databases must be at least on Oracle Database 10g, be on at least version 10.0.1, and have the COMPATIBLE initialization parameter set to 10.0.

Transporting Tablespaces Between Databases: A General Procedure

Perform the following steps to move or copy a set of tablespaces.

  1. You must pick a self-contained set of tablespaces. Verify this using the dbms_tts.transport_set_check package.
     

  2. Next, generate a transportable tablespace set, using the Export utility.
     

  3. A transportable tablespace set consists of the set of datafiles for the set of tablespaces being transported and an Export file containing metadata information for the set of tablespaces.
     

  4. Transporting a tablespace set to a platform different from the source platform will require connection to the Recovery Manager (RMAN) and invoking the CONVERT command.  An alternative is to do the conversion on the target platform after the tablespace datafiles have been transported.
     

  5. The final step is to plug in the tablespace - You use the Import utility to plug the set of tablespaces metadata, and hence the tablespaces themselves, into the target database.

If you are transporting these tablespaces to a different platform, use the v$platform view to find the platform name. You can then use the Recovery Manager CONVERT command to perform the conversion.

Note - As an alternative to conversion before transport, the CONVERT command can be used for the conversion on the target platform after the tablespace set has been transported.

The limitation requiring transportable tablespaces to be transferred to the same operating system has been removed. However, to transport tablespaces across different platforms, both the source and target databases must be at least on Oracle Database 10g, be on at least version 10.0.1, and have the COMPATIBLE initialization parameter set to 10.0.

Transporting Tablespaces Between Databases: A General Procedure

Perform the following steps to move or copy a set of tablespaces.

  1. You must pick a self-contained set of tablespaces. Verify this using the dbms_tts.transport_set_check package.
     

  2. Next, generate a transportable tablespace set, using the Export utility.
     

  3. A transportable tablespace set consists of the set of datafiles for the set of tablespaces being transported and an Export file containing metadata information for the set of tablespaces.
     

  4. Transporting a tablespace set to a platform different from the source platform will require connection to the Recovery Manager (RMAN) and invoking the CONVERT command.  An alternative is to do the conversion on the target platform after the tablespace datafiles have been transported.
     

  5. The final step is to plug in the tablespace - You use the Import utility to plug the set of tablespaces metadata, and hence the tablespaces themselves, into the target database.

If you are transporting these tablespaces to a different platform, use the v$platform view to find the platform name. You can then use the Recovery Manager CONVERT command to perform the conversion.

Note - As an alternative to conversion before transport, the CONVERT command can be used for the conversion on the target platform after the tablespace set has been transported.

 

 


 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% 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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster