平台环境： 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 行被导出
易网时代-编程资源站