传输表空间步骤

Task 1: Determine if Platforms are Supported and Determine Endianness

This task is only necessary if you are transporting the tablespace set to a platform different from the source platform.
这步骤只是跨平台的时候才是必须
If you are transporting the tablespace set to a platform different from the source platform, then determine if cross-platform tablespace transport is supported for both the source and destination platforms, and determine the endianness of each platform. If both platforms have the same endianness, no conversion is necessary. Otherwise you must do a conversion of the tablespace set either at the source or destination database.

如果是同字节，则无需convert数据文件，如果是异字节，则需要convert数据文件。

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

You can see that the endian formats are different and thus a conversion is necessary for transporting the tablespace set.

可以看到两个平台的字节格式不同，需要转换传输表空间集的文件格式。

Task 2: Pick a Self-Contained Set of Tablespaces

There may be logical or physical dependencies between objects in the transportable set and those outside of the set. You can only transport a set of tablespaces that is self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces.
在传输的集合和那些外部表空间的集合有逻辑回事物理的依赖。你只能传输那些自包含的表空间。自包含的意思是传输的表空间在指向外部的表空间的过程中没有任何的参考。

Some examples of self contained tablespace violations are:
一些违反自包含的例子：
An index inside the set of tablespaces is for a table outside of the set of tablespaces.
内部表空间的索引被外部表空间的表所使用

Note:

It is not a violation if a corresponding index for a table is outside of the set of tablespaces.
如果一个表的索引是在外部表空间则不算违反自包含。

A partitioned table is partially contained in the set of tablespaces.
分区表只是部分包含在表空间中。

The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. To transport a subset of a partition table, you must exchange the partitions into tables.
想要传输的表空间必须包含分区表的所有分区或是非分区表，传输分区表的子集必须对其余的分区进行交换分区操作。

A referential integrity constraint points to a table across a set boundary.
参照完整性约束在集合范围内以外指向某张表

When transporting a set of tablespaces, you can choose to include referential integrity constraints. However, doing so can affect whether a set of tablespaces is self-contained. If you decide not to transport constraints, then the constraints are not considered as pointers.
传输一个表空间集，可以选择包含参照完整性约束。这么做会影响自包含的空间集。如果不考虑传输约束，约束就不会被考虑。

• A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of tablespaces.

传输的表空间集中的表包含指向外部表空间集的LOBS类型的LOB类型列。

To determine whether a set of tablespaces is self-contained, you can invoke the TRANSPORT_SET_CHECK  procedure in the Oracle supplied package DBMS_TTS . You must have been granted the EXECUTE_CATALOG_ROLE  role (initially signed to SYS ) to execute this procedure.

When you invoke the DBMS_TTS package, you specify the list of tablespaces in the transportable set to be checked for self containment. You can optionally specify if constraints must be included. For strict or full containment, you must additionally set the TTS_FULL_CHECK parameter to 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.

严格的全包含检查不进检查指向外部表空间集的参照性，也检查内部表空间集的参照性。TSPITR就是一个例子。

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. 

例如，执行TSPITR只对表而不针对索引的话是对限制条件的违反，因为索引和数据会在传输后变得不一致。全包含检查确保在传输表空间以外的表空间没有任何依赖关系。

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

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 the TRANSPORT_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. As noted in the next task, one choice for bypassing the integrity constraint violation is to not export the integrity constraints.

Task 3: Generate a Transportable Tablespace Set

After ensuring you have a self-contained set of tablespaces that you want to transport, generate a transportable tablespace set by completing the following steps:

1、Start SQL*Plus and connect to the database as an administrator or as a user who has either the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.
2、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.
3、Invoke the Data Pump export utility as user system and specify the tablespaces in the transportable set.

$ 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 the TRANSPORT_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

In this case, the Data Pump export utility verifies that there are no dependencies between the objects inside the transportable set and objects outside the transportable set. If the tablespace set being transported is not self-contained, then the export fails and indicates that the transportable set is not self-contained. You must then return to Task 2 to resolve all violations.

Notes:

The Data Pump utility is used to export only data dictionary structural information (metadata) for the tablespaces. No actual data is unloaded, so this operation goes relatively quickly even for large tablespace sets.

4、Check the log file for errors, and take note of the dump file and datafiles that you must transport to the destination database. EXPDP outputs the names and paths of these files in messages like these:

******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/salesdb/dpdump/expdat.dmp
******************************************************************************
Datafiles required for transportable tablespace SALES_1:
  /u01/app/oracle/oradata/salesdb/sales_101.dbf
Datafiles required for transportable tablespace SALES_2:
  /u01/app/oracle/oradata/salesdb/sales_201.dbf

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 the sales_1 and sales_2 tablespaces:

1. From SQL*Plus, return to the host system:

   SQL> HOST
   

2. 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)
   

3. 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

Task 4: Transport the Tablespace Set

Complete the following steps:

1、Transport  both the datafiles and the export (dump) file  of the tablespaces to a place that is accessible to the destination database. To accomplish this, do one of the following:
*  If no endianness conversion of the tablespace set is needed, or if you already converted the tablespace set:
a、Transport the dump file to the directory pointed to by the DATA_PUMP_DIR directory object, or to any other directory of your choosing.
b、 Transport the datafiles to the location of the existing datafiles of the destination database.

Note:

If you converted the datafiles, obtain the new names and locations of the datafiles from the CONVERT TABLESPACE  command output.
*  If you intend to perform endianness conversion after transporting to the destination host:

1. Transport the dump file to the directory pointed to by the DATA_PUMP_DIR directory object, or to any other directory of your choosing.

    b.   Transport the datafiles to a temporary location on the destination host (for example, /tmp or C:\TEMP). During conversion, you can move the converted datafiles to the location of the existing datafiles of the destination database.

2、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;

You identify the datafiles by filename, not by tablespace name. Until the tablespace metadata is imported, the destination instance has no way of knowing the desired tablespace names.

If the source location, the target location, or both do not use Oracle Automatic Storage Management (Oracle ASM), then the source and target platforms are optional. RMAN determines the source platform by examining the datafile, and the target platform defaults to the platform of the host running the conversion.

If both the source and target locations use Oracle ASM, then you must specify the source and target platforms in the DB_FILE_NAME_CONVERT clause.

Task 5: (Optional) Restore Tablespaces to Read/Write Mode

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;

You can postpone this task to first ensure that the import process succeeds.

Task 6: Import the Tablespace Set

Note:

If you are transporting a  tablespace of a different block size than the standard block size of the database receiving the tablespace set, then you must first have a DB_ nK_CACHE_SIZE  initialization parameter entry in the receiving database parameter file.

For example, if you are transporting a tablespace with an 8K block size into a database with a 4K standard block size, then you must include a DB_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 the ALTER SYSTEM SET statement.

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

1、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

After this statement executes successfully, all tablespaces in the set being copied remain in read-only mode. Check the import log file to ensure that no error has occurred.

2、If required, put the tablespaces into read/write mode on the destination database.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29802484/viewspace-2057788/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29802484/viewspace-2057788/