ORACLE数据库DG管理-更新psu和jvm补丁

（1）更新psu和jvm补丁

• PSU顺序

• 在主库上停止传送日志到备库。

• SQL> select sequence#,applied from v$archived_log where dest_id=2;    SEQUENCE# APPLIED ---------- ---------          9 YES         30 YES         31 YES         32 YES         33 YES         34 YES         35 YES         36 YES         37 YES         38 YES         39 YES    SEQUENCE# APPLIED ---------- ---------         40 YES         41 YES         42 YES         43 YES         44 YES         45 YES         46 YES         47 YES         48 YES         49 NO 21 rows selected.   SQL> archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /u01/arch Oldest online log sequence     48 Next log sequence to archive   50 Current log sequence           50     SQL> recover managed standby database cancel; Media recovery complete. SQL> recover managed standby database using current logfile disconnect from session Media recovery complete. SQL> SQL> select sequence#,applied from v$archived_log where dest_id=2;    SEQUENCE# APPLIED ---------- ---------          9 YES         30 YES         31 YES         32 YES         33 YES         34 YES         35 YES         36 YES         37 YES         38 YES         39 YES    SEQUENCE# APPLIED ---------- ---------         40 YES         41 YES         42 YES         43 YES         44 YES         45 YES         46 YES         47 YES         48 YES         49 YES   21 rows selected.   SQL> archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /u01/arch Oldest online log sequence     48 Next log sequence to archive   50 Current log sequence           50 SQL>   SQL> alter system set log_archive_dest_state_2=defer; SQL> archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /u01/arch Oldest online log sequence     49 Next log sequence to archive   51 Current log sequence           51 SQL>  select sequence#,applied from v$archived_log where dest_id=2;    SEQUENCE# APPLIED ---------- ---------          9 YES         30 YES         31 YES         32 YES         33 YES         34 YES         35 YES         36 YES         37 YES         38 YES         39 YES    SEQUENCE# APPLIED ---------- ---------         40 YES         41 YES         42 YES         43 YES         44 YES         45 YES         46 YES         47 YES         48 YES         49 YES         50 NO   22 rows selected.   SQL>

   

  2. 关闭备库，按照README安装补丁集到RDBMS 软件。这包括Patchset/Patchset Update(PSU)/Critical Patch Update

  (CPU)。对备库RDBMS本身您将无法也不需要运行脚本（catpatch.sql等）。启动备库到mount状态，不要启动日志应用服

  务。

  SQL> recover managed standby database cancel; Media recovery complete. SQL> shutdown immediate; ORA-01109: database not open     Database dismounted. ORACLE instance shut down. SQL> [oracle@pri arch]$ lsnrctl stop   LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUN-2019 22:11:42   Copyright (c) 1991, 2013, Oracle.  All rights reserved.   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pri)(PORT=1521))) The command completed successfully   [root@pri oracle]# chown oracle:oinstall p* [oracle@pri ~]$ unzip p6880880_112000_Linux-x86-64.zip [oracle@pri db_1]$ mv OPatch OPatchbak [oracle@pri db_1]$ mv /home/oracle/OPatch ./ [oracle@pri ~]$ opatch version OPatch Version: 11.2.0.3.21 [oracle@pri ~]$ unzip p29141056_112040_Linux-x86-64.zip [oracle@pri ~]$ cd 29141056/ [oracle@pri 29141056]$ opatch apply Oracle Interim Patch Installer version 11.2.0.3.21 Copyright (c) 2019, Oracle Corporation.  All rights reserved.     Oracle Home       : /u01/app/oracle/product/11.2/db_1 Central Inventory : /u01/app/oraInventory    from           : /u01/app/oracle/product/11.2/db_1/oraInst.loc OPatch version    : 11.2.0.3.21 OUI version       : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2/db_1/cfgtoollogs/opatch/opatch2019-06-20_22-16-29PM_1.log   Verifying environment and performing prerequisite checks... OPatch continues with these patches:   17478514  18031668  18522509  19121551  19769489  20299013  20760982  21352635  21948347  22502456  23054359  24006111  24732075  25869727  26609445  26392168  26925576  27338049  27734982  28204707  28729262  29141056    Do you want to proceed? [y|n] Provide your email address to be informed of security issues, install and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name:   You have not provided an email address for notification of security issues. Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  y       Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/app/oracle/product/11.2/db_1')     Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying sub-patch '17478514' to OH '/u01/app/oracle/product/11.2/db_1'   Patching component oracle.rdbms, 11.2.0.4.0...     [oracle@pri 29141056]$ lsnrctl start   LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUN-2019 22:20:08   Copyright (c) 1991, 2013, Oracle.  All rights reserved.   Starting /u01/app/oracle/product/11.2/db_1/bin/tnslsnr: please wait...   TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/pri/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pri)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pri)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date                20-JUN-2019 22:20:08 Uptime                    0 days 0 hr. 0 min. 20 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/diag/tnslsnr/pri/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pri)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "pri" has 1 instance(s).   Instance "pri", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@pri 29141056]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 20 22:20:37 2019   Copyright (c) 1982, 2013, Oracle.  All rights reserved.   Connected to an idle instance.   SQL> startup mount; ORACLE instance started.   Total System Global Area  534462464 bytes Fixed Size                  2254952 bytes Variable Size             419432344 bytes Database Buffers          109051904 bytes Redo Buffers                3723264 bytes Database mounted. SQL>

   

  3. 关闭主库，按照README安装Patchset/PSU/CPU到RDBMS软件和RDBMS本身（运行catpatch/catbundle/catcpu

  等）。

  注意: Oracle11gR2的（11.2.0）的最新补丁集需要被安装到一个新的ORACLE_HOME。所以记得重置您的环境变量并复制

  相应的文件（SPFILE，网络文件等..）到新的ORACLE_HOME。详细信息参看数据库升级指南。

  [oracle@std ~]$ lsnrctl stop   LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUN-2019 22:21:42   Copyright (c) 1991, 2013, Oracle.  All rights reserved.   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=std)(PORT=1521))) The command completed successfully [oracle@std ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 20 22:21:48 2019   Copyright (c) 1982, 2013, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> [oracle@std 29141056]$ opatch apply Oracle Interim Patch Installer version 11.2.0.3.21 Copyright (c) 2019, Oracle Corporation.  All rights reserved.     Oracle Home       : /u01/app/oracle/product/11.2/db_1 Central Inventory : /u01/app/oraInventory    from           : /u01/app/oracle/product/11.2/db_1/oraInst.loc OPatch version    : 11.2.0.3.21 OUI version       : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2/db_1/cfgtoollogs/opatch/opatch2019-06-20_22-23-53PM_1.log   Verifying environment and performing prerequisite checks...

  4. 启动主库，重新启用日志传送到备库。

  [oracle@std 29141056]$ lsnrctl start   LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUN-2019 22:27:56   Copyright (c) 1991, 2013, Oracle.  All rights reserved.   Starting /u01/app/oracle/product/11.2/db_1/bin/tnslsnr: please wait...   TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/std/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=std)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=std)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date                20-JUN-2019 22:27:56 Uptime                    0 days 0 hr. 0 min. 20 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/diag/tnslsnr/std/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=std)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "std" has 1 instance(s).   Instance "std", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@std 29141056]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 20 22:28:54 2019   Copyright (c) 1982, 2013, Oracle.  All rights reserved.   Connected to an idle instance.   SQL> startup ORACLE instance started.   Total System Global Area  534462464 bytes Fixed Size                  2254952 bytes Variable Size             402655128 bytes Database Buffers          125829120 bytes Redo Buffers                3723264 bytes Database mounted. Database opened. SQL>@?/rdbms/admin/catbundle.sql psu apply SQL> alter system set log_archive_dest_state_2=enable;   System altered.   SQL> archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /u01/arch Oldest online log sequence     51 Next log sequence to archive   52 Current log sequence           53 SQL>

   

  5. 在备库启动日志应用服务，主库中的RDBMS变更（catpatch/ catbundle/catcpu 脚本）通过重做日志被应用到备库。

  [oracle@pri ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 20 22:32:54 2019   Copyright (c) 1982, 2013, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> select process,status from v$managed_standby;   PROCESS   STATUS --------- ------------ ARCH      CLOSING ARCH      CONNECTED ARCH      CONNECTED ARCH      CLOSING RFS       IDLE RFS       IDLE RFS       IDLE   7 rows selected.   SQL> recover managed standby database using current logfile disconnect from session ; Media recovery complete. SQL> SQL> select process,status from v$managed_standby;   PROCESS   STATUS --------- ------------ ARCH      CLOSING ARCH      CONNECTED ARCH      CONNECTED ARCH      CLOSING RFS       IDLE RFS       IDLE RFS       IDLE MRP0      APPLYING_LOG   8 rows selected.

   

  注意: 备库上的数据库软件升级后应立即执行步骤5。这是为了确保数据字典（CATPROC）版本与数据库软件的版本匹配。

  如果不匹配（例如，您首先升级了备库软件，升级主库前在备库中执行了角色转换（switchover）），你可能会遇到严重的

  问题。在Data Guard物理备库环境中不支持不同的补丁级别，详细的信息请参看

  Document 785347.1 Mixed Oracle Version support with Data Guard Redo Transport Services

  6. 执行检查，以确保补丁已成功安装在主库和备库。

  opatch lsinventory  -bugs_fixed|grep "MOLECULE" 执行完后我们可以查到PSU更新信息： set line 150 col ACTION_TIME for a30 col ACTION for a8 col NAMESPACE for a8 col VERSION for a10 col BUNDLE_SERIES for a5 col COMMENTS for a20 select * from dba_registry_history; $ opatch lsinventory $opatch lsinventory –bugs_fixed 主 SQL> set line 150 SQL> col ACTION_TIME for a30 SQL> col ACTION for a8 SQL> col NAMESPACE for a8 SQL> col VERSION for a10 SQL> col BUNDLE_SERIES for a5 SQL> col COMMENTS for a20 SQL> select * from dba_registry_history;   ACTION_TIME                    ACTION   NAMESPAC VERSION            ID BUNDL COMMENTS ------------------------------ -------- -------- ---------- ---------- ----- -------------------- 24-AUG-13 12.03.45.119862 PM   APPLY    SERVER   11.2.0.4            0 PSU   Patchset 11.2.0.2.0 19-JUN-19 08.06.28.480376 PM   APPLY    SERVER   11.2.0.4            0 PSU   Patchset 11.2.0.2.0 20-JUN-19 10.31.25.457297 PM   APPLY    SERVER   11.2.0.4       190416 PSU   PSU 11.2.0.4.190416   3 rows selected. 备 SQL> recover managed standby database cancel; Media recovery complete. SQL> alter database open read only   2  ;   Database altered.   SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete. SQL> set line 150 SQL> col ACTION_TIME for a30 SQL> col ACTION for a8 SQL> col NAMESPACE for a8 SQL> col VERSION for a10 SQL> col BUNDLE_SERIES for a5 SQL> col COMMENTS for a20 SQL> select * from dba_registry_history;   ACTION_TIME                    ACTION   NAMESPAC VERSION            ID BUNDL COMMENTS ------------------------------ -------- -------- ---------- ---------- ----- -------------------- 24-AUG-13 12.03.45.119862 PM   APPLY    SERVER   11.2.0.4            0 PSU   Patchset 11.2.0.2.0 19-JUN-19 08.06.28.480376 PM   APPLY    SERVER   11.2.0.4            0 PSU   Patchset 11.2.0.2.0 20-JUN-19 10.31.25.457297 PM   APPLY    SERVER   11.2.0.4       190416 PSU   PSU 11.2.0.4.190416   SQL>               JVM MRP0: Background Media Recovery terminated with error 10485 Errors in file /u01/app/oracle/diag/rdbms/pri/pri/trace/pri_mrp0_8690.trc: ORA-10485: Real-Time Query cannot be enabled while applying migration redo. Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 1221198 MRP0: Background Media Recovery process shutdown (pri) Fri Jun 21 00:43:17 2019 到mount下可以恢复成功