Oracle 10g的可傳輸表空間操作（轉並驗證）

xlfsy發表於2010-10-12
Oracle 10g

最近做了一次跨平臺的資料遷移，遷移要求是把部分使用者的資料遷移到另一個資料庫中。
源資料庫環境是Solaris 10(x86_64) + ORACLE 10203 RAC + ASM
目標資料庫環境是Linux AS 4(x86) + ORACLE 10203 + 檔案系統

我在這次遷移用的是傳輸表空間技術。


1、檢查需要傳輸的使用者對應的表空間

SQL> SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='NDMAIN';

TABLESPACE_NAME
------------------------------
NDMAIN

SQL> SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='ZHEJIANG';

TABLESPACE_NAME
------------------------------
ZHEJIANG

2、查詢是否滿足傳輸表空間的條件
1）查詢是否支援平臺轉換
--查詢源庫的平臺資訊
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
---------------------------------------- --------------
Solaris[tm] OE (64-bit) Big

--查詢目標庫的平臺資訊
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

--從v$transportable_platform檢視查詢以上兩個平臺的備份集是否可以轉換
SQL> select * from v$transportable_platform where PLATFORM_NAME in ('Solaris[tm] OE (64-bit)','Linux IA (32-bit)');

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
2 Solaris[tm] OE (64-bit) Big
10 Linux IA (32-bit) Little
有兩個記錄，表示這兩種平臺下的備份集可以互相轉換稱對應的平臺備份集。

2）檢視錶空間集是否是子包含
如果某個表空間集引用了其他表空間的物件（如外來鍵引用的表在其他表空間、表空間包含物化檢視等），則這個表空間不是自包含的；否則就是自包含。
只有自包含的表空間集才可以用表空間傳輸技術。

