Oracle 9206 upgrade to 10.2.0.2

Posted: 三月 6, 2011 in Uncategorized

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:\oracle\ora92\database\INITzhangye.ORA)到oracle 10g的pfile(D:\oracle10g\database\INITzhangye.ORA).

 

检查info.log,对oracle 10g的pfile修改.

 

–oracle 10g修改前的pfile

 

 

*.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\zhangye\bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle\oradata\zhangye\control01.ctl',

'D:\oracle\oradata\zhangye\control02.ctl',
'D:\oracle\oradata\zhangye\control03.ctl'
*.core_dump_dest='D:\oracle\admin\zhangye\cdump'
*.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:\oracle\admin\zhangye\udump'
*.log_archive_start=true
*.log_archive_format=%s.arc
*.log_archive_dest=D:\oracle\archive 

–oracle 10g修改后的pfile

 

 

*.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\zhangye\bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle\oradata\zhangye\control01.ctl',

'D:\oracle\oradata\zhangye\control02.ctl',
'D:\oracle\oradata\zhangye\control03.ctl'
*.core_dump_dest='D:\oracle\admin\zhangye\cdump'
*.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:\oracle\admin\zhangye\udump'
#*.log_archive_start=true
#*.log_archive_format=%s.arc
#*.log_archive_dest=D:\oracle\archive
*.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:\oracle10g\database\INITzhangye.ORA 

 

7.UPGRADE选项启动DB.

 

 

SQL> STARTUP UPGRADE 

 

8.创建SYSAUX表空间

 

 

CREATE TABLESPACE sysaux DATAFILE 'D:\oracle\oradata\zhangye\sysaux01.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:\oracle10g\database\INITzhangye.ORA
SQL> @D:\oracle10g\RDBMS\ADMIN\utlrp.sql 

 

编译后没有无效对象

 

 

SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
COUNT(*)
----------
0

 

升级完成。

 

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s