将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 2016Copyright （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.logtransport_tablespaces=TSPITR < Export: Release 10.2.0.4.0 - 64bit Production on Monday, 24 October, 2016 17:12:42Copyright （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:22Copyright （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_onlyexclude=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_orrj67icImport: Release 11.2.0.4.0 - Production on Mon Oct 24 17:22:29 2016Copyright （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 ONLYSQL> alter tablespace tspitr read write;Tablespace altered.[oracle@sjjh transport]$ impdp tspitr/tspitr directory=test_dump dumpfile=tspitr_metadata_only.dmpImport: Release 11.2.0.4.0 - Production on Mon Oct 24 17:47:38 2016Copyright （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:01SQL> 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 TEMPSQL> 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更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址
易网时代-编程资源站