传输表空间

最新推荐文章于 2024-05-07 11:35:13 发布

juliaputao

最新推荐文章于 2024-05-07 11:35:13 发布

阅读量624

点赞数

分类专栏： DATABASE

DATABASE 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

可传输表空间概述

Oracle 的可传输表空间特性通过将元数据和数据文件简单地从一个数据库移动到另一个数据库，提供在数据库之间有效移动大数据的一种简易方法。

字节顺序和平台

数据文件所以不能跨平台，主要是由于不同平台的字节顺序不同，目前在各种体系的计算机中通常采用的字节存储机制主要有两种：Big-Endian和Little-Endian 。
一些操作系统（包括Windows）在低位内存地址中存放二进制数据的最低有效字节，因此这种系统被称为Little Endian；一些操作系统（包括Solaris）将最高有效字节存储在低位内存地址中，因此这种系统被称为Big Endian。
举一个简单点的例子，假如1122这样一个数据要存入不同系统，对于Little Endian的系统，存储的顺序就是2211，小头在前；而对于Big Endian的系统来说，存储顺序就是1122，大头在前，显然Big Endian更符合我们通常的语言习惯。
那么跨平台的问题就出现了，当一个Little Endian的系统试图从一个Big Endian的系统中读取数据时，就需要通过转换，否则不同的字节顺序将导致数据不能被正确读取。

数据库所处平台的字节序可通过如下查询得到

SQL> 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 IA (32-bit)									  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
	 15 HP Open VMS 											  Little
	  8 Microsoft Windows IA (64-bit)									  Little

PLATFORM_ID PLATFORM_NAME											  ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
	  9 IBM zSeries Based Linux										  Big
	 13 Linux x86 64-bit											  Little
	 16 Apple Mac OS											  Big
	 12 Microsoft Windows x86 64-bit									  Little
	 17 Solaris Operating System (x86)									  Little
	 18 IBM Power Based Linux										  Big
	 19 HP IA Open VMS											  Little
	 20 Solaris Operating System (x86-64)									  Little
	 21 Apple Mac OS (x86-64)										  Little

20 rows selected.

1.不用的平台间传输表空间，两个数据库的compatibility set 至少都是10.0.0以上。

可以传输表空间的特性，将表空间从一个数据库copy到另一个数据库。

传输表空间用在以下场景：

.从数据仓库中表分区

.备份历史数据

.表空间的point-in-time-recovery

2.在不同的平台之间传输表空间

从10g开始，可以在不同平台之间传出表空间,支持大部分平台之间传输数据，但不是所有，可以通过查询v$transportable_platform视图来查看哪些平台受支持。

SQL> COLUMN PLATFORM_NAME FORMAT A36
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;

PLATFORM_ID PLATFORM_NAME                        ENDIAN_FORMAT
----------- ------------------------------------ --------------
          6 AIX-Based Systems (64-bit)           Big
         16 Apple Mac OS                         Big
         19 HP IA Open VMS                       Little
         15 HP Open VMS                          Little
          5 HP Tru64 UNIX                        Little
          3 HP-UX (64-bit)                       Big
          4 HP-UX IA (64-bit)                    Big
         18 IBM Power Based Linux                Big
          9 IBM zSeries Based Linux              Big
         10 Linux IA (32-bit)                    Little
         11 Linux IA (64-bit)                    Little
         13 Linux x86 64-bit                     Little
          7 Microsoft Windows IA (32-bit)        Little
          8 Microsoft Windows IA (64-bit)        Little
         12 Microsoft Windows x86 64-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.

3.传输表空间的限制

一：源数据库和目标数据库的字符集必须兼容。包括以下几种情况

数据库字符集：

http://blog.csdn.net/juliaputao/article/details/8710672

a 源数据库字符集和目的数据库字符集一样

b 源数据库字符集是目标数据库字符集的严格意义上的子集，而且必须符合以下三种条件

The source database is in version 10.1.0.3 or higher.
The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.
The tablespaces to be transported contain no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.

c The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:

The source database is in a version lower than 10.1.0.3.
The maximum character width is the same in the source and target database character sets.

二：源数据库和目标数据库必须使用兼容的国家字符集，以下两种情况必须保证其中一条符合条件

a 源和目的的国家字符集是一样的

b 源数据库版本等于或高于10.1.0.3，要传输的表空间不包含NCHAR,NVARCHAR2,NCLOB数据类型。

