ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 9206升级到10.2.0.2

oracle 9206升级到10.2.0.2

原创 Linux操作系统 作者:crpp0902 时间:2019-04-16 16:27:06 0 删除 编辑

最近一台数据库报FATAL ERROR IN TWO-TASK SERVER: error = 12152错误.

经过metalink查询,这是oracle一个未公布的bug:3816595。

1.安装oracle 10g r2的软件
2.打oracle 10g最新的补丁,我打的是升级到10.2.0.2的补丁
(我这里由于有已经安装好的10.2.0.2的包,所以直接打包拷贝到要升级的机器上)
目录为/opt/oracle/ora10g

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.
Oracle Database 10.2 Upgrade Information Utility 09-08-2009 16:42:39
.
**********************************************************************
Database:
**********************************************************************
--> name: KONG
--> version: 9.2.0.4.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: 987 MB
.... AUTOEXTEND additional space required: 77 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
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
--> Oracle Workspace Manager [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
.... WMSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER PHOTO has 4 INVALID objects.
.... USER SYS has 6 INVALID objects.
.... USER SYSTEM has 1 INVALID objects.
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.

PL/SQL procedure successfully completed.


修改后的参数为:

more /opt/oracle/ora10g/product/10.2.0/dbs/initkong.ora

*.background_dump_dest='/opt/oracle/admin/kong/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/opt/oracle/oradata/kong/control01.ctl','/opt/oracle/oradata/kong/control02.ctl','/opt/oracle/oradata/kong/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/kong/cdump'
*.db_block_size=8192
*.db_cache_size=1210612736
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='kong'
*.fast_start_mttr_target=300
#*.hash_join_enabled=TRUE
*.instance_name='kong'
*.java_pool_size=67108864
*.job_queue_processes=10
*.large_pool_size=157286400
*.streams_pool_size=50331648
*.session_max_open_files=20
#*.log_archive_dest='/opt/oracle/admin/kong/arch'
#*.log_archive_start=TRUE
#*.log_buffer=1048576
*.open_cursors=1000
*.pga_aggregate_target=524288000
*.processes=1000
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=209715200
*.sort_area_size=1048576
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/kong/udump'

4.rman备份DB.(由于我这边每天凌晨都有备份,所以这部不执行)

5.stop所有oracle 9i的服务.
sqlplus>shutdown immediate

lsnrctl stop


6.UPGRADE选项启动DB.
SQL> STARTUP UPGRADE
7.创建SYSAUX表空间
CREATE TABLESPACE sysaux DATAFILE '/opt/oracle/oradata/kong/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
8.运行10g的$ORACLE_HOME/rdbms/admin/catupgrd.sql,然后运行utlu102s.sql查看升级结果.
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> @utlu102s.sql
SQL> SPOOL OFF
SQL> @/opt/oracle/ora10g/product/10.2.0/rdbms/admin/utlu102s.sql
Oracle Database 10.2 Upgrade Status Utility 09-08-2009 17:10:22
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.2.0 00:08:19
Oracle Workspace Manager VALID 10.2.0.1.0 00:00:29
.
Total Upgrade Time: 00:09:35

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1744830464 bytes
Fixed Size 1261284 bytes
Variable Size 503316764 bytes
Database Buffers 1224736768 bytes
Redo Buffers 15515648 bytes
Database mounted.
Database opened.
SQL> exit

9、重起数据库,重新编译失效的对象。
SQL> @/opt/oracle/ora10g/product/10.2.0/rdbms/admin/utlrp.sql
编译后没有无效对象
SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';

COUNT(*)
----------
0
升级完成.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28419/viewspace-615347/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-11-14

  • 博文量
    23
  • 访问量
    15953