Transportable Tablespaces Across Different Platforms (Doc ID 243304 1)

Prévia do material em texto

12/2/21, 11:19 PM Document 243304.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=15p49rhoec_362&id=243304.1 1/7
Copyright (c) 2021, Oracle. All rights reserved. Oracle Confidential.
10g+: Transportable Tablespaces Across Different Platforms (Doc ID 243304.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.2.0.1 [Release 10.1 to 12.2]
 Oracle Database - Standard Edition - Version 10.1.0.2 to 12.2.0.1 [Release 10.1 to 12.2]
 Oracle Database Cloud Schema Service - Version N/A and later
 Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
 Oracle Cloud Infrastructure - Database Service - Version N/A and later
 Information in this document applies to any platform.
 
PURPOSE
Ask Questions, Get Help, And Share Your Experiences With This Article
Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?
Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this
specific article.
 Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support
Community page for Database Datawarehousing.
Introduction
This bulletin explains how tablespaces can now be transported between different OS platforms (cross-platform), as well as
different RDBMS versions. The cross-platform feature is available in 10g onwards. This list is taken from 11.2.0.3; older
RDBMS versions may contain fewer platforms.
SQL> -- This list taken from 11.2.0.3. Older RDBMS versions may contain fewer platforms.
 SQL> -- The list will not contain the platform info for the database from which you are running
the query.
 SQL> col platform_name for a35
 SQL> select * from v$transportable_platform order by platform_id;
 
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 IA (32-bit) Little
 8 Microsoft Windows IA (64-bit) Little
 9 IBM zSeries Based Linux Big
 10 Linux IA (32-bit) Little
 11 Linux IA (64-bit) Little
 12 Microsoft Windows x86 64-bit Little
 13 Linux x86 64-bit Little
 14 Linux x86 32-bit Little
 15 HP Open VMS Little
 16 Apple Mac OS Big
 17 Solaris Operating System (x86) Little
 18 IBM Power Based Linux Big
 19 HP IA Open VMS Little
 20 Solaris Operating System (x86-64) Little
 21 Apple Mac OS (x86-64) Little
SQL> -- You can easily find the platform info for the database running the above query by using
the following SQL:
SQL> SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT
 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
https://community.oracle.com/message/11940630
https://community.oracle.com/community/support/oracle_database/database_datawarehousing
12/2/21, 11:19 PM Document 243304.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=15p49rhoec_362&id=243304.1 2/7
The output of the query can change with version. 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
- 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
DETAILS
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 TBS1 read only;
 Tablespace altered.
3. Export metadata
<HP-UX> exp userid=\'/ as sysdba\' transport_tablespace=y 
 tablespaces=TBS1
 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 TBS1 ...
 . exporting cluster definitions
 . exporting table definitions
 . . exporting table COL_CHG
 . . exporting table DATABASES 
 ....
https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=243304.1&id=371556.1
https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=243304.1&id=579136.1
12/2/21, 11:19 PM Document 243304.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=15p49rhoec_362&id=243304.1 3/7
 . . exporting table 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=TBS1 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=TBS1 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$TRANSPORTABLE_PLATFORM;
 
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
 
File conversion is NOT needed for files thatmeet all three of the following requirements: (1) have a source and target OS
with the same endianness (bitness does not matter), (2) will be imported into an RDBMS version that contains the patch
for unpublished Bug 8973825 (10.2.0.5, or 11.2.0.2 and higher), and (3) do not contain undo and rollback segments. If
the fix for unpublished Bug 8973825 is not available for your target database version, then you need to use the RMAN
convert feature as shown for Case 2 below. 
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"
 
12/2/21, 11:19 PM Document 243304.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=15p49rhoec_362&id=243304.1 4/7
"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/db1/VB2/datafile/tbs1df.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:
<Solaris> rman target=/
 
Recovery Manager: Release 10.2.0.4.0 - 64bit 
 connected to target database: VB2 (DBID=3287908689)
 
RMAN> convert tablespace 'TBS1'
 2> to platform="Linux IA (32-bit)"
 3> db_file_name_convert='/database/db1/VB2/datafile/tbs1df.dbf',
 4> '/tmp/tbs1df.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/db1/VB2/datafile/tbs1df.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/db1/VB2/datafile/tbs1df.dbf'
 TO PLATFORM="Linux IA (32-bit)"
 FROM PLATFORM="HP TRu64 UNIX"
 DB_FILE_NAME_CONVERT="/database/db1/VB2/datafile/", "/tmp/";
5. Move datafiles and export dump file
$ ftp tts.dmp
 +/database/db1/VB2/datafile/tbs1df.dbf (no conversion)
or
/tmp/tbs1df.dbf (converted file if conversion had been required)
6. Import metadata
Note: Users need to be created in the target database first with a default tablespace of an existing tablespace name.
$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y 
 datafiles=/database/db1/VB2/datafile/tbs1df.dbf 
12/2/21, 11:19 PM Document 243304.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=15p49rhoec_362&id=243304.1 5/7
 
(or /tmp/tbs1df.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 "COL_CHG"
...
. . importing table "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/tbs1.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=<source_user>:<target_user> 
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=(<source_tbs1>:<target_tbs1>,<source_tbs2>:<target_tbs2>,...)
7. Set the imported tablespace to READ WRITE
Note: After the tablespaces are read write, you will want to alter your users' default tablespaces to the correct ones.
SQL> alter tablespace reposit read write;
 Tablespace altered.
8. Take a full, no-rows export of the source database and import it into the target database to create the missing
objects that are not transported with TTS, such as sequences, roles, etc.
-- For traditional export, the parameter is ROWS set to N
exp FULL=y GRANTS=y CONSTRAINTS=y ROWS=n
-- For Data pump export, the parameter is CONTENT set to METADATA_ONLY
expdp FULL=y CONTENT=metadata_only
 
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.
See Document 1166564.1 Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues
Use MOS Data Warehousing community to search for similar discussions or start a new discussion on this subject.
https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=243304.1&id=394798.1
https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=243304.1&id=1166564.1
https://community.oracle.com/community/support/oracle_database/database_datawarehousing
12/2/21, 11:19 PM Document 243304.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=15p49rhoec_362&id=243304.1 6/7
The window below is a live discussion of this article (not a screenshot). We encourage you to join the
discussion by clicking the "Reply" link below for the entry you would like to provide feedback on. If you
have questions or implementation issues with the information in the article above, please share that below.
Permission Problem
You don't have permission to do that.
Your Username "User_GD0V5" was auto-generated. Visit your pro�le page to create your personal
Username.
REFERENCES
NOTE:394798.1 - How to Create Transportable Tablespaces Where the Source and Destination are ASM-Based
 NOTE:371556.1 - How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN
https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=243304.1&id=394798.1
https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=243304.1&id=371556.1
12/2/21, 11:19 PM Document 243304.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=15p49rhoec_362&id=243304.1 7/7
Didn't find what you are looking for?
 
NOTE:100693.1 - Getting Started with Transportable Tablespaces
NOTE:733824.1 - How To Recreate A Database Using TTS (Transportable TableSpace)
NOTE:77523.1 - Transportable Tablespaces -- An Example to Setup and Use
NOTE:243245.1 - 10G New Storage Features and Enhancements
https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=243304.1&id=100693.1
https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=243304.1&id=733824.1
https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=243304.1&id=77523.1
https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=243304.1&id=243245.1