三：确保目的数据库不存在相同名称的表空间。

四：如果传输的对象包含底层试图，或者包含分区结果，是不符合传输条件的，除非要传输的表空间包含所有底层对象。

五：

4.传输表空间的兼容性

从11g开始，可以向相同或者更高compatibility 的目标数据库传输表空间，而不管目标数据库是相同的或者不同的平台。如果目标数据库的compatibility高于源数据库，会报错误信息。

5.传输表空间示例

注意：前提要求必须要将要传输的表空间设置成read only，负责可以使用rman。

a 不同平台间的传输，要check endian v$transporttable_platform

b 选择自包含的表空间集 ----就是和要传输的表空间对象相关的对象也都在这个要传输的表空间集合里。

c 将表空间设置成read only，并生成传输表空间集合

如果源目的的endian不同，必须转换表空间集的endian，确保和目的库一致。你可以在源端转换，也可以在目的端转换。

d 将导出的文件copy到目的库端

e 将源端表空间设置为read write

f 将表空间集合导入到目标数据库

task 1 查看源目标端platform是否受支持：

If you are transporting sales_1 and sales_2 to a different platform, you can execute the following query on each platform. If the query returns a row, the platform supports cross-platform tablespace transport.

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

The following is the query result from the source platform:

PLATFORM_NAME                      ENDIAN_FORMAT
---------------------------------- --------------
Solaris[tm] OE (32-bit)            Big

The following is the result from the destination platform:

PLATFORM_NAME                      ENDIAN_FORMAT
---------------------------------- --------------
Microsoft Windows IA (32-bit)      Little

task 2 检查自包含性质

只能传输自包含的表空间，self-contained.-"self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces

a.索引在要传输的表空间里，而表在别的表空间.

如果表在要传输的表空间，而索引在别的地方，没有问题

b.要传输的表空间包含分区表的一部分。要全部包含才可以

c.完整性约束要参照的表（例如外键约束），在要传输的表空间之外。可以选择不传输完整性约束。

e.An XML DB schema (*.xsd) that was registered by user A imports a global schema that was registered by user B, and the following is true: the default tablespace for user A is tablespace A, the default tablespace for user B is tablespace B, and only tablespace A is included in the set of tablespaces

决定一个表空间是不是self-contained，可以使用DBMS_TTS.TRANSPORT_SET_CHECK。而且必须有excute_catalog_role的权限，sys 有。

严格或者全部包含，要set TTS_FULL_CHECK参数为true。

全部包含：

The strict or full containment check is for cases that require capturing not only references going outside the transportable set, but also those coming into the set. Tablespace Point-in-Time Recovery (TSPITR) is one such case where dependent objects must be fully contained or fully outside the transportable set.

For example, it is a violation to perform TSPITR on a tablespace containing a table t but not its index i because the index and data will be inconsistent after the transport. A full containment check ensures that there are no dependencies going outside or coming into the transportable set.

The following statement can be used to determine whether tablespaces sales_1 andsales_2 are self-contained, with referential integrity constraints taken into consideration (indicated byTRUE).

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);

After invoking this PL/SQL package, you can see all violations by selecting from theTRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty. The following example illustrates a case where there are two violations: a foreign key constraint,dept_fk, across the tablespace set boundary, and a partitioned table,jim.sales, that is partially contained in the tablespace set.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
---------------------------------------------------------------------------
Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and table
JIM.DEPT in tablespace OTHER
Partitioned table JIM.SALES is partially contained in the transportable set

These violations must be resolved before sales_1 and sales_2 are transportable.

task 3 生成可传输表空间集

a.has either the ALTER TABLESPACE or MANAGETABLESPACE system privilege.

Make all tablespaces in the set read-only.

SQL> ALTER TABLESPACE sales_1 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE sales_2 READ ONLY;

Tablespace altered.

Invoke the Data Pump export utility as user system and specify the tablespaces in the transportable set.

SQL> HOST

$ expdp system dumpfile=expdat.dmp directory=data_pump_dir 
        transport_tablespaces=sales_1,sales_2 logfile=tts_export.log

Password: password

Triggers and indexes are included in the export operation by default..

触发器和索引自动包含在导出操作中。

To perform a transport tablespace operation with a strict containment check, use theTRANSPORT_FULL_CHECK parameter, as shown in the following example:

