OracleTransportableTablespaces (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 you find that the source and target databases have different endian, you need to 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

3. Generate a set of deletable tablespaces

3.1 set the tablespace to read only

Altertablespace IOTEXTBS read only;

3.2 exportmetadata

Mkdir-p/u01/apps/oracle/tts_dir

Create directory tts_dir as '/u01/apps/oracle/tts_dir ';

Expdp system/xiaojundumpfile = tts_iotextbs.dmp directory = tts_dir transport_tablespaces = iotextbslogfile = exp_tts_iotextbs.log

3.3 (this is the same platform, so there is no need to convert it. The following is an example statement)

Rman convert

CONVERT TABLESPACEsales_1, sales_2
To platform 'Microsoft Windows IA (32-bit )'
FORMAT '/tmp/% U ';

4. Transfer the data file and the meta file exported by expdp to the target database server. 5. Place the source tablespace in read write mode.

Altertablespace IOTEXTBS read write;

6. Import metadata to the target database.

Impdpsystem/xiaojundumpfile = tts_iotextbs.dmp directory = tts_dir transport_datafiles =/u01/oinsdir/ottbs01.dbf remap_schema = test: dexter logfile = tts_import.log

Self-contained questions

The following is the test process

Source	Target
Sid	Gg1	Gg2
Schema	Dexter	Dex
Tablespace	Dextbs, dextertbs	Dextbs, dextertbs
Ip	192.168.100.20	192.168.100.21

Sys @ GG1> create tablespace dextbs datafile '/u01/apps/oracle/oradata/gg1/dextbs01.dbf' size 10 m autoextend on next 100 m;

Tablespace created.

Sys @ GG1> create tablespace dextertbs datafile '/u01/apps/oracle/oradata/gg1/dextertbs01.dbf' size 10 m autoextend on next 100 m;

Tablespace created.

Create table test01 tablespace dextbs as selectlevel id, level | 'name' as name from dual connect by level <= 10000;

Create table test02 tablespace dextertbs asselect level id, level | 'name' as name from dual connect by level <= 10000;

Alter table test01modify id primary key;
Alter table test02 add constraint fk_test02_id foreign key (id) references test01 (id );

Only the tablespace in which the primary table is located (because the relation is maintained by the sub-table, the tablespace in which the primary table is migrated separately has no effect)

Sys @ GG1> EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK ('dextbs ', true );
PL/SQL procedure successfully completed.
 

Sys @ GG1> SELECT * FROMTRANSPORT_SET_VIOLATIONS;
VIOLATIONS
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index DEXTER. SYS_C0011314 in tablespace USERS enforces primaryconstraints of table DEXTER. TEST01 in tablespace DEXTBS.

As shown above

The primary key index SYS_C0011314 is not self-contained in the users tablespace.

There are two solutions

1. include users tablespace

2. move the index to the dextbs tablespace.

Sys @ GG1> alter index DEXTER. SYS_C0011314rebuild tablespace dextbs;

Index altered.

Sys @ GG1> EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK ('dextbs ', true );

PL/SQL procedure successfully completed.

Sys @ GG1> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

No rows selected

This indicates that only the primary tablespace dextbs can be migrated without any problems.

We can see that only the tablespace of the sub-table is included.

Sys @ GG1> EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK ('dextertbs ', true );

PL/SQL procedure successfully completed.

Sys @ GG1> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39906: Constraint FK_TEST02_ID between table DEXTER. TEST01 in tablespaceDEXTBS and table DEXTER. TEST02 in tablespace DEXTERTBS.

An error is reported because of the foreign key relationship.

Sys @ GG1> EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK ('dextertbs, dextbs ', true );

PL/SQL procedure successfully completed.

Sys @ GG1> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

No rows selected

OK. Now we can migrate the dextertbs and dextbs tablespaces.

Sys @ GG1> alter tablespace dextbs read only;

Tablespace altered.

Sys @ GG1> alter tablespace dextertbs read only;

Tablespace altered.

Expdpsystem/xiaojun dumpfile = tts_dextbs_dextertbs.dmpdirectory = tts_dir transport_tablespaces = dextbs, dextertbslogfile = exp_tts_dextbs_dextertbs.log

The following content is not detailed. Note:

The user of the target database to import traditional Chinese medicine must exist. Otherwise, an error is reported.