rolling upgrade

11.2.0.3 补丁集在美国时间9月23日发布了，关于11.2.0.3 发布的更多信息可以参考<Oracle 11gR2发布11.2.0.3 Patchset补丁集-又一重量级更新>一文。

这里我们来快速浏览由11.2.0.2 RAC升级到11.2.0.3的过程：

在正式升级GI/CRS之前需要先打上”Patch 12539000: 11203:ASM UPGRADE FAILED ON FIRST NODE WITH ORA-03113″

我们仅需要针对GI/CRS打上补丁，无需在RDBMS/DB上实施。该Patch可以滚动升级Rolling upgrade, 简易的实施流程如下：

1. 在所有节点上安装最新的opatch工具，该步骤不需要停止任何服务

[root@vrh1 ~]# su - grid
[grid@vrh1 ~]$ cd $CRS_HOME
[grid@vrh1 grid]$ mv OPatch OPatch_old

[grid@vrh1 grid]$ unzip /tmp/p6880880_112000_Linux-x86-64.zip -d $CRS_HOME

[grid@vrh1 grid]$ opatch
Invoking OPatch 11.2.0.1.3

Oracle Interim Patch Installer version 11.2.0.1.3
Copyright (c) 2010, Oracle Corporation.  All rights reserved.

2. 解压之前下载的 p12539000_112020_Linux-x86-64.zip 的补丁包，！！注意不要解压在/tmp目录下！！

[grid@vrh1 ~]$ mkdir /g01/patch

[grid@vrh1 ~]$ cd /g01/patch

[grid@vrh1 patch]$ unzip /tmp/p12539000_112020_Linux-x86-64.zip

Archive:  /tmp/p12539000_112020_Linux-x86-64.zip
   creating: 12539000/
   creating: 12539000/files/
   creating: 12539000/files/lib/
   creating: 12539000/files/lib/libserver11.a/
  inflating: 12539000/files/lib/libserver11.a/ksxp.o  
   creating: 12539000/etc/
   creating: 12539000/etc/config/
  inflating: 12539000/etc/config/inventory.xml  
  inflating: 12539000/etc/config/actions.xml  
  inflating: 12539000/etc/config/deploy.xml  
   creating: 12539000/etc/xml/
  inflating: 12539000/etc/xml/GenericActions.xml  
  inflating: 12539000/etc/xml/ShiphomeDirectoryStructure.xml 

3. 以root用户执行# opatch auto <UNZIPPED_PATCH_LOCATION> 命令

[root@vrh1 ~]# /g01/11.2.0/grid/OPatch/opatch auto /g01/patch -oh $CRS_HOME

Executing /usr/bin/perl /g01/11.2.0/grid/OPatch/crs/patch112.pl -patchdir /g01 -patchn patch -oh
/g01/11.2.0/grid -paramfile /g01/11.2.0/grid/crs/install/crsconfig_params
2011-09-24 22:34:41: Parsing the host name
2011-09-24 22:34:41: Checking for super user privileges
2011-09-24 22:34:41: User has super user privileges
Using configuration parameter file: /g01/11.2.0/grid/crs/install/crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'vrh1'
CRS-2673: Attempting to stop 'ora.crsd' on 'vrh1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'vrh1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'vrh1'
................................
Backing up files affected by the patch 'NApply' for restore. This might take a while...

Applying patch 12539000...

ApplySession applying interim patch '12539000' to OH '/g01/11.2.0/grid'
Backing up files affected by the patch '12539000' for rollback. This might take a while...

Patching component oracle.rdbms, 11.2.0.2.0...
Updating archive file "/g01/11.2.0/grid/lib/libserver11.a"  with "lib/libserver11.a/ksxp.o"
ApplySession adding interim patch '12539000' to inventory

Verifying the update...
Inventory check OK: Patch ID 12539000 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 12539000 are present in Oracle Home.
Running make for target ioracle

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.
CRS-4123: Oracle High Availability Services has been started.

4. 在所有节点上重复以上步骤，并确认补丁状态

[root@vrh1 ~]# su - grid

[grid@vrh1 ~]$ opatch lsinventory
Interim patches (1) :

