  |
| 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: |
- 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.
- Check that the transportable set is self-contained (there should be no references from objects within the tablespace set to objects outside the set).
- If you will be migrating across platforms, check whether you will need to convert the datafiles for the same endianess.
- Make the tablespaces that will be transported READ ONLY
- Export the metadata for the transportable set
- Copy the datafiles and export file to the destination host.
- Import the metadata and datafiles
- 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:
|
(4) Check if set is self contained.
- Use
DBMS_TTS.TRANSPORT_SET_CHECKto validate the set of tablespaces.
- If any violation if found it will be listed in the
TRANSPORT_SET_VIOLATIONSview. - In this case,
HR.HR_EMPhas a foreign key referencingHR.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
