Oracle传输表空间总结

0、准备工作

--创建被传输的表空间

SQL> create tablespace tbs_single

datafile 'c:\oracle\oradata\ora9i\tbs_single01.dbf' size 100M

extent management local;

--创建用户，并将表空间作为默认表空间

SQL> create user tranp identified byoracledefault tablespace tbs_single;

SQL> grant connect,resource to tranp;

--在该表空间创建表，用于测试

SQL> createtabletranp.t01 as select * from sys.dba_objects;

1、检查源、目标平台Endianness

在源数据库平台上：

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT

2 FROM V$TRANSPORTABLE_PLATFORM. tp, V$DATABASE d

3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT

------------------------------------ --------------

Microsoft Windows IA (32-bit) Little

在目标数据库平台上：

SQL> 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

------------------------------------ --------------

Linux IA (32-bit) Little

由于源和目标平台的Endianness一致，可以省去convert这一步。

2、检查要表空间是否自包含

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS_SINGLE',true);

PL/SQL过程已成功完成。

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

未选定行

说明表空间是自包含的。

3、产生传输表空间集

创建目录对象

SQL> create DIRECTORY tranp_dir as 'c:\software';

目录已创建。

SQL> grant read,write on DIRECTORY tranp_dir to public;

授权成功。

将表空间置为只读。

SQL> alter tablespace tbs_single read only;

表空间已更改。

使用数据泵导出传输表空间的元数据

注：如果Endianness不一致，还需要使用RMAN进行转换表空间的数据文件。

4、传送表空间集

将表空间的数据文件和导出的DMP文件，传送到目标数据库平台上。

5、导入表空间

在目标数据库中，创建相应的目录对象和用户。

SQL> create directory tranp_dir as '/home/oracle';

Directory created.

SQL> grant read,write on directory tranp_dir to public;

Grant succeeded.

SQL> create user tranp identified by oracle;

User created.

SQL> grant connect,resource to tranp;

Grant succeeded.

使用数据库泵，导入到目标数据库中。

[oracle@ocmu ~]$ impdp system/oracle dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp

Import: Release 10.2.0.1.0 - Production on Thursday, 30 August, 2012 23:40:25

Copyright (c) 2003,2005, Oracle. All rights reserved.

Connected to: Oracle Database10gEnterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP andDataMining options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp

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 23:40:29

将被导入的表空间置为read write。

SQL> alter tablespace TBS_SINGLE read write;

Tablespace altered.

6、测试

目标库中，进行测试。

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

USERS

TEMP

TBS_SINGLE

。。。。。。。。。。。。。。。。。

13 rows selected.

SQL> conn tranp/oracle

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

T01 TABLE

SQL> select count(*) from t01;

COUNT(*)

----------

49795

SQL> conn / as sysdba

Connected.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

TBS_SINGLE ONLINE

。。。。。。。。。。。。。。。。。。。。。。。。

13 rows selected.

7、问题

问题描述：

oracle@ocmu ~]$ impdp system/oracle dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp

Import: Release 10.2.0.1.0 - Production on Thursday, 30 August, 2012 23:25:47

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29345: cannot plug a tablespace into a database using an incompatible character set

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 23:25:53

a元数据库：

SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from

2 (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,

3 (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,

4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;

TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC

----------------------------------------------------------------------

AMERICAN_AMERICA.ZHS16GBK

b目标数据库：

SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from

2 (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,

3 (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,

4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;

TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC

----------------------------------------------------------------------

AMERICAN_AMERICA.AL32UTF8

原因：由于源和目标数据库的字符集不一致或不是子集和超集的关系，所以造成不能导入表空间的字符集。

解决方法：将源数据库和目标数据库的字符集调整为一致的字符集；或者源数据库的字符集是目标数据库的子集。

源数据库的修改：

SQL> alter database character set internal_use utf8;

alter database character set internal_use utf8

*

第1行出现错误:

ORA-12719:操作要求数据库处于RESTRICTED模式下

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE例程已经关闭。

SQL> startup mount;

ORACLE例程已启动

Total System Global Area 444596224 bytes

Fixed Size 1219904 bytes

Variable Size 138412736 bytes

Database Buffers 301989888 bytes

Redo Buffers 2973696 bytes

数据库已装载

SQL> alter system enable restricted session;

系统已更改

SQL> alter database open;

数据库已更改

SQL> alter database character set internal_use utf8;

数据库已更改

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE例程已经关闭。

SQL> startup mount;

ORACLE例程已启动

Total System Global Area 444596224 bytes

Fixed Size 1219904 bytes

Variable Size 138412736 bytes

Database Buffers 301989888 bytes

Redo Buffers 2973696 bytes

数据库已装载

SQL> alter system disable restricted session;

系统已更改

SQL> alter database open;

数据库已更改

SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from

2 (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,

3 (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,

4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;

TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC

----------------------------------------------------------------------

AMERICAN_AMERICA.UTF8

目标数据库修改：

SQL> alter database character set internal_use utf8;

alter database character set internal_use utf8

*

ERROR at line 1:

ORA-12719: operation requires database is in RESTRICTED mode

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 444596224 bytes

Fixed Size 1219904 bytes

Variable Size 138412736 bytes

Database Buffers 301989888 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> alter system enable restricted session;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set internal_use utf8;

Database altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 444596224 bytes

Fixed Size 1219904 bytes

Variable Size 138412736 bytes

Database Buffers 301989888 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> alter system disable restricted session;

System altered.

SQL> alter database open;

Database altered.

SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from

2 (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,

3 (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,

4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;

TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC

----------------------------------------------------------------------

AMERICAN_AMERICA.UTF8

修改完成，再重新导出/导入一遍，即可成功。