10g跨小版本的expdp/impdp表空間遷移(一)

由於工作關係，需要將10.2.0.4的表空間遷移到10.2.0.1的資料庫上，先試了一個跨小版本表空間遷移的可行性。[@more@]

1.確定自包含表空間集合。自包含表空間集合是指具有關聯關係的表空間集合。當搬移表空間是，如果在兩個表空間之間存在關聯關係，則必須同時搬移這兩個表空間。

透過執行包DBMS_TTS的過程TRANSPORT_SET_CHECK可以完成表空間集合是否為自包含的檢測。執行完該過程後，系統將違反子包含表空間集合規則的資訊寫入到臨時表transport_set_violations。

SQL> exec sys.dbms_tts.transport_set_check('VIP2006');

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

2.匯出表空間的後設資料

[oracle@qht107 ~]$ expdp l5m/l5m directory=dump_dir dumpfile=vip2006.dmp

transport_tablespaces=VIP2006

Export: Release 10.2.0.4.0 - Production on Monday, 09 February, 2009 14:18:13

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "L5M"."SYS_EXPORT_TRANSPORTABLE_01": l5m/******** directory=dump_dir

dumpfile=vip2006.dmp transport_tablespaces=VIP2006
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29335: tablespace 'VIP2006' is not read only

Job "L5M"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 14:18:15

表空間移植時必須readonly表空間。

SQL> alter tablespace vip2006 read only;

Tablespace altered.

[oracle@qht107 ~]$ expdp l5m/l5m directory=dump_dir dumpfile=vip2006.dmp

transport_tablespaces=VIP2006

Export: Release 10.2.0.4.0 - Production on Monday, 09 February, 2009 14:19:49

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "L5M"."SYS_EXPORT_TRANSPORTABLE_01": l5m/******** directory=dump_dir

dumpfile=vip2006.dmp transport_tablespaces=VIP2006
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "L5M"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for L5M.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/oradata/dump_dir/vip2006.dmp
Job "L5M"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:20:18

3.將後設資料和資料庫複製到目標庫

[oracle@qht107 ~]$ scp /u01/oradata/orcl/VIP2006_01.dbf

172.17.61.131:/u01/app/oracle/admin/orcl/dpdump

[oracle@qht107 oradata]$ cd /u01/oradata/dump_dir/

[oracle@qht107 ~]$ scp vip2006.dmp 172.17.61.131:/u01/app/oracle/admin/orcl/dpdump

4.impdp匯入到目錄庫

[oracle@rhel131 ~]$ impdp dumpfile=vip2006.dmp

transport_datafiles='VIP2006_001.dbf' directory=DATA_PUMP_DIR

Import: Release 10.2.0.1.0 - Production on Saturday, 07 February, 2009 1:35:36

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": dumpfile=vip2006.dmp

transport_datafiles=VIP2006_001.dbf directory=DATA_PUMP_DIR
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-00721: changes by release 10.2.0.3.0 cannot be used by release 10.2.0.1.0

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 01:35:46

發現從高版本無法匯入到底版本的資料庫。

expdp有個version的引數，不要誤以為加了這個引數就是能夠匯出低版本的dmp檔案，這個引數只是匯出指定引數版本完全相容的資料型別。文件上是這樣寫的：

Specifies the version of database objects to be exported. This can be used to create a dump

file set that is compatible with a previous release of Oracle Database. Note that this does

not mean that Data Pump Export can be used with versions of Oracle Database prior to 10.1.

Data Pump Export only works with Oracle Database 10g release 1 (10.1) or later. The VERSION

parameter simply allows you to identify the version of the objects being exported.

VERSION={COMPATIBLE | LATEST | version_string}

Database objects or attributes that are incompatible with the specified version will not be

exported. For example, tables containing new datatypes that are not supported in the

specified version will not be exported.

接著試著把10.2.0.1版本的expdp文件複製過去執行，但是不象exp一樣可行。可以從輸出看到執行的還是10204的版本。

[oracle@qht107 ~]$ ./expdp10201 l5m/l5m directory=dump_dir dumpfile=vip2006.dmp

transport_tablespaces=VIP2006

Export: Release 10.2.0.4.0 - Production on Monday, 09 February, 2009 15:51:02

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "L5M"."SYS_EXPORT_TRANSPORTABLE_01": l5m/******** directory=dump_dir

dumpfile=vip2006.dmp transport_tablespaces=VIP2006
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "L5M"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for L5M.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/oradata/dump_dir/vip2006.dmp
Job "L5M"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:51:27

透過簡單的測試，遇到了一點小麻煩，由於10.2.0.4資料庫的compatible引數為10.2.0.3，如兩臺資料庫的compatible是一致的，會不會成功？