expdp system dumpfile=expdat.dmp directory=data_pump_dir
      transport_tablespaces=sales_1,sales_2 transport_full_check=y
      logfile=tts_export.log

task 4 ，不同endianness的转换

If sales_1 and sales_2 are being transported to a different platform, and the endianness of the platforms is different, and if you want to convert before transporting the tablespace set, then convert the datafiles composing thesales_1 and sales_2 tablespaces:

From SQL*Plus, return to the host system:
```
SQL> HOST
```

Start RMAN and connect to the source database:

$ RMAN TARGET /

Recovery Manager: Release 11.2.0.0.1 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: salesdb (DBID=3295731590)

Use the RMAN CONVERT TABLESPACE command to convert the datafiles into a temporary location on the source platform.

In this example, assume that the temporary location, directory /tmp, has already been created. The converted datafiles are assigned names by the system.

RMAN> CONVERT TABLESPACE sales_1,sales_2 
2> TO PLATFORM 'Microsoft Windows IA (32-bit)'
3> FORMAT '/tmp/%U';

Starting conversion at source at 30-SEP-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00007 name=/u01/app/oracle/oradata/salesdb/sales_101.dbf
converted datafile=/tmp/data_D-SALESDB_I-1192614013_TS-SALES_1_FNO-7_03jru08s
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00008 name=/u01/app/oracle/oradata/salesdb/sales_201.dbf
converted datafile=/tmp/data_D-SALESDB_I-1192614013_TS-SALES_2_FNO-8_04jru0aa
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
Finished conversion at source at 30-SEP-08

If you are transporting the tablespace set to a platform with endianness that is different from the source platform, and you have not yet converted the tablespace set, do so now with RMAN.

The following example places the converted datafiles into C:\app\orauser\oradata\orawin\,which is the location of the existing datafiles for the destination database:

C:\>RMAN TARGET /
 
Recovery Manager: Release 11.2.0.0.1 
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORAWIN (DBID=3462152886)

RMAN> CONVERT DATAFILE 
2>'C:\Temp\sales_101.dbf',
3>'C:\Temp\sales_201.dbf'
4>TO PLATFORM="Microsoft Windows IA (32-bit)"
5>FROM PLATFORM="Solaris[tm] OE (32-bit)"
6>DB_FILE_NAME_CONVERT=
7>'C:\Temp\', 'C:\app\orauser\oradata\orawin\'
8> PARALLELISM=4;

task 5

Make the transported tablespaces read/write again at the source database, as follows:

ALTER TABLESPACE sales_1 READ WRITE;
ALTER TABLESPACE sales_2 READ WRITE;

task 6

————————————————————————————————————————————————————

notes:数据库大小不同的情况

if you are transporting a tablespace with an 8K block size into a database with a 4K standard block size, then you must include aDB_8K_CACHE_SIZE initialization parameter entry in the parameter file. If it is not already included in the parameter file, this parameter can be set using theALTER SYSTEM SET statement.

________________________________________________________________________________________

Any privileged user can perform this task. To import a tablespace set, complete the following steps:

Import the tablespace metadata using the Data Pump Import utility, impdp:

impdp system dumpfile=expdat.dmp directory=data_pump_dir
   transport_datafiles=
   c:\app\orauser\oradata\orawin\sales_101.dbf,
   c:\app\orauser\oradata\orawin\sales_201.dbf
   remap_schema=sales1:crm1  remap_schema=sales2:crm2
   logfile=tts_import.log

Password: password

The REMAP_SCHEMA parameter changes the ownership of database objects. If you do not specifyREMAP_SCHEMA, all database objects (such as tables and indexes) are created in the same user schema as in the source database, and those users must already exist in the destination database.

可以使用参数文件

you can use an import parameter file. For example, you can invoke the Data Pump import utility as follows:

impdp system parfile='par.f'

where the parameter file, par.f contains the following:

DUMPFILE=expdat.dmp 
DIRECTORY=data_pump_dir
TRANSPORT_DATAFILES=
C:\app\orauser\oradata\orawin\sales_101.dbf,
C:\app\orauser\oradata\orawin\sales_201.dbf
REMAP_SCHEMA=sales1:crm1  REMAP_SCHEMA=sales2:crm2
LOGFILE=tts_import.log

trobleshooting ....................

file:///home/julia/Downloads/oracle%20hand%20book/E11882_01/server.112/e25494/tspaces013.htm#i1007169