Patch  12539000     : applied on Sat Sep 24 22:36:35 CST 2011
Unique Patch ID:  13976979
   Created on 28 Jul 2011, 12:37:42 hrs PST8PDT
   Bugs fixed:
     12539000

如果没有安装以上12539000补丁，在使用OUI升级GI/CRS时会出现以下 Warning:

升级11.2.0.2 GI/CRS到11.2.0.3

1.解压软件包，第三个zip包为grid软件

[grid@vrh1 tmp]$ unzip p10404530_112030_Linux-x86-64_3of7.zip

2. 以GI拥有者用户启动GI/CRS的OUI安装界面，并选择Out of Place的安装目录

(grid)$ unset ORACLE_HOME ORACLE_BASE ORACLE_SID
(grid)$ export DISPLAY=:0
(grid)$ cd /tmp/grid
(grid)$ ./runInstaller
Starting Oracle Universal Installer…

3. 依次在所有节点上以root用户运行rootupgrade.sh升级脚本

su - root 

First Node [root@vrh1 ~]# /g01/11.2.0.3/grid/rootupgrade.sh

Performing root user operation for Oracle 11g 

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /g01/11.2.0.3/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /g01/11.2.0.3/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation

ASM upgrade has started on first node.

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'vrh1'
CRS-2673: Attempting to stop 'ora.crsd' on 'vrh1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'vrh1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'vrh1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'vrh1'
CRS-2673: Attempting to stop 'ora.oc4j' on 'vrh1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'vrh1'
CRS-2673: Attempting to stop 'ora.MACLEAN.dg' on 'vrh1'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'vrh1'
CRS-2673: Attempting to stop 'ora.SYSTEMDG.dg' on 'vrh1'
CRS-2673: Attempting to stop 'ora.cvu' on 'vrh1'
CRS-2677: Stop of 'ora.cvu' on 'vrh1' succeeded
CRS-2672: Attempting to start 'ora.cvu' on 'vrh2'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'vrh1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.vrh1.vip' on 'vrh1'
CRS-2677: Stop of 'ora.scan1.vip' on 'vrh1' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'vrh2'
CRS-2677: Stop of 'ora.vrh1.vip' on 'vrh1' succeeded
CRS-2672: Attempting to start 'ora.vrh1.vip' on 'vrh2'
CRS-2677: Stop of 'ora.registry.acfs' on 'vrh1' succeeded
CRS-2676: Start of 'ora.scan1.vip' on 'vrh2' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'vrh2'
CRS-2676: Start of 'ora.cvu' on 'vrh2' succeeded
CRS-2676: Start of 'ora.vrh1.vip' on 'vrh2' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'vrh2' succeeded
CRS-2677: Stop of 'ora.MACLEAN.dg' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'vrh1' succeeded
CRS-2672: Attempting to start 'ora.oc4j' on 'vrh2'
CRS-2676: Start of 'ora.oc4j' on 'vrh2' succeeded
CRS-2677: Stop of 'ora.SYSTEMDG.dg' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'vrh1'
CRS-2677: Stop of 'ora.asm' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'vrh1'
CRS-2677: Stop of 'ora.ons' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'vrh1'
CRS-2677: Stop of 'ora.net1.network' on 'vrh1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'vrh1' has completed
CRS-2677: Stop of 'ora.crsd' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'vrh1'
CRS-2673: Attempting to stop 'ora.evmd' on 'vrh1'
CRS-2673: Attempting to stop 'ora.asm' on 'vrh1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'vrh1'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'vrh1'
CRS-2677: Stop of 'ora.asm' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'vrh1'
CRS-2677: Stop of 'ora.evmd' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'vrh1'
CRS-2677: Stop of 'ora.cssd' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'vrh1'
CRS-2673: Attempting to stop 'ora.gipcd' on 'vrh1'
CRS-2677: Stop of 'ora.gipcd' on 'vrh1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'vrh1'
CRS-2677: Stop of 'ora.gpnpd' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'vrh1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'vrh1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
OLR initialization - successful
Replacing Clusterware entries in inittab
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 11g Release 2.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Last Node 

