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
升级完成.
转载请注明原文链接：oracle 9206升级到10.2.0.2简要步骤
