用CONVERT实现跨平台表空间迁移

前一篇简单介绍了10g的新特性，RMAN的CONVERT命令，不过由于篇幅，没有给出一个具体的例子，这篇利用CONVERT在SOLARIS平台和LINUX平台的10.2.0.3版本数据库之前实现表空间的迁移。

Oracle10g新增CONVERT语法：http://yangtingkun.itpub.net/post/468/483871

首先检查源数据库的版本，并检查表空间是否满足迁移要求：

$ uname -a
SunOS racnode1 5.8 Generic_117350-46 sun4u sparc SUNW,Sun-Fire-480R
$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 3月 5 23:31:24 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> show parameter compati

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.1.0
plsql_v2_compatibility boolean FALSE
SQL> show parameter block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select tablespace_name, block_size from dba_tablespaces
2 where tablespace_name = 'TEST';

TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
TEST 8192

SQL> select property_name, property_value
2 from database_properties
3 where property_name like 'NLS%CHARACTERSET';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET ZHS16GBK

SQL> select distinct owner from dba_segments
2 where tablespace_name = 'TEST';

OWNER
------------------------------
TEST

SQL> select table_name from dba_tables
2 where tablespace_name = 'TEST';

TABLE_NAME
------------------------------
T_P
T_C

SQL> exec dbms_tts.transport_set_check('TEST', true)

PL/SQL 过程已成功完成。

SQL> select * from transport_set_violations;

未选定行

检查目标数据库是否满足表空间迁移要求，

[oracle@yanttest ~]$ uname -a
Linux yanttest 2.6.18-8.el5 #1 SMP Tue Jun 5 23:25:19 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
[oracle@yanttest ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 5月 5 15:43:17 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> show parameter compati

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.1.0
plsql_v2_compatibility boolean FALSE
SQL> show parameter block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select property_name, property_value
2 from database_properties
3 where property_name like 'NLS%CHARACTERSET';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
DRSYS
INDX
TOOLS
USERS
XDB
LT_INDEX_TS
NDINDEX
NDMAIN
QUEST
SYSAUX
SHIYQ
STEST

已选择15行。

SQL> select username from dba_users
2 where username = 'TEST';

USERNAME
------------------------------
TEST

SQL> select object_name from dba_objects
2 where owner = 'TEST'
3 and object_name in ('T_P', 'T_C');

未选定行

目标数据库的版本和源数据库一致，兼容性设置也一样。数据库字符集和国家字符集设置一致。默认的数据块大小也一样，且TEST表空间在目标数据库不存在。TEST用户在目标数据库存在，不过迁移表空间操作所要导入的表在目标数据库TEST用户中不存在。

下面执行导出操作：

SQL> alter tablespace test read only;

表空间已更改。

SQL> host
$ expdp system directory=d_output dumpfile=trans_test.dp transport_tablespaces=test transport_full_check=y

Export: Release 10.2.0.3.0 - 64bit Production on 星期五, 06 3月, 2009 0:26:55

Copyright (c) 2003, 2005, Oracle. All rights reserved.口令:

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=d_output dumpfile=trans_test.dp transport_tablespaces=test transport_full_check=y 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK处理对象类型 TRANSPORTABLE_EXPORT/TABLE处理对象类型 TRANSPORTABLE_EXPORT/INDEX处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
/export/home/oracle/trans_test.dp作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 00:28:00 成功完成

$ rman target /

恢复管理器: Release 10.2.0.3.0 - Production on 星期五 3月 6 00:31:16 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到目标数据库: TESTRAC (DBID=4291216984)

RMAN> convert tablespace 'TEST'
2> to platform 'Linux 64-bit for AMD'
3> format '/data1/backup/test01.dbf';

启动 backup 于 06-3月 -09使用目标数据库控制文件替代恢复目录分配的通道: ORA_DISK_1通道 ORA_DISK_1: sid=289 实例=testrac1 devtype=DISK分配的通道: ORA_DISK_2通道 ORA_DISK_2: sid=316 实例=testrac2 devtype=DISK通道 ORA_DISK_1: 启动数据文件转换输入数据文件 fno=00008 name=+DISK/testrac/datafile/test01.dbf已转换的数据文件 = /data1/backup/test01.dbf通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:07完成 backup 于 06-3月 -09

RMAN> exit

恢复管理器完成。
$ exit

SQL> alter tablespace test read write;

表空间已更改。

将表空间置于READ ONLY状态，随后利用EXPDP执行传输表空间的源数据导出，然后利用RMAN的CONVERT命令将SOLARIS平台的数据文件转化为Linux X86 64格式。由于数据文件的拷贝已经完成，这时已经可以将源数据库的表空间修改为可写状态。

将导出的源数据和数据文件ftp到目标数据库服务器上：

SQL> select directory_path from dba_directories
2 where directory_name = 'D_TEST';

DIRECTORY_PATH
-----------------------------------------
/data

SQL> host
[oracle@yanttest ~]$ cd /data
[oracle@yanttest data]$ ftp 172.25.198.222
Connected to 172.25.198.222.
220 racnode1 FTP server (SunOS 5.8) ready.
500 'AUTH GSSAPI': command not understood.
500 'AUTH KERBEROS_V4': command not understood.
KERBEROS_V4 rejected as an authentication type
Name (172.25.198.222:oracle): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Type set to I.
ftp> cd /export/home/oracle
250 CWD command successful.
ftp> prompt
Interactive mode off.
ftp> mget tra*.dp
local: trans_test.dp remote: trans_test.dp
227 Entering Passive Mode (172,25,198,222,251,243)
150 Binary data connection for trans_test.dp (172.25.13.231,50373) (110592 bytes).
226 Binary Transfer complete.
110592 bytes received in 0.0083 seconds (1.3e+04 Kbytes/s)
ftp> cd /data1/backup
250 CWD command successful.
ftp> lcd /data/oradata/testzj/
Local directory now /data/oradata/testzj
ftp> mget test01.dbf
local: test01.dbf remote: test01.dbf
227 Entering Passive Mode (172,25,198,222,251,253)
150 Binary data connection for test01.dbf (172.25.13.231,46996) (104865792 bytes).
226 Binary Transfer complete.
104865792 bytes received in 8.9 seconds (1.1e+04 Kbytes/s)
ftp> quit
221 Goodbye.

将数据文件和导出源数据放到目标数据库指定位置，就可以执行数据泵的导入进行表空间的加载了：

[oracle@yanttest data]$ impdp system directory=d_test dumpfile=trans_test.dp transport_datafiles='/data/oradata/testzj/test01.dbf'

Import: Release 10.2.0.3.0 - 64bit Production on 星期二, 05 5月, 2009 16:42:27

Copyright (c) 2003, 2005, Oracle. All rights reserved.口令:

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=d_test dumpfile=trans_test.dp transport_datafiles=/data/oradata/testzj/test01.dbf 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK处理对象类型 TRANSPORTABLE_EXPORT/TABLE处理对象类型 TRANSPORTABLE_EXPORT/INDEX处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 16:42:35 成功完成

数据文件成功导入，下面回到数据库中进行检查：

[oracle@yanttest data]$ exit
exit

SQL> select owner, table_name, tablespace_name from dba_tables
2 where owner = 'TEST'
3 and tablespace_name = 'TEST';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST T_P TEST
TEST T_C TEST

SQL> select count(*) from test.t_p;

COUNT(*)
----------
2

SQL> select count(*) from test.t_c;

COUNT(*)
----------
2

SQL> alter tablespace test read write;

表空间已更改。

检查无误后，将表空间置于可写状态。

至此利用CONVERT语法，执行的跨平台的表空间迁移完成。