oracle 9201升级到9208 for windows 步骤

今天有网友说他的数据库服务今天又自动停了，以前也说过查看alert日志并没报错，stackpack也看不出什么问题，windows排错还是必较麻烦(黑盒)，所以建议升级到9208版试试
我刚好本机有9i201的环境，帮他测一把看升级有没有什么异常（不排除特殊环境）
网上下载了升级补丁p4547809_92080_WINNT，我的xp上装有9i和10g
下面看我的操作
set oracle_sid=anbob
set oracle_home=D:\\oracle\\product\\9.2.0\\Db_2
set path=D:\\oracle\\product\\9.2.0\\Db_2\\bin;%path%
sqlplus "/ as sysdba"
另存为ora9.bat ，因为我是先装的9I后装的10G所以每次要配一下环境

SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 12月 13 16:29:32 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL>

停掉所有oracle服务
及Distributed Transaction Coordinator 服务
下载升级补丁p4547809_92080_WINNT
找开升级程序调出OUI，选择9I 的ORACLE_HOME下一步
有可能出现一个乱码的提示框，确认9I的服务已停止，（我的有弹出只因为我停服务前的一个cmd窗口的sqlplus 没有退出），重试 下一步 点安装
短短2分钟安装完成
刷新服务，启动oracle 服务

C:\\>ora9
C:\\>set oracle_sid=anbob
C:\\>set oracle_home=D:\\oracle\\product\\9.2.0\\Db_2
C:\\>set path=D:\\oracle\\product\\9.2.0\\Db_2\\bin;d:\\oracle\\product\\9.2.0\\Db_2\\jre\\1.4.2\\bin\\client;d:\\oracle\\product\\9.2.0\\Db_2\\jre\\1.4.2\\bin;D:\\oracle10g\\product\\
10.1.0\\Db_1\\bin;D:\\oracle10g\\product\\10.1.0\\Db_1\\jre\\1.4.2\\bin\\client;D:\\oracle10g\\product\\10.1.0\\Db_1\\jre\\1.4.2\\bin;c:\\java6\\bin;d:\\oracle\\product\\9.2.0\\Db_2\\b
in;C:\\Program Files\\Oracle\\jre\\1.3.1\\bin;C:\\Program Files\\Oracle\\jre\\1.1.8\\bin;C:\\Program Files\\Common Files\
etSarang;E:\\oracle\\product\\10.1.0\\Db_2\\BIN;E:\\orac
le\\product\\10.1.0\\Db_2\\jre\\1.4.2\\bin\\client;E:\\oracle\\product\\10.1.0\\Db_2\\jre\\1.4.2\\bin;C:\\WINDOWS\\system32;C:\\WINDOWS;C:\\WINDOWS\\System32\\Wbem;C:\\Program Files
\\Common Files\\Thunder Network\\KanKan\\Codecs;C:\\Program Files\\Microsoft SQL Server\\80\\Tools\\BINN;C:\\Program Files\\T58KTV\\9158VirtualCamera\\Package\\bpl;C:\\Program
Files\\T58KTV\\9158VirtualCamera\\bin
C:\\>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 12月 13 17:05:37 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL>
Check SYSTEM Tablespace Size
If JServer is part of the installation ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace
SQL> select tablespace_name, sum(bytes)/(1024*1024) as free_space from
dba_free_space
where tablespace_name = 'SYSTEM'
group by tablespace_name;
TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
SYSTEM 2.6875
SQL> col file_name for a50
SQL> l
1 select file_name, round(bytes/(1024*1024),0) total_space
2 from dba_data_files
3* where tablespace_name = 'SYSTEM'
SQL> /
FILE_NAME TOTAL_SPACE
-------------------------------------------------- -----------
D:\\ORACLE\\ORADATA\\ANBOB\\SYSTEM01.DBF 400
SQL> alter database datafile 'D:\\ORACLE\\ORADATA\\ANBOB\\SYSTEM01.DBF' resize 420m;
数据库已更改。

SQL> select tablespace_name, sum(bytes)/(1024*1024) as free_space from
2 dba_free_space
3 where tablespace_name = 'SYSTEM'
4 group by tablespace_name;
TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
SYSTEM 22.6875
Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file
注意是至少是150M并不是非要150M，不过这也是建议值，不过我的机器内存太小不再设置，下面是我的大小，千万不要按我的设置
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------
shared_pool_reserved_size big integer 2516582
shared_pool_size big integer 50331648
SQL> show parameter java_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------
java_pool_size big integer 33554432
java_pool视java应用而定，shared_pool这个有几个经验值

物理内存|shared_pool
512 50
1024 100
2048 150
检查组件版本并运行升级脚本
SQL> l
1 select comp_name,status,substr(version,1,10)
2* from dba_registry
SQL> /
COMP_NAME STATUS SUBSTR(VERSION,1,10)
------------------------------ ----------- --------------------
Oracle9i Catalog Views VALID 9.2.0.1.0
Oracle9i Packages and Types VALID 9.2.0.1.0
Oracle Workspace Manager VALID 9.2.0.1.0
JServer JAVA Virtual Machine VALID 9.2.0.1.0
Oracle XDK for Java VALID 9.2.0.2.0
Oracle9i Java Packages VALID 9.2.0.1.0
Oracle interMedia LOADED 9.2.0.1.0
Spatial LOADED 9.2.0.1.0
Oracle Text VALID 9.2.0.1.0
Oracle XML Database VALID 9.2.0.1.0
Oracle Ultra Search VALID 9.2.0.1.0
Oracle Data Mining LOADED 9.2.0.1.0
OLAP Analytic Workspace LOADED 9.2.0.1.0
Oracle OLAP API LOADED 9.2.0.1.0
OLAP Catalog VALID 9.2.0.1.0
sql>shutdown immeidate
SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @ORACLE_BASE\\ORACLE_HOME\\rdbms\\admin\\catpatch.sql
SQL> SPOOL OFF
脚本如果没有变量可以指定完整路径，用时23分钟

SQL> SHUTDOWN
SQL> STARTUP
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @ORACLE_BASE\\ORACLE_HOME\\rdbms\\admin\\utlrp.sql
这个运行很快
SQL> l
1 select comp_name,status,substr(version,1,10)
2* from dba_registry
SQL> /
COMP_NAME STATUS SUBSTR(VERSION,1,10)
----------------------------------- ----------- --------------------
Oracle9i Catalog Views VALID 9.2.0.8.0
Oracle9i Packages and Types VALID 9.2.0.8.0
Oracle Workspace Manager VALID 9.2.0.1.0
JServer JAVA Virtual Machine VALID 9.2.0.8.0
Oracle XDK for Java VALID 9.2.0.10.0
Oracle9i Java Packages VALID 9.2.0.8.0
Oracle interMedia VALID 9.2.0.8.0
Spatial VALID 9.2.0.8.0
Oracle Text VALID 9.2.0.8.0
Oracle XML Database VALID 9.2.0.8.0
Oracle Ultra Search VALID 9.2.0.8.0
COMP_NAME STATUS SUBSTR(VERSION,1,10)
----------------------------------- ----------- --------------------
Oracle Data Mining VALID 9.2.0.8.0
OLAP Analytic Workspace UPGRADED 9.2.0.8.0
Oracle OLAP API UPGRADED 9.2.0.8.0
OLAP Catalog VALID 9.2.0.8.0
已选择15行。
到这里升级完成，建议有时间仔细先阅读一下升级包自带的README.HTML