[root@vrh2 ~]# /g01/11.2.0.3/grid/rootupgrade.sh

Performing root user operation for Oracle 11g 

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /g01/11.2.0.3/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /g01/11.2.0.3/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'vrh2'
CRS-2673: Attempting to stop 'ora.crsd' on 'vrh2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'vrh2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'vrh2'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'vrh2'
CRS-2673: Attempting to stop 'ora.SYSTEMDG.dg' on 'vrh2'
CRS-2673: Attempting to stop 'ora.oc4j' on 'vrh2'
CRS-2673: Attempting to stop 'ora.cvu' on 'vrh2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'vrh2'
CRS-2677: Stop of 'ora.cvu' on 'vrh2' succeeded
CRS-2672: Attempting to start 'ora.cvu' on 'vrh1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'vrh2' succeeded
CRS-2673: Attempting to stop 'ora.vrh2.vip' on 'vrh2'
CRS-2677: Stop of 'ora.vrh2.vip' on 'vrh2' succeeded
CRS-2672: Attempting to start 'ora.vrh2.vip' on 'vrh1'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'vrh2' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'vrh2'
CRS-2677: Stop of 'ora.scan1.vip' on 'vrh2' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'vrh1'
CRS-2676: Start of 'ora.cvu' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'vrh2' succeeded
CRS-2676: Start of 'ora.vrh2.vip' on 'vrh1' succeeded
CRS-2676: Start of 'ora.scan1.vip' on 'vrh1' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'vrh1'
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'vrh2' succeeded
CRS-2672: Attempting to start 'ora.oc4j' on 'vrh1'
CRS-2676: Start of 'ora.oc4j' on 'vrh1' succeeded
CRS-2677: Stop of 'ora.SYSTEMDG.dg' on 'vrh2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'vrh2'
CRS-2677: Stop of 'ora.asm' on 'vrh2' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'vrh2'
CRS-2677: Stop of 'ora.ons' on 'vrh2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'vrh2'
CRS-2677: Stop of 'ora.net1.network' on 'vrh2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'vrh2' has completed
CRS-2677: Stop of 'ora.crsd' on 'vrh2' succeeded
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'vrh2'
CRS-2673: Attempting to stop 'ora.ctssd' on 'vrh2'
CRS-2673: Attempting to stop 'ora.evmd' on 'vrh2'
CRS-2673: Attempting to stop 'ora.asm' on 'vrh2'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'vrh2'
CRS-2677: Stop of 'ora.asm' on 'vrh2' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'vrh2'
CRS-2677: Stop of 'ora.evmd' on 'vrh2' succeeded
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'vrh2' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'vrh2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'vrh2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'vrh2'
CRS-2677: Stop of 'ora.cssd' on 'vrh2' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'vrh2'
CRS-2673: Attempting to stop 'ora.gipcd' on 'vrh2'
CRS-2677: Stop of 'ora.gipcd' on 'vrh2' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'vrh2'
CRS-2677: Stop of 'ora.diskmon' on 'vrh2' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'vrh2' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'vrh2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'vrh2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
OLR initialization - successful
Replacing Clusterware entries in inittab
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 11g Release 2.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Started to upgrade the Oracle Clusterware. This operation may take a few minutes.
Started to upgrade the CSS.
Started to upgrade the CRS.
The CRS was successfully upgraded.
Oracle Clusterware operating version was successfully set to 11.2.0.3.0

ASM upgrade has finished on last node.

PRKO-2116 : OC4J is already enabled
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

4. 确认GI/CRS成功升级到11.2.0.3 :

[grid@vrh2 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.3.0]

升级11.2.0.2 RDBMS/DB到 11.2.0.3

1. 解压RDBMS/DB 相关的第1-2个 zip包：

[root@vrh1 ~]# su - oracle
[oracle@vrh1 tmp]$ mkdir /s01/patch
[oracle@vrh1 tmp]$ cd /s01/patch

[oracle@vrh1 patch]$ unzip /tmp/p10404530_112030_Linux-x86-64_1of7.zip
[oracle@vrh1 patch]$ unzip /tmp/p10404530_112030_Linux-x86-64_2of7.zip

