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.
-
You must pick a self-contained set of
tablespaces. Verify this using the dbms_tts.transport_set_check
package.
-
Next, generate a transportable tablespace set, using the
Export utility.
-
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.
-
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.
-
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.
-
You must pick a self-contained set of
tablespaces. Verify this using the dbms_tts.transport_set_check
package.
-
Next, generate a transportable tablespace set, using the
Export utility.
-
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.
-
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.
-
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.
|
