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

升级完成.

更多信息请查看IT技术专栏