1.安装oracle 10g r2的软件 2.打oracle 10g最新的补丁，我打的是升级到10.2.0.2的补丁 3.运行pre-upgrade information tool.它用来分析升级到10g前你要做的一些操作，比如需要增加或减少的参数等. 启动要升级的db，用sysdba运行10g的oracle_home/rdbms/admin
1.安装oracle 10g r2的软件
2.打oracle 10g最新的补丁，我打的是升级到10.2.0.2的补丁
3.运行pre-upgrade information tool.它用来分析升级到10g前你要做的一些操作，比如需要增加或减少的参数等.
启动要升级的db，用sysdba运行10g的oracle_home/rdbms/admin下的utlu102i.sql，查看产生的日志.
sql> spool info.log
sql> @utlu102i.sql
sql> spool off
下面是我的info.log.
sql> @d:utlu102i.sql;
oracle database 10.2 upgrade information utility 01-17-2007 13:22:37
.
**********************************************************************
database:
**********************************************************************
--> name: zhangye
--> version: 9.2.0.6.0
--> compatible: 9.2.0.0.0
--> blocksize: 8192
.
**********************************************************************
logfiles: [make adjustments in the current environment]
**********************************************************************
--> the existing log files are adequate. no changes are required.
.
**********************************************************************
tablespaces: [make adjustments in the current environment]
**********************************************************************
--> system tablespace is adequate for the upgrade.
.... minimum required size: 248 mb
--> temp tablespace is adequate for the upgrade.
.... minimum required size: 58 mb
.... autoextend additional space required: 18 mb
.
**********************************************************************
update parameters: [update oracle database 10.2 init.ora or spfile]
**********************************************************************
warning: --> shared_pool_size needs to be increased to at least 177861837
warning: --> java_pool_size needs to be increased to at least 67108864
warning: --> streams_pool_size is not currently defined and needs a value of
at least 50331648
warning: --> session_max_open_files needs to be increased to at least 20
.
**********************************************************************
renamed parameters: [update oracle database 10.2 init.ora or spfile]
**********************************************************************
-- no renamed parameters found. no changes are required.
.
**********************************************************************
obsolete/deprecated parameters: [update oracle database 10.2 init.ora or spfile]
**********************************************************************
--> hash_join_enabled
--> log_archive_start
.
**********************************************************************
components: [the following database components will be upgraded or installed]
**********************************************************************
--> oracle catalog views [upgrade] valid
--> oracle packages and types [upgrade] valid
.
**********************************************************************
miscellaneous warnings
**********************************************************************
warning: --> deprecated connect role granted to some user/roles.
.... connect role after upgrade has only create session privilege.
warning: --> database contains stale optimizer statistics.
.... refer to the 10g upgrade guide for instructions to update
.... statistics prior to upgrading the database.
.... component schemas with stale statistics:
.... sys
.
**********************************************************************
sysaux tablespace:
[create tablespace in the oracle database 10.2 environment]
**********************************************************************
--> new sysaux tablespace
.... minimum required size for database upgrade: 500 mb
.
pl/sql 过程已成功完成。
sql> spool off;
copy oracle 9i的pfile(d:oracleora92databaseinitzhangye.ora)到oracle 10g的pfile(d:oracle10gdatabaseinitzhangye.ora).
检查info.log，对oracle 10g的pfile修改.
--oracle 10g修改前的pfile
*.aq_tm_processes=1
*.background_dump_dest='d:oracleadminzhangyebdump'
*.compatible='9.2.0.0.0'
*.control_files='d:oracleoradatazhangyecontrol01.ctl','d:oracleoradatazhangyecontrol02.ctl',
'd:oracleoradatazhangyecontrol03.ctl'
*.core_dump_dest='d:oracleadminzhangyecdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='zhangye'
*.fast_start_mttr_target=300
*.hash_join_enabled=true*.instance_name='zhangye'
*.java_pool_size=0
*.job_queue_processes=10
*.large_pool_size=8388608
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='false'
*.remote_login_passwordfile='exclusive'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='false'
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=10800
*.undo_tablespace='undotbs1'
*.user_dump_dest='d:oracleadminzhangyeudump'
*.log_archive_start=true
*.log_archive_format=%s.arc
*.log_archive_dest=d:oraclearchive
--oracle 10g修改后的pfile *.aq_tm_processes=1
*.background_dump_dest='d:oracleadminzhangyebdump'
*.compatible='9.2.0.0.0'
*.control_files='d:oracleoradatazhangyecontrol01.ctl','d:oracleoradatazhangyecontrol02.ctl',
'd:oracleoradatazhangyecontrol03.ctl'
*.core_dump_dest='d:oracleadminzhangyecdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='zhangye'
*.fast_start_mttr_target=300
#*.hash_join_enabled=true
*.instance_name='zhangye'
*.java_pool_size=67108864
*.job_queue_processes=10
*.large_pool_size=8388608
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='false'
*.remote_login_passwordfile='exclusive'
*.shared_pool_size=177861837
*.sort_area_size=524288
*.star_transformation_enabled='false'
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=10800
*.undo_tablespace='undotbs1'
*.user_dump_dest='d:oracleadminzhangyeudump'
#*.log_archive_start=true
#*.log_archive_format=%s.arc
#*.log_archive_dest=d:oraclearchive
*.streams_pool_size=50331648
*.session_max_open_files=20
4.运行oracle net configuration assistant，生成10g的listener.ora
5.rman备份db.
6.stop所有oracle 9i的服务.
删除oracle 9i的服务.
c:> oradim -delete -sid zhangye
新建oracle 10g的服务
c:> oradim -new -sid zhangye -maxusers 10 -startmode auto -pfile d:oracle10gdatabaseinitzhangye.ora
7.upgrade选项启动db.
sql> startup upgrade
8.创建sysaux表空间
create tablespace sysaux datafile 'd:oracleoradatazhangyesysaux01.dbf'
size 500m reuse
extent management local
segment space management auto
online;
9.运行10g的oracle_home/rdbms/admin/catupgrd.sql，然后运行utlu102s.sql查看升级结果.
sql> spool upgrade.log
sql> @catupgrd.sql
sql> @utlu102s.sql
sql> spool off
检查upgrade.log，如果有错误，处理错误后再次运行catupgrd.sql.
10.重新启动db，，编译无效对象.
sql> shutdown immediate
sql> startup pfile=d:oracle10gdatabaseinitzhangye.ora
sql> @d:oracle10grdbmsadminutlrp.sql
编译后没有无效对象
sql> select count(*) from dba_objects where status='invalid';
count(*)
----------
0
升级完成.