2.
因为11.2.0.2的Patchset以后都是out of place的，所以我们可以不用像在11gr2以前那样必须在原有安装低版本软件的基础上才能升级软件，而可以选择在别的位置完全新安装。

注意该步骤不需要停止数据库实例，可以在前期工作中完成。

以DB/RDBMS数据库软件的拥有者身份(oracle用户)启动方才解压目录下的oui安装界面:
su – oracle

(oracle)$ unset ORACLE_HOME ORACLE_BASE ORACLE_SID
(oracle)$ export DISPLAY=:0
(oracle)$ cd $PATCHHOME
(oracle)$ ./runInstaller

依次在所有节点上执行root.sh脚本

/s01/orabase/product/11.2.0/dbhome_3/root.sh

3. 使用DBUA静默模式升级RAC数据库的数据字典

su - oracle
[oracle@vrh1 ~]$ export ORACLE_HOME=/s01/orabase/product/11.2.0/dbhome_3

/*  这里的SID指定数据库名 */

[oracle@vrh1 ~]$ $ORACLE_HOME/bin/dbua -silent -sid VPROD

Log files for the upgrade operation are located at: /s01/orabase/cfgtoollogs/dbua/VPROD/upgrade2
Performing Pre Upgrade
1% complete
7% complete
Upgrading Oracle Server
9% complete
10% complete
12% complete
13% complete
15% complete
16% complete
18% complete
20% complete
21% complete
23% complete
24% complete
26% complete
27% complete
29% complete
30% complete
32% complete
33% complete
35% complete
36% complete
Upgrading Real Application Clusters
38% complete
Upgrading Oracle Workspace Manager
40% complete
41% complete
43% complete
44% complete
Performing Post Upgrade
46% complete
84% complete
85% complete
86% complete
92% complete
Generating Summary
Database upgrade has been completed successfully, and the database is ready to use.
100% complete
Check the log file "/s01/orabase/cfgtoollogs/dbua/logs/silent1.log" for upgrade details.

4.更新所有节点上.bash_profile 中的ORACLE_HOME等变量

5.执行过DBUA升级工具的节点上的orapw$SID密码文件已被更新，将该文件传播到其他节点上

6.确认数据字典升级成功，并重启所有实例

SQL> col comp_name for a40
SQL> col version for a20
SQL> set linesize 140 pagesize 1200
SQL> select comp_name,version from dba_server_registry;

COMP_NAME                                VERSION
---------------------------------------- --------------------
Oracle Workspace Manager                 11.2.0.3.0
Oracle Database Catalog Views            11.2.0.3.0
Oracle Database Packages and Types       11.2.0.3.0
Oracle Real Application Clusters         11.2.0.3.0

SQL> select * from v$version;

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

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------
www.askmac.cn & www.askmac.cn

[oracle@vrh1 dbs]$ opatch lsinventory
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.

Oracle Home       : /s01/orabase/product/11.2.0/dbhome_3
Central Inventory : /g01/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /s01/orabase/product/11.2.0/dbhome_3/cfgtoollogs/opatch/opatch2011-09-25_00-18-57AM.log

Lsinventory Output file location : /s01/orabase/product/11.2.0/dbhome_3/cfgtoollogs/opatch
/lsinv/lsinventory2011-09-25_00-18-57AM.txt

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

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

There are no Interim patches installed in this Oracle Home.

Rac system comprising of multiple nodes
  Local node = vrh1
  Remote node = vrh2

[oracle@vrh1 dbs]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 25 00:19:14 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown immediate;

SQL> startup ;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2227944 bytes
Variable Size             402653464 bytes
Database Buffers          838860800 bytes
Redo Buffers                8921088 bytes
Database mounted.
Database opened.

11.2.0.3上optimizer_features_enable造成的一些变化

我们知道几乎每个Patchset都会引入Oracle Optimizer优化器的一些微妙变化，升级到11.2.0.3后默认的optimizer_features_enable(OFE)为11.2.0.3，我们来了解一下这与11.2.0.2时有哪些区别：

