Transportable Tablespaces

---

Almost done with this crash course in RDBMS HA functionality, and we wanted to discuss one more tool for your consideration. Transportable Tablespaces (TTS) allows you to copy a set of datafiles for a tablespace on one database and plug that tablespace into a different database. The time it takes to transfer data is limited to how long it takes to move the datafiles to the target database via ftp, copy, and so forth.

The core functionality serves multiple purposes. The most obvious usage of Transportable Tablespaces is for warehousing data from an OLTP system to a DSS system. Another use would be to publish data from a central database to multiple smaller databases that need certain fact tables, or to publish data to customers that use Oracle for data storage. TTS can also be used for forms of point-in-time-recovery, to create test copies of data sets, or to migrate data to a new database without using export/import utilities.

The Overview

Transporting a tablespace from one database to another is relatively simple, although you will want to become familiar with the processes before integrating TTS into your production database environment.

Determine what the transport set will be. The transport set refers to the set of tablespaces that you will move together, such as a data tablespace and the index tablespace that corresponds to the data. You use the package DBMS_TTS to make sure that a tablespace set qualifies for transportation. After executing the procedure, you must check a view to see if there are any violations:

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('WDSCRORD_SEP_2003, WDSCRORD_OCT_2003, WDSCRORD_NOV_2003, WDSCRORD_DEC_2003', TRUE); SELECT * FROM TRANSPORT_SET_VIOLATIONS;

After determining if there are any violations, you must turn the tablespaces into read-only tablespaces. This is required in order to confirm that no DML operations (inserts, updates, or deletes) are occurring while we copy the underlying datafiles.

After setting the tablespaces to read-only, you export the metadata for the tablespaces. You can use the new export data pump utility to do this export. The metadata that gets exported will be used to identify the tablespace and its contents to the target database.

After the export is taken, you can copy the datafiles to the new location, by whatever methodology best suits you. After the file copy has been completed, you can put the tablespace set back in read-write mode at the source database, and the work at the source is complete.

At the target database, after you move the copied datafiles into position, you just have to import the metadata from the export you created at the source. Then, if necessary, you convert the new tablespaces to read-write mode, and you are finished.

A Few Restrictions

There are a few restrictions on what tablespaces can qualify for transportability that you should be aware of, since they will determine whether you want to employ TTS in your environment.

• You cannot transport the SYSTEM tablespace or any of its contents. This means that you cannot use TTS for PL/SQL, triggers, or views. These would have to be moved with export.

• The source and target database must have the same character set and national language set.

• You cannot transport a table with a materialized view unless the mview is in the transport set you create.

• You cannot transport a partition of a table without transporting the entire table.

Cross-Platform Transportable Tablespaces

Transportable Tablespace functionality has existed since 8i, but there is a revolutionary upgrade that has occurred in Oracle Database 10g. Now, TTS can transport tablespaces across different platforms. In the past, TTS required that the source and target database both be the same operating system; now, TTS can pass the datafiles between different platforms without blinking (okay, sometimes, it requires a blink).

There are a limited number of operating systems that allow cross-platform transportation. You can view the list by querying any Oracle Database 10g database:

COLUMN PLATFORM_NAME FORMAT A30 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           7 Microsoft Windows NT           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 

Note the column ENDIAN_FORMAT. The endianness of the platform determines how many steps will be required during transportation to make the transition to a new platform. If the endianness is the same between the two platforms, then transporting a tablespace between platforms is the same as doing it on matching platforms. However, if the endian_format is different, you will have to convert the datafiles to the new endian_format before you can plug them into the new platform. The conversion steps require that you use RMAN to alter the datafiles.

Other than the endian format, the only other new restriction is that you can only transfer Oracle Database 10g datafiles between platforms. Otherwise, the restrictions for same-platform TTS apply.

HA Workshop: Transport a Tablespace from Solaris to Linux

Workshop Notes

