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 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production 11:35:13 SYS@ prod >select name,dbid from v$database; NAME DBID --------- ---------- PROD 219724276 11:35:32 SYS@ prod >col file_name for a50 11: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 TEST1 10 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(*) ---------- 14 Elapsed: 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 120 SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> select name,dbid from v$database; NAME DBID --------- ---------- CUUG 1340865938 SQL> col file_name for a50 SQL> 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.30 11: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/tiger Connected. 11:48:09 SCOTT@ prod >select index_name,table_name,tablespace_name from user_indexes 11:48:37 2 where index_name=‘PK_EMP2‘; INDEX_NAME TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ PK_EMP2 EMP2 USERS 11: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_indexes 11:49:32 2 where index_name=‘PK_EMP2‘; INDEX_NAME TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ PK_EMP2 EMP2 TEST1 Elapsed: 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.44 11: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=test1 Export: Release 11.2.0.1.0 - Production on Tue Sep 30 13:25:58 2014 Copyright (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 - Production With the Partitioning, 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=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=true Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master 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.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 13:27:04
查看导出文件：
[oracle@rh6 data]$ ls -lh total 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 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD (DBID=219724276) RMAN> convert tablespace test1 2> to platform ‘AIX-Based Systems (64-bit)‘ 3> format ‘/home/oracle/data/test1_%s.dbf‘; Starting conversion at source at 30-SEP-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=46 device type=DISK channel ORA_DISK_1: starting datafile conversion input datafile file number=00010 name=/u01/app/oracle/oradata/prod/test1.dbf converted datafile=/home/oracle/data/test1_2.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 Finished 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_directories OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ -------------------------------------------------- SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/state SYS 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/xml SYS 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/Sep SYS DUMP_DIR /home/oracle/data 9 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): oracle 331 Password required for oracle. Password: 230-Last unsuccessful login: Tue Sep 30 10:02:26 2014 on /dev/pts/1 from 192.168.8.22 230-Last login: Tue Sep 30 14:13:31 2014 on /dev/pts/1 from 192.168.8.22 230 User oracle logged in. Remote system type is UNIX. Using binary mode to transfer files. ftp> bin 200 Type set to I. ftp> mput /home/oracle/data/*.dmp mput /home/oracle/data/expdat.dmp? y 227 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? y 227 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> quit 221 Goodbye.
目标库查看文件：
[oracle@aix211 data]$ls -l total 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 datafile 2> ‘/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-14 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile conversion input file name=/home/oracle/data/test1_2.dbf converted datafile=/u01/app/oracle/oradata/cuug/test1.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 Finished 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 2014 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 - 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": userid="sys/******** AS SYSDBA" dumpfile=expdat.dmp directory=dump_dir transport_datafiles=/u01/app/oracle/oradata/cuug/test1.dbf logfile=test.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:14:41
7、查看目标库传输结果
查看表空间及数据文件： SQL> set linesize 120 SQL> 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 TEST1 6 rows selected. 查看导入对象： SQL> conn scott/tiger Connected. 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 10 14 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