Author: Sanjay Kumar
Purpose
This post explains the best possible way for cross platform Oracle database migration
Method
It depends upon the situation and available resources. There are many ways in which an Oracle database can be migrated across platforms. The most common methods for cross platform migration are:
- Export and Import
- Goldengate/Stream
- Transportable Tablespaces
- Transportable Database
This document will cover Transportable Tablespaces. The Oracle Goldengate and Transportable Database methods will be covered in a future document.
Transportable Tablespaces
Now, I will explain how tablespaces can now be transported from any platform to any platform in 10g, 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 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 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 64-bit for AMD Little 13 Linux 64-bit for AMD Little 15 HP Open VMS Little 16 Apple Mac OS Big
The output of this 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.
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. Check that the tablespace will be self-containe
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
3. Set the tablespace to READ ONLY
SQL> alter tablespace REPOSIT read only;
4. Export metadata
exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=reposit file=tts.dmp log=exp_tts.log statistics=none
5. Check the endianness of the target database and convert, if necessary
Case 1:
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:
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 it will error out see below 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
6. You have to convert the files locally before the import step so that the files are endian compatible
rman target / Recovery Manager: Release 10.1.0.1.0 - 64bit Beta connected to target database: V101B2 (DBID=3287908659)
RMAN> convert tablespace 'REPOSIT' to platform="Linux IA (32-bit)" db_file_name_convert='/database/db101b2/V101B2/datafile/reposit01.dbf', '/tmp/reposit01.dbf';
Starting backup at 24-NOV-03 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 24-NOV-03
The converted datafile is staged in /tmp directory until it is copied to the target server. It is available remotely on the target server after having copied them onto the server.
7. 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)
8. 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.1.0.1.0 - Beta on Mon Nov 24 03:37:20 2003 Export file created by EXPORT:V10.01.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.
9. Set the imported tablespace to READ WRITE
SQL> alter tablespace reposit read write;
Tablespace altered.
Note:
All notes referenced are provided by Oracle support. Please review for the latest updates on Oracle’s support site. Here are the steps:
- Login to My Oracle Support (http://support.oracle.com)
- Search for “Cross Platform Database Conversion”.