Data Movement: Transporting Tablespaces


            Transporting tablespaces

Requirements:
  • Oracle 8i or later: Enterprise Edition required to generate a transportable tablespace set
  • Since 9i: Block size may be different across Databases
  • Since 10g: Tablespaces can be transported across platforms (both databases need to have the COMPATIBLE parameter set to at least 10.0.0)
  • Since 11gR1: You must use DataPump utilitties (expdb, impdp) to transport tablespaces

When to use:
  • To export/import partitions of large tables (see example here)
  • For publishing structured data on CDs
  • Copying multiple read-only version of a tablespace to multiple databases
  • Archive historical data
  • Perform tablespace point-in-time recovery

Procedure for Transporting a tablespace set:
  1. Determine the Transportable Set. A transportable set is formed by the datafiles of all tablespaces being transported plus the export file containing metadata for the entire set.
  2. Check that the transportable set is self-contained (there should be no references from objects within the tablespace set to objects outside the set).
  3. If you will be migrating across platforms, check whether you will need to convert the datafiles for the same endianess.
  4. Make the tablespaces that will be transported READ ONLY
  5. Export the metadata for the transportable set
  6. Copy the datafiles and export file to the destination host.
  7. Import the metadata and datafiles
  8. Make tablespaces READ WRITE


Transporting tablespaces. Example:

(1) Check the endian format of both platforms (if they are different):

Query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine each platform's endian format (byte ordering).
You can check the endian_format for your specific platform:
SQL> select d.platform_name, endian_format
  2  from v$transportable_platform tp, v$database d 
  3  where tp.platform_name = d.platform_name;

PLATFORM_NAME                            ENDIAN_FORMAT
---------------------------------------- --------------
Linux IA (32-bit)                        Little

(2) setup the tablespaces to be transported.
This example create two tablespaces on database DB1. It creates a table and index on the tablespaces to be transported to database DB2.
SQL> Create smallfile tablespace HR1 
  datafile '/u01/app/oracle/oradata/db1/hr1_1.dff' 
  size 5M logging 
  extent management local 
  segment space management auto; 

SQL> Create smallfile tablespace HR2 
  datafile '/u01/app/oracle/oradata/db1/hr2_1.dbf' 
  size 5M reuse autoextend on next 1m maxsize 10m logging 
  extent management local 
  segment space management auto; 

SQL> create table hr.hr_emp
  2  tablespace hr1
  3  as select * from hr.employees;

SQL> create index hr.idx_hr_emp 
     on hr.hr_emp(first_name, last_name)
     tablespace hr2;

SQL> alter table hr.hr_emp
  2  add constraint fk_hr_emp_dept
  3  foreign key (department_id) references hr.departments(department_id);

-- check the location of the created objects.
-- We'll transport HR1 and HR2 between two Oracle 11gr2 running on Linux 32-bit.
SQL>  select owner, segment_name, segment_type, tablespace_name
  2  from dba_segments
  3  where tablespace_name in ('HR1', 'HR2');

OWNER   SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME
------- --------------- ------------------ ------------------------------
HR      HR_EMP          TABLE              HR1
HR      IDX_HR_EMP      INDEX              HR2

(3) Select the self-contained tablespace set.
  • You can only transport a self-contained tablespace set, which means that there may be no references from an object inside the set of tablespaces to another tablespace not included in the set.
  • In our example, because table HR_EMP on tablespace HR1 has an index on tablespace HR2, both tablespaces need to be transported.
  • In addition, since HR.HR_EMP has a foreign key references to HR.DEPARTMENTS (in another tablespace) the third tablespace should also be included in the set. Otherwise a violation will occur.

Violations of self-containement can be caused by:
  • Index inside the set belongs to table outside the set
  • Partitioned tables has a partition outside the set
  • Referential integrity constraint points to table outside the set
  • Table has a LOB column that point to LOBs outside the set






