• 博客访问: 1879718
  • 博文数量: 575
  • 博客积分: 10716
  • 博客等级: 上将
  • 技术积分: 7191
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-01 22:35
文章分类

全部博文(575)

文章存档

2017年(30)

2016年(22)

2015年(1)

2013年(12)

2012年(20)

2011年(18)

2010年(18)

2009年(271)

2008年(183)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
跨平台表空间传输WIN-LIUNX 2017-07-12 14:44:19

分类: Oracle

从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; 中文没有乱码

阅读(45) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册