Oracle采用XTTS从小机迁移X86平台时,system/SYSAUX中的表如何迁移

发布时间:  2016-03-03 浏览次数:  2774 下载次数:  0
问题描述
Oracle采用XTTS从小机迁移X86平台时,只能迁移用户表空间中的数据,不支持SYSTEM和SYSAUX系统表空间的数据。若SYSTEM和SYSAUX中的有表需要迁移时,如何迁移?
处理过程

有两种方式迁移。第一种为在目标数据库中新创建相应的表,在此不详述该方法;第二种为采用imp/exp来迁移,本案例主要介绍该种迁移方法。

需要注意:不能采用数据泵方式迁移,因为数据泵不能导出SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS等schemas。 

实验室设备清单如下:

设备或软件

用途

OceanStor S2600T

源存储

OceanStor 18500

目标存储

SNS2120

交换机

E6000 刀片

SUSE目标主机

Power710小型机

AIX源主机

Oracle

源和目标集群和数据库软件


本案例为imp/exp迁移的实际样例参考。将源oracle数据库(AIX小机)中system表空间的bigtable1迁移到目标数据库(SUSE)中。

1、在源数据库将表导出:

aix01:oracle:[/home/oracle]$exp system/huawei file=/home/oracle/table.dmp tables=sys.bigtable1

Export: Release 11.2.0.3.0 - Production on Thu Mar 3 14:49:18 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table                      BIGTABLE1          2 rows exported
Export terminated successfully without warnings.
aix01:oracle:[/home/oracle]$

2、将导出数据复制到目标库中

aix01:oracle:[/home/oracle]$scp table.dmp oracle@192.168.0.81:/home/oracle/tts_transfer/
Password:
table.dmp                                                                                                                                                                         100% 8192     8.0KB/s   00:00   
aix01:oracle:[/home/oracle]$


3、在目标库导入表:


oracle@ogg01:~/tts_transfer> imp userid="'sys/zhangli as sysdba'" file=/home/oracle/tts_transfer/table.dmp tables=bigtable1

Import: Release 11.2.0.1.0 - Production on Sat Mar 5 02:43:19 2016

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYSTEM's objects into SYS
. importing SYS's objects into SYS
. . importing table                    "BIGTABLE1"          2 rows imported
Import terminated successfully without warnings. 

建议与总结

Oracle采用XTTS从小机迁移X86平台时,只能迁移用户表空间中的数据,不支持SYSTEM和SYSAUX系统表空间的数据。若SYSTEM和SYSAUX中的有表需要迁移时,推荐采用imp/exp方式迁移。

需要注意:不能采用数据泵(data pumb)方式迁移,因为数据泵不能导出SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS等schemas。 

END