SQL> col PARAMETER for a30
SQL> col "11.2.0.3" for a20
SQL> col  "11.2.0.2" for a20
SQL> col DESCRIB for a50
SQL> set linesize 200 

SQL> select V11203.NAME    Parameter,
  2         V11203.VALUE   "11.2.0.3",
  3         V11202.VALUE   "11.2.0.2",
  4         V11203.describ
  5    from ofe_11203 V11203, ofe_11202 V11202
  6   where V11203.NAME = V11202.NAME
  7     and V11203.VALUE != V11202.VALUE;

PARAMETER                      11.2.0.3             11.2.0.2             DESCRIB
------------------------------ -------------------- -------------------- --------------------------------------------------
_fastpin_enable                241174785            404585473            enable reference count based fast pins
_db_flash_cache_keep_limit     241098320            404509008            Flash cache keep buffer upper limit in percentage
optimizer_features_enable      11.2.0.3             11.2.0.2             optimizer plan compatibility parameter
_optimizer_undo_cost_change    11.2.0.3             11.2.0.2             optimizer undo cost change

从Oracle 10.1.0.3开始引入了利用逻辑备库(logical standby)实施滚动升级(rolling upgrade)的特性；在滚动升级期间(rolling upgrade)，允许主库(primary database)与逻辑备库(logical standby)间运行不同的数据库版本，以最小化宕机时间。

到了11g中增加了可以将物理备库(physical standby)临时性转换成逻辑备库(logical standby)以完成滚动升级，之后将该临时逻辑备库反转为物理备库的功能。使用以上临时转换功能只需要在转换语句”ALTER DATABASE RECOVER TO LOGICAL STANDBY”后加上”KEEP IDENTITY”选项。除去转换细节的区别外，使用物理备库进行滚动升级的过程与10g中的逻辑备库滚动升级没有太大的区别。

这里我们通过实验来亲身体验一下这一新特性，我们假设环境中存在一套11.1.0.7的Data Guard系统：

Database Role	DB_UNIQUE_NAME	Version
Primary Database	PROD	11.1.0.7
Physical Standby	SBDB2	11.1.0.7

当前的需求是将生产数据库升级到11.2.0.2，但要求最小化应用downtime；这里我们就可以充分利用Data Guard的环境借势升级，同时也不会破坏原有的HA可用性：

1.滚动升级前需要为Primary Database做必要的准备工作，这里如果数据库之前没有启用闪回数据库的话需要enable

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK ON;

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> ALTER DATABASE OPEN;

/* 同时创建保证还原点以保证升级失败时可以回退 */

SQL> create restore point pre_rolling_upgrd guarantee flashback database;

2.将物理备库临时转换为逻辑备库，不同于普通的转换这里要用KEEP IDENTITY

SQL> shutdown immediate;
SQL> startup mount;

之后需要在Primary Database中创建Logminer dictionary日志挖掘字典：
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

以上步骤完成后可以开始将physical standby转换为logical standby了：
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;

SQL> alter database open;

