1.查询当前数据库版本信息：
col comp_name for a30
col comp_id for a10
col version for a10
select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle9i Catalog Views CATALOG 9.2.0.4.0 VALID
Oracle9i Packages and Types CATPROC 9.2.0.4.0 VALID
Oracle Workspace Manager OWM 9.2.0.1.0 VALID
JServer JAVA Virtual Machine JAVAVM 9.2.0.4.0 VALID
Oracle XDK for Java XML 9.2.0.6.0 VALID
Oracle9i Java Packages CATJAVA 9.2.0.4.0 VALID
Oracle interMedia ORDIM 9.2.0.4.0 VALID
Spatial SDO 9.2.0.4.0 LOADED
Oracle Text CONTEXT 9.2.0.4.0 VALID
Oracle XML Database XDB 9.2.0.4.0 VALID
Oracle Ultra Search WK 9.2.0.4.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle Data Mining ODM 9.2.0.1.0 LOADED
OLAP Analytic Workspace APS 9.2.0.4.0 LOADED
Oracle OLAP API XOQ 9.2.0.4.0 LOADED
OLAP Catalog AMD 9.2.0.4.0 VALID
2.发现几个组件状态是LOADED，将它们修改正常：
2.1运行脚本@?/olap/admin/xoqpatch.sql
之后。
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle9i Catalog Views CATALOG 9.2.0.4.0 VALID
Oracle9i Packages and Types CATPROC 9.2.0.4.0 VALID
Oracle Workspace Manager OWM 9.2.0.1.0 VALID
JServer JAVA Virtual Machine JAVAVM 9.2.0.4.0 VALID
Oracle XDK for Java XML 9.2.0.6.0 VALID
Oracle9i Java Packages CATJAVA 9.2.0.4.0 VALID
Oracle interMedia ORDIM 9.2.0.4.0 VALID
Spatial SDO 9.2.0.4.0 LOADED
Oracle Text CONTEXT 9.2.0.4.0 VALID
Oracle XML Database XDB 9.2.0.4.0 VALID
Oracle Ultra Search WK 9.2.0.4.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle Data Mining ODM 9.2.0.1.0 LOADED
OLAP Analytic Workspace APS 9.2.0.4.0 LOADED
Oracle OLAP API XOQ 9.2.0.4.0 UPGRADED
OLAP Catalog AMD 9.2.0.4.0 VALID
2.2运行脚本@?/olap/admin/apspatch.sql之后
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle9i Catalog Views CATALOG 9.2.0.4.0 VALID
Oracle9i Packages and Types CATPROC 9.2.0.4.0 VALID
Oracle Workspace Manager OWM 9.2.0.1.0 VALID
JServer JAVA Virtual Machine JAVAVM 9.2.0.4.0 VALID
Oracle XDK for Java XML 9.2.0.6.0 VALID
Oracle9i Java Packages CATJAVA 9.2.0.4.0 VALID
Oracle interMedia ORDIM 9.2.0.4.0 VALID
Spatial SDO 9.2.0.4.0 LOADED
Oracle Text CONTEXT 9.2.0.4.0 VALID
Oracle XML Database XDB 9.2.0.4.0 VALID
Oracle Ultra Search WK 9.2.0.4.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle Data Mining ODM 9.2.0.1.0 LOADED
OLAP Analytic Workspace APS 9.2.0.4.0 UPGRADED
Oracle OLAP API XOQ 9.2.0.4.0 UPGRADED
OLAP Catalog AMD 9.2.0.4.0 VALID
2.3运行脚本@?/javavm/install/jvmpatch.sql
之后。
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle9i Catalog Views CATALOG 9.2.0.4.0 VALID
Oracle9i Packages and Types CATPROC 9.2.0.4.0 VALID
Oracle Workspace Manager OWM 9.2.0.1.0 VALID
JServer JAVA Virtual Machine JAVAVM 9.2.0.4.0 LOADING
Oracle XDK for Java XML 9.2.0.6.0 VALID
Oracle9i Java Packages CATJAVA 9.2.0.4.0 VALID
Oracle interMedia ORDIM 9.2.0.4.0 VALID
Spatial SDO 9.2.0.4.0 LOADED
Oracle Text CONTEXT 9.2.0.4.0 VALID
Oracle XML Database XDB 9.2.0.4.0 VALID
Oracle Ultra Search WK 9.2.0.4.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle Data Mining ODM 9.2.0.1.0 LOADED
OLAP Analytic Workspace APS 9.2.0.4.0 UPGRADED
Oracle OLAP API XOQ 9.2.0.4.0 UPGRADED
OLAP Catalog AMD 9.2.0.4.0 VALID
2.4运行脚本@?/md/admin/sdopatch
之后
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle9i Catalog Views CATALOG 9.2.0.4.0 VALID
Oracle9i Packages and Types CATPROC 9.2.0.4.0 VALID
Oracle Workspace Manager OWM 9.2.0.1.0 VALID
JServer JAVA Virtual Machine JAVAVM 9.2.0.4.0 LOADING
Oracle XDK for Java XML 9.2.0.6.0 VALID
Oracle9i Java Packages CATJAVA 9.2.0.4.0 VALID
Oracle interMedia ORDIM 9.2.0.4.0 VALID
Spatial SDO 9.2.0.4.0 VALID
Oracle Text CONTEXT 9.2.0.4.0 VALID
Oracle XML Database XDB 9.2.0.4.0 VALID
Oracle Ultra Search WK 9.2.0.4.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle Data Mining ODM 9.2.0.1.0 LOADED
OLAP Analytic Workspace APS 9.2.0.4.0 UPGRADED
Oracle OLAP API XOQ 9.2.0.4.0 UPGRADED
OLAP Catalog AMD 9.2.0.4.0 VALID
2.5重新执行了一次@?/rdbms/admin/catpatch.sql
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle9i Catalog Views CATALOG 9.2.0.4.0 VALID
Oracle9i Packages and Types CATPROC 9.2.0.4.0 VALID
Oracle Workspace Manager OWM 9.2.0.1.0 VALID
JServer JAVA Virtual Machine JAVAVM 9.2.0.4.0 VALID
Oracle XDK for Java XML 9.2.0.6.0 VALID
Oracle9i Java Packages CATJAVA 9.2.0.4.0 VALID
Oracle interMedia ORDIM 9.2.0.4.0 VALID
Spatial SDO 9.2.0.4.0 VALID
Oracle Text CONTEXT 9.2.0.4.0 VALID
Oracle XML Database XDB 9.2.0.4.0 VALID
Oracle Ultra Search WK 9.2.0.4.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle Data Mining ODM 9.2.0.4.0 VALID
OLAP Analytic Workspace APS 9.2.0.4.0 UPGRADED
Oracle OLAP API XOQ 9.2.0.4.0 UPGRADED
OLAP Catalog AMD 9.2.0.4.0 VALID
3.修改后的状态
在9i中status为VALID或者UPGRADED，说明组件都是正常的。
这只在9i中是这样的，10g和11g中不是。
4.从9.2.0.4升级到10.2.0.4
修改.bash_profile文件。
将其中的ORACLE_HOME=/u01/oracle/10.2修改，以及注释掉
#LD_ASSUME_KERNEL=2.4.19
安装Oracle10g 10.2.0.1
注意，9i数据库中使用到的组件都要选上。
5.如果oracle数据库有使用到组件Java Virtual Machine 或 Oracle interMedia，需要安装Oracle Database 10g Companion。
典型安装它，包括了Natively Compiled Java Library files，将提高java性能，如果没有安装Natively Compiled Java Library files，在升级以上组件时会报如下错误。
ORA-29558: JAccelerator (NCOMP) not installed error
安装Oracle Companion。
安装Oracle Database 10g Products Installation Type
6.安装Oracle 10.2.0.4的PATCH。
检查数据库是否安装了Oracle Label Security
如果有脚本$ORACLE_HOME/rdbms/admin/catnools.sql则安装了，没有则没有安装。
9.2升级到10.2，不能有Oracle Label Security
如果有，按照下面两步执行：
(1).Use Oracle Universal Installer release 9.2 to install Oracle Label Security using the Custom installation type.
(2).Run the $ORACLE_HOME/rdbms/admin/catnools.sql script with the SYSDBA privilege to remove Oracle Label Security components from the database.
7.在9isqlplus下运行脚本 @/u01/oracle/10.2/rdbms/admin/utlu102i.sql 。
8.通过建pfile的方式修改参数文件。
用新的ORACLE_HOME的环境，的sqlplus建立spfile。
将原来ORACLE_HOME环境的密码文件和listener.ora文件都cp到新的ORACLE_HOME中。
9.使用dbua，不要手动建立SYSAUX表空间
运行dbua升级，升级过程中会自动建立SYSAUX表空间
10.升级到10g后的组件状态。
组件状态全部正常
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
Oracle Enterprise Manager EM 10.2.0.4.0 VALID
Oracle Data Mining ODM 10.2.0.4.0 VALID
OLAP Catalog AMD 10.2.0.4.0 VALID
Oracle Ultra Search WK 10.2.0.4.0 VALID
Oracle XML Database XDB 10.2.0.4.0 VALID
Oracle Text CONTEXT 10.2.0.4.0 VALID
Spatial SDO 10.2.0.4.0 VALID
Oracle interMedia ORDIM 10.2.0.4.0 VALID
Oracle Workspace Manager OWM 10.2.0.4.3 VALID
Oracle Database Catalog Views CATALOG 10.2.0.4.0 VALID
Oracle Database Packages and T CATPROC 10.2.0.4.0 VALID
COMP_NAME COMP_ID VERSION STATUS
------------------------------ ---------- ---------- ----------------------
JServer JAVA Virtual Machine JAVAVM 10.2.0.4.0 VALID
Oracle XDK XML 10.2.0.4.0 VALID
Oracle Database Java Packages CATJAVA 10.2.0.4.0 VALID
OLAP Analytic Workspace APS 10.2.0.4.0 VALID
Oracle OLAP API XOQ 10.2.0.4.0 VALID
11.升级之后由于CONNECT角色的权限缩减了，为了保证应用不出问题，可能需要将10.2中的CONNECT角色的权限设置得与9i一样。
9i和10.1中CONNECT有以下权限：
GRANTEE PRIVILEGE
------------------------------ ---------------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK
在10.2中只有CREATE SESSION权限。
12.查询v$timezone_file，如果返回的是4，则不需要做任何操作。
如果高于或低于4，需要参考OracleMetalink document 553812.1 "Actions for the DSTv4 update in the Release 10.2.0.4 patchset".
SQL> select version from v$timezone_file;
VERSION
----------
4
