利用imp/impdp传输表空间transport_tablespace满足以下条件
1.字符集相同
2.要导出的表空间必须是read only,而且是自包含的，就是说该表空间的对象不能依赖其他表空间。
3.先导出源数据，传输表空间的数据文件拷贝到另一个数据库相应目录下。
而RMAN不需要表空间READ ONLY，因为它是备份集进行传输，但是也需要字符集相同和自包含。
下面基于RMAN传输表空间的测试。
测试环境
源数据库：
OS=RHEL 6.4;
DB=Oracle11GR2 ;
IP=192.168.1.171;
SID=hjj;
ASM存储。
目标数据库：OS=WIN7 64BIT;
DB=ORACLE11GR2;
IP=192.168.1.1;
SID=orcl;
FS存储。
一、在源数据库端
1.创建测试表空间
SQL> create tablespace tt1 datafile '+DATA_DG/hjj/datafile/test01.dbf' size 100m;
Tablespace created.
2.创建用户并授权
SQL> create user tt1 identified by oracle default tablespace tt1;
User created.
SQL> grant connect,resource to tt1;
Grant succeeded.
3.创建测试表
SQL> create table tt1.t as select * from dba_objects;
Table created.
4.查看数据
SQL> conn tt1/oracle
Connected.
SQL> select count(*) from t;
COUNT(*)
----------
21293
4.确认传输表空间的平台是否兼容
在源数据库端：
SQL> select tp.* from v$transportable_platform tp,v$database d where tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------------- --------------
10 Linux IA (32-bit) Little
在目标数据库端：
SQL> select tp.* from v$transportable_platform tp,v$database d where tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------------- ----------------------------
12 Microsoft Windows x86 64-bit Little
可以看到windows平台和linux都是Little，是兼容的。
5.确认源数据库的表空间是否自包含
SQL> exec dbms_tts.transport_set_check('tt1',true,true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
6.备份源数据库
[oracle@myrac1 ~]$ rman target sys/oracle@hjj catalog rman/rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Mar 5 19:54:06 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HJJ (DBID=2845675742)
connected to recovery catalog database
RMAN> sql "alter system checkpoint";
starting full resync of recovery catalog
full resync complete
sql statement: alter system checkpoint
RMAN> sql "alter system archive log current";
sql statement: alter system archive log current
RMAN> sql "alter system switch logfile";
sql statement: alter system switch logfile
RMAN> backup database;
Starting backup at 05-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA_DG/hjj/datafile/system.260.837919351
input datafile file number=00002 name=+DATA_DG/hjj/datafile/sysaux.261.837919391
input datafile file number=00005 name=+DATA_DG/hjj/datafile/tbs01.dbf
input datafile file number=00003 name=+DATA_DG/hjj/datafile/undotbs1.262.837919417
input datafile file number=00007 name=+DATA_DG/hjj/datafile/tt01.dbf
input datafile file number=00006 name=+DATA_DG/hjj/datafile/rman_tbs0.dbf
input datafile file number=00004 name=+DATA_DG/hjj/datafile/users.264.837919457
channel ORA_DISK_1: starting piece 1 at 05-MAR-14
channel ORA_DISK_1: finished piece 1 at 05-MAR-14
piece handle=+DG_FRA/hjj/backupset/2014_03_05/nnndf0_tag20140305t215129_0.265.841441893 tag=TAG20140305T215129 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 05-MAR-14
Starting Control File and SPFILE Autobackup at 05-MAR-14
piece handle=+DG_FRA/hjj/autobackup/2014_03_05/s_841441968.267.841441969 comment=NONE
Finished Control File and SPFILE Autobackup at 05-MAR-14
RMAN> backup archivelog all;
Starting backup at 05-MAR-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=88 RECID=47 STAMP=841355567
input archived log thread=1 sequence=89 RECID=48 STAMP=841366180
input archived log thread=1 sequence=90 RECID=49 STAMP=841433642
input archived log thread=1 sequence=91 RECID=50 STAMP=841434920
input archived log thread=1 sequence=92 RECID=51 STAMP=841434930
input archived log thread=1 sequence=93 RECID=52 STAMP=841435200
input archived log thread=1 sequence=94 RECID=53 STAMP=841439190
input archived log thread=1 sequence=95 RECID=54 STAMP=841439198
input archived log thread=1 sequence=96 RECID=55 STAMP=841439439
input archived log thread=1 sequence=97 RECID=56 STAMP=841439759
input archived log thread=1 sequence=98 RECID=57 STAMP=841440347
input archived log thread=1 sequence=99 RECID=58 STAMP=841441558
input archived log thread=1 sequence=100 RECID=59 STAMP=841441564
input archived log thread=1 sequence=101 RECID=60 STAMP=841442014
channel ORA_DISK_1: starting piece 1 at 05-MAR-14
channel ORA_DISK_1: finished piece 1 at 05-MAR-14
piece handle=+DG_FRA/hjj/backupset/2014_03_05/annnf0_tag20140305t215336_0.264.841442017 tag=TAG20140305T215336 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 05-MAR-14
Starting Control File and SPFILE Autobackup at 05-MAR-14
piece handle=+DG_FRA/hjj/autobackup/2014_03_05/s_841442025.263.841442027 comment=NONE
Finished Control File and SPFILE Autobackup at 05-MAR-14
RMAN> backup current controlfile;
Starting backup at 05-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 05-MAR-14
channel ORA_DISK_1: finished piece 1 at 05-MAR-14
piece handle=+DG_FRA/hjj/backupset/2014_03_05/ncnnf0_tag20140305t215401_0.262.841442045 tag=TAG20140305T215401 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-MAR-14
Starting Control File and SPFILE Autobackup at 05-MAR-14
piece handle=+DG_FRA/hjj/autobackup/2014_03_05/s_841442048.261.841442051 comment=NONE
Finished Control File and SPFILE Autobackup at 05-MAR-14
说明：这里使用全库备份，因为除了目标表空间的备份以外，RMAN还需要SYSTEM、SYSAUX以及UNDO表空间的备份，如果任意一个表空间的备份不存在，则就会导致“RMAN-06019: 无法转换表空间名称"TTB1"”的错误。
7.生成传输集(源数据库上)
RMAN> transport tablespace tt1 tablespace destination '/s01/dd' auxiliary destination '/s01/dd';
Creating automatic instance, with SID='CDty'
initialization parameters used for automatic instance:
db_name=HJJ
db_unique_name=CDty_tspitr_HJJ
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/s01/dd
log_archive_dest_1='location=/s01/dd'
#No auxiliary parameter file used
starting up automatic instance HJJ
Oracle instance started
Total System Global Area 292933632 bytes
Fixed Size 1336092 bytes
Variable Size 100666596 bytes
Database Buffers 184549376 bytes
Redo Buffers 6381568 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully
contents of Memory Script:
{
# set requested point in time
set until scn 2004003;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# resync catalog
resync catalog;}
executing Memory Script
executing command: SET until clause
Starting restore at 05-MAR-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=59 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DG_FRA/hjj/autobackup/2014_03_05/s_841441968.267.841441969
channel ORA_AUX_DISK_1: piece handle=+DG_FRA/hjj/autobackup/2014_03_05/s_841441968.267.841441969 tag=TAG20140305T215248
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
output file name=/s01/dd/HJJ/controlfile/o1_mf_9kj3fpmt_.ctl
Finished restore at 05-MAR-14
sql statement: alter database mount clone database
sql statement: alter system archive log current
starting full resync of recovery catalog
full resync complete
contents of Memory Script:
{
# set requested point in time
set until scn 2004003;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 7 to
"/s01/dd/tt01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 7;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /s01/dd/HJJ/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 05-MAR-14
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /s01/dd/HJJ/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /s01/dd/HJJ/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /s01/dd/HJJ/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /s01/dd/tt01.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DG_FRA/hjj/backupset/2014_03_05/nnndf0_tag20140305t215129_0.265.841441893
channel ORA_AUX_DISK_1: piece handle=+DG_FRA/hjj/backupset/2014_03_05/nnndf0_tag20140305t215129_0.265.841441893 tag=TAG20140305T215129
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 05-MAR-14
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=841442234 file name=/s01/dd/HJJ/datafile/o1_mf_system_9kj3g6mv_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=841442234 file name=/s01/dd/HJJ/datafile/o1_mf_undotbs1_9kj3g77n_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=841442234 file name=/s01/dd/HJJ/datafile/o1_mf_sysaux_9kj3g706_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=841442234 file name=/s01/dd/tt01.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 2004003;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 7 online";
# recover and open resetlogs
recover clone database tablespace "TT1", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 7 online
Starting recover at 05-MAR-14
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 101 is already on disk as file /s01/app/oracle/archivelog/1_101_837919326.dbf
archived log file name=/s01/app/oracle/archivelog/1_101_837919326.dbf thread=1 sequence=101
media recovery complete, elapsed time: 00:00:00
Finished recover at 05-MAR-14
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TT1 read only';
# create directory for datapump export
sql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''
/s01/dd''";
}
executing Memory Script
sql statement: alter tablespace TT1 read only
sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/s01/dd''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_CDty":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_CDty" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_CDty is:
EXPDP> /s01/dd/dmpfile.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TT1:
EXPDP> /s01/dd/tt01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_CDty" successfully completed at 22:00:01
Export completed
--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/s01/dd/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/s01/dd';
DECLARE
-- the datafiles
tbs_files dbms_streams_tablespace_adm.file_set;
cvt_files dbms_streams_tablespace_adm.file_set;
-- the dumpfile to import
dump_file dbms_streams_tablespace_adm.file;
dp_job_name VARCHAR2(30) := NULL;
-- names of tablespaces that were imported
ts_names dbms_streams_tablespace_adm.tablespace_set;
BEGIN
-- dump file name and location
dump_file.file_name := 'dmpfile.dmp';
dump_file.directory_object := 'STREAMS$DIROBJ$DPDIR';
-- forming list of datafiles for import
tbs_files( 1).file_name := 'tt01.dbf';
tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1';
-- import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name => dp_job_name,
dump_file => dump_file,
tablespace_files => tbs_files,
converted_files => cvt_files,
tablespace_names => ts_names);
-- output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line('imported tablespace '|| ts_names(i));
END LOOP;
END IF;
END;
/
-- dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------
Removing automatic instance
shutting down automatic instance
database closed
database dismounted
Oracle instance shut down
Automatic instance removed
auxiliary instance file /s01/dd/HJJ/datafile/o1_mf_temp_9kj3l8ph_.tmp deleted
auxiliary instance file /s01/dd/HJJ/onlinelog/o1_mf_3_9kj3l3lr_.log deleted
auxiliary instance file /s01/dd/HJJ/onlinelog/o1_mf_2_9kj3l20t_.log deleted
auxiliary instance file /s01/dd/HJJ/onlinelog/o1_mf_1_9kj3l0b0_.log deleted
auxiliary instance file /s01/dd/HJJ/datafile/o1_mf_sysaux_9kj3g706_.dbf deleted
auxiliary instance file /s01/dd/HJJ/datafile/o1_mf_undotbs1_9kj3g77n_.dbf deleted
auxiliary instance file /s01/dd/HJJ/datafile/o1_mf_system_9kj3g6mv_.dbf deleted
auxiliary instance file /s01/dd/HJJ/controlfile/o1_mf_9kj3fpmt_.ctl deleted
传输完成后会自动删除临时目录/s01/dd下的文件。
查看目录/s01/dd
[root@myrac1 dd]# ll
total 102508
-rw-r----- 1 oracle asmadmin 90112 Mar 5 22:00 dmpfile.dmp
drwxr-x--- 5 oracle asmadmin 4096 Mar 5 21:25 HJJ
-rw-r--r-- 1 oracle oinstall 2022 Mar 5 22:00 impscrpt.sql
-rw-r----- 1 oracle asmadmin 104865792 Mar 5 21:57 tt01.dbf
impscrpt.sql --元数据信息，目标数据库必须存在相应的对象属主，数据文件路径不一致还要修改脚本。
dmpfile.dmp --也是元数据信息，如果使用dump文件导入元数据信息则就没有上面的限制了。
tt01.dbf --数据文件，可以发现生成的数据文件和原来的数据文件一模一样。
HJJ是/s01/dd下的一个临时目录。
8.目标数据库中导入元数据集
a.使用asm存储体系，使用dump导入
convert datafile '/opt/tts/td/tts.288.755520393' format '+datagroup';
如果是使用的asm则需要把os上面的文件放到asm里面，上面这条命令可以在os和asm之间转换数据文件。
impdp system/oracle@orcl directory=dp dumpfile=dmpfile.dmp nologfile=y transport_datafiles='+DATAGROUP/ORCL/DATAFILE/TTS.290.755528197';
b.使用文件系统，使用dump导入。
impdp system/oracle@orcl directory=dp dumpfile=dmpfile.dmp nologfile=y transport_datafiles='/opt/oradata/tts.288.755520393';
c.@/opt/tts/td/impsctpt.sql
9.复制源数据库/s01/dd目录和数据文件到目标数据库D:\dd
D:\dd>ls
dmpfile.alg dmpfile.dmp impscrpt.sql tt01.dbf
10.检查源数据库和目标数据库的数据块大小是否一致
源数据库：
SQL> select block_size from dba_tablespaces where tablespace_name = 'TT1';
BLOCK_SIZE
----------
8192
目标数据库：
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ---------------------- ---------
db_block_size integer 8192
11.在目标数据上创建用户
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 6 14:25:09 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user tt1 identified by oracle;
User created.
SQL> grant connect,resource to tt1;
Grant succeeded.
运行impscrpt.sql脚本
SQL> @D:\dd\impscrpt.sql
Directory created.
Directory created.
DECLARE
*
ERROR at line 1:
ORA-06512: at "SYS.DBMS_STREAMS_TABLESPACE_ADM", line 1854
ORA-06512: at line 18
Directory dropped.
Directory dropped.
查看dmpfile.alg文件，发现
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_04" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_04":
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 "SYS"."SYS_IMPORT_TRANSPORTABLE_04" stopped due to fatal error at 14:58:40提示不能将表空间导入到字符集不兼容的数据库
查看源数据库和目标数据库的字符集
源数据库：
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
目标数据库：
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
------------------------------------------
AMERICAN_AMERICA.AL32UTF8
字符集确实不一致，但是我可以使用impdp手动导入设置，在win下设置NLS_LANG
按照自己的想法试试
SQL> create directory dp as 'd:\dd';
Directory created.
D:\dd>impdp system/oracle@orcl directory=dp dumpfile=dmpfile.dmp nologfile=y transport_datafiles='tt01.dbf';
Import: Release 11.2.0.1.0 - Production on Thu Mar 6 14:51:07 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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/********@orcl directory=dp dumpfile=dmpfile.dmp nologfile=y transport_datafiles='tt01.dbf';
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 14:51:15
发现报同样的错误，字符集不兼容。修改目标数据库的字符集为源数据库字符集
SQL> conn / as sysdba
Connected to an idle instance.
SQL> alter database character set INTERNAL_USE WE8MSWIN1252;
alter database character set INTERNAL_USE WE8MSWIN1252
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup mount
ORACLE instance started.
Total System Global Area 1235959808 bytes
Fixed Size 2175288 bytes
Variable Size 956305096 bytes
Database Buffers 268435456 bytes
Redo Buffers 9043968 bytes
Database mounted.
SQL> alter database character set INTERNAL_USE WE8MSWIN1252;
alter database character set INTERNAL_USE WE8MSWIN1252
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode
SQL> alter system enable restricted session;
System altered.
SQL> alter database character set internal_use WE8MSWIN1252;
alter database character set internal_use WE8MSWIN1252
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01109: database not open
SQL> alter database open;
Database altered.
SQL> alter database character set internal_use WE8MSWIN1252;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1235959808 bytes
Fixed Size 2175288 bytes
Variable Size 956305096 bytes
Database Buffers 268435456 bytes
Redo Buffers 9043968 bytes
Database mounted.
SQL> alter system disable restricted session;
System altered.
SQL> alter database open;
Database altered.
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
再次使用impdp导入
D:\dd>impdp system/oracle@orcl directory=dp dumpfile=dmpfile.dmp nologfile=y transport_datafiles='d:\dd\tt01.dbf'
Import: Release 11.2.0.1.0 - Production on Thu Mar 6 15:59:43 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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/********@orcl directory=dp dumpfile=dmpfile.dmp nologfile=y transport_datafiles='d:\dd\tt01.dbf'
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 15:59:57
impdp执行，你可以运行impscrpt.sql，也是成功。因为两种方式报的错误都是一样的。
D:\dd>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 6 16:02:02 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter user tt1 defalut tablespace tt1;
alter user tt1 defalut tablespace tt1
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> alter user tt1 default tablespace tt1;
User altered.
目标端导入的表空间默认是READ-ONLY；将表空间改为READ WRITE
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name = 'TT1';
TABLESPACE_NAME STATUS
------------------------------ ---------
TT1 READ ONLY
SQL> alter tablespace tt1 read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name = 'TT1';
TABLESPACE_NAME STATUS
------------------------------ ---------
TT1 ONLINE
SQL> conn tt1/oracle
Connected.
SQL> select count(*) from t;
COUNT(*)
----------
21293
至此表空间从linux ASM磁盘组迁移到win FS系统下。
遇到的问题以及注意项
1.源数据库表空间的名字不能为test，因为test是RMAN的关键字。
RMAN> transport tablespace test tablespace destination "/s01/dd" auxiliary destination "/s01/dd";
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "test": expecting one of: "double-quoted-string, identifier, single-quoted-string"
RMAN-01007: at line 1 column 22 file: standard inputhttp://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmcomma001.htm#RCMRF909有说明
2.如果两个平台的字节顺序不一致，可以使用rman进行转化。
RMAN> convert tablespace tt1 to platform 'Microsoft Windows IA (32-bit)' format '/tmp/%N_%F';
RMAN> convert datafile '/tmp/BOOKS_5' db_file_name_convert '/tmp/BOOKS_5','/tmp/books01.dbf';
3.创建传输集过程报错如下
ORA-19502: write error on file "/s01/dd/HJJ/datafile/o1_mf_system_9kj14qc1_.dbf", block number 82816 (block size=8192)
ORA-27072: File I/O error
Linux Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 82816
Additional information: 344064
auxiliary instance file /s01/dd/HJJ/controlfile/o1_mf_9kj148lp_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of transport tablespace command at 03/05/2014 21:18:59
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA_DG/hjj/datafile/system.260.837919351'因为创建传输集时，需要一个临时目录/s01/dd，会将备份集中的datafile，onlinelog，controlfile存放于此，所以要提前估算好空间。
这个错误就是因为空间不足引起的。
思考：利用RMAN跨平台传输表空间，个人挺麻烦的，首先要备份全库，归档日志和控制文件，最后还得用imp/impdp进行导入。那为啥不直接使用imp/expdp导入，简单问题复杂化了，目前还不知道这种方式的优势在哪。
--------------------------------------推荐阅读 --------------------------------------
--------------------------------------分割线 --------------------------------------
148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
