分类： Oracle
2017-07-12 14:44:19
从11g开始，oracle支持跨平台传输表空间。
查看支持平台列表，如果源库和目标库的endian format不一致，需要convert
SQL> col platform_name for a32; SQL> select * from v$transportable_platform; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- -------------------------------- ---------------------------- 1 Solaris[tm] OE (32-bit) Big 2 Solaris[tm] OE (64-bit) Big 7 Microsoft Windows IA (32-bit) Little 10 Linux IA (32-bit) Little 6 AIX-Based Systems (64-bit) Big 3 HP-UX (64-bit) Big 5 HP Tru64 UNIX Little 4 HP-UX IA (64-bit) Big 11 Linux IA (64-bit) Little 15 HP Open VMS Little 8 Microsoft Windows IA (64-bit) Little PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- -------------------------------- ---------------------------- 9 IBM zSeries Based Linux Big 13 Linux x86 64-bit Little 16 Apple Mac OS Big 12 Microsoft Windows x86 64-bit Little 17 Solaris Operating System (x86) Little 18 IBM Power Based Linux Big 19 HP IA Open VMS Little 20 Solaris Operating System (x86-64 Little ) 21 Apple Mac OS (x86-64) Little 20 rows selected.
1.源库和目标库必须是同样的字符集和国家字符集
SQL> col parameter for a32; SQL> col value for a30; SQL> select * from nls_database_parameters; PARAMETER VALUE -------------------------------- ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CHARACTERSET AL32UTF8 NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 11.2.0.3.0 ...... ......
2.所有要传输的表空间对象必须是self-contained.意为：A表空间里的对象有引用B表空间的对象，那么表空间A和B都必须包含在transpotable set里。
不过不用担心，有DBMS_TTS包帮我们检查
下面开始实验吧：
source : windows oracle 11203 64bit
target : OEL 6.3 oracle 11203 64bit
platform,endian检查：
source:
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 -------------------------------- --------------------------- Microsoft Windows x86 64-bit Little
target:
SQL> col platform_name for a30; 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 x86 64-bit Little
检查表空间是否是self-cotained:
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('ERM',TRUE); PL/SQL procedure successfully completed.
在TRANSPORT_SET_VIOLATIONS视图查看执行结果，如果有依赖对象不在提供的tablespace里，会给出详细提示
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; no rows selected
生成传输表空间集：
SQL> ALTER TABLESPACE ERM READ ONLY; Tablespace altered. SQL> create directory erm_dump_dir as 'E:\app\susu\dumpdir\'; Directory created. SQL> grant read,write on directory erm_dump_dir to szpdc; Grant succeeded. SQL> host Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:\Windows\system32>expdp system/password dumpfile=erm_tts.dmp directory=erm_dum p_dir transport_tablespaces=ERM
使用RMAN convert转换文件格式：
C:\Windows\system32>rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Fri May 24 14:41:32 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ERMDB (DBID=1977612728) RMAN> convert tablespace ERM to platform 'Linux x86 64-bit' format 'e:\%N%f'; Starting conversion at source at 24-MAY-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=194 device type=DISK channel ORA_DISK_1: starting datafile conversion input datafile file number=00006 name=E:\APP\SUSU\ORADATA\ERMDB\ERM01.DBF converted datafile=E:\ERM6 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 Finished conversion at source at 24-MAY-13
将RMAN CONVERT生成的transport tablespace set和 expdp导出的文件 copy到目标数据库
我这里拷贝到了
[oracle@db1 dumpdir]$ pwd /s01/app/oracle/dumpdir [oracle@db1 dumpdir]$ ls ERM6 ERM_TTS.DMP import.log
接下来，在目标数据库操作：
RMAN> convert datafile '/s01/app/oracle/dumpdir/ERM6' db_file_name_convert '/s01/app/oracle/dumpdir/ERM6','/s01/app/oracle/oradata/DB11G/erm01w.dbf'; Starting conversion at target at 24-MAY-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile conversion input file name=/s01/app/oracle/dumpdir/ERM6 converted datafile=/s01/app/oracle/oradata/DB11G/erm01w.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07 Finished conversion at target at 24-MAY-13 Starting Control File and SPFILE Autobackup at 24-MAY-13 piece handle=/s01/app/oracle/fast_recovery_area/DB11G/autobackup/2013_05_24/o1_mf_s_816275177_8sy4cc2w_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-MAY-13
[oracle@db1 dumpdir]$ impdp system/password directory=ERM_DUMP_DIR dumpfile=ERM_TTS.DMP transport_datafiles=/s01/app/oracle/oradata/DB11G/erm01w.dbf remap_schema=SZPDC:ERM Import: Release 11.2.0.3.0 - Production on Fri May 24 15:11:15 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.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/******** directory=ERM_DUMP_DIR dumpfile=ERM_TTS.DMP transport_datafiles=/s01/app/oracle/oradata/DB11G/erm01w.dbf remap_schema=SZPDC:ERM 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:11:31
大功告成，查询
select * from erm.test; 中文没有乱码
