系统： redhat-as4-u7
oracle：10.2.0.4
源数据库：
主机：192.168.1.241
实例：orcl
目标数据库：
主机：192.168.1.215
实例：lizidb
所要迁移的表空间名称：lizi，数据文件lizi.dbf
步骤：
第一步：源数据库以sys身份登陆，将lizi表空间置为read only状态：
SQL> alter tablespace lizi read only;
Tablespace altered.
SQL>
第二步：在客户端pc机上把源数据库lizi表空间用exp导出：
D:\>EXP \" as sysdba\" FILE=lizi.DMP LOG=log.LOG TRANSPORT_TABLESPACE=y TABLESPACES=\"lizi\"
Export: Release 10.2.0.1.0 - Production on 星期四 3月 4 16:46:13 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 UTF8 NCHAR 字符集
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
对于表空间 LIZI...
. 正在导出簇定义
. 正在导出表定义
. 正在导出引用完整性约束条件
. 正在导出触发器
. 结束导出可传输的表空间元数据
成功终止导出, 没有出现警告。
*************************************************************
该步骤中，开始对exp以sys身份导出表空间的命令一直不正确，经仔细的研究和google查询才找到以上正确的命令。以后在对本问题进行补充。
*************************************************************
第三步：将源数据库lizi表空间数据文件lizi.dbf拷贝到目标数据库相应的目录下：/u00/oracle/lizidb/lizi.dbf
第四步：将源数据库lizi表空间置为read write
SQL> alter tablespace lizi read write;
Tablespace altered.
SQL>
第五步：在客户端pc机上用imp进行表空间迁移，将lizi表空间导入至目标数据库
D:\>imp \" as sysdba\" FILE=lizi.DMP LOG=log.LOG TRANSPORT_TABLESPACE=y TABLESPACES=\"lizi\" datafiles='/u00/oracle/lizidb/lizi.dbf'
Import: Release 10.2.0.1.0 - Production on 星期三 3月 10 11:03:59 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
即将导入可传输的表空间元数据...
已经完成 ZHS16GBK 字符集和 UTF8 NCHAR 字符集中的导入
. 正在将 SYS 的对象导入到 SYS
. 正在将 SYS 的对象导入到 SYS
成功终止导入, 没有出现警告。
注意：
*************************************************************
1、导入之前不要在目标数据库中创建相同的表空间记数据文件，否则会报下面的错误：
D:\>imp \" as sysdba\" FILE=users.DMP LOG=log.LOG TRANSPORT_TA
BLESPACE=y TABLESPACES=\"users\" datafiles='/u00/oracle/lizidb/users02.dbf'
Import: Release 10.2.0.1.0 - Production on 星期二 3月 30 16:53:44 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
即将导入可传输的表空间元数据...
已经完成 ZHS16GBK 字符集和 UTF8 NCHAR 字符集中的导入
. 正在将 SYS 的对象导入到 SYS
. 正在将 SYS 的对象导入到 SYS
IMP-00017: 由于 ORACLE 错误 29349, 以下语句失败:
"BEGIN sys.dbms_plugts.beginImpTablespace('USERS',4,'SYS',1,0,8192,1,65328"
"833,1,2147483645,8,128,8,0,1,0,8,2470502657,1,33,10743,NULL,0,0,NULL,NULL);"
" END;"
IMP-00003: 遇到 ORACLE 错误 29349
ORA-29349: tablespace 'USERS' already exists
ORA-06512: at "SYS.DBMS_PLUGTS", line 1801
ORA-06512: at line 1
IMP-00000: 未成功终止导入
2、
开始这一步骤中遇到了以下错误：
G:\>imp \" as sysdba\" FILE=lizi.DMP LOG=log.LOG TRANSPORT_TABLESPACE=y TABLESPACES=\"lizi\" datafiles='/u00/oracle/lizidb/lizi.dbf'
Import: Release 10.2.0.1.0 - Production on 星期三 3月 10 09:37:05 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
即将导入可传输的表空间元数据...
已经完成 ZHS16GBK 字符集和 UTF8 NCHAR 字符集中的导入
. 正在将 SYS 的对象导入到 SYS
. 正在将 SYS 的对象导入到 SYS
IMP-00017: 由于 ORACLE 错误 721, 以下语句失败:
"BEGIN sys.dbms_plugts.checkCompType('COMPATSG','10.2.0.3.0'); END;"
IMP-00003: 遇到 ORACLE 错误 721
ORA-00721: changes by release 10.2.0.3.0 cannot be used by release 10.2.0.1.0
ORA-06512: at "SYS.DBMS_PLUGTS", line 2004
ORA-06512: at line 1
IMP-00000: 未成功终止导入
经google搜索检查，原来是目标数据库在由10.2.0.1版本升级到10.2.0.4版本时候，不知道是什么原因compatible参数并没有升级成功，但是升级过程中并没有报错，导致源数据库与目标数据库的compatible参数的版本不一样，检查init文件：
源数据库：compatible=10.2.0.3.0
目标数据库：compatible=10.2.0.1.0
因此这个问题解决方法需要先将compatible升级，但是升级compatible并不是简单的将pfile和spfile中的compatible直接修改就能解决，需要修改所有datafile的datafile header，否则会报contrlfile同compatible版本不一致的错误：
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2089400 bytes
Variable Size 276827720 bytes
Database Buffers 788529152 bytes
Redo Buffers 6295552 bytes
ORA-00201: control file version 10.2.0.1.0 incompatible with ORACLE version
10.0.2.3.0
ORA-00202: control file: '/u00/oracle/lizidb/control01.ctl'
经高手指点，升级compatible方法如下：
SQL> alter system set compatible='10.2.0.3.0' scope=spfile;
System altered.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2089400 bytes
Variable Size 264244808 bytes
Database Buffers 801112064 bytes
Redo Buffers 6295552 bytes
Database mounted.
Database opened.
SQL> @/opt/oracle/product/10.2.0/rdbms/admin/catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
。。。
。。。
。。。
（这一步需要执行漫长的sql批处理脚本，耐心等待）
。。。
。。。
。。。
Total Upgrade Time: 00:20:57
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
（执行完毕）
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2089400 bytes
Variable Size 318770760 bytes
Database Buffers 746586112 bytes
Redo Buffers 6295552 bytes
Database mounted.
Database opened.
SQL> @/opt/oracle/product/10.2.0/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2010-03-10 10:57:50
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2010-03-10 10:59:25
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
（无错误）
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
（无错误，说明compatible升级成功）
SQL> show parameter compatible;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.3.0
SQL> SQL>
**************************************************************
第六步：在目标数据库上将表空间lizi置为read write
SQL> alter tablespace lizi read write;
Tablespace altered.
SQL>
以下进行数据迁移
第六步、导出源数据库用户及所有数据：
D:\>exp
Export: Release 10.2.0.1.0 - Production on 星期三 3月 17 10:54:51 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
输入数组提取缓冲区大小: 4096 >
导出文件: EXPDAT.DMP > lizidata.dmp
(2)U(用户), 或 (3)T(表): (2)U > 2
导出权限 (yes/no): yes >
导出表数据 (yes/no): yes >
压缩区 (yes/no): yes >
已导出 ZHS16GBK 字符集和 UTF8 NCHAR 字符集
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 LIZI 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 LIZI 的对象类型定义
即将导出 LIZI 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 LIZI 的表通过常规路径...
. . 正在导出表 TESTA导出了 10004 行
. 正在导出同义词
. 正在导出视图
. 正在导出存储过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计信息
成功终止导出, 没有出现警告。
第七步、在目标数据库中创建用户
[oracle@db-primary ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 17 11:07:07 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user lizi identified by lizi default tablespace lizi temporary tablespace temp;
User created.
SQL> GRANT "CONNECT" TO "lizi";
GRANT "CONNECT" TO "lizi"
*
ERROR at line 1:
ORA-01917: user or role 'lizi' does not exist
SQL> GRANT "CONNECT" TO lizi;
Grant succeeded.
SQL> grant resource to lizi;
Grant succeeded.
SQL> alter user lizi default role all;
User altered.
SQL> conn lizi
Enter password:
Connected.
SQL> alter user lizi identified by lizi123;
User altered.
SQL> exit
第八步、在目标数据库中导入用户及数据
D:\>imp file=lizidata.dmp fromuser=lizi touser=lizi grants=y
Import: Release 10.2.0.1.0 - Production on 星期三 3月 17 11:08:52 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
已经完成 ZHS16GBK 字符集和 UTF8 NCHAR 字符集中的导入
. . 正在导入表 "TESTA"导入了 10004 行
成功终止导入, 没有出现警告。
登陆数据库检查：
[oracle@db-primary ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 17 11:07:07 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn lizi/lizi123
Connected.
SQL>
SQL>
SQL> select tname from tab;
TNAME
------------------------------
TESTA
SQL> desc testa;
Name Null? Type
----------------------------------------- -------- ----------------------------
CODE NUMBER(10)
NAME VARCHAR2(10)
SQL> select * from testa where rownum<11;
CODE NAME
---------- ----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
