10g : Transportable Tablespaces Across Different Platforms [ID 243304.1]

		Modified 19-SEP-2011 Type BULLETIN Status PUBLISHED

Applies to:

Oracle Server - Enterprise Edition
Information in this document applies to any platform.

Purpose

This bulletin explains how tablespaces can now be transported from any platform.
to any platform. in 10g and onwards, provided the platforms belong to the list below:

SQL> select * from v$transportable_platform;

   PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT
   ----------- ------------------------------   --------------
             1 Solaris[tm] OE (32-bit)          Big
             2 Solaris[tm] OE (64-bit)          Big
             7 Microsoft Windows NT             Little
            10 Linux IA (32-bit)                Little
             6 AIX-Based Systems (64-bit)       Big
             3 HP-UX (64-bit)                   Big
             5 HP Tru64 UNIX                    Little
             4 HP-UX IA (64-bit)                Big
            11 Linux IA (64-bit)                Little
            15 HP Open VMS                      Little
             8 Microsoft Windows IA (64-bit)    Little
             9 IBM zSeries Based Linux          Big
            13 Linux 64-bit for AMD             Little
            16 Apple Mac OS                     Big
            12 Microsoft Windows 64-bit for AMD Little

The output of the query can change. So please use the query above to find the
current support platforms.
In previous releases, the transportable tablespace feature allowed the transfer
between platforms of the same architecture only.

Scope and Application

- Publish structured data and distribute for integration on other platforms
- Distribute data from a DW environment to data marts (typically different platforms)
- Share read only tablespaces across heterogeneous clusters
- Migrate a database from one platform. to another by only rebuilding the catalog
and transporting the datafiles

10g : Transportable Tablespaces Across Different Platforms

Steps

1. Check for restrictions

Review the "Limitations on Transportable Tablespace Use" section in Note 371556.1.
Among other things, objects that reside in the SYSTEM tablespace and objects owned
by SYS will not be transported. This includes but is not limited to users, privileges,
PL/SQL stored procedures, and views.

If you use spatial indexes, apply the solution in Note 579136.1 "IMPDP TRANSPORTABLE
TABLESPACE FAILS for SPATIAL INDEX)" before continuing.

2. Prepare the database

Check that the tablespace will be self-contained

SQL>execute sys.dbms_tts.transport_set_check('TBS1,TBS2', true);
SQL> select * from sys.transport_set_violations;

==>These violations must be resolved before the tablespaces can be transported
Set the tablespace to READ ONLY

SQL> alter tablespace REPOSIT read only;
Tablespace altered.

3. Export metadata

exp userid=\'/ as sysdba\' transport_tablespace=y
tablespaces=reposit
file=tts.dmp log=exp_tts.log
statistics=none

Export: Release 10.2.0.4.0 - Mon Nov 26 11:49:49 2007
...

Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace REPOSIT ...
. exporting cluster definitions
. exporting table definitions
. . exporting table MTG_COL_DEP_CHG
. . exporting table MTG_DATABASES
....
. . exporting table SYBASE11_SYSUSERS
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

Review the export log for warnings and errors and resolve issues
before continuing. Failure to do so can result in data loss.

Datapump can be used for that purpose too:

expdp \'/ as sysdba\' directory=tts_dump dumpfile=tts1_dp.dmp logfile=tts_dump_log:tts.log
transport_tablespaces=tts_1,tts_2 transport_full_check=y

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02": system/******** directory=tts_datafile dumpfile=tts1.dmp logfile=tts_dump_log:tts.log transport
_tablespaces=tts_1,tts_2 transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully loaded/unloaded
***********************************************************************Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_02 is:
+DATA/tts1.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully completed at 14:00:34

Movement of data and Enabling TTS

4. Check the endianness of the target database and convert, if necessary

Case 1: Same Endianness (Big->Big or Little->Little) The source platform. is Sun SPARC Solaris: endianness Big
The target platform. is HP-UX (64-bit): endianness Big

SQL> select PLATFORM_ID , PLATFORM_NAME from v$database;

          PLATFORM_ID PLATFORM_NAME
          ----------- ------------------------------
                    3 HP-UX (64-bit)

No conversion is needed for files that (1) do NOT contain UNDO/Rollback
segments and (2) have a source and target OS with the same endianness.
Refer to Note.415884.1 "Cross Platform. Database Conversion
with same Endian" to determine which files contain UNDO/Rollback segments.
Case 2: Different Endianness (Big->Little or Little->Big) The source platform. is Microsoft WIndows NT: endianness Little
The target platform. is HP-UX (64-bit): endianness Big

If we move the files and import the tablespace:

. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 1565:
"BEGIN sys.dbms_plugts.beginImpTablespace('TBS_TTS',37,'SYS',1,0,8192,2,57"
"54175,1,2147483645,8,128,8,0,1,0,8,462754339,1,1,5754124,NULL,0,0,NULL,NULL"
"); END;"
IMP-00003: ORACLE error 1565 encountered
ORA-01565: error in identifying file '/database/db101b2/V101B2/datafile/reposit01.dbf'
ORA-27047: unable to read the header block of file
HP-UX Error: 2: No such file or directory
Additional information: 2
ORA-06512: at "SYS.DBMS_PLUGTS", line 1540
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully

You have to convert the files; the files can be converted on source OR on target :
--> locally on the SOURCE before the import step so that the files are endian compatible:

rman target=/

Recovery Manager: Release 10.2.0.4.0 - 64bit
connected to target database: V101B2 (DBID=3287908659)

RMAN> convert tablespace 'REPOSIT'
2> to platform="Linux IA (32-bit)"
3> db_file_name_convert='/database/db101b2/V101B2/datafile/reposit01.dbf',
4> '/tmp/reposit01.dbf';

Starting backup at 26-NOV-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=8 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/database/db101b2/V101B2/datafile/reposit01.dbf
converted datafile=/tmp/reposit01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 26-NOV-07

The converted datafile is staged in /tmp directory until it is copied to the target server.

--> remotely on the target server after having copied them on the server.

Conversion on target platform. is a way forward when the v$transportable_platform. of the source does Not list the target platform.
When conversion is done on the target platform. then CONVERT DATAFILE is used instead of CONVERT TABLESPACE, ie:

RMAN> CONVERT DATAFILE
'/database/db101b2/V101B2/datafile/reposit01.dbf'
TO PLATFORM="Linux IA (32-bit)"
FROM PLATFORM="HP TRu64 UNIX"
DB_FILE_NAME_CONVERT="/database/db101b2/V101B2/datafile/", "/tmp/";

5. Move datafiles and export dump file

$ftp tts.dmp
+
/database/db101b2/V101B2/datafile/reposit01.dbf (no conversion)

or

/tmp/reposit01.dbf (converted file if conversion had been required)

6. Import metadata

$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y
datafiles=/database/db101b2/V101B2/datafile/reposit01.dbf
(or /tmp/reposit01.dbf )
file=tts.dmp log=imp_tts.log

Import: Release 10.2.0.4.0 - on Mon Nov 26 03:37:20 2007

Export file created by EXPORT:V10.02.00 via conventional path
About to import transportable tablespace(s) metadata...
...
. importing SYS's objects into SYS
. importing OMWB's objects into OMWB
. . importing table "MTG_COL_DEP_CHG"
...
. . importing table "SYBASE11_SYSUSERS"
Import terminated successfully without warnings.

Review the import log for warnings and errors and resolve issues
before continuing. Failure to do so can result in data loss.

If we exported with DataPump, import must be done with that same tool:

impdp \'/ as sysdba\' directory=tts_dump dumpfile=tts1_dp.dmp logfile=tts_dump_log:tts.log
transport_datafiles='/database/oradata/tts1_db1.dbf','/database/oradata/tts2_db1.dbf'

It's not possible to import when tablespace already exists or when target schema is not created.
If users don't exist, DataPump provides an alternative by using remap_schema (for import utility
we can create the schema), ie:

REMAP_SCHEMA=:

If tablespace already exists in target, we can use remap_tablespace
parameter on impdp (there is no option in import but rename tablespace at source or the existing
one at target).

REMAP_TABLESPACE=(:,:,...)

7. Set the imported tablespace to READ WRITE

SQL> alter tablespace reposit read write;
Tablespace altered.

Still have questions

In case you need to move an ASM tablespace, the steps are quite similar and Note 394798.1 can help as it describes the full process.
Use MOS Data Warehousing community to search for similar discussions or start a new discussion on this subject.

@RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN

References

NOTE:100693.1 - Getting Started with Transportable Tablespaces
NOTE:243245.1 - 10G New Storage Features and Enhancements
NOTE:371556.1 - How to Move Tablespaces Across Platforms Using Transportable Tablespaces With RMAN
NOTE:394798.1 - How to Create Transportable Tablespaces Where the Source and Destination are ASM-Based
NOTE:413586.1 - How To Use RMAN CONVERT DATABASE on Source Host for Cross Platform. Migration
NOTE:579136.1 - Impdp Transportable Tablespace Fails When Having A Spatial Index
NOTE:77523.1 - Transportable Tablespaces -- An Example to Setup and Use

ITPub博客

10g : Transportable Tablespaces Across Different Platforms [ID 243304.1]