以下为转换期间的日志:
ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY
Media Recovery Start: Managed Standby Recovery (SBDB2)
Fast Parallel Media Recovery enabled
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
Media Recovery Waiting for thread 1 sequence 184
2011-03-25 19:36:51.012000 +08:00
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
kcrrvslf: active RFS archival for log 8 thread 1 sequence 188
RFS[3]: Successfully opened standby log 7: '/standby/oradata/SBDB2/onlinelog/o1_mf_7_6qf9b6cw_.log'
2011-03-25 19:40:43.074000 +08:00
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[3]: Successfully opened standby log 8: '/standby/oradata/SBDB2/onlinelog/o1_mf_8_6qf9b8j4_.log'
Media Recovery Log /standby/flash_recovery_area/SBDB2/archivelog/2011_03_25/o1_mf_1_188_6rrzsqvm_.arc
2011-03-25 19:40:45.459000 +08:00
Media Recovery Log /standby/flash_recovery_area/SBDB2/archivelog/2011_03_25/o1_mf_1_189_6rrzsv10_.arc
Incomplete Recovery applied until change 11881985 time 03/25/2011 19:40:41
Media Recovery Complete (SBDB2)
tkcrrxms: Killing 140733193388036 processes (all RFS)
2011-03-25 19:40:48.011000 +08:00
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 11881985
Resetting resetlogs activation ID 157033137 (0x95c22b1)
Online log /standby/oradata/SBDB2/onlinelog/o1_mf_1_6qf99xt6_.log: Thread 1 Group 1 was previously cleared
Online log /standby/flash_recovery_area/SBDB2/onlinelog/o1_mf_1_6qf99y1p_.log: Thread 1 Group 1 was previously cleared
Online log /standby/oradata/SBDB2/onlinelog/o1_mf_2_6qf9b01v_.log: Thread 1 Group 2 was previously cleared
Online log /standby/flash_recovery_area/SBDB2/onlinelog/o1_mf_2_6qf9b06y_.log: Thread 1 Group 2 was previously cleared
Online log /standby/oradata/SBDB2/onlinelog/o1_mf_3_6qf9b21o_.log: Thread 1 Group 3 was previously cleared
Online log /standby/flash_recovery_area/SBDB2/onlinelog/o1_mf_3_6qf9b268_.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 11881983
Setting recovery target incarnation to 4
Converting standby mount to primary mount.
ACTIVATE STANDBY: Complete - Database mounted as primary (SBDB2)
Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY

我们还需要完成一系列逻辑备库的配置:

a.禁止在逻辑备库端删除外籍日志 

SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');
BEGIN DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE'); END;

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOGSTDBY.APPLY_SET' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

/* 缺少DBMS_LOGSTDBY包，可以从以下脚本创建 */

SQL> @?/rdbms/admin/dbmslsby.sql

SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');
PL/SQL procedure successfully completed.

b.执行以下语句可以将逻辑备库不支持的而又在主库上运行过的事务记录到DBA_LOGSTDBY_EVENTS中

SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED',DBMS_LOGSTDBY.MAX_EVENTS);
PL/SQL procedure successfully completed.

c.启动在逻辑备库上的SQL APPLY:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.

3.在步骤3中我们实际实施对逻辑备库的升级操作

1).在逻辑备库上停止SQL APPLY 

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

2).升级逻辑备库(upgrade logical standby)

SQL> shutdown immediate;

[maclean@rh6 admin]$ cp $ORACLE_HOME/dbs/spfileSBDB2.ora /s01/product/11.2.0/dbhome_2/dbs
[maclean@rh6 dbs]$ orapwd file=orapwSBDB2 password=XXXXX entries=10 

/* 将原SBDB2实例的spfile复制到新的11.2.0.2版本的ORACLE_HOME下，并在新的ORACLE_HOME/dbs目录下创建
   与主库一致的密码文件 */

[maclean@rh6 ~]$ source 11gr2env.sh

[maclean@rh6 ~]$ export ORACLE_SID=SBDB2

SQL> startup upgrade;

SQL> @?/rdbms/admin/catupgrd
..................
/* 确认升级数据字典成功，值得一提的是11g的数据字典升级工作极度复杂，因此该阶段耗费大量时间 */

3)在逻辑备库上启用SQL APPLY

SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.

SQL> SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;
SYSDATE 	   APPLIED_TIME
------------------ ------------------
25-MAR-11 21:26:26 25-MAR-11 21:26:21

/* 监控以上SQL APPLY进度，是否与当前时间接近 */

4)审查升级过程中的监控事件，可以直接查询DBA_LOGSTDBY_EVENTS视图，
该视图记录了在逻辑备库上没有applied的DDL与DML操作

SQL> SET LONG 1000
SQL> SET PAGESIZE 180
SQL> SET LINESIZE 79
SQL> SELECT EVENT_TIMESTAMP, EVENT, STATUS FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIMESTAMP;

EVENT_TIMESTAMP
---------------------------------------------------------------------------
EVENT
-------------------------------------------------------------------------------
STATUS
-------------------------------------------------------------------------------
25-MAR-11 07.53.39.593831 PM

ORA-16111: log mining and apply setting up

