Cross Platform Transportable Tablespace Migration in Oracle 11g

Transportable Technologies

Transportable Database 

As explained in "Overview of Cross-Platform Data Transport Using Image Copies", you can use the RMAN CONVERT DATABASE command to automate the copying of an entire database from one platform to another. You convert the database data files on either the source or destination platforms.

Before converting the database, see the list of CONVERT DATABASE command prerequisites described in Oracle Database Backup and Recovery Reference. Confirm that you meet all these prerequisites before attempting the procedure in this section.

One prerequisite is that both the source and destination platform must share the same endian format. For example, you can transport a database from Microsoft Windows to Linux for x86 (both little-endian), or from HP-UX to AIX (both big-endian), but not from HP-UX to Linux for x86 (big-endian to little-endian).

Transportable Tablespace

Transportable Tablespaces is a feature designed to move a subnet of one database into another, eve among platform that differ in endian format. The cross-platform capability of transportable tablespace can be used to migrate all user data within a database to new platform with a different endian format
leveraging transportable table spaces in this manner permits a minimal downtime migration to a new platform by a avoiding the time consuming method of unloading all user data from source database and loading it into the target database.

Source Server:

------------------

Solaris 11 - Sparc 64-bit
Database 11.2.0.4


SQL> select d.platform_name,endian_format from v$transportable_platform tp, v$database d WHERE tp.platform_name=d.platform_name;

PLATFORM_NAME
--------------------------------------------------------------------------------
ENDIAN_FORMAT
--------------
Solaris[tm] OE (64-bit) Big


SQL>

Target Server
----------------
Oracle Linux 6 32-bit (x86_64)
Database 11.2.0.4




SQL> select d.platform_name,endian_format from v$transportable_platform tp, v$database d WHERE tp.platform_name=d.platform_name;

PLATFORM_NAME
--------------------------------------------------------------------------------
ENDIAN_FORMAT
--------------
Linux x86 64-bit Little


SQL>

Source Database

We plan to move to three tablespaces EMP,EMP1,USERS

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EMP
EMP1

7 rows selected.

SQL>

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK('USERS,EMP,EMP1',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL>

Generate a Transportable tablespace set

SQL> create directory dump_dir as '/ora/export';

SQL>grant read,write on directory dump_dir to system

SQL>alter tablespace users read only;

SQL>alter tablespace emp1 read only;

SQL>alter tablespace emp read only;

Invoke the data pump export utility on the host system an specify which tablespaces are in the trasnportable set.

oracle@afi-ssaerp:~/app/oracle/product/11.2.0/dbhome1104/bin$ ./expdp system/afi123 DUMPFILE=expdat.dmp DIRECTORY=dump_dir TRANSPORT_TABLESPACES=users,emp,emp1 logfile=expdat.log

Export: Release 11.2.0.4.0 - Production on Mon Dec 26 14:08:03 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** DUMPFILE=expdat.dmp DIRECTORY=dump_dir TRANSPORT_TABLESPACES=users,emp,emp1 logfile=expdat.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /ora/export/expdat.dmp
******************************************************************************
Datafiles required for transportable tablespace EMP:
  /ora/app/1104/oracle/oradata/oratest/emp.pdf
Datafiles required for transportable tablespace EMP1:
  /ora/app/1104/oracle/oradata/oratest/emp1.pdf
Datafiles required for transportable tablespace USERS:
  /ora/app/1104/oracle/oradata/oratest/users01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Dec 26 14:08:55 2016 elapsed 0 00:00:50

oracle@afi-ssaerp:~/app/oracle/product/11.2.0/dbhome1104/bin$

collect the required  users,roles,grants,profile to create again in the target database.



oracle@afi-ssaerp:~/app/oracle/product/11.2.0/dbhome1104/bin$ ./expdp system/afi123 DUMPFILE=exp_meta_dmp.dmp DIRECTORY=dump_dir full=y include=USER,ROLE,ROLE_GRANT,PROFILE content=METADATA_ONLY

Export: Release 11.2.0.4.0 - Production on Mon Dec 26 14:14:37 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** DUMPFILE=exp_meta_dmp.dmp DIRECTORY=dump_dir full=y include=USER,ROLE,ROLE_GRANT,PROFILE content=METADATA_ONLY
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /ora/export/exp_meta_dmp.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Mon Dec 26 14:14:52 2016 elapsed 0 00:00:14

oracle@afi-ssaerp:~/app/oracle/product/11.2.0/dbhome1104/bin$

Convert before transporting the tablespace  set and then convert the datafiles composing the users and emp,emp1 tablespaces.

RMAN convert command is used to do the conversion. Start RMAN and connect to the target database.

$ RMAN target /

RMAN> convert tablespace 'users','emp','emp1' to platform 'Linux IA (32-bit)'

Copy all export move to destination server.

Import folder we have to create.

$ mkdir /ora/import

$chmod 700 /ora/import

$ scp *.* 192.0.0.45:/ora/import

Convert the database files in target machine

$ rman target /

RMAN> convert database '/ora/import/ data_D-ORATEST_I-3466644995_TS-EMP1_FNO-6_01ro4k07' format "/ora/oracle11/app/oracle/oradata/test/users.dbf";

Same steps we have to do to other data files.

import the tablespace set



$./impdp system/afi123 directory=dump_dir dumpfile=exp_meta.dmp.dmp full=y

If above command show error like in my case one users was missing. So I created that missing user in destination server after that I run the above.



 ./impdp system/afi123 DIRECTORY=dump_dir DUMPFILE=expdat.dmp transport_datafiles='/ora/oracle11/app/oracle/oradata/test/emp1.dbf','/ora/oracle11/app/oracle/oradata/test/emp.dbf','/ora/oracle11/app/oracle/oradata/test/user011.dbf'


Same steps u have to do it for other datafiles as well.


Verify the tablespace from destination server.





SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS100
TEMP
EMP
EMP1
USERS

8 rows selected.

SQL>

SQL> alter tablespace users read write;

SQL> alter tablespace emp read write;

SQL> alter tablespace emp1 read write;