This workshop will outline how to transport a single partitioned tablespace from the woodscrew_orders table from Oracle Database 10g on Solaris to Oracle Database 10g running on Linux. As we noted, you cannot transport a single partition of a table without transporting the entire table. Therefore, we will need to exchange the partition with a stand-alone table temporarily, so that the partition becomes its own table. Then, we can move forward with the TTS process.

Step 1.  Create a temporary table for the partition exchange.

create tablespace ws_sep_trans datafile    'u01/product/oracle/oradata/orcl/ws_sep_trans01.dbf' size 50m; create table woodscrew_orders_sep ( ord_id            number not null, ord_date          date, cust_id           number not null, scr_id            number not null, ord_cnt           number, warehouse_id      number not null, region            varchar2(20), constraint pk_woodscrew_orders_sep primary key (ord_id, ord_date) using index tablespace ws_app_idx) tablespace ws_sep_trans;

Step 2.  Exchange the partition with the table.

alter table woodscrew_orders exchange partition wdscrord_sep_2003 with table woodscrew_orders_sep; 

Step 3.  Confirm that the stand-alone table in the WDSCRORD_SEP_2003 tablespace qualifies for transportation. Note that Oracle does not actually move any data in the partition exchange-it simply swaps data dictionary information. Therefore, the partition is now in our new tablespace, WS_SEP_TRANS, but that is not the one being moved. The tablespace we will actually move is the original tablespace named WDSCRORD_SEP_2003 (which happens to be the same name as our original partition), because that tablespace now contains our stand-alone table.

connect / as sysdba; EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('WDSCRORD_SEP_2003', TRUE); SELECT * FROM TRANSPORT_SET_VIOLATIONS;

Note that with the way we have set up our examples, there is a constraint violation when we select from TRANSPORT_SET_VIOLATIONS, as our index is in a tablespace that is not in our transport set. We can overcome this by choosing not to export constraints when we do the actual export (as you will see in Step 5).

Step 4.  Set the tablespace to read-only.

alter tablespace wdscrord_sep_2003 read only;

Step 5.  Export the metadata. Note that we specify no constraints. We will have to rebuild the primary key on the table at the new database.

exp file=/u01/product/oracle/oradata/orcl/ws_sep_dat.dmp transport_tablespace=y    constraints=n tablespaces= wdscrord_sep_2003 Export: Release 10.1.0.1.0 - Beta on Mon Dec 29 11:15:02 2003 Copyright (c) 1982, 2003, Oracle.  All rights reserved. Username: / as sysdba

Step 6.  Use RMAN to convert the datafile to little endian for transfer to Linux.

convert tablespace wdscrord_sep_2003 to platform 'Linux IA (32-bit)' FORMAT ='/u01/product/oracle/oradata/orcl/wscrord_sep_2003_for_LNX.dbf';

Step 7.  Move the datafile to the Linux system. You can use any type of file transfer that matches your needs for speed and ease of use. We used a binary ftp,  renaming the datafile upon transfer (from wscrord_sep_2003_for_LNX.dbf to wscrord_sep_2003.dbf).

Step 8.  On the source, set the tablespace to read-write and then exchange the partition back into place. You will need to revalidate the indices for the table.

Connect / as sysdba alter tablespace wdscrord_sep_2003 read write; connect ws_app/ws_app alter table woodscrew_orders exchange partition wdscrord_sep_2003 with table woodscrew_orders_sep;

Step 9.  On the target Linux system, import the metadata for the datafile.

imp file=/u01/product/oracle/oradata/orcl/ws_sep_dat.dmp transport_tablespace=y tablespaces=wdscrord_sep_2003 datafiles='/u01/product/oracle/oradata/orcl/wscrord_sep_2003.dbf' tts_owners=(ws_app)

Step 10.  Set the new tablespace to read-write.

Connect / as sysdba alter tablesapce wedscrord_sep_2003 read write;

---