0、准备工作 –创建被传输的表空间 SQL create tablespace tbs_single datafile c:\Oracle\oradata\ora9i\tbs_single01.dbf size 100M extent management local; –创建用户，并将表空间作为默认表空间 SQL create user tranp identified by Oracle default t
0、准备工作
–创建被传输的表空间
SQL> create tablespace tbs_single
datafile ‘c:\Oracle\oradata\ora9i\tbs_single01.dbf’ size 100M
extent management local;
–创建用户，并将表空间作为默认表空间
SQL> create user tranp identified by Oracle default tablespace tbs_single;
SQL> grant connect,resource to tranp;
–在该表空间创建表，用于测试
SQL> create table tranp.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 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
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
修改完成，再重新导出/导入一遍，即可成功。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/466915.html
微信扫一扫 
支付宝扫一扫 
