Oracle，异构数据迁移：可传输表空间（XTTS）
Adamhuan发表于 2017-06-02 10:22:45
在我的环境中，结构如下：
源：
OS: MS Windows 2012
DB: Oracle database 11g
IP： 192.168.92.170
目标：
OS: RHEL 6
DB: Oracle database 11g
IP：192.168.92.169
查看两端数据库中的endian的值，确认当前场景是否支持可传输表空间：
源：
PS C:\Users\Administrator> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 6月 2 16:55:06 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col platform_name for a30
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platfor
m_name;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ ----------------------------
Microsoft Windows x86 64-bit Little
SQL>
SQL> select * from v$transportable_platform where platform_name like '%Linux%' order by platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ ----------------------------
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little
18 IBM Power Based Linux Big
SQL>
目标：
[oracle@oracle-me ~]$ export ORACLE_SID=lenka
[oracle@oracle-me ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 2 02:25:04 2017
Copyright (c) 1982, 2013, Oracle. 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
SQL> col platform_name for a30
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
SQL> SQL> col platform_name for a40
SQL> select * from v$transportable_platform where platform_name like 'Microsoft Windows%' order by platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
SQL>可以看到，上面两个环境的Endian是相互匹配的。
源：
创建测试数据：
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\LENKA\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\ORADATA\LENKA\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\ORADATA\LENKA\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\LENKA\USERS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\LENKA\EXAMPLE01.DBF
SQL>
SQL> create tablespace lenka datafile 'C:\APP\ADMINISTRATOR\ORADATA\LENKA\lenka01.dbf' siz
表空间已创建。
SQL>
SQL>
SQL> create user lenka identified by "oracle" default tablespace lenka;
用户已创建。
SQL> grant connect,resource to lenka;
授权成功。
SQL>
SQL> connect lenka/oracle;
已连接。
SQL> create table people( p_id int,p_name varchar2(40));
表已创建。
SQL> insert into people values(0,'Lenka Angel');
已创建 1 行。
SQL> insert into people values(1,'Allah');
已创建 1 行。
SQL> insert into people values(1,'Ruoer');
已创建 1 行。
SQL> commit;
提交完成。
SQL>
SQL> col p_name for a18
SQL> select * from people;
P_ID P_NAME
---------- ------------------
0 Lenka Angel
1 Allah
1 Ruoer
SQL>
SQL>
开始操作可传输表空间：
1. 将需要操作的表空间设置为只读：
PS C:\Users\Administrator> sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期五 6月 2 17:45:52 2017 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> alter tablespace lenka read only; 表空间已更改。 SQL> SQL> exit 从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开 PS C:\Users\Administrator>
2. 导出要传输的表空间：
PS C:\Users\Administrator> exp "'/ as sysdba'" tablespaces=lenka transport_tablespace=y file=e:\oracle_me\exp_tts_lenka. dmp Export: Release 11.2.0.1.0 - Production on 星期五 6月 2 17:53:46 2017 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 服务器使用 AL32UTF8 字符集 (可能的字符集转换) 注: 将不导出表数据 (行) 即将导出可传输的表空间元数据... 对于表空间 LENKA... . 正在导出簇定义 . 正在导出表定义 . . 正在导出表 PEOPLE . 正在导出引用完整性约束条件 . 正在导出触发器 . 结束导出可传输的表空间元数据 成功终止导出, 没有出现警告。 PS C:\Users\Administrator>
使用RMAN转换文件格式：
PS C:\Users\Administrator> rman target / 恢复管理器: Release 11.2.0.1.0 - Production on 星期五 6月 2 17:56:26 2017 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 连接到目标数据库: LENKA (DBID=1989407512) RMAN> convert tablespace lenka to platform 'Linux x86 64-bit' format 'E:\oracle_me\rman_%N_%f'; 启动 conversion at source 于 02-6月 -17 使用目标数据库控制文件替代恢复目录 分配的通道: ORA_DISK_1 通道 ORA_DISK_1: SID=32 设备类型=DISK 通道 ORA_DISK_1: 启动数据文件转换 输入数据文件: 文件号=00006 名称=C:\APP\ADMINISTRATOR\ORADATA\LENKA\LENKA01.DBF 已转换的数据文件 = E:\ORACLE_ME\RMAN_LENKA_6 通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:07 完成 conversion at source 于 02-6月 -17 RMAN> exit 恢复管理器完成。 PS C:\Users\Administrator>
这时候，我们已经有了两个文件：
将这两个文件发送到目标端的Linux上面：
[root@oracle-me ~]# chmod -R 777 /software [root@oracle-me ~]# su - oracle [oracle@oracle-me ~]$ cd /software/oracle_me/ [oracle@oracle-me oracle_me]$ ls exp_tts_lenka.dmp RMAN_LENKA_6 [oracle@oracle-me oracle_me]$ du -sh * 4.0K exp_tts_lenka.dmp 201M RMAN_LENKA_6 [oracle@oracle-me oracle_me]$
在目标库导入：
先查看下目标库的状态：
[oracle@oracle-me oracle_me]$ ps -ef | grep pmon oracle 7244 1 0 Jun01 ? 00:00:10 ora_pmon_lenka oracle 60698 60625 0 03:05 pts/1 00:00:00 grep pmon [oracle@oracle-me oracle_me]$ [oracle@oracle-me oracle_me]$ env | grep SID ORACLE_SID=edendb1 [oracle@oracle-me oracle_me]$ export ORACLE_SID=lenka [oracle@oracle-me oracle_me]$ [oracle@oracle-me oracle_me]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 2 03:08:55 2017 Copyright (c) 1982, 2013, Oracle. 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 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/lenka/system01.dbf /u01/app/oracle/oradata/lenka/sysaux01.dbf /u01/app/oracle/oradata/lenka/undotbs01.dbf /u01/app/oracle/oradata/lenka/users01.dbf /u01/app/oracle/oradata/lenka/example01.dbf SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oracle-me oracle_me]$
Rman的操作：
[oracle@oracle-me oracle_me]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 2 03:09:57 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: LENKA (DBID=1988574324) RMAN> convert datafile '/software/oracle_me/RMAN_LENKA_6' db_file_name_convert '/software/oracle_me/RMAN_LENKA_6','/u01/app/oracle/oradata/lenka/lenka01.dbf'; Starting conversion at target at 02-JUN-2017 03:12:17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=64 device type=DISK channel ORA_DISK_1: starting datafile conversion input file name=/software/oracle_me/RMAN_LENKA_6 converted datafile=/u01/app/oracle/oradata/lenka/lenka01.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07 Finished conversion at target at 02-JUN-2017 03:12:25 RMAN>
创建用户并授权：
[oracle@oracle-me oracle_me]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 2 03:15:22 2017 Copyright (c) 1982, 2013, Oracle. 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 SQL> SQL> create user lenka identified by "oracle"; User created. SQL> grant connect,resource to lenka; Grant succeeded. SQL>
IMP导入：
[oracle@oracle-me oracle_me]$ imp \'/ as sysdba\' tablespaces=lenka transport_tablespace=y file=/software/oracle_me/exp_tts_lenka.dmp datafiles='/u01/app/oracle/oradata/lenka/lenka01.dbf'; Import: Release 11.2.0.4.0 - Production on Fri Jun 2 03:19:05 2017 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 Export file created by EXPORT:V11.02.00 via conventional path About to import transportable tablespace(s) metadata... import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) export client uses ZHS16GBK character set (possible charset conversion) . importing SYS's objects into SYS . importing SYS's objects into SYS . importing LENKA's objects into LENKA . . importing table "PEOPLE" . importing SYS's objects into SYS Import terminated successfully without warnings. [oracle@oracle-me oracle_me]$
然后，将表空间解除只读状态，并访问：
[oracle@oracle-me oracle_me]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 2 03:19:32 2017
Copyright (c) 1982, 2013, Oracle. 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
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/lenka/system01.dbf
/u01/app/oracle/oradata/lenka/sysaux01.dbf
/u01/app/oracle/oradata/lenka/undotbs01.dbf
/u01/app/oracle/oradata/lenka/users01.dbf
/u01/app/oracle/oradata/lenka/example01.dbf
/u01/app/oracle/oradata/lenka/lenka01.dbf
6 rows selected.
SQL>
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
LENKA READ ONLY
7 rows selected.
SQL> alter tablespace lenka read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
LENKA ONLINE
7 rows selected.
SQL> desc lenka.people;
Name Null? Type
----------------------------------------- -------- ----------------------------
P_ID NUMBER(38)
P_NAME VARCHAR2(40)
SQL> col p_name for a18
SQL> select * from lenka.people;
P_ID P_NAME
---------- ------------------
0 Lenka Angel
1 Allah
1 Ruoer
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle-me oracle_me]$这样，可传输表空间，就操作完了。
——————————————
Done。