oracle 传输表空间总结。

来源：互联网发布：金蝶数据库安装教程编辑：程序博客网时间：2024/04/29 18:16

今天是2013-11-26，之前朋友问过我怎么把一个表空间的数据拿到一个测试库，有什么有什么好的办法，我当时就是提出传输表空间，这样快，可以自己一直也懒的在复习一下这部分内容，今天就开始在此练习以前搞过的东西，在此感谢一下大灰狼，是我用他的windows安装的oracle进行演示的。

基本知识：

Introduction to Transportable Tablespaces

You can use the Transportable Tablespaces feature to copy a set of tablespaces from one Oracle Database to another.

Note:

This method for transporting tablespaces requires that you place the tablespaces to be transported in read-only mode until you complete the transporting process. If this is undesirable, you can use the Transportable Tablespaces from Backup feature, described in Oracle Database Backup and Recovery User's Guide.

The tablespaces being transported can be either dictionary managed or locally managed. Starting with Oracle9i, the transported tablespaces are not required to be of the same block size as the destination database standard block size.

Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the data files containing all of the actual data are just copied to the destination location, and you use Data Pump to transfer only the metadata of the tablespace objects to the new database.

Note:

Beginning with Oracle Database 11g Release 1, you must use Data Pump for transportable tablespaces. The only circumstance under which you can use the original import and export utilities, IMP and EXP, is for a backward migration of XMLType data to a database version 10g Release 2 or earlier. See Oracle Database Utilities for more information on these utilities and to Oracle XML DB Developer's Guide for more information on XMLTypes.

The transportable tablespace feature is useful in several scenarios, including:

Exporting and importing partitions in data warehousing tables
Publishing structured data on CDs
Copying multiple read-only versions of a tablespace on multiple databases
Archiving historical data
Performing tablespace point-in-time-recovery (TSPITR)

These scenarios are discussed in "Using Transportable Tablespaces: Scenarios".

There are two ways to transport a tablespace:

Manually, following the steps described in this section. This involves issuing commands to SQL*Plus, RMAN, and Data Pump.
Using the Transport Tablespaces Wizard in Enterprise Manager
To run the Transport Tablespaces Wizard:
1. Log in to Enterprise Manager with a user that has the EXP_FULL_DATABASE role.
2. At the top of the Database Home page, click Data Movement to view the Data Movement page.
3. Under Move Database Files, click Transport Tablespaces.

About Transporting Tablespaces Across Platforms

Starting with Oracle Database Release 10g, you can transport tablespaces across platforms. This functionality can be used to:

Allow a database to be migrated from one platform to another
Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle Database on different platforms
Simplify the distribution of data from a data warehouse environment to data marts, which are often running on smaller platforms
Enable the sharing of read-only tablespaces between Oracle Database installations on different operating systems or platforms, assuming that your storage system is accessible from those platforms and the platforms all have the same endianness, as described in the sections that follow.

Many, but not all, platforms are supported for cross-platform tablespace transport. You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine each platform's endian format (byte ordering). The following query displays the platforms that support cross-platform tablespace transport:

SQL> COLUMN PLATFORM_NAME FORMAT A36SQL> 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.

If the source platform and the destination platform are of different endianness, then an additional step must be done on either the source or destination platform to convert the tablespace being transported to the destination format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.

Before a tablespace can be transported to a different platform, the data file header must identify the platform to which it belongs. In an Oracle Database with compatibility set to 10.0.0 or later, you can accomplish this by making the data file read/write at least once.

Limitations on Transportable Tablespace Use

Be aware of the following limitations as you plan to transport tablespaces:

The source and the destination databases must use compatible database character sets. That is, one of the following must be true:
- The database character sets of the source and the target databases are the same.
- The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:
  - 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.