25-MAR-11 07.53.39.594478 PM

Apply LWM 11881984, HWM 11881984, SCN 11881984

25-MAR-11 07.57.54.467786 PM

Shutdown acknowledged

25-MAR-11 07.57.57.516431 PM

ORA-16128: User initiated stop apply successfully completed

25-MAR-11 09.06.42.150573 PM

ORA-16111: log mining and apply setting up

25-MAR-11 09.06.42.345581 PM

Apply LWM 11883610, HWM 11883615, SCN 11883614

25-MAR-11 09.12.56.429392 PM

ORA-16128: User initiated stop apply successfully completed

25-MAR-11 09.26.16.124581 PM

ORA-16111: log mining and apply setting up

25-MAR-11 09.26.16.145925 PM

Apply LWM 11883610, HWM 11883615, SCN 11883614

25-MAR-11 09.26.20.723913 PM
ALTER DATABASE OPEN
ORA-16226: DDL skipped due to lack of support

ORA-16226错误表示相关DDL操作在逻辑备库上不受支持，常见的原因是该类操作涉及内部对象
ORA-16129错误表示相关DML操作在逻辑备库上不受支持

5)开始主备库之间的切换(switchover)

ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

以上切换完成后当前主库(Primary Database)的日志将无法再传输到逻辑备库(PROD)中，当前主库SBDB的版本为11.2.0.2，
而逻辑备库RPOD(原主库)仍为11.1.0.7；因为当前主库的日志都无法传送到低版本的logical standby中，这意味着当前主库是不受
任何保护的状态，这是滚动升级中无法避免的一个真空期。
现在我们可以在原逻辑备库上启动用户应用程序了。

4.将原主库(PROD)闪回到之前创建的还原点上
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
PRE_ROLLING_UPGRD

SQL> flashback database to restore point pre_rolling_upgrd;

5.以新的ORACLE_HOME binary加载原主库(PROD)实例
SQL> shutdown immediate;
/* 先将原主库(PROD)实例关闭  */

[maclean@rh6 dbs]$ cp /standby/product/11.1.0/db_3/dbs/spfilePROD.ora $ORACLE_HOME/dbs
[maclean@rh6 dbs]$ orapwd file=orapwPROD password=XXXXX entries=10 

/* 在新版本的ORACLE_HOME下复制spfile并重建密码文件 */

[maclean@rh6 dbs]$ export ORACLE_SID=PROD

SQL> startup mount;

/* 这里我们不需要运行升级脚本，后面的redo apply会自动升级PROD上的数据字典 */

6.将原主库(PROD)切换会物理备库(physical standby)
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;

7.启动原主库(PROD)当前物理备库上的介质恢复进程
SQL> STARTUP MOUNT;
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT FROM SESSION;

8.在主库与物理备库间实施切换

SBDB2 switchover回物理备库状态
SQL>  alter database commit to switchover to physical standby;

PROD切换为Primary Database
SQL> alter database commit to switchover to primary;

因为数据字典升级期间产生了大量的重做日志，切换之前需要完成对这些redo的应用，
所以该阶段将耗费大量时间，可以从PROD的告警日志中看到类似下面的内容:

Media Recovery Log /standby/arch/1_51_746739648.dbf
2011-03-26 00:32:09.625000 +08:00
Media Recovery Log /standby/arch/1_52_746739648.dbf
2011-03-26 00:32:16.931000 +08:00
Media Recovery Log /standby/arch/1_53_746739648.dbf
2011-03-26 00:32:23.149000 +08:00
Media Recovery Log /standby/arch/1_54_746739648.dbf
2011-03-26 00:32:31.732000 +08:00
Media Recovery Log /standby/arch/1_55_746739648.dbf
2011-03-26 00:32:43.807000 +08:00
Media Recovery Log /standby/arch/1_56_746739648.dbf

9.出于节约磁盘空间的考虑将PROD上最初建立的还原点删除：
SQL> DROP RESTORE POINT PRE_ROLLING_UPGRD

快速升级Oracle 11.2.0.2 RAC到11.2.0.3

11g新特性:Rolling Upgrade With Physical Standby