oracle跨版本與平臺執行傳輸表空間
將aix(10.2.0.4)平臺上的源資料庫中的tspitr表空間傳到linux(11.2.0.4)平臺，並在源主機上使用目錄/yb_oradata/transport來儲存被轉換的資料檔案。操作步驟如下:
1.將要被傳輸的表空間tspitr設定為只讀
SQL> alter tablespace tspitr read only; Tablespace altered. SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPITR'; TABLESPACE_NAME STATUS ------------------------------ --------- TSPITR READ ONLY
2.檢查源平臺與目標平臺資訊看是滯支援傳輸操作，資料庫所支援的平臺資訊如下:
SQL> select platform_name,endian_format from v$transportable_platform; PLATFORM_NAME ENDIAN_FORMAT -------------------------------------------------------------------------------- -------------- Solaris[tm] OE (32-bit) Big Solaris[tm] OE (64-bit) Big Microsoft Windows IA (32-bit) Little Linux IA (32-bit) Little AIX-Based Systems (64-bit) Big HP-UX (64-bit) Big HP Tru64 UNIX Little HP-UX IA (64-bit) Big Linux IA (64-bit) Little HP Open VMS Little Microsoft Windows IA (64-bit) Little IBM zSeries Based Linux Big Linux x86 64-bit Little Apple Mac OS Big Microsoft Windows x86 64-bit Little Solaris Operating System (x86) Little IBM Power Based Linux Big Solaris Operating System (x86-64) Little HP IA Open VMS Little
源平臺:
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 -------------------------------------------------------------------------------- -------------- AIX-Based Systems (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 x86 64-bit Little
3.確認要被傳輸的表空間是否是自包含表空間(TSPITR):
SQL> exec sys.dbms_tts.transport_set_check('TSPITR',true);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected
沒有記錄，表示該表空間只包含表資料，可以傳輸。
4.記錄表空間傳輸前表tspitr中的記錄：
SQL> select count(*) from tspitr.tspitr;
COUNT(*)
----------
50315
5.使用RMAN將源資料庫中的表空間tspitrt轉換為目標平臺位元組序格式，使用format引數來控制被轉換後資料檔案的檔名和儲存目錄.
[IBMP740-1:oracle:/yb_oradata/transport]$export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss' [IBMP740-1:oracle:/yb_oradata/transport]$rman target/ Recovery Manager: Release 10.2.0.4.0 - Production on Mon Oct 24 17:07:59 2016 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: RLZY (DBID=1589671076) RMAN> convert tablespace "TSPITR" to platform 'Linux x86 64-bit' format ='/yb_oradata/transport/%U'; Starting backup at 2016-10-24 17:09:31 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=1265 devtype=DISK channel ORA_DISK_1: starting datafile conversion input datafile fno=00021 name=/yb_oradata/transport_after/TSPITR01.DBF converted datafile=/yb_oradata/transport/data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 Finished backup at 2016-10-24 17:09:35 [IBMP740-1:oracle:/yb_oradata/transport]$ls -lrt total 102416 -rw-r----- 1 oracle dba 52436992 Oct 24 17:09 data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic
6.使用匯出工具建立傳輸表空間後設資料dump檔案
SQL> create or replace directory test_dump as '/yb_oradata/transport'; Directory created. SQL> grant read,write on directory test_dump to public; Grant succeeded. -rw-r----- 1 oracle dba 52436992 Oct 24 17:09 data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic [IBMP740-1:oracle:/yb_oradata/transport]$ expdp \'sys/admin_7817600@RLZY as sysdba\' directory=test_dump dumpfile=tspitr.dmp logfile=tspitr.log transport_tablespaces=TSPITR < Export: Release 10.2.0.4.0 - 64bit Production on Monday, 24 October, 2016 17:12:42 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": 'sys/********@RLZY AS SYSDBA' directory=test_dump dumpfile=tspitr.dmp logfile=tspitr.log transport_tablespaces=TSPITR Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is: /yb_oradata/transport/tspitr.dmp Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:13:41
匯出除表之外的使用者tspitr中的其它物件的後設資料
[IBMP740-1:oracle:/yb_oradata/transport]$expdp tspitr/tspitr directory=test_dump dumpfile=tspitr_metadata_only.dmp logfile=tspitr_metadata_only.log content=metadata_only exclude=table Export: Release 10.2.0.4.0 - 64bit Production on Monday, 24 October, 2016 17:45:22 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "TSPITR"."SYS_EXPORT_SCHEMA_01": tspitr/******** directory=test_dump dumpfile=tspitr_metadata_only.dmp logfile=tspitr_metadata_only.log content=metadata_only exclude=table 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/VIEW/VIEW Master table "TSPITR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TSPITR.SYS_EXPORT_SCHEMA_01 is: /yb_oradata/transport/tspitr_metadata_only.dmp Job "TSPITR"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:45:25
7.將轉換後儲存在/u02/transport目錄中的資料檔案與匯出的後設資料檔案,傳輸到目標主機的目錄/home/transport中
SQL> create or replace directory test_dump as '/home/transport'; Directory created. SQL> grant read,write on directory test_dump to public; Grant succeeded. [oracle@sjjh transport]$ ftp 10.138.129.3 Connected to 10.138.129.3. 220 IBMP740-1 FTP server (Version 4.2 Mon Nov 28 14:12:02 CST 2011) ready. 502 authentication type cannot be set to GSSAPI Name (10.138.129.3:oracle): oracle 331 Password required for oracle. Password: 230-Last unsuccessful login: Mon Dec 1 16:07:13 BEIST 2014 on ftp from ::ffff:10.138.135.235 230-Last login: Mon Oct 24 14:51:18 BEIST 2016 on /dev/pts/0 from 10.138.133.203 230 User oracle logged in. Remote system type is UNIX. Using binary mode to transfer files. ftp> cd /yb_oradata/transport 250 CWD command successful. ftp> lcd /home/transport Local directory now /home/transport ftp> bin 200 Type set to I. ftp> get data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic local: data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic remote: data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic 227 Entering Passive Mode (10,138,129,3,215,220) 150 Opening data connection for data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic (52436992 bytes). 226 Transfer complete. 52436992 bytes received in 0.5 seconds (1e+05 Kbytes/s) ftp> get tspitr.dmp local: tspitr.dmp remote: tspitr.dmp 227 Entering Passive Mode (10,138,129,3,215,222) 150 Opening data connection for tspitr.dmp (86016 bytes). 226 Transfer complete. 86016 bytes received in 0.0028 seconds (3e+04 Kbytes/s) ftp> get tspitr_metadata_only.dmp local: tspitr_metadata_only.dmp remote: tspitr_metadata_only.dmp 227 Entering Passive Mode (10,138,129,3,217,16) 150 Opening data connection for tspitr_metadata_only.dmp (147456 bytes). 226 Transfer complete. 147456 bytes received in 0.0036 seconds (4e+04 Kbytes/s)
8.將要被傳輸的表空間附加到目標資料庫中
SQL> create user tspitr identified by "tspitr"; User created. SQL> grant dba,connect,resource to tspitr; Grant succeeded. SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TSPITR'; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ TSPITR USERS TEMP [oracle@sjjh transport]$ impdp \'sys/xxzx7817600@SJJH as sysdba\' directory=test_dump dumpfile=tspitr.dmp transport_datafiles=/home/transport/data_D-RLZY_I- 1589671076_TS-TSPITR_FNO-21_orrj67ic Import: Release 11.2.0.4.0 - Production on Mon Oct 24 17:22:29 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/********@SJJH AS SYSDBA" directory=test_dump dumpfile=tspitr.dmp transport_datafiles=/home/transport/data_D- RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Oct 24 17:22:37 2016 elapsed 0 00:00:04
匯入使用者tspitr下其它物件的後設資料:
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPITR'; TABLESPACE_NAME STATUS ------------------------------ --------- TSPITR READ ONLY SQL> alter tablespace tspitr read write; Tablespace altered. [oracle@sjjh transport]$ impdp tspitr/tspitr directory=test_dump dumpfile=tspitr_metadata_only.dmp Import: Release 11.2.0.4.0 - Production on Mon Oct 24 17:47:38 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "TSPITR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "TSPITR"."SYS_IMPORT_FULL_01": tspitr/******** directory=test_dump dumpfile=tspitr_metadata_only.dmp Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"TSPITR" already exists 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/VIEW/VIEW Job "TSPITR"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Mon Oct 24 17:47:40 2016 elapsed 0 00:00:01 SQL> select owner,view_name,text from dba_views where owner='TSPITR'; OWNER VIEW_NAME TEXT ------------------------------ ------------------------------ -------------------------------------------------------------------------------- TSPITR TSPITR_VIEW select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE
查詢tspitr表中的記錄，與源資料庫中的記錄數一致。
SQL> select count(*) from tspitr.tspitr;
COUNT(*)
----------
50315
現在使用者tspitr的預設表空間不是tspitr
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TSPITR'; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ TSPITR USERS TEMP
將使用者tspitr的預設表空間修改為tspitr
SQL> alter user tspitr default tablespace tspitr;
User altered
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TSPITR';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TSPITR TSPITR TEMP
SQL> select count(*) from tspitr.tspitr;
COUNT(*)
----------
50315
現在表tspitr的儲存表空間也變為tspitr
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='TSPITR'; OWNER TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ TSPITR TSPITR TSPITR
9.將源資料庫中的tspitr表空間修改為讀寫模式
SQL> alter tablespace tspitr read write; Tablespace altered. SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TSPITR'; TABLESPACE_NAME STATUS ------------------------------ --------- TSPITR ONLINE
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2127016/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 用傳輸表空間跨平臺遷移資料
- Oraclc 12C使用不一致備份執行跨平臺傳輸表空間
- 【資料遷移】XTTS跨平臺傳輸表空間v4TTS
- 【資料遷移】XTTS跨平臺傳輸表空間（1.傳統方式）TTS
- 【XTTS】Oracle傳輸表空間xtts增量方式TTSOracle
- 【資料遷移】XTTS跨平臺傳輸表空間v3（3.DFT方式）TTS
- 【資料遷移】XTTS跨平臺傳輸表空間v3（2.RMAN增量）TTS
- MySQL 傳輸表空間MySql
- mysql之 表空間傳輸MySql
- 12c – 使用跨平臺增量備份來減少傳輸表空間的停機時間 (文件 ID 2102859.1)
- 12c – 使用跨平臺增量備份來減少傳輸表空間的停機時間 (Doc ID 2102859.1)
- 【XTTS】Oracle11g 使用XTTS增量跨平臺傳輸減少停機時間TTSOracle
- Oracle表空間Oracle
- oracle 表空間Oracle
- 淺談跨平臺框架Flutter的搭建與執行框架Flutter
- 【XTTS】使用XTTS傳輸表空間將Oracle11.2.0.4資料遷移至Oracle19CTTSOracle
- 增加oracle表空間Oracle
- oracle temp 表空間Oracle
- oracle 表移動表空間Oracle
- Oracle表移動表空間Oracle
- SecureFX for Mac(跨平臺檔案傳輸客戶端)Mac客戶端
- MySQL傳輸表空間的簡單使用方法MySql
- oracle表空間的整理Oracle
- Oracle 批量建表空間Oracle
- Oracle清理SYSAUX表空間OracleUX
- ORACLE expdp在表空間較多的情況下執行非常緩慢Oracle
- Oracle Temp 表空間切換Oracle
- Oracle 表空間增加檔案Oracle
- Oracle OCP(49)：表空間管理Oracle
- Oracle表空間收縮方案Oracle
- Oracle RMAN 表空間恢復Oracle
- Linux scp 後臺執行傳輸檔案Linux
- Oracle新建使用者、表空間、表Oracle
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- Oracle資料庫遷移 - 異構傳輸表空間TTS HP-UX遷移至Redhat Linux 7.7Oracle資料庫TTSRedhatLinux
- Oracle的表空間quota詳解Oracle
- oracle臨時表空間相關Oracle
- oracle sql 表空間利用率OracleSQL
- Oracle OCP(47)：表空間的建立Oracle