oracle 升级到 11.2.0.2

11.2.0.2 的补丁出了2个多月了，一直在说要做升级测试，忙着处理杭州搬机房的事情，一直拖着，最近搬完机房，终于有时间来做一次升级测试。

【升级前版本： 】
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

【升级后版本：】
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

【升级目的：】解决secure file 的 ora-07445 错误

【升级步骤：】

1.  准备步骤：导入之前在做 update 和 delete 操作的时候提示 ora-7445 错误的表和相关记录，尝试通过 update 和 delete 操作在测试环境重现 ora-7445 错误

2.  下载补丁包、确认系统包足够

3.  开始安装 oracle 补丁包

提示是否需要更新相关的软件

可以有如下选择：

（1）选择只安装软件，之后通过DBUA或者手工执行升级脚本来升级

（2）选择升级数据库，之后不需要DBUA升级，也不需要手工执行升级脚本

对单实例的数据库进行升级

选择语言：英语+简体中文

选择企业版



剩下的就和正常安装数据库差不多。

4.  软件安装完毕之后：
（1）如果之前选择了升级存在的数据库，那么会进入升级界面：




升级程序会检查 recycle bin 是否清空，包括 dba_recycle 和 user_recycle；同时也会检查系统里面的无效对象，最好在升级前就确认无效对象。






如果选择在升级前备份数据，需要确认磁盘空间是否足够做备份。如果磁盘空间不足，那么会出现提示：










开始升级啦，升级和以往一样慢。

（2）如果之前选择了只安装软件，简单描述一下手工升级数据字典流程：

【步骤1：】 关闭数据库：
shut immediate;

【步骤2：】 关闭监听器：
lsnrctl stop

【步骤3：】 修改 $ORACLE_HOME：
cd /home/oracle
mv  app app_old   
mv appnew app

【步骤4：】不一定必要，取决于 datafile、logfile、controlfile 存储的位置，如果和 $ORACLE_HOME 无关，则不需要修改。
mv /home/oracle/app_old/oracle/oradata/$ORACLE_SID/datafile/  /home/oracle/app/oracle/oradata/$ORACLE_SID/datafile/ 
mv /home/oracle/app_old/oracle/oradata/$ORACLE_SID/onlinelog/  /home/oracle/app/oracle/oradata/$ORACLE_SID/onlinelog/ 
mv /home/oracle/app_old/oracle/oradata/$ORACLE_SID/controlfile/ /home/oracle/app/oracle/oradata/$ORACLE_SID/controlfile/

【步骤5：】拷贝密码文件 orapw$ORACLE_SID 
SPFILE 文件 spfile$ORACLE_SID.ora  
监听配置文件 listener.ora 
本地 tnsnames.ora  sqlnet.ora文件
到新 $ORACLE_HOME

【步骤6：】重启数据到upgrade，升级数据字典
sqlplus / as sysdba
startup upgrade;
@?/rdbms/admin/catupgrd
shut immediate

【步骤7：】重启数据库，编译无效对象
sqlplus / as sysdba
startup
@?/rdbms/admin/utlrp

【步骤8：】确认升级后版本
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> col namespace for a20;
SQL> col version for a15;
SQL> col comments for a40;
SQL> select namespace,version,comments from registry$history;

NAMESPACE            VERSION         COMMENTS
-------------------- --------------- ----------------------------------------
view invalidation
SERVER               11.2.0.2.0      Upgraded from 11.2.0.1.0
SERVER               11.2.0.2        Patchset 11.2.0.2.0
view invalidation
SERVER               11.2.0.2.0      Upgraded from 11.2.0.1.0

SQL>

【步骤9：】确认 opatch 能用：opatch lsinventory -detail

Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : /home/oracle/app/oracle/product/11.2.0
Central Inventory : /home/oracle/app/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /home/oracle/app/oracle/product/11.2.0/oui
Log file location : /home/oracle/app/oracle/product/11.2.0/cfgtoollogs/opatch/opatch2010-11-10_14-54-16PM.log

Patch history file: /home/oracle/app/oracle/product/11.2.0/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /home/oracle/app/oracle/product/11.2.0/cfgtoollogs/opatch/lsinv/lsinventory2010-11-10_14-54-16PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0
There are 1 products installed in this Oracle Home.


Installed Products (136):

Agent Required Support Files                                         10.2.0.4.3
Assistant Common Files                                               11.2.0.2.0
Bali Share                                                           1.1.18.0.0
Buildtools Common Files                                              11.2.0.2.0
Character Set Migration Utility                                      11.2.0.2.0
Cluster Verification Utility Common Files                            11.2.0.2.0
Database Configuration and Upgrade Assistants                        11.2.0.2.0
Database SQL Scripts                                                 11.2.0.2.0
Database Workspace Manager                                           11.2.0.2.0
Deinstallation Tool                                                  11.2.0.2.0
Enterprise Edition Options                                           11.2.0.2.0
Enterprise Manager Agent                                             10.2.0.4.3
Enterprise Manager Agent Core Files                                  10.2.0.4.3
Enterprise Manager Common Core Files                                 10.2.0.4.3
Enterprise Manager Common Files                                      10.2.0.4.3
Enterprise Manager Database Plugin -- Agent Support                  11.2.0.2.0
Enterprise Manager Database Plugin -- Repository Support             11.2.0.2.0
Enterprise Manager Grid Control Core Files                           10.2.0.4.3
Enterprise Manager plugin Common Files                               11.2.0.2.0
Enterprise Manager Repository Core Files                             10.2.0.4.3
Exadata Storage Server                                               11.2.0.1.0
Expat libraries                                                       2.0.1.0.1
Generic Connectivity Common Files                                    11.2.0.2.0
HAS Common Files                                                     11.2.0.2.0
HAS Files for DB                                                     11.2.0.2.0
Installation Common Files                                            11.2.0.2.0
Installation Plugin Files                                            11.2.0.2.0
Installer SDK Component                                              11.2.0.2.0
JAccelerator (COMPANION)                                             11.2.0.2.0
LDAP Required Support Files                                          11.2.0.2.0
...

XDK Required Support Files                                           11.2.0.2.0
XML Parser for Java                                                  11.2.0.2.0
XML Parser for Oracle JVM                                            11.2.0.2.0
There are 136 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.

如果 Opatch 不能用，需要确认 /etc/oratab 和 /etc/oraInst.loc 里面指向的值都正确。
拷贝原 oraInventory 到现在的 oraInventory  目录下：
mv /home/oracle/app_old/oraInventory /home/oracle/app/oraInventory