安装oracle g r的软件
打oracle g最新的补丁我打的是升级到的补丁
运行PreUpgrade Information Tool它用来分析升级到g前你要做的一些操作比如需要增加或减少的参数等
启动要升级的DB用SYSDBA运行g的ORACLE_HOME/rdbms/admin下的utluisql查看产生的日志
SQL> SPOOL infolog
SQL> @utluisql
SQL> SPOOL OFF
下面是我的infolog
SQL> @d:utluisql;
Oracle Database Upgrade Information Utility ::
**********************************************************************
Database:
**********************************************************************
> name: ZHANGYE
> version:
> compatible:
> blocksize:
**********************************************************************
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: MB
> TEMP tablespace is adequate for the upgrade
minimum required size: MB
AUTOEXTEND additional space required: MB
**********************************************************************
Update Parameters: [Update Oracle Database initora or spfile]
**********************************************************************
WARNING: > "shared_pool_size" needs to be increased to at least
WARNING: > "java_pool_size" needs to be increased to at least
WARNING: > "streams_pool_size" is not currently defined and needs a value of
at least
WARNING: > "session_max_open_files" needs to be increased to at least
**********************************************************************
Renamed Parameters: [Update Oracle Database initora or spfile]
**********************************************************************
No renamed parameters found No changes are required
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database initora 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 g 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 environment]
**********************************************************************
> New "SYSAUX" tablespace
minimum required size for database upgrade: MB
PL/SQL 过程已成功完成
SQL> spool off;
copy oracle i的pfile(D:oracleoradatabaseINITzhangyeORA)到oracle g的pfile(D:oraclegdatabaseINITzhangyeORA)
检查infolog对oracle g的pfile修改
oracle g修改前的pfile
*aq_tm_processes=
*background_dump_dest=D:oracleadminzhangyebdump
*compatible=
*control_files=D:oracleoradatazhangyecontrolctlD:oracleoradatazhangyecontrolctl
D:oracleoradatazhangyecontrolctl
*core_dump_dest=D:oracleadminzhangyecdump
*db_block_size=
*db_cache_size=
*db_domain=
*db_file_multiblock_read_count=
*db_name=zhangye
*fast_start_mttr_target=
*hash_join_enabled=TRUE*instance_name=zhangye
*java_pool_size=
*job_queue_processes=
*large_pool_size=
*open_cursors=
*pga_aggregate_target=
*processes=
*query_rewrite_enabled=FALSE
*remote_login_passwordfile=EXCLUSIVE
*shared_pool_size=
*sort_area_size=
*star_transformation_enabled=FALSE
*timed_statistics=TRUE
*undo_management=AUTO
*undo_retention=
*undo_tablespace=UNDOTBS
*user_dump_dest=D:oracleadminzhangyeudump
*log_archive_start=true
*log_archive_format=%sarc
*log_archive_dest=D:oraclearchive
oracle g修改后的pfile *aq_tm_processes=
*background_dump_dest=D:oracleadminzhangyebdump
*compatible=
*control_files=D:oracleoradatazhangyecontrolctlD:oracleoradatazhangyecontrolctl
D:oracleoradatazhangyecontrolctl
*core_dump_dest=D:oracleadminzhangyecdump
*db_block_size=
*db_cache_size=
*db_domain=
*db_file_multiblock_read_count=
*db_name=zhangye
*fast_start_mttr_target=
#*hash_join_enabled=TRUE
*instance_name=zhangye
*java_pool_size=
*job_queue_processes=
*large_pool_size=
*open_cursors=
*pga_aggregate_target=
*processes=
*query_rewrite_enabled=FALSE
*remote_login_passwordfile=EXCLUSIVE
*shared_pool_size=
*sort_area_size=
*star_transformation_enabled=FALSE
*timed_statistics=TRUE
*undo_management=AUTO
*undo_retention=
*undo_tablespace=UNDOTBS
*user_dump_dest=D:oracleadminzhangyeudump
#*log_archive_start=true
#*log_archive_format=%sarc
#*log_archive_dest=D:oraclearchive
*streams_pool_size=
*session_max_open_files=
运行Oracle Net Configuration Assistant生成g的listenerora
rman备份DB
stop所有oracle i的服务
删除oracle i的服务
C:> ORADIM DELETE SID ZHANGYE
新建oracle g的服务
C:> ORADIM NEW SID ZHANGYE MAXUSERS STARTMODE AUTO PFILE D:oraclegdatabaseINITzhangyeORA
UPGRADE选项启动DB
SQL> STARTUP UPGRADE
创建SYSAUX表空间
CREATE TABLESPACE sysaux DATAFILE D:oracleoradatazhangyesysauxdbf
SIZE M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
运行g的ORACLE_HOME/rdbms/admin/catupgrdsql然后运行utlussql查看升级结果
SQL> SPOOL upgradelog
SQL> @catupgrdsql
SQL> @utlussql
SQL> SPOOL OFF
检查upgradelog如果有错误处理错误后再次运行catupgrdsql
重新启动DB编译无效对象
SQL> shutdown immediate
SQL> startup pfile=D:oraclegdatabaseINITzhangyeORA
SQL> @D:oraclegRDBMSADMINutlrpsql
编译后没有无效对象
SQL> SELECT count(*) FROM dba_objects WHERE status=INVALID;
COUNT(*)
升级完成