- 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.
The source and the target databases must use compatible national character sets. Specifically, one of the following must be true:
- The national character sets of the source and target databases are the same.
- The source database is in version 10.1.0.3 or higher and the tablespaces to be transported contain no columns with NCHAR,NVARCHAR2, or NCLOB data type.
Note:
The subset-superset relationship between character sets recognized by Oracle Database is documented in theOracle Database Globalization Support Guide.
You cannot transport a tablespace to a destination database that contains a tablespace of the same name. However, before the transport operation, you can rename either the tablespace to be transported or the destination tablespace.
Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
Encrypted tablespaces have the following the limitations:
- Before transporting an encrypted tablespace, you must copy the Oracle wallet manually to the destination database, unless the master encryption key is stored in a Hardware Security Module (HSM) device instead of an Oracle wallet. When copying the wallet, the wallet password remains the same in the destination database. However, it is recommended that you change the password on the destination database so that each database has its own wallet password. See Oracle Database Advanced Security Administrator's Guide for information about HSM devices, about determining the location of the Oracle wallet, and about changing the wallet password with Oracle Wallet Manager.
- You cannot transport an encrypted tablespace to a database that already has an Oracle wallet for transparent data encryption. In this case, you must use Oracle Data Pump to export the tablespace's schema objects and then import them to the destination database. You can optionally take advantage of Oracle Data Pump features that enable you to maintain encryption for the data while it is being exported and imported. See Oracle Database Utilities for more information.
- You cannot transport an encrypted tablespace to a platform with different endianness.
Tablespaces that do not use block encryption but that contain tables with encrypted columns cannot be transported. You must use Oracle Data Pump to export and import the tablespace's schema objects. You can take advantage of Oracle Data Pump features that enable you to maintain encryption for the data while it is being exported and imported. See Oracle Database Utilities for more information.
Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes. Beginning with Oracle Database 11gRelease 1, you must use only Data Pump to export and import the tablespace metadata for tablespaces that contain XMLTypes.
The following query returns a list of tablespaces that contain XMLTypes:
```
select distinct p.tablespace_name from dba_tablespaces p,   dba_xml_tables x, dba_users u, all_all_tables t where  t.table_name=x.table_name and t.tablespace_name=p.tablespace_name  and x.owner=u.username
```
See Oracle XML DB Developer's Guide for information on XMLTypes.
Transporting tablespaces with XMLTypes has the following limitations:
- The destination database must have XML DB installed.
- Schemas referenced by XMLType tables cannot be the XML DB standard schemas.
- Schemas referenced by XMLType tables cannot have cyclic dependencies.
- XMLType tables with row level security are not supported, because they cannot be exported or imported.
- If the schema for a transported XMLType table is not present in the destination database, it is imported and registered. If the schema already exists in the destination database, an error is returned unless the ignore=y option is set.
- If an XMLType table uses a schema that is dependent on another schema, the schema that is depended on is not exported. The import succeeds only if that schema is already in the destination database.

Additional limitations include the following:

SYSTEM Tablespace Objects You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences.

Opaque Types Types whose interpretation is application-specific and opaque to the database (such as RAW, BFILE, and the AnyTypes) can be transported, but they are not converted as part of the cross-platform transport operation. Their actual structure is known only to the application, so the application must address any endianness issues after these types are moved to the new platform. Types and objects that use these opaque types, either directly or indirectly, are also subject to this limitation.

Floating-Point Numbers BINARY_FLOAT and BINARY_DOUBLE types are transportable using Data Pump.

Compatibility Considerations for Transportable Tablespaces

When you create a transportable tablespace set, Oracle Database computes the lowest compatibility level at which the destination database must run. This is referred to as the compatibility level of the transportable set. Beginning with Oracle Database 11g, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the destination database is on the same or a different platform. The database signals an error if the compatibility level of the transportable set is higher than the compatibility level of the destination database.

The following table shows the minimum compatibility requirements of the source and destination tablespace in various scenarios. The source and destination database need not have the same compatibility setting.

Table 14-1 Minimum Compatibility Requirements

Transport ScenarioMinimum Compatibility SettingSource DatabaseDestination Database

Databases on the same platform

8.0

Tablespace with different database block size than the destination database

9.0

Databases on different platforms

10.0

Transporting Tablespaces Between Databases: A Procedure and Example

The following list of tasks summarizes the process of transporting a tablespace. Details for each task are provided in the subsequent example.

Note:

This method of generating a transportable tablespace requires that you temporarily make the tablespace read-only. If this is undesirable, you can use the alternate method known as transportable tablespace from backup. See Oracle Database Backup and Recovery User's Guide for details.

For cross-platform transport, check the endian format of both platforms by querying the V$TRANSPORTABLE_PLATFORM view.
Ignore this task if you are transporting your tablespace set to the same platform.
Pick a self-contained set of tablespaces.
At the source database, place the set of tablespaces in read-only mode and generate a transportable tablespace set.
A transportable tablespace set (or transportable set) consists of data files for the set of tablespaces being transported and an export file containing structural information (metadata) for the set of tablespaces. You use Data Pump to perform the export.
If you are transporting the tablespace set to a platform with different endianness from the source platform, you must convert the tablespace set to the endianness of the destination platform. You can perform a source-side conversion at this step in the procedure, or you can perform a destination-side conversion as part of Task 4.
Transport the tablespace set.
Copy the data files and the export file to a place that is accessible to the destination database.
If you transported the tablespace set to a platform with different endianness from the source platform, and you have not performed a source-side conversion to the endianness of the destination platform, perform a destination-side conversion now.
(Optional) Restore tablespaces to read/write mode.
At the destination database, import the tablespace set.
Invoke the Data Pump utility to import the metadata for the tablespace set.

上面写的很清楚了，不在多说。

eg：

首先在大灰狼的机器上已经转好了文件，他的系统是windows的下面将进行跨平台传输表空间。

1、查看源库信息源库：
SQL> select d.platform_name,endian_format from v$database d ,v$transportable_platform vt where vt.platform_name=d.platform_name;

PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
Linux IA (32-bit) Little

2、搭建环境
SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size                  1219016 bytes
Variable Size              79693368 bytes
Database Buffers          197132288 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------
/u01/oracle/oradata/tf1/users01.dbf
/u01/oracle/oradata/tf1/sysaux01.dbf
/u01/oracle/oradata/tf1/undotbs01.dbf
/u01/oracle/oradata/tf1/system01.dbf
/u01/oracle/oradata/tf1/test1.dbf

SQL> create tablespace rhys datafile '/u01/oracle/oradata/tf1/rhys.dbf' size 10M;

Tablespace created.

SQL> create user rhys identified by root default tablespace rhys;

User created.

SQL> grant connect,resource,create session to rhys;

Grant succeeded.

SQL> grant select on dba_objects to rhys;

Grant succeeded.

SQL> conn rhys/root;
Connected.
SQL> create table t as select * from dba_objects;

Table created.

SQL> commit;

Commit complete.

SQL> select count(*) from t;

COUNT(*)
----------
49820

SQL>
SQL> conn sys/root as sysdba
Connected.
SQL> alter tablespace rhys read only;

Tablespace altered.

SQL>
SQL> create directory rhys_dir as '/home/oracle/study';

Directory created.

SQL> grant read,write on directory rhys_dir to system;

Grant succeeded.

SQL> SQL> exit
[oracle@tf tf1]$ expdp system/root dumpfile=rhys.dmp directory=rhys_dir transport_tablespaces=rhys transport_full_check=y logfile=expdp.log

Export: Release 10.2.0.1.0 - Production on Tuesday, 26 November, 2013 22:59:10

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=rhys.dmp directory=rhys_dir transport_tablespaces=rhys transport_full_check=y

logfile=expdp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/study/rhys.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 22:59:57

[oracle@tf tf1]$
传输数据文件和expdp元数据文件。

destination platform：
SQL> select d.platform_name,endian_format from v$database d ,v$transportable_platform vt where vt.platform_name=d.platform_name;

PLATFORM_NAME ENDIAN_FORMAT
-------------------------------------------------- --------------
Linux x86 64-bit Little

SQL>
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEMP01                         ONLINE

6 rows selected.

SQL>
SQL> select username,default_tablespace from dba_users where username='RHYS';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
RHYS USERS

SQL>
[oracle@oracle-one dpdump]$ impdp system/root dumpfile=rhys.dmp directory=data_pump_dir transport_datafiles=/home/oracle/tmp/rhys/rhys.dbf

Import: Release 11.2.0.4.0 - Production on Wed Nov 27 15:06:10 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=rhys.dmp directory=data_pump_dir transport_datafiles=/home/oracle/tmp/rhys/rhys.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39151: Table "RHYS"."T" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 1 error(s) at Wed Nov 27 15:06:37 2013 elapsed 0 00:00:10

[oracle@oracle-one dpdump]$
提示存在相同名字的table：

SQL>
SQL> select table_name,owner from dba_tables where table_name='T';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
T                              RHYS
T                              SCOTT
[oracle@oracle-one dpdump]$ impdp system/root dumpfile=rhys.dmp directory=data_pump_dir transport_datafiles=/home/oracle/tmp/rhys/rhys.dbf remap_table=rhys.t:tx

Import: Release 11.2.0.4.0 - Production on Wed Nov 27 15:21:21 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=rhys.dmp directory=data_pump_dir transport_datafiles=/home/oracle/tmp/rhys/rhys.dbf remap_table=rhys.t:tx
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Nov 27 15:21:32 2013 elapsed 0 00:00:07

[oracle@oracle-one dpdump]$
SQL> col file_name for a70
SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME                                                              TABLESPACE_NAME                STATUS
---------------------------------------------------------------------- ------------------------------ ---------
/opt/app/oracle/oradata/RHYS/users01.dbf                               USERS                          AVAILABLE
/opt/app/oracle/oradata/RHYS/undotbs01.dbf                             UNDOTBS1                       AVAILABLE
/opt/app/oracle/oradata/RHYS/sysaux01.dbf                              SYSAUX                         AVAILABLE
/opt/app/oracle/oradata/RHYS/system01.dbf                              SYSTEM                         AVAILABLE
/home/oracle/tmp/rhys/rhys.dbf                                         RHYS                           AVAILABLE

SQL>
注意此时数据文件还是在原先传在的目录，因此做传输的时候最好把数据文件放到原先预定的目录下。或是使用rman db_file_name_convert进行转换
SQL> col file_name for a70
SQL> select file_name,tablespace_name,status from dba_data_files;

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEMP01                         ONLINE
RHYS                           READ ONLY

7 rows selected.

SQL> alter tablespace rhys read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEMP01                         ONLINE
RHYS                           ONLINE

7 rows selected.

SQL>

可以看到只要字节存储此项一直，平台位数是不考虑的。

That's all!