OracleTransportableTablespaces (TTS)

OracleTransportableTablespaces (TTS)

Oracle Transportable Tablespaces (TTS)

This section describes some precautions for online Oracle table space transfer. Examples are provided.

Principle Analysis

Use copy data file + import metadata to migrate data

Table space can be transferred across platforms

COLUMNPLATFORM_NAME FORMAT A36
SELECT * from v $ TRANSPORTABLE_PLATFORM order by PLATFORM_NAME;

If the source and target databases have different endian, you must use rman convert for conversion. Otherwise, you do not need

Restrictions

The source and target databases must have compatible character sets.

Same Character Set

The character set of the source database must be a subset of the target database. (ALL of the following must be met)

(1) The source database version must be later than 10.1.0.3

(2) The column in the table does not contain the definition information of semantics. The maximum character length is the same as that of the target database.

(3) It does not contain the clob data type, or the character sets of the two databases are the same as single-byte orboth multibyte.

Both databases must have compatible national character sets

Must meet one of the following requirements:

(1) Same National Character Set

(2) source databaseis in version 10.1.0.3 or higher, and there is no data of the NCHAR, NVARCHAR2, or NCLOB type

The destination database cannot contain tablespaces with the same name. (Rename before transmission)

The underlying dependent objects must all be included in the tablespace set.

Use Cases

1. tablespace + partition

2. Back up data to cd

3. copying a read-only table may be critical to multiple databases.

4. Archive historical data

5. Execute the spitr time-point-based tablespace recovery

Online table space transfer workflow (processes)

1. Check the endian format to see if rman convert is needed to convert the endian (skip this step if it is the same platform ).

2. Select a self-contained tablespace set.

3. At the source database end, set the tablespace to read only mode and generate a set of deletable tablespaces. (Export metadatawith transportable tablespace)

4. Transmit a table space set (use scp or other transmission methods to send the metadata and data files exported by expdp to the target server)

5. Restore the tablespace To read write mode (optional)

6. import metadata)

Example 1: Check whether the source and target endian are the same

Test @ ORCL>

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
Bytes -------------------------------------------------------------------------------------------------------------------
Linux x8664-bit Little

Test @ ORCL>

SELECT * from v $ TRANSPORTABLE_PLATFORM ORDER BYPLATFORM_NAME;

PLATFORM_IDPLATFORM_NAME ENDIAN_FORMAT
Bytes ------------------------------------------------------------------------------------------------------------------------------
6 AIX-Based Systems (64-bit) Big
16 Apple MacOS Big
19 hp ia OpenVMS Little
15 HP OpenVMS Little
5 HP Tru64UNIX Little
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
18 IBM Power BasedLinux Big
9 IBM zSeries BasedLinux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
13 Linux x8664-bit Little
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
12 Microsoft Windows x8664-bit Little
17 Solaris Operating System (x86) Little
20 Solaris Operating System (x86-64) Little
1 Solaris [tm] OE (32-bit) Big
2 Solaris [tm] OE (64-bit) Big

19 rows selected.

Linux x86 64-bit all use little endian, so you do not need to use rman convert for conversion.

(Remember to set the read only status of the tablespace before conversion to ensure that the data files are in a consistent state)

2. Select a self-contained tablespace.

Test @ ORCL>

Select t. name, d. name from v $ tablespace t, v $ datafile d where t. ts # = d. ts #;

NAME
Bytes ------------------------------------------------------------------------------------------------------------------------
SYSTEM/u01/apps/oracle/oradata/orcl/system01.dbf
UNDOTBS1/u01/apps/oracle/oradata/orcl/undotbs01.dbf
SYSAUX/u01/apps/oracle/oradata/orcl/sysaux01.dbf
USERS/u01/apps/oracle/oradata/orcl/users01.dbf
EXAMPLE/u01/apps/oracle/oradata/orcl/example01.dbf
IOTTBS/u01/apps/oracle/oradata/orcl/iottbs01.dbf
IOTEXTBS/u01/apps/oracle/oradata/orcl/iotextbs01.dbf
UNDOTBS1/u01/apps/oracle/oradata/orcl/undotbs02.dbf
DEXTBS/u01/apps/oracle/oradata/orcl/dextbs01.dbf
SQLTDBS/u01/apps/oracle/oradata/orcl/sqlttbs01.dbf

10 rows selected.

Transmit the data file corresponding to the iotextbs tablespace

/U01/apps/oracle/oradata/orcl/iotextbs01.dbf

If no output exists, the table space is self-contained.

Sys @ ORCL> EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK ('iotextbs ', true );

PL/SQL procedure successfully completed.

Sys @ ORCL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

No rows selected