oracle提供dbms_tts.transport_set_check過程來幫助我們判斷某個表空間集是否是自包含的：
SQL> execute dbms_tts.transport_set_check('NDMAIN,ZHEJIANG',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

如果查詢TRANSPORT_SET_VIOLATIONS有記錄返回，則表示表空間集不是自包含的，無法對這些表空間使用表空間傳輸技術；反之，則表示表空間集是自包含的，可以對這些表空間使用表空間傳輸技術。

注意：
1）使用dbms_tts.transport_set_check時，要把需要表空間傳輸的表空間列表都寫上，用都好分割，不要每一個表空間執行一次dbms_tts.transport_set_check，這樣可能會引起誤解：
例如：
A表在表空間T1上，
B表在表空間T2上，並且B表上有一個外來鍵引用A表。
如果要遷移A、B兩個表空間，如果分別執行：
execute dbms_tts.transport_set_check('T1',TRUE);
execute dbms_tts.transport_set_check('T2',TRUE);
則在檢查T2時TRANSPORT_SET_VIOLATIONS肯定會有記錄，因為B表引用了A表，兩個表不在一個表空間上。
但實際上，A、B兩個表的表空間都在我們要遷移的範圍內，所以遷移後資料的完整性也是有保證的，這個問題就在於我們把T1、T2分別檢驗造成了誤解。
正確的方法應該是：
execute dbms_tts.transport_set_check('T1,T2',TRUE);
2）TRANSPORT_SET_VIOLATIONS是一個臨時表，必須在執行dbms_tts.transport_set_check同一個會話中查詢這個表。


3、在源庫匯出資料
1）建立directory
SQL> create directory tran as '/backup/dmp';

Directory created.

2）把對應表空間置於只讀模式
SQL> alter tablespace ndmain read only;

Tablespace altered.

SQL> alter tablespace zhejiang read only;

Tablespace altered.

3）用資料泵匯出
指定了transport_tablespaces後，資料泵匯出的只是一些結構方面的資訊，所以匯出檔案會比較小。
bash-3.00$ expdp system/test directory=tran transport_tablespaces=ndmain,zhejiang

Export: Release 10.2.0.3.0 - 64bit Production on 星期二, 23 10月, 2007 1:21:36

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=tran transport_tablespaces=ndmain,zhejiang
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/backup/dmp/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:24:55


4、轉換資料檔案格式
由於源資料庫和目標資料庫所在平臺不同，所以要把要傳輸的表空間對應的資料檔案轉換為目標平臺的格式。
10g下rman提供這一功能，你可以選擇在源資料庫或者目標資料庫進行轉換：
1）在源資料庫轉換：conver tablespace ... to platform ....
2）在目標資料庫轉換：conver datafile ... from fplatform ....<source>

在這裡選擇第一種方式：
bash-3.00$ export ORACLE_SID=prerac1
bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on 星期二 10月 23 01:26:01 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: PRETRADE (DBID=3201410814)

RMAN> run{
2> allocate channel c1 device type disk connect ;
3> convert tablespace ndmain,zhejiang to platform 'Linux IA (32-bit)' Format '/backup/dmp/%U';4> release channel c1;
5> }

--這段指令碼的意思是把ndmain、zhejiang這兩個表空間對應的資料檔案轉換成Linux IA (32-bit)，並把轉換後的資料檔案放在/backup/dmp下

allocated channel: c1
channel c1: sid=316 instance=prerac1 devtype=DISK

Starting backup at 23-10月-07
channel c1: starting datafile conversion
input datafile fno=00014 name=+DATA/datafile/ndmain1.ora
converted datafile=/backup/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-14_64iv64lb
channel c1: datafile conversion complete, elapsed time: 00:05:06
......
channel c1: starting datafile conversion
input datafile fno=00038 name=+DATA/datafile/zhejiang18.ora
converted datafile=/backup/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-38_6oiv6bac
channel c1: datafile conversion complete, elapsed time: 00:05:45
Finished backup at 23-10月-07

released channel: c1


5、傳送檔案到目標資料庫
[oracle@tdata dmp]$ sftp 172.0.2.1
Password:
sftp> cd /backup
sftp> ls
dmp lost+found soft
sftp> cd dmp
sftp> mget *


6、在目標資料庫建立對應的使用者及授權

在匯入資料時，對應的使用者必須已經存在，並有合適的許可權，否則會報錯。
[oracle@tdata dmp]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 10月 23 09:32:00 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options

SQL> create user ndmain identified by testndmain202;

User created.

SQL> create user zhejiang identified by testzj202;

User created.

SQL> create user ZHEJIANG_OPERATOR identified by testzjop202;

User created.

SQL> create user ZHEJIANG_KHD identified by testzjkhd202;

User created.

SQL> grant connect,resource to ndmain;

Grant succeeded.

SQL> grant connect,resource to zhejiang;

Grant succeeded.

SQL> grant connect,resource to ZHEJIANG_OPERATOR;

Grant succeeded.

SQL> grant connect,resource to ZHEJIANG_KHD;

Grant succeeded.

SQL> grant create materialized view to zhejiang;

Grant succeeded.

SQL> grant create materialized view to ndmain;

Grant succeeded.

SQL> grant create synonym to zhejiang;

Grant succeeded.

SQL> grant create synonym to ndmain;

Grant succeeded.

SQL> grant create materialized view to ZHEJIANG_KHD;

Grant succeeded.

SQL> grant create materialized view to ZHEJIANG_OPERATOR;

Grant succeeded.

SQL> grant create synonym to ZHEJIANG_KHD;

Grant succeeded.

SQL> grant create synonym to ZHEJIANG_OPERATOR;

Grant succeeded.

--建立directory
SQL> create directory tran as '/oradata/dmp';

Directory created.


7、在目標庫轉換檔案路徑和名稱
在目標資料庫以rman執行：
run{
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-14_64iv64lb' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-14_64iv64lb' , '/oradata/oradata/testzj/ndmain1.dbf';
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-15_65iv64ut' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-15_65iv64ut' , '/oradata/oradata/testzj/ndmain2.dbf';
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-16_66iv658p' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-16_66iv658p' , '/oradata/oradata/testzj/ndmain3.dbf';
......
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-36_6miv6ald' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-36_6miv6ald' , '/oradata/oradata/testzj/zhejiang16.dbf';
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-37_6niv6b06' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-37_6niv6b06' , '/oradata/oradata/testzj/zhejiang17.dbf';
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-38_6oiv6bac' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-38_6oiv6bac' , '/oradata/oradata/testzj/zhejiang18.dbf';}
--這段話的意思是把源資料庫複製過來的檔案複製並重新命名。

8、把資料匯入到目標庫中

[oracle@tdata dmp]$ impdp system/test dumpfile=expdat.dmp directory=tran exclude=transportable_export/materialized_view,transportable_export/table_statistics,transportable_export/grant/owner_grant/object_grant transport_datafiles='/oradata/oradata/testzj/ndmain1.dbf','/oradata/oradata/testzj/ndmain2.dbf','/oradata/oradata/testzj/ndmain3.dbf','/oradata/oradata/testzj/zhejiang1.dbf','/oradata/oradata/testzj/zhejiang2.dbf','/oradata/oradata/testzj/zhejiang3.dbf','/oradata/oradata/testzj/zhejiang4.dbf','/oradata/oradata/testzj/zhejiang5.dbf','/oradata/oradata/testzj/zhejiang6.dbf','/oradata/oradata/testzj/zhejiang7.dbf','/oradata/oradata/testzj/zhejiang8.dbf','/oradata/oradata/testzj/zhejiang9.dbf','/oradata/oradata/testzj/zhejiang10.dbf','/oradata/oradata/testzj/zhejiang11.dbf','/oradata/oradata/testzj/zhejiang12.dbf','/oradata/oradata/testzj/zhejiang13.dbf','/oradata/oradata/testzj/zhejiang14.dbf','/oradata/oradata/testzj/zhejiang15.dbf','/oradata/oradata/testzj/zhejiang16.dbf','/oradata/oradata/testzj/zhejiang17.dbf','/oradata/oradata/testzj/zhejiang18.dbf'

Import: Release 10.2.0.3.0 - Production on 星期二, 23 10月, 2007 17:08:14

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=tran exclude=transportable_export/materialized_view,transportable_export/table_statistics,transportable_export/grant/owner_grant/object_grant transport_datafiles=/oradata/oradata/testzj/ndmain1.dbf,/oradata/oradata/testzj/ndmain2.dbf,/oradata/oradata/testzj/ndmain3.dbf,/oradata/oradata/testzj/zhejiang1.dbf,/oradata/oradata/testzj/zhejiang2.dbf,/oradata/oradata/testzj/zhejiang3.dbf,/oradata/oradata/testzj/zhejiang4.dbf,/oradata/oradata/testzj/zhejiang5.dbf,/oradata/oradata/testzj/zhejiang6.dbf,/oradata/oradata/testzj/zhejiang7.dbf,/oradata/oradata/testzj/zhejiang8.dbf,/oradata/oradata/testzj/zhejiang9.dbf,/oradata/oradata/testzj/zhejiang10.dbf,/oradata/oradata/testzj/zhejiang11.dbf,/oradata/oradata/testzj/zhejiang12.dbf,/oradata/oradata/testzj/zhejiang13.dbf,/oradata/oradata/testzj/zhejiang14.dbf,/oradata/oradata/testzj/zhejiang15.dbf,/oradata/oradata/testzj/zhejiang16.dbf,/oradata/oradata/testzj/zhejiang17.dbf,/oradata/oradata/testzj/zhejiang18.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 17:10:00


在匯入時有三個部分物件排除出去：
transportable_export/materialized_view：因為是測試環境，不需要同步資料，所以沒有必要建立物化檢視
transportable_export/grant/owner_grant/object_grant：因為新環境有很多使用者不存在，保留授權會導致一大堆的報錯資訊，故排除
transportable_export/table_statistics：不匯入表統計資訊是為了避免bug：
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-06550: line 12, column 17:
PL/SQL: ORA-00917: missing comma
ORA-06550: line 4, column 127:
PL/SQL: SQL Statement ignored


注意：用傳輸表空間的方法，只是把傳輸過來的表空間的資料都導過來，使用者的很多物件如序列、檢視等是不會導過來的，你需要手工補上這些物件。

[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14844492/viewspace-1039628/，如需轉載，請註明出處，否則將追究法律責任。

相關文章