Expdp/Impdp Transportable Tablespace Movement across Different OS Platforms in Oracle 9i, 10g, 11g Examples, Advantages

Transportable Tablespace Advantages:
Oracle allows you to transport the tablespaces from one database to other database across different OS platforms. It is a quick and most efficient way of bulk data movement
It is much faster than any other traditional data transfer methods like export import.
Why because it is just plugging of datafiles from source to destination after some set of checking. Also, it avoids the index rebuilds after moving the data to the target database if you are copying the index tablespaces along with data tablespaces.
Oracle 10g Onwards it allows cross OS platform tablespace movement. Prior to 10g It allows only with the same OS platform.
Endian Formats:
Even if your source and target platforms are compatible or same, you won’t be able to transport the tablespace from source platform to target platform directly. Before you start transporting or copying the datafile, you need to check the endian format of the data files in the source and target platform. Endian format refers to the byte ordering in the datafile. It is just the way bytes are stored in the datafile. There are 2 types of endian formats – Big and Small.
If your target platform is not compatible with your source platform in terms of endian format, then you have to make the endian format of all the datafiles belonging to the tablespace you want to transport same. You can accomplish this using RMAN. I will let you know later how to convert the endian format. For now, we will see the platform compatibility and other details.

Example for Transportable tablespace
Step 1. Connect to the database and create a tablespace with name as TEST. You can use your existing tablespace for real time purpose.
SQL> select name from v$database;

NAME
---------
PRODDB9
SQL> create tablespace test datafile '/data/oracle/oradata/proddb9/test.dbf' size 100m;

Tablespace created.

Moving some objects to test tablespace
SQL> alter table scott.emp move tablespace test;

Table altered.
SQL> select tablespace_name, table_name from dba_tables where table_name='EMP';

TABLESPACE_NAME                TABLE_NAME
------------------------------ ----------------------------
TEST                           EMP

Step 2. Check your tablespaces (source & destination) tablepsaces where compatible for movement. There is a data dictionary view to check the ENDIAN_FORMAT compatibility v$transportable_platform. The endian_format must be similar for transportable tablespace movement. In case if it is different you have to convert to the target format which is described belowJ. You can check the platform version from v$database.
col PLATFORM_NAME for a45
set pages 1000
SQL> set lines 120

SQL> l
  1* select * from v$transportable_platform
SQL> /

PLATFORM_ID PLATFORM_NAME                                 ENDIAN_FORMAT
----------- --------------------------------------------- --------------
          1 Solaris[tm] OE (32-bit)                       Big
          2 Solaris[tm] OE (64-bit)                       Big
          7 Microsoft Windows IA (32-bit)                 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 x86 64-bit                              Little
         16 Apple Mac OS                                  Big
         12 Microsoft Windows x86 64-bit                  Little
         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

19 rows selected.
SQL> select platform_name from v$database;

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

Step 3. Check the characterset of the source and destination database and it should be of same.
SQL> select * from nls_database_parameters where parameter like '%CHARA%';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
Step 4. Also check the below mentioned criterias are passed and these are the General requirements.
  • Databases (source & target) must be with Oracle 8i or above
  • Database Version need not be identical
  • System & Sys related tablespaces or objects cannot be transported
  • If you want to transport partitioned table, all the partitions must be included in transportable table set. If you are transporting index, all the tablespace containing respective tables also needs to be transported
  • You can transport the tablespace to a target database only if it has same or higher compatibility settting
Step 5. Check whether the tablespace is self contained using DBMS_TTS.TRANSPORT_SET_CHECK procedure.
PROCEDURE DBMS_TTS.TRANSPORT_SET_CHECK
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TS_LIST                        CLOB                    IN
 INCL_CONSTRAINTS               BOOLEAN                 IN     DEFAULT
 FULL_CHECK                     BOOLEAN                 IN     DEFAULT

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST',TRUE);

PL/SQL procedure successfully completed.

Step 6. Check for any violations from TRANSPORT_SET_VIOLATIONS table.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected


Step 7. Make the tablespace (TEST) read only.
SQL> alter tablespace test read only;
Tablespace altered.
Step 8. Create a export directory for the expdp
create or replace directory exp_dir as '/home/oracle/scott';

Step 9. Export the metadata using expdp utitlity
$ expdp TRANSPORT_TABLESPACES=test directory=exp_dir dumpfile=tt_test.dmp logfile=tt_test.log

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 21 June, 2011 21:13:11

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** TRANSPORT_TABLESPACES=test directory=exp_dir
dumpfile=tt_test.dmp logfile=tt_test.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/COMMENT
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:
  /home/oracle/scott/tt_test.dmp
******************************************************************************
Datafiles required for transportable tablespace TEST:
  /data/oracle/oradata/proddb9/test.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 21:16:14

Step 10. Now convert your datafile(Test tablespaces) to source endian format. If you have the same endian format in target database you just need to copy the source datafile to target. Either way copy the datafile to the target location.
rman target /
RMAN> CONVERT TABLESPACE 'TEST'
2> TO PLATFORM = 'Linux IA (32-bit)'
3> DB_FILE_NAME_CONVERT = '/data/oracle/oradata/proddb9', '/data/oracle/oradata/proddb8';
Starting conversion at source at 21-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00026 name=/data/oracle/oradata/proddb9/test.dbf
converted datafile=/data/oracle/oradata/proddb8/test.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at source at 21-JUN-11
Step 11. Make the tablespace (TEST) read write in source database
SQL> alter tablespace test read write;
Tablespace altered.
Step 12. Rest of the steps are in Target database.  Create the imp_dir in target directory for impdp utility
create or replace directory imp_dir as '/home/oracle/scott';
Step 13. Import the table into target database
$ impdp TRANSPORT_DATAFILES='/data/oracle/oradata/proddb8/test.dbf' directory=imp_dir dumpfile=tt_test.dmp logfile=imptt_test.log

Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 21 June, 2011 21:47:00

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA TRANSPORT_DATAFILES=/data/oracle/oradata/proddb8/test.dbf
directory=imp_dir dumpfile=tt_test.dmp logfile=imptt_test.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 21:47:12
Step 14. Check whether it is properly transported using following methods.
Alert Log:
DM00 started with pid=38, OS id=25879, job SYS.SYS_IMPORT_TRANSPORTABLE_01
Tue Jun 21 21:45:58 2011
DW01 started with pid=39, OS id=25881, wid=1, job SYS.SYS_IMPORT_TRANSPORTABLE_01
Tue Jun 21 21:47:07 2011
DM00 started with pid=28, OS id=25897, job SYS.SYS_IMPORT_TRANSPORTABLE_01
Tue Jun 21 21:47:08 2011
DW01 started with pid=35, OS id=25899, wid=1, job SYS.SYS_IMPORT_TRANSPORTABLE_01
Plug in tablespace TEST with datafile
  '/data/oracle/oradata/proddb8/test.dbf'
SQLPLUS
SQL> set pages 100
SQL> set lines 120

SQL> col SEGMENT_NAME for a40
SEGMENT_NAME                             SEGMENT_TYPE       OWNER
---------------------------------------- ------------------ ------------------------------
EMP                                      TABLE              scott

  1* select tablespace_name, file_name from dba_data_files where tablespace_name ='TEST'
SQL> /

TABLESPACE_NAME                FILE_NAME
------------------------------ ----------------------------------------
TEST                           /data/oracle/oradata/proddb8/test.dbf
DATA PUMP Reference Guide ….

0 comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...
 

dba topics. Copyright 2011-14 All Rights Reserved | Site Map | Contact | Disclaimer