Oracle Study之案例--异构平台传输表空间（Linux至AIX)
系统架构：
| 源 库 | 目标库 | |
| 操作系统 | Linux RH6 | AIX 5.3-09 |
| 主机名 | rh6（192.168.8.245） | aix211（192.168.8.211） |
| 数据版本 | Oracle 11gR2 | Oracle 11gR2 |
| 数据库名 | prod | orcl |
| 表空间 | test1 | test1 |
可传输表空间概述
Oracle 的可传输表空间特性通过将 元数据和数据文件 简单地从一个数据库移动到另一个数据库，提供 在数据库之间有效移动大数据的一种简易方法。代替重新创建对象，可移植表空间可以让 毫不费力地移动大对象，而所花费的时间是你手动创建这些对象的时间。 可移植表空间包括将属于源数据库的所有数据文件拷贝到目标数据库，并将关于表空间 数据目录信息从源数据库拷贝到目标数据库。因此，数据泵取导出和导入实用程序是可移 表空间特性的一部分。还可以传送属于表的索引表空间，使整个数据移植非常地快。
可移植表空间的应用场景 ：
把数据从源数据库移动到数据仓库
把数据从升级数据库移动到数据集
把数据从数据仓库移动到数据集
执行表空间时间点恢复 (PITR)
归档历史数据
然而，在 Oracle9i 数据库和更低版本中，可传输表空间仅限于在目标数据库和源数据库都运行在同一操作系统平台上的少数情况下才有用 — 例如，您不能在 Solaris 和 HP-UX 平台之间传输表空间。
在Oracle 数据库 10g 中，这个局限消失了。
数据文件所以不能跨平台，主要是由于不同平台的字节顺序不同，这是计算机领域由来已久的问题之一，在各种计算机体系结构中，由于对于字、字节等的存储机制有所不同，通信双方交流的信息单元（比特、字节、字、双字等）应该以什么样的顺序进行传送就成了一个问题，如果不达成一致的规则，通信双方将无法进行正确的编/译码从而导致通信失败。
目前在各种体系的计算机中通常采用的字节存储机制主要有两种：Big-Endian和Little-Endian 。
一些操作系统（包括Windows）在低位内存地址中存放二进制数据的最低有效字节，因此这种系统被称为Little Endian；一些操作系统（包括Solaris）将最高有效字节存储在低位内存地址中，因此这种系统被称为Big Endian。
举一个简单点的例子，假如1122这样一个数据要存入不同系统，对于Little Endian的系统，存储的顺序就是2211，小头在前；而对于Big Endian的系统来说，存储顺序就是1122，大头在前，显然Big Endian更符合我们通常的语言习惯。
那么跨平台的问题就出现了，当一个Little Endian的系统试图从一个Big Endian的系统中读取数据时，就需要通过转换，否则不同的字节顺序将导致数据不能被正确读取。
1、查看操作系统字节顺序
SQL> col PLATFORM_NAME for a50
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- -------------------------------------------------- --------------
6 AIX-Based Systems (64-bit) Big
16 Apple Mac OS Big
21 Apple Mac OS (x86-64) Little
19 HP IA Open VMS Little
15 HP Open VMS Little
5 HP Tru64 UNIX Little
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
18 IBM Power Based Linux Big
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------------- --------------
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
20 Solaris Operating System (x86-64) Little
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
20 rows selected.
本案例从Linux系统传输到AIX系统，需要进行转换！
传输表空间的简要操作步骤
1) 确定平台的 Endian 格式
2) 确保表空间为自包含并使其只读(如果利用rman操作，可不用将表空间至于只读)
3) 用 exp、expdp等实用程序导出元数据
4) 转换数据文件以匹配 Endian 格式 ( 若一致可跳过）
5) 拷贝文件到目标系统
6) 使用 imp、impdp导入实用程序导入元数据
2、查看数据库信息
源库：（Linux)
数据库版本：09:42:59 SYS@ prod >select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production11:35:13 SYS@ prod >select name,dbid from v$database;NAME DBID--------- ----------PROD 21972427611:35:32 SYS@ prod >col file_name for a5011:35:54 SYS@ prod >select file_id,file_name,tablespace_name from dba_data_files;FILE_ID FILE_NAME TABLESPACE_NAME---------- -------------------------------------------------- ------------------------------ 4 /u01/app/oracle/oradata/prod/users01.dbf USERS 3 /u01/app/oracle/oradata/prod/undotbs01.dbf UNDOTBS1 2 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX 1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM 5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE 6 /u01/app/oracle/oradata/prod/users02.dbf USERS 7 /u01/app/oracle/oradata/prod/catatbs1.dbf CATATBS 8 /u01/app/oracle/oradata/prod/perfertbs1.dbf PERFERTBS 9 /u01/app/oracle/oradata/prod/oggtbs1.dbf OGG_TBS 10 /u01/app/oracle/oradata/prod/test1.dbf TEST110 rows selected.test1表空间用于传输：11:40:34 SCOTT@ prod >create table emp2 tablespace test1 as select * from emp;Table created.在test1表空间建立对象用于测试：11:40:54 SCOTT@ prod >alter table emp2 add constraint pk_emp2 primary key (empno);Table altered.11:41:18 SCOTT@ prod >select count(*) from emp2; COUNT(*)---------- 14Elapsed: 00:00:00.01
操作系统信息：
11:42:52 SYS@ prod >SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
11:43:25 2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
11:43:25 3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT----------------------------------------------------------------------------------------------------- --------------Linux IA (32-bit) Little
数据库字符集：
11:43:26 SYS@ prod >select userenv('LANGUAGE') FROM DUAL;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.ZHS16GBK
目标库：（AIX）
数据库信息：
数据库版本：SQL> set linesize 120SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - ProductionSQL> select name,dbid from v$database;NAME DBID--------- ----------CUUG 1340865938SQL> col file_name for a50SQL> select file_id,file_name,tablespace_name from dba_data_files; FILE_ID FILE_NAME TABLESPACE_NAME---------- -------------------------------------------------- ------------------------------ 4 /u01/app/oracle/oradata/cuug/users01.dbf USERS 3 /u01/app/oracle/oradata/cuug/undotbs01.dbf UNDOTBS1 2 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX 1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM 5 /u01/app/oracle/oradata/cuug/example01.dbf EXAMPLE
主机操作系统信息:
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT 2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d 3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;PLATFORM_NAME ENDIAN_FORMAT-------------------------------------------------- --------------AIX-Based Systems (64-bit) Big
数据库字符集：
SQL> select userenv('LANGUAGE') FROM DUAL;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.ZHS16GBK
3、传输之前检查表空间自包含
11:46:45 SYS@ prod >EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST1',true);PL/SQL procedure successfully completed.Elapsed: 00:00:39.3011:47:43 SYS@ prod >SELECT * FROM TRANSPORT_SET_VIOLATIONS;VIOLATIONS------------------------------------------------------------------------------------------------------------------------ORA-39908: Index SCOTT.PK_EMP2 in tablespace USERS enforces primary constraints of table SCOTT.EMP2 in tablespace TEST1
由于EMP2主键建立的索引在users表空间，所以导致test1表空间不能自包含，将索引rebuild到test1表空间
11:47:46 SYS@ prod >conn scott/tigerConnected.11:48:09 SCOTT@ prod >select index_name,table_name,tablespace_name from user_indexes11:48:37 2 where index_name='PK_EMP2';INDEX_NAME TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------PK_EMP2 EMP2 USERS11:48:47 SCOTT@ prod >alter index pk_emp2 rebuild tablespace test1;Index altered.11:49:24 SCOTT@ prod >select index_name,table_name,tablespace_name from user_indexes11:49:32 2 where index_name='PK_EMP2';INDEX_NAME TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------PK_EMP2 EMP2 TEST1Elapsed: 00:00:00.00重新检查，解决自包含
11:49:56 SYS@ prod >EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST1',true);PL/SQL procedure successfully completed.Elapsed: 00:00:36.4411:50:41 SYS@ prod >SELECT * FROM TRANSPORT_SET_VIOLATIONS;no rows selected
4、导出传输表空间（test1）
设置test1为read only模式：11:50:48 SYS@ prod >alter tablespace test1 read only;Tablespace altered.建立导出目录：11:53:16 SYS@ prod >create directory dump_dir as '/home/oracle/data';Directory created.11:54:20 SYS@ prod >grant read ,write on directory dump_dir to public;Grant succeeded.导出表空间元数据：[oracle@rh6 data]$ expdp system dumpfile=expdat.dmp directory=dump_dir transport_full_check=y logfile=tbs.log transport_tablespaces=test1Export: Release 11.2.0.1.0 - Production on Tue Sep 30 13:25:58 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Password:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsLegacy Mode Active due to the following parameters:Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, Replaced with: "transport_tablespaces=test1"Legacy Mode has set reuse_dumpfiles=true parameter.Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=dump_dir transport_full_check=y logfile=tbs.log reuse_dumpfiles=trueProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEXProcessing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINTProcessing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMaster table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /home/oracle/data/expdat.dmp******************************************************************************Datafiles required for transportable tablespace TEST1: /u01/app/oracle/oradata/prod/test1.dbfJob "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 13:27:04
查看导出文件：
[oracle@rh6 data]$ ls -lhtotal 116K-rw-r----- 1 oracle oinstall 112K Sep 30 12:01 expdat.dmp-rw-r--r-- 1 oracle oinstall 1.4K Sep 30 12:01 tbs.log
在源端转换数据文件（RMAN)
[oracle@rh6 data]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 30 12:02:27 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: PROD (DBID=219724276)RMAN> convert tablespace test12> to platform 'AIX-Based Systems (64-bit)'3> format '/home/oracle/data/test1_%s.dbf';Starting conversion at source at 30-SEP-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=46 device type=DISKchannel ORA_DISK_1: starting datafile conversioninput datafile file number=00010 name=/u01/app/oracle/oradata/prod/test1.dbfconverted datafile=/home/oracle/data/test1_2.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03Finished conversion at source at 30-SEP-14
在目标库建立导入目录：
SQL> create directory dump_dir as '/home/oracle/data';Directory created.SQL> grant read,write on directory dump_dir to public;Grant succeeded.SQL> r 1* select * from dba_directoriesOWNER DIRECTORY_NAME DIRECTORY_PATH------------------------------ ------------------------------ --------------------------------------------------SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/stateSYS DATA_PUMP_DIR /u01/app/oracle/admin/cuug/dpdump/SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/pr oduct_media/SYS XMLDIR /ade/b/3705469564/oracle/rdbms/xmlSYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/sa les_history/SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/lo g/OWNER DIRECTORY_NAME DIRECTORY_PATH------------------------------ ------------------------------ --------------------------------------------------SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/or der_entry/SYS SUBDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/or der_entry//2002/SepSYS DUMP_DIR /home/oracle/data9 rows selected.
5、传输dmp文件和转换后数据文件到目标库
[oracle@rh6 data]$ ftp 192.168.8.211 ；；以二进制方式上传Connected to 192.168.8.211 (192.168.8.211).220 aix211 FTP server (Version 4.2 Wed Apr 2 15:38:27 CDT 2008) ready.Name (192.168.8.211:oracle): oracle331 Password required for oracle.Password:230-Last unsuccessful login: Tue Sep 30 10:02:26 2014 on /dev/pts/1 from 192.168.8.22230-Last login: Tue Sep 30 14:13:31 2014 on /dev/pts/1 from 192.168.8.22230 User oracle logged in.Remote system type is UNIX.Using binary mode to transfer files.ftp> bin200 Type set to I.ftp> mput /home/oracle/data/*.dmpmput /home/oracle/data/expdat.dmp? y227 Entering Passive Mode (192,168,8,211,139,18)150 Opening data connection for /home/oracle/data/expdat.dmp.226 Transfer complete.114688 bytes sent in 0.0466 secs (2461.86 Kbytes/sec)ftp> mput test1*mput test1_2.dbf? y227 Entering Passive Mode (192,168,8,211,139,21)150 Opening data connection for /home/oracle/data/test1_2.dbf.226 Transfer complete.10493952 bytes sent in 2.52 secs (4168.10 Kbytes/sec)ftp> quit221 Goodbye.
目标库查看文件：
[oracle@aix211 data]$ls -ltotal 20720-rw-r----- 1 oracle oinstall 10493952 Sep 30 14:30 test2.dbf-rw-r----- 1 oracle oinstall 114688 Sep 30 14:30 expdat.dmp
6、目标库转换数据文件并导入表空间
转换数据文件：
RMAN> convert datafile2> '/home/oracle/data/test1_2.dbf'3> db_file_name_convert=4> '/home/oracle/data/test1_2.dbf','/u01/app/oracle/oradata/cuug/test1.dbf';Starting conversion at target at 30-SEP-14using channel ORA_DISK_1channel ORA_DISK_1: starting datafile conversioninput file name=/home/oracle/data/test1_2.dbfconverted datafile=/u01/app/oracle/oradata/cuug/test1.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01Finished conversion at target at 30-SEP-14
导入表空间：
[oracle@aix211 data]$impdp userid=\'sys/oracle as sysdba\' dumpfile=expdat.dmp directory=dump_dir transport_datafiles=/u01/app/oracle/oradata/cuug/test1.dbf logfile=test.log
Import: Release 11.2.0.1.0 - Production on Tue Sep 30 16:14:32 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": userid="sys/******** AS SYSDBA" dumpfile=expdat.dmp directory=dump_dir transport_datafiles=/u01/app/oracle/oradata/cuug/test1.dbf logfile=test.logProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEXProcessing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINTProcessing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:14:41
7、查看目标库传输结果
查看表空间及数据文件：SQL> set linesize 120SQL> r 1* select file_id,file_name,tablespace_name from dba_data_files FILE_ID FILE_NAME TABLESPACE_NAME---------- -------------------------------------------------- ------------------------------ 4 /u01/app/oracle/oradata/cuug/users01.dbf USERS 3 /u01/app/oracle/oradata/cuug/undotbs01.dbf UNDOTBS1 2 /u01/app/oracle/oradata/cuug/sysaux01.dbf SYSAUX 1 /u01/app/oracle/oradata/cuug/system01.dbf SYSTEM 5 /u01/app/oracle/oradata/cuug/example01.dbf EXAMPLE 6 /u01/app/oracle/oradata/cuug/test1.dbf TEST16 rows selected.查看导入对象：SQL> conn scott/tigerConnected.SQL> select * from emp2; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> select table_name,tablespace_name from user_tables 2 where table_name='EMP2';TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------EMP2 TEST1设置表空间到read write：14:55:32 SYS@ prod >alter tablespace test1 read write;Tablespace altered.SQL> alter tablespace test1 read write;Tablespace altered.
@至此，跨平台表空间传输成功！
本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1559804