ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用CONVERT实现跨平台表空间迁移

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

原创 Linux操作系统 作者:yangtingkun 时间:2009-05-05 23:16:58 0 删除 编辑

前一篇简单介绍了10g的新特性,RMANCONVERT命令,不过由于篇幅,没有给出一个具体的例子,这篇利用CONVERTSOLARIS平台和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 wner = '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执行传输表空间的源数据导出,然后利用RMANCONVERT命令将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 wner = '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语法,执行的跨平台的表空间迁移完成。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-592695/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10488540