事實上Transport Tablespace根本就是expdp 與 impdp 的方法之一，差別在實際的data仍放在datafile中，試想如果我要匯出的tablespace中的其中一個table有1百萬筆資料時，你覺得是expdpd含資料快還是直接複製datafile快??
Transport Tablespace的注意事項
Oracle8/8i=>兩個database必須有相同oracle版本,OS平台,block size,characterset
Oracle9i=>兩個database必須有相同oracle版本,OS平台,characterset.block size可以不相同
(可以有1個standard block,4個non-standard block同時存在)
Oracle10g/11g=>兩個database必須有相同oracle版本,characterset.block size與os平台可以不相同
如果你了解了，請參閱下方步驟...
Step 1.建立幾個驗證用資料環境
Step 1.1 建立一個tablespace transportA1
SQL> create tablespace transportA1 datafile '/u02/oradata/orcl/transportA101.dbf' size 100M;
Tablespace created.
Step 1.2 建立4個table (注意如果來源端所屬的table owner 目的端也要有)
SQL> create table hr.P1 tablespace transportA1 as select * from dba_objects;
Table created.
SQL> create table hr.P2 tablespace transportA1 as select * from hr.employees;
Table created.
SQL> create table scott.P3 tablespace transportA1 as select * from v$version;
Table created.
SQL> create table scott.P4 tablespace transportA1 as select * from dba_tables;
Table created.
Step 1.3 驗證table 計算筆數
SQL> select count(*) from hr.P1;
COUNT(*) = 72721
SQL> select count(*) from hr.P2;
COUNT(*) = 107
SQL> select count(*) from scott.P3;
COUNT(*) = 5
SQL> select count(*) from scott.P4;
COUNT(*) = 2818
Step 1.4 確認有datafile
RMAN> report schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 860 SYSTEM *** +DATA/orcl/datafile/system.256.820621145
2 800 SYSAUX *** +DATA/orcl/datafile/sysaux.257.818332275
3 325 UNDOTBS1 *** +DATA/orcl/datafile/undotbs1.258.818332275
4 120 USERS *** +DATA/orcl/datafile/users.259.820605537
5 100 EXAMPLE *** +DATA/orcl/datafile/example.265.818332605
6 100 DEMOTS1 *** /u02/oradata/orcl/demots01.dbf
7 100 FRANKTS *** /u02/oradata/orcl/frankts01.dbf
8 100 INDEX_TS *** /u02/oradata/orcl/index_ts01.dbf
9 100 TRANSPORTA1 *** /u02/oradata/orcl/transportA101.dbf
10 100 FDA_TS *** /u02/oradata/orcl/fda_ts01.dbf
11 3482 TEMP 32767 +DATA/orcl/tempfile/temp.264.818332591
Step 1.5 確認有不同的Owner table放在TRANSPORTA1 tablespace中
SQL> select table_name,tablespace_name,owner from dba_tables where tablespace_name='TRANSPORTA1';
TABLE_NAME TABLESPACE_NAME OWNER
-------------------- ------------------------------ --------------------
P3 TRANSPORTA1 SCOTT
P4 TRANSPORTA1 SCOTT
P1 TRANSPORTA1 HR
P2 TRANSPORTA1 HR
Step 2. Transport Tablespace (使用 expdp在相同ENDIAN_FORMAT)
在此步驟中，因為來源與目的地的ENDIAN_FORMAT均為Little因此可以直接使用
如果需要到不同Endian的Platform,需要使用rman轉換byte order (例如:
Linux IA 32-bit ( Little) to Solaris[tm] OE 64-bit( Big)
Step 2.1 檢驗fromlinux tablespace是否為self-contained
SQL> execute dbms_tts.transport_set_check('transporta1',true,true);
PL/SQL procedure successfully completed.
Step 2.2 確認結果
如果結果不是空的表示有違反self-contained
SQL> select * from transport_set_violations;
no rows selected
Step 2.3 Expdp tablespace
請在OS command下輸入:
[oracle@oracledb ~]$ expdp system/oracle directory=home_dir dumpfile=transporta1_ts_meta.dmp transport_tablespace=transporta1;
以下為執行過程將會把許多的object type一一匯出
Export: Release 11.2.0.1.0 - Production on Sat Jul 27 01:27:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, Replaced with: "transport_tablespaces="
Legacy Mode has set reuse_dumpfiles=true parameter.
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=home_dir dumpfile=transporta1_ts_meta.dmp reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE" 6.335 KB 2 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES" 6.890 KB 28 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD" 5.843 KB 19 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS" 5.492 KB 3 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES" 6.296 KB 2 rows
. . exported "SYSTEM"."DEF$_AQCALL" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_AQERROR" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_CALLDEST" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_DEFAULTDEST" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_DESTINATION" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_ERROR" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_LOB" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_ORIGIN" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_PROPAGATOR" 0 KB 0 rows
. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS" 0 KB 0 rows
. . exported "SYSTEM"."MVIEW$_ADV_INDEX" 0 KB 0 rows
. . exported "SYSTEM"."MVIEW$_ADV_PARTITION" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_CONFLICT" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_DDL" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_EXCEPTIONS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_EXTENSION" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_FLAVORS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_GENERATED" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCAT" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCATLOG" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPCOLUMN" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPOBJECT" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPPROP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_REPSCHEMA" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITES_NEW" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
完成後會把檔案放在剛剛設定的home_dir路徑
Directory home 路徑建法為:
SQL> create directory home_dir as '/home/oracle';
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/transporta1_ts_meta.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 01:30:37
Dump file >請copy放到目的端 Home_dir 路徑 (複製時，來源端Tablespace 仍保持 Read only)
Datafile >完成後請將transporta1 tablespace 變成read only 模式並將該datafile複製到目的端存放data file位置
Step 2A. Transport Tablespace (DATAFILE使用 RMAN轉換不同平台ENDIAN_FORMAT)
與Step 2A步驟相同，僅在datafile 必須先使用RMAN Convert後在複製到目的端位置上
使用RAMN並輸入以下指令
[oracle@oracledb ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jul 27 01:55:47 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1346113250)
RMAN> convert tablespace transporta1 to platform='Linux IA (32-bit)' format '/home/oracle/transporta2.dbf';
Starting conversion at source at 27-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=151 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00009 name=/u02/oradata/orcl/transportA101.dbf
converted datafile=/home/oracle/transporta2.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
Finished conversion at source at 27-JUL-13
Datafile >完成後請將transporta1 tablespace 變成read only 模式並將該datafile複製到目的端存放data file位置
Step 3. Impdp Tablespace meta
在Step 2 提到對於ENDIAN_FORMAT不同的兩種解決方法並已經將相對應的dump file 與 datafile複製到目的端去了，在匯入時方法都是一樣的。
dump file: transporta1_ts_meta.dmp
datafile: transportA101.dbf
dump file: transporta1_ts_meta2.dmp
datafile: transporta2.dbf
[oracle@oracledbEx3]>impdp system/oracle directory=home_dir3 dumpfile=transporta1_ts_meta.dmp transport_datafiles=/u02/oradata/orcl/transport_folder/transportA1.dbf
Import: Release 11.2.0.1.0 - Production on Sat Jul 27 05:46:29 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, 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=home_dir dumpfile=transporta1_ts_meta.dmp transport_datafiles=/u02/oradata/orcl/transport_folder/transportA1.dbf
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 05:46:37
Step 4-1 檢查Datafile
SQL>select file_id,file_name,status from dba_data_files;
FILE_NAME FILE_ID STATUS ONLINE_
------------------------------ ---------- --------- -------
4 +DATA/orcl/datafile/users.259.820605537 AVAILABLE
3 +DATA/orcl/datafile/undotbs1.258.818332275 AVAILABLE
2 +DATA/orcl/datafile/sysaux.257.818332275 AVAILABLE
1 +DATA/orcl/datafile/system.256.820621145 AVAILABLE
5 +DATA/orcl/datafile/example.265.818332605 AVAILABLE
6 /u02/oradata/orcl/demots01.dbf AVAILABLE
7 /u02/oradata/orcl/frankts01.dbf AVAILABLE
8 /u02/oradata/orcl/index_ts01.dbf AVAILABLE
10 /u02/oradata/orcl/fda_ts01.dbf AVAILABLE
9 /u02/oradata/orcl/transporttx101.dbf AVAILABLE
11 /u02/oradata/orcl/transport_folder/transportA1.dbf AVAILABLE
Step 4-2 檢查Tablespace
SQL>select tablespace_name,status from dba_tablespaces ;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
DEMOTS1 ONLINE
INDEX_TS ONLINE
FRANKTS ONLINE
FDA_TS ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
TRANSPORTA1 READ ONLY
Step 4-3 檢查筆數是否正確
SQL> select count(*) from hr.P1;
COUNT(*) = 72721
SQL> select count(*) from hr.P2;
COUNT(*) = 107
SQL> select count(*) from scott.P3;
COUNT(*) = 5
SQL> select count(*) from scott.P4;
COUNT(*) = 2818
SQL> alter tablespace transporta1 read write;
HI ，ˇ我想問個問題
回覆刪除