(4) Check if set is self contained.
  • Use DBMS_TTS.TRANSPORT_SET_CHECK to validate the set of tablespaces.
  • If any violation if found it will be listed in the TRANSPORT_SET_VIOLATIONS view.
  • In this case, HR.HR_EMP has a foreign key referencing HR.DEPARTMENTS (in tablespace EXAMPLE). An violation will be found, since we are not including the third tablespace in the transportable set.
SQL> execute dbms_tts.transport_set_check('hr1, hr2', true);
        
PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

VIOLATIONS
----------------------------------------------------------------------------------------------------
ORA-39906: Constraint FK_HR_EMP_DEPT between table HR.DEPARTMENTS in tablespace EXAMPLE and table HR.HR_EMP in tablespace HR1.

So let's drop the foreign key constraint: 

SQL> alter table hr.hr_emp drop constraint fk_hr_emp_dept;
Table altered.

(4) Make tablespaces read only
SQL> alter tablespace hr1 read only;
SQL> alter tablespace hr2 read only;

(5) Invoke data pump as system and export the tablespace in the transportable set.
  • directory: IS the directory objects pointing the directory where you want the export file to be generated.
  • transport_tablespaces: parameter indicating a transportable tablespace export. Only the metadata for the tables and dependent objects within the transportable set are exported.
oracle@quark:~$ expdp system dumpfile=exphr1_2.dmp directory=data_pump_dir transport_tablespaces=hr1, hr2 logfile=tts_exphr1_2.log

Export: Release 11.2.0.1.0 - Production on Thu Oct 07 15:22:51 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=exphr1_2.dmp directory=data_pump_dir transport_tablespaces=hr1, hr2 logfile=tts_exphr1_2.log 
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/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/db1/dpdump/exphr1_2.dmp
******************************************************************************
Datafiles required for transportable tablespace HR1:
  /u01/app/oracle/oradata/db1/hr1_1.dff
Datafiles required for transportable tablespace HR2:
  /u01/app/oracle/oradata/db1/hr2_1.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:24:01 

(6) If the tablespaces are being transported to a different platform, you need to convert the datafiles before copying them.
For example, if the destination database in on a Windows machine, then:
oracle@quark:/$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 07 15:31:55 2010

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

connected to target database: DB1 (DBID=64992127)

RMAN> convert tablespace hr1, hr2
2> to platform 'Microsoft Windows IA (32-bit)' 
3> format '/tmp/%U'; 

Starting conversion at source at 07-Oct-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00007 name=/u01/app/oracle/oradata/db1/hr1_1.dff
converted datafile=/tmp/data_D-DB1_I-54881118_TS-HR1_FNO-6_01mrt1cn
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00008 name=/u01/app/oracle/oradata/db1/hr2_1.dbf
converted datafile=/tmp/data_D-DB1_I-54881118_TS-HR2_FNO-7_02mrt1co
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 07-OCT-10

(7) Transport the Tablespace set
  • Step 1: Copy the export file to the directory pointed by the DATA_PUMP_DIR directory object
  • Step 2: Copy the data files to the location of the existing datafiles of the destination database.

(8) Import the tablespaces
oracle@proton:$ impdp system dumpfile=exphr1_2.dmp directory=data_pump_dir transport_datafiles=/u01/app/oracle/oradata/DB2/hr1_1.dff, /u01/app/oracle/oradata/DB2/tts/hr2_1.dbf remap_schema=hr:crm1 logfile=tts_import.log

Import: Release 11.2.0.1.0 - Production on Thu Oct 07 17:18:56 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=exphr1_2.dmp directory=data_pump_dir transport_datafiles=/u01/app/oracle/oradata/DB2/hr1_1.dff, /u01/app/oracle/oradata/DB2/hr2_1.dbf remap_schema=hr:crm1 logfile=tts_import.log 
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/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 17:19:16

-- Check the imported tablespaces
SQL> select owner, segment_name, segment_type, tablespace_name
  2  from dba_segments
  3* where tablespace_name in ('HR1' , 'HR2');

OWNER    SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME
-------- -------------------- ------------------ ------------------------------
CRM1     HR_EMP               TABLE              HR1
CRM1     IDX_HR_EMP           INDEX              HR2

No comments:

Post a Comment