环境:RHEL6.5 + Oracle 11.2.0.4 DataGuard physical standby 主库和备库都是单节点。 需求:主备库同时应用160719的PSU和OJVM PSU补丁。
先参考MOS文档 ID 278641.1 的解决方案如下:
SOLUTION Process Overview:
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.
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.
提炼核心步骤就是:
主库;
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;
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
lsnrctl stop SQL> shutdown immediate;
1).OPatch更新 2).应用PSU补丁 执行数据库脚本 3).应用OJVM PSU补丁 执行数据库脚本
最后确认正常启动主库数据库和监听, 然后重新开启日志传送到备库 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;
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;
$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补丁。