首页 / 数据库 / MySQL / Oracle在不同平台间表空间迁移
平台环境：
1、源库:
Windows Service 2003 SP2 x86
Oracle Service 10.2.0.4 x86
2、目标库
RedHat Linux 5.5 X64
Oracle Service 10.2.0.5
3、检查目标数据库的系统环境
3、检查目标系统环境
内存大小：
[root@lgxt ~]# free -m
total used free shared buffers cached
Mem: 3937 2625 1311 0 184 1544
-/+ buffers/cache: 896 3040
Swap: 5951 0 5951
磁盘空间，规划表空间存放路径
[root@lgxt ~]# df -h
文件系统 容量 已用 可用 已用% 挂载点
/dev/mapper/VolGroup00-LogVol00 33G 12G 20G 39% /
/dev/sda1 99M 13M 82M 14% /boot
tmpfs 2.0G 0 2.0G 0% /dev/shm
/dev/mapper/data1-data1 195G 6.5G 179G 4% /data1
/dev/mapper/data2-data2 196G 1.4G 184G 1% /data2
/dev/mapper/data3-data3 214G 188M 203G 1% /data3
启动数据库
[root@lgxt ~]# su - oracle
[oracle@lgxt ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Nov 11 13:16:42 2011
Copyright （c） 1982, 2010, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2097696 bytes
Variable Size 637537760 bytes
Database Buffers 1493172224 bytes
Redo Buffers 14675968 bytes
Database mounted.
Database opened.
检查现有的表空间及状态
SQL> set line 100
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
表空间的数据文件路径
SQL> select TABLESPACE_NAME,FILE_NAME,STATUS from dba_data_files
TABLESPACE FILE_NAME STATUS
---------- ---------------------------------------- ---------
USERS /data2/oradata/lgxt/users01.dbf AVAILABLE
SYSAUX /data2/oradata/lgxt/sysaux01.dbf AVAILABLE
UNDOTBS1 /data2/oradata/lgxt/undotbs01.dbf AVAILABLE
SYSTEM /data2/oradata/lgxt/system01.dbf AVAILABLE
检查字符集
SQL> col property_value format a50
SQL> col description format a50
SQL> set line 300
SQL> select * from database_properties
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
NLS_LANGUAGE AMERICAN Language
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NO_USERID_VERIFIER_SALT 7A5EEDE64CF9425191B719548533F708
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
GLOBAL_DB_NAME LGXT.REGRESS.RDBMS.DEV.US.ORACLE.COM Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
DBTIMEZONE 00:00 DB time zone
NLS_TERRITORY AMERICA Territory
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET ZHS16GBK Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_RDBMS_VERSION 10.2.0.5.0 RDBMS version for NLS parameters
WORKLOAD_CAPTURE_MODE CAPTURE implies workload capture is in progress
29 rows selected.
SQL>
检查是否可以做表空间传输迁移，我们是Redhat Linux 5.5 X64 和 Windows Service 2003 SP2 x86 平台，
正好这两个平台都是Little模式是可以无缝的进行表空间传输迁移，当然在10个里面模式不一样也可以迁移，在后期的实验里面会提到。
SQL> select * from v$transportable_platform
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE （32-bit） Big
2 Solaris[tm] OE （64-bit） Big
7 Microsoft Windows IA （32-bit） Little
10 Linux IA （32-bit） Little
6 AIX-Based Systems （64-bit） Big
3 HP-UX （64-bit） Big
5 HP Tru64 UNIX Little
4 HP-UX IA （64-bit） Big
11 Linux IA （64-bit） Little
15 HP Open VMS Little
8 Microsoft Windows IA （64-bit） Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System （x86） Little
18 IBM Power Based Linux Big
20 Solaris Operating System （x86-64） Little
19 HP IA Open VMS Little
19 rows selected.
四、检查源库环境：
1、查看源库的表空间及用户名相关状态
SQL> col username format a12
SQL> select username,default_tablespace,temporary_tablespace,created,account_status from dba_users;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED ACCOUNT_STATUS
------------ ------------------------------ ------------------------------ ---------- --------------------------------
SYS SYSTEM TEMP 12-5月 -02 OPEN
SYSTEM SYSTEM TEMP 12-5月 -02 OPEN
DBSNMP SYSTEM TEMP 12-5月 -02 OPEN
JXTELE_HOMS HOMS TEMP 23-8月 -10 OPEN
SCOTT SYSTEM TEMP 12-5月 -02 OPEN
REPADMIN USERS TEMP 25-4月 -11 OPEN
ORACLEDBA SYSTEM TEMP 09-8月 -11 OPEN
ORADATA SYSTEM TEMP 09-8月 -11 OPEN
OUTLN SYSTEM TEMP 12-5月 -02 EXPIRED & LOCKED
WMSYS SYSTEM TEMP 12-5月 -02 EXPIRED & LOCKED
ORDSYS SYSTEM TEMP 12-5月 -02 EXPIRED & LOCKED
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED ACCOUNT_STATUS
------------ ------------------------------ ------------------------------ ---------- --------------------------------
ORDPLUGINS SYSTEM TEMP 12-5月 -02 EXPIRED & LOCKED
MDSYS SYSTEM TEMP 12-5月 -02 EXPIRED & LOCKED
CTXSYS DRSYS TEMP 12-5月 -02 EXPIRED & LOCKED
QS_ES EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
QS_WS EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
QS EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
QS_ADM EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
SH EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
PM EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
OE EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
HR EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED ACCOUNT_STATUS
------------ ------------------------------ ------------------------------ ---------- --------------------------------
RMAN TOOLS TEMP 12-5月 -02 EXPIRED & LOCKED
QS_CS EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
QS_CB EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
QS_CBADM EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
QS_OS EXAMPLE TEMP 12-5月 -02 EXPIRED & LOCKED
XDB XDB TEMP 12-5月 -02 EXPIRED & LOCKED
ANONYMOUS XDB TEMP 12-5月 -02 EXPIRED & LOCKED
WKSYS DRSYS TEMP 12-5月 -02 EXPIRED & LOCKED
WKPROXY DRSYS TEMP 12-5月 -02 EXPIRED & LOCKED
ODM ODM TEMP 12-5月 -02 EXPIRED & LOCKED
ODM_MTR ODM TEMP 12-5月 -02 EXPIRED & LOCKED
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED ACCOUNT_STATUS
------------ ------------------------------ ------------------------------ ---------- --------------------------------
OLAPSYS CWMLITE TEMP 12-5月 -02 EXPIRED & LOCKED
已选择34行。
2、检查表空间大小
SQL>
SQL> select file_id,file_name,tablespace_name ,bytes/1024/1024 "Size M" from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME Size M
---------- ---------------------------------------- -------------------- ----------
1 E:ORACLEORADATAHOMSSYSTEM01.DBF SYSTEM 4960
2 E:ORACLEORADATAHOMSUNDOTBS01.DBF UNDOTBS1 435
3 E:ORACLEORADATAHOMSCWMLITE01.DBF CWMLITE 20
4 E:ORACLEORADATAHOMSDRSYS01.DBF DRSYS 20
5 E:ORACLEORADATAHOMSEXAMPLE01.DBF EXAMPLE 149.375
6 E:ORACLEORADATAHOMSINDX01.DBF INDX 25
7 E:ORACLEORADATAHOMSODM01.DBF ODM 20
8 E:ORACLEORADATAHOMSTOOLS01.DBF TOOLS 10
9 E:ORACLEORADATAHOMSUSERS01.DBF USERS 25
10 E:ORACLEORADATAHOMSXDB01.DBF XDB 38.125
11 E:ORACLEORADATAHOMSHOMS.ORA HOMS 3000
FILE_ID FILE_NAME TABLESPACE_NAME Size M
---------- ---------------------------------------- -------------------- ----------
12 E:ORACLEORADATAHOMSXDB02.DBF XDB 2048
13 E:ORACLEORADATAHOMSHOMS01.ORA HOMS 3000
14 E:ORACLEORADATAHOMSHOMS02.ORA HOMS 3000
已选择14行。
3、检查表空间状态
SQL> desc v$tablespace;
名称 是否为空？ 类型
--------------------------------------- -------- ------
TS# NUMBER
NAME VARCHAR2（30）
INCLUDED_IN_DATABASE_BACKUP VARCHAR2（3）
SQL> select * from v$tablespace;
TS# NAME INC
---------- ------------------------------ ---
3 CWMLITE YES
4 DRSYS YES
5 EXAMPLE YES
6 INDX YES
7 ODM YES
0 SYSTEM YES
8 TOOLS YES
1 UNDOTBS1 YES
9 USERS YES
10 XDB YES
2 TEMP YES
TS# NAME INC
---------- ------------------------------ ---
12 HOMS YES
已选择12行。
4、检查字符集,源库和目标库是一样的，可以做数据库迁移
SQL> set line 200
SQL> col property_value format a50
SQL> col description format a50
SQL> l
1* select * from database_properties
SQL> r
1* select * from database_properties
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DBTIMEZONE -07:00 DB time zone
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET ZHS16GBK Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
NLS_RDBMS_VERSION 9.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME HOMS.JXTELE.COM.CN Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
已选择25行。
SQL>
只需要迁移一个用户下的所有数据 JXTELE_HOMES
SQL> select username,default_tablespace,temporary_tablespace,created,account_status from dba_users where USERNAME= "JXTELE_HOMS";
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED ACCOUNT_STATUS
------------ ------------------------------ ------------------------------ ---------- --------------------------------
JXTELE_HOMS HOMS TEMP 23-8月 -10 OPEN
SQL>
SQL> alter tablespace HOMS read only;
表空间已更改。
SQL>
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
CWMLITE ONLINE
DRSYS ONLINE
EXAMPLE ONLINE
INDX ONLINE
ODM ONLINE
TOOLS ONLINE
USERS ONLINE
XDB ONLINE
TABLESPACE_NAME STATUS
-------------------- ---------
HOMS READ ONLY
已选择12行。
SQL>
SQL> exec dbms_tts.transport_set_check （"HOMS",true）;
PL/SQL 过程已成功完成。
SQL> select * from transport_set_violations;
no rows selected
SQL>
C:>exp """/ as sysdba""" wner=JXTELE_HOMS file=exp_HOMS.dmp log=exp_HOMS.LOG;
Export: Release 9.2.0.1.0 - Production on 星期五 11月 11 15:24:04 2011
Copyright （c） 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 JXTELE_HOMS 的外部函数库名称
. 导出 PUBLIC 类型同义词
. 导出私有类型同义词
. 正在导出用户 JXTELE_HOMS 的对象类型定义
即将导出 JXTELE_HOMS 的对象 ...
. 正在导出数据库链接
. 正在导出序号
. 正在导出群集定义
. 即将导出 JXTELE_HOMS 的表通过常规路径 ...
. . 正在导出表 ADMIN 1734 行被导出
. . 正在导出表 BULLETIN 183 行被导出
. . 正在导出表 CHECKCONDITION 0 行被导出
. . 正在导出表 COUNTRYINFO 235 行被导出
. . 正在导出表 DICTIONARY_APANAGE 6 行被导出
. . 正在导出表 DICTIONARY_CHINA_CARD 7 行被导出
. . 正在导出表 DICTIONARY_COUNTRY 3527 行被导出
. . 正在导出表 DICTIONARY_HOTELGRADE 6 行被导出
. . 正在导出表 DICTIONARY_INTERNATIONAL_CARD 51 行被导出
. . 正在导出表 DICTIONARY_NATION 58 行被导出
. . 正在导出表 DICTIONARY_ORDERCASE 4 行被导出
. . 正在导出表 DICTIONARY_PENALCASE 5 行被导出
. . 正在导出表 DICTIONARY_POSITIONGRADE 5 行被导出
. . 正在导出表 DICTIONARY_PUNISH 5 行被导出
. . 正在导出表 DICTIONARY_STAR 6 行被导出
. . 正在导出表 DICTIONARY_STATUS 5 行被导出
. . 正在导出表 DICTIONARY_USERSNAME 1736 行被导出
. . 正在导出表 DICTIONARY_USERSNAME_BACK 1328 行被导出
. . 正在导出表 DICTIONARY_VISA_UNIT 255 行被导出
易网时代-编程资源站