共计 5083 个字符，预计需要花费 13 分钟才能阅读完成。
环境：RHEL6.5 + Oracle 11.2.0.4 DataGuard physical standby
主库和备库都是单节点。
需求：主备库同时应用 160719 的 PSU 和 OJVM PSU 补丁。
先参考 MOS 文档 ID 278641.1 的解决方案如下：
SOLUTION
Process Overview:
In the primary disable log shipping to the standby site.
Shutdown the standby site and apply interim patchsets to the RDBMS binaries as per the README. This includes Patchset/Patchset Update(PSU)/Critical Patch Update (CPU). You will not be able to and do not need to run the scripts(catpatch.sql etc) against the standby rdbms itself. Start the standby site to mount only, do not restart managed recovery.
Shutdown the primary site, apply the Patchset/PSU/CPU patch to the RDBMS binaries and patch the RDBMS itself using the instructions in the README (run catpatch/catbundle/catcpu etc).
NOTE: The latest Patchsets for Oracle 11gR2 (11.2.0) require to be installed into a new ORACLE_HOME. So mind to reset your Environment and copy corresponding Files (like SPFILE, Network Files,..) to the new ORACLE_HOME, too. Follow the Database Upgrade Guide for further Details.
Start the primary site, re-enable log shipping to the standby.
At the standby site restart redo apply/managed recovery and the RDBMS changes implemented in the Primary Site through catpatch/catbundle/catcpu will also be applied to the standby.
NOTE: Step 5. should be done immediately after upgrading the Database Binaries on the Standby Database. It is to ensure the Data Dictionary (CATPROC)-Version matches the Version of the Database Binaries. If this does not match (eg. when you upgrade the Standby Database Binaries first and perform a Role Change on the Standby before you upgrade the Primary) you may run into severe Problems. Having different Patchlevels in a Data Guard Physical Standby Database Environment is not supported anyway, see
Mixed Oracle Version support with Data Guard Redo Transport Services (Doc ID 785347.1)
for further Details and Reference.
- Checks to perform to ensure the patch has been applied successfully at the primary and standby sites.
提炼核心步骤就是：
- 1. 主库禁用日志传送到备库
- 2. 关闭备库应用补丁，但不执行脚本(catpatch.sql 等)，启动到 mount，不启用日志恢复
- 3. 关闭主库应用补丁，执行脚本(run catpatch/catbundle/catcpu 等)
- 4. 启动主库，重新开启日志传送到备库
- 5. 备库开启日志恢复
- 6. 检查补丁应用情况
1. 主库禁用日志传送到备库
主库；show parameter log_archive_dest_state_X
alter system set log_archive_dest_state_X=defer scope=both sid='*';
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=ZHZYNEW2 ASYNC LGWR VA
LID_FOR=(ONLINE_LOGFILES,PRIMA
RY_ROLE) DB_UNIQUE_NAME=ZHZYNE
W2
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string enable所以我这里的环境执行：
SQL> alter system set log_archive_dest_state_2=defer scope=both;2. 关闭备库应用补丁，但不执行脚本(catpatch.sql 等)，启动到 mount，不启用日志恢复
lsnrctl stop
SQL> shutdown immediate;
1).OPatch 更新
2). 应用 PSU 补丁 但不执行数据库脚本
3). 应用 OJVM PSU 补丁 但不执行数据库脚本
NOTE: 上面步骤跟《Oracle 11.2.0.4 单实例打 PSU,OJVM PSU 补丁快速参考》区别不大，但是注意只是软件应用补丁，不要执行数据库脚本。
最后启动数据库到 mount，启动监听
SQL> startup mount;
lsnrctl start
3. 关闭主库应用补丁，执行脚本(run catpatch/catbundle/catcpu 等)
lsnrctl stop
SQL> shutdown immediate;
1).OPatch 更新
2). 应用 PSU 补丁 执行数据库脚本
3). 应用 OJVM PSU 补丁 执行数据库脚本
4. 启动主库，重新开启日志传送到备库
最后确认正常启动主库数据库和监听, 然后重新开启日志传送到备库
SQL> alter system set log_archive_dest_state_X=enable scope=both sid=’*’
这里环境就是：
SQL> alter system set log_archive_dest_state_2=enable scope=both;5. 备库开启日志恢复
mount 状态开启日志恢复：
SQL> startup mount;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect;最终可以切换为 ADG：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;6. 检查补丁应用情况
$ORACLE_HOME/OPatch/opatch lspatches
$ORACLE_HOME/OPatch/opatch lsinventory
SQL> select ACTION_TIME,ACTION,NAMESPACE,VERSION,BUNDLE_SERIES,ID from registry$history;
这里尤其注意第三项，备库只有在 ADG 模式下才可以查询。
主库补丁应用的查询结果：
SQL> r
1* select ACTION_TIME,ACTION,NAMESPACE,VERSION,BUNDLE_SERIES,ID from registry$history
ACTION_TIME ACTION NAMESPACE VERSION BUNDLE_SERIES ID
--------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------
24-AUG-13 12.03.45.119862 PM APPLY SERVER 11.2.0.4 PSU 0
08-JUL-16 02.50.00.349757 PM APPLY SERVER 11.2.0.4 PSU 0
22-SEP-16 03.48.39.295292 PM jvmpsu.sql SERVER 11.2.0.4.160719OJVMPSU 0
22-SEP-16 03.49.37.935537 PM APPLY SERVER 11.2.0.4 PSU 160719
22-SEP-16 03.51.55.999773 PM APPLY SERVER 11.2.0.4.160719OJVMPSU 0
22-SEP-16 03.51.56.010800 PM APPLY 23177551
6 rows selected.ADG 模式下，最终备库补丁应用的查询结果：
SQL> select ACTION_TIME,ACTION,NAMESPACE,VERSION,BUNDLE_SERIES,ID from registry$history;
ACTION_TIME ACTION NAMESPACE VERSION BUNDLE_SERIES ID
--------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------
24-AUG-13 12.03.45.119862 PM APPLY SERVER 11.2.0.4 PSU 0
08-JUL-16 02.50.00.349757 PM APPLY SERVER 11.2.0.4 PSU 0
22-SEP-16 03.48.39.295292 PM jvmpsu.sql SERVER 11.2.0.4.160719OJVMPSU 0
22-SEP-16 03.49.37.935537 PM APPLY SERVER 11.2.0.4 PSU 160719
22-SEP-16 03.51.55.999773 PM APPLY SERVER 11.2.0.4.160719OJVMPSU 0
22-SEP-16 03.51.56.010800 PM APPLY 23177551
6 rows selected.至此已确定主库备库都已经成功完成应用 PSU，OJVM PSU 补丁。
Reference
- How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration (文档 ID 278641.1)
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-09/135427.htm
