[oracle實驗]跨平台傳輸資料表空間 win -> linux

文章目錄

• 使用傳輸資料表空間的限制：

從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)    LittlePLATFORM_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)            Little20 rows selected.

使用傳輸資料表空間的限制：

1.源庫和目標庫必須是同樣的字元集和國家字元集 

SQL> col parameter for a32;SQL> col value for a30;SQL> select * from nls_database_parameters;PARAMETER                        VALUE-------------------------------- ------------------------------NLS_LANGUAGE                     AMERICANNLS_TERRITORY                    AMERICANLS_CHARACTERSET                 AL32UTF8NLS_NCHAR_CHARACTERSET           AL16UTF16NLS_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> hostMicrosoft 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_dump_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 2013Copyright (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-13using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=194 device type=DISKchannel ORA_DISK_1: starting datafile conversioninput datafile file number=00006 name=E:\APP\SUSU\ORADATA\ERMDB\ERM01.DBFconverted datafile=E:\ERM6channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03Finished 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]$ lsERM6  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-13using channel ORA_DISK_1channel ORA_DISK_1: starting datafile conversioninput file name=/s01/app/oracle/dumpdir/ERM6converted datafile=/s01/app/oracle/oradata/DB11G/erm01w.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07Finished conversion at target at 24-MAY-13Starting Control File and SPFILE Autobackup at 24-MAY-13piece handle=/s01/app/oracle/fast_recovery_area/DB11G/autobackup/2013_05_24/o1_mf_s_816275177_8sy4cc2w_.bkp comment=NONEFinished 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:ERMImport: Release 11.2.0.3.0 - Production on Fri May 24 15:11:15 2013Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloadedStarting "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_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 15:11:31

大功告成，查詢
select * from erm.test; 中文沒有亂碼