Release Schedule of Current Database Releases (Doc ID 742060.1)
从2018年开始，版本号重新的模式，使用3个字段格式( Year.Update.Revision)
| 名称 | 说明 |
|---|---|
| Update (Release Update) | 2017年7月引入补丁修复策略 |
| Revision (Release Update Revision) | 补丁集修订 |
| BP(Bundle Patch) 捆绑补丁 | Windows平台修复补丁包 |
| Final CPU | |
| On-Request | 通过SR发起请求提供补丁 |
| PSU (Patch Set Update) 补丁集更新 | 季度补丁，包含功能更新，安全修复累积补丁包 |
| SPU (Security Patch Update) 安全修补程序更新 | |
| Overlay SPU | |
| NA (Not Applicable) | |
| CPU (Critical Patch Update) | 关键安全更新，用以修复安全漏洞 |
| DBBP (Database Bundle Patch) | (Database Proactive Bundle Patch)是多个PSU的超集， 包含了GI PSU、DB PSU及EXPDATA和DBIM的相关修复。 dba_registry_sqlpatch视图查看版本信息 |
从计划于2018年发布的下一个数据库版本（最初指定为12.2.0.2）开始，每年将提供数据库产品的新功能版本，并且不再发布补丁集。
总结：RU注重安全和功能修补，而RUR主安全漏洞更新。
Oracle的季度更新包含针对客户最有可能遇到的错误的修复程序：
除每季度更新外，还将在同一季度发布发行版本更新修复（修复），以利用已知的修复来扩充更新，并包括最新的安全漏洞修复。
-- on windows 用 findstr /i 替换 grep -iopatch lsinventoryopatch lsinventory -bugs_fixed | egrep -i ‘PSU|DATABASE PATCH SET UPDATE‘-- For CRS (Cluster Ready Services) PSUs, enter the following commandopatch lsinventory -bugs_fixed | grep -i ‘TRACKING BUG‘ | grep -i ‘PSU‘-- For GI (Grid Infrastructure) PSUs, enter the following commandopatch lsinventory -bugs_fixed | grep -i ‘GRID INFRASTRUCTURE PATCH SET UPDATE‘-- For Enterprise Manager OMS PSUs, enter the following command:opatch lsinventory -bugs_fixed | grep -i ‘ENTERPRISE MANAGER‘ | grep -i ‘OMS‘-- For Enterprise Manager Agent PSUs, enter the following command:opatch lsinventory -bugs_fixed | grep -i ‘ENTERPRISE MANAGER‘ | grep -i ‘AGENT‘# 1. 可以依据需求选择下载补丁版本Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (文档 ID 2118136.2) # 最新发布补丁（good）Master Note for Database Proactive Patch Program (Doc ID 756671.1)# OPatch工具https://updates.oracle.com/download/6880880.html# 关键补丁更新和安全公告https://www.oracle.com/security-alerts/#CriticalPatchUpdates# 最近一次Critical Patch Update (CPU) Program Apr 2020 Patch Availability Document (PAD) (Doc ID 2633852.1)# PSU 补丁下载地址文档Patch Set Updates for Oracle Products (Doc ID 854428.1)OPatch
OCM（Oracle Configuration Manager）：响应文件
从OPatch 12.2.0.1.5和11.2.0.3.14版本开始之后，已经不需要-ocmrf选项，即（不再需要OCM）Document 2161861.1。
在历史版本，需要使用emocmrsp工具创建响应文件
su - grid$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/ocm.rspsu - grid[[ -f "${ORACLE_HOME}/OPatch/ocm/bin/emocmrsp" ]] && (sleep 3;echo -e ‘\n‘;sleep 3;echo -e ‘Y\n‘)|${ORACLE_HOME}/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/ocm.rsp在12.2.0.1.5 和 11.2.0.3.14 版本及以后版本增强了补丁处理的效率
当前版本
升级错误
处理
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/ocm.rspopatch apply -local -ocmrf /tmp/ocm.rsp
Oracle OJVM组件作为数据库PSU补丁更新的一部分。它包含JDBC（客户端）修补和JavaVM组件修复。缓解补丁（Mitigation Patch）可防止数据库中所有当前已知的Oracle JavaVM安全漏洞。OJVM PSU 依赖DB PSU
startup upgrade这种受限模式下应用OJVM补丁到各个数据库中。【在RAC环境中，cluster_database应将参数设置为FALSE，以便STARTUP UPGRADE】
JAVA_DEV_STATUS查看是否安装)关闭本地节点上的实例和服务
应用DB PSU
应用缓解补丁
（可选）应用JDBC补丁
更新数据字典
在修补后的主目录中的所有数据库上执行dbms_java_dev.disable，禁用JAVA
重新启动所有实例和服务
下载地址: Patch 19721304
SELECT version, status FROM dba_registry WHERE comp_id=‘JAVAVM‘;select count(*) from x$kglob where KGLOBTYP = 29 OR KGLOBTYP = 56;v$session视图查看col service_name format a20col username format a20col program format a20set num 8select sess.service_name, sess.username,sess.program, count(*)fromv$session sess,dba_users usr,x$kgllk lk,x$kglobwhere kgllkuse=saddrand kgllkhdl=kglhdadrand kglobtyp in (29,56)and sess.user# = usr.user_idand usr.oracle_maintained = ‘N‘ #### omit this line on 11.2.0.4group by sess.service_name, sess.username, sess.programorder by sess.service_name, sess.username, sess.program;打开网址查看安全公告（https://www.oracle.com/security-alerts/）
找到对应产品CPU建议
CVE-2020-2735l漏洞
| 操作系统版本 | rhel7.6 x86_64 |
|---|---|
| 数据库版本 | 12.2.0.1+ |
| 节点数量 | 2 |
| 待应用补丁 | RU: 26610291 (GRID INFRASTRUCTURE RELEASE UPDATE 12.2.0.1.170814) |
Cluster Verify Utility(CVU): Oracle 提供了一个校验工具,主要功能是检查系统的硬件和软件环境是否满足安装的要求。CVU主要包括2个脚本（${GI_HOME}/runcluvfy.sh和${GI_HOME}/bin/cluvfy）
su - gridexport REMOTE_NODE_NAME=‘o19c1,o19c2,o19c3‘./runcluvfy.sh stage -pre crsinst -n ${REMOTE_NODE_NAME} -verbose >/tmp/cvu_chk.logor./runcluvfy.sh stage -pre crsinst -n ${REMOTE_NODE_NAME} -fixup -verbose # -fixup: 新增的参数，产生一个名为runfixup.sh的修复脚本# 语法./runcluvfy.sh -helpUSAGE:runcluvfy.sh [-help|-version]runcluvfy.sh stage {-list|-help}runcluvfy.sh stage {-pre|-post} <stage-name> <stage-specific options> [-verbose]runcluvfy.sh comp {-list|-help}runcluvfy.sh comp <component-name> <component-specific options> [-verbose]提前发起全备，升级前增备
# 1. opatch工具# 1.1 GI OPatchsu - root. /home/grid/.bash_profileexport GI_HOME=${ORACLE_HOME}export UNZIPPED_PATCH_LOCATION=/ups/softmv ${GI_HOME}/OPatch ${GI_HOME}/OPatch_$(date +%Y%m%d)cd ${UNZIPPED_PATCH_LOCATION}unzip -qo p6880880_*_Linux-x86-64.zip -d ${GI_HOME}chown -R grid:oinstall ${GI_HOME}/OPatchchmod -R +x ${GI_HOME}/OPatchls -ld ${GI_HOME}/OPatch*su - grid -c "${GI_HOME}/OPatch/opatch version"# 1.2 DB OPatchsu - oracleexport DB_HOME=${ORACLE_HOME}mv ${DB_HOME}/OPatch ${DB_HOME}/OPatch_$(date +%Y%m%d)export UNZIPPED_PATCH_LOCATION=/ups/softcd ${UNZIPPED_PATCH_LOCATION}unzip -qo p6880880_*_Linux-x86-64.zip -d ${DB_HOME}ls -ld ${DB_HOME}/OPatch*${ORACLE_HOME}/OPatch/opatch version# 2. PSUunzip -qo p26610291_122010_Linux-x86-64.zip -d /ups/soft/# 1. su - gridexport UNZIPPED_PATCH_LOCATION=/ups/soft$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ${UNZIPPED_PATCH_LOCATION}/28828733/28822515$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ${UNZIPPED_PATCH_LOCATION}/28828733/28870605$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ${UNZIPPED_PATCH_LOCATION}/28828733/28864846$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ${UNZIPPED_PATCH_LOCATION}/28828733/26839277$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ${UNZIPPED_PATCH_LOCATION}/28828733/28566910 cat >> ${UNZIPPED_PATCH_LOCATION}/patch_list_gihome.txt << EOF${UNZIPPED_PATCH_LOCATION}/28828733/28822515${UNZIPPED_PATCH_LOCATION}/28828733/28870605${UNZIPPED_PATCH_LOCATION}/28828733/28864846${UNZIPPED_PATCH_LOCATION}/28828733/26839277${UNZIPPED_PATCH_LOCATION}/28828733/28566910EOF# 检查文件系统空间(可选)$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile ${UNZIPPED_PATCH_LOCATION}/patch_list_gihome.txt$ORACLE_HOME/OPatch/opatch prereq CheckSystemCommandAvailable -phBaseFile ${UNZIPPED_PATCH_LOCATION}/patch_list_gihome.txt# 2. su - oracleexport UNZIPPED_PATCH_LOCATION=/ups/soft$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ${UNZIPPED_PATCH_LOCATION}/28828733/28822515$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ${UNZIPPED_PATCH_LOCATION}/28828733/28870605 cat >> ${UNZIPPED_PATCH_LOCATION}/patch_list_dbhome.txt << EOF${UNZIPPED_PATCH_LOCATION}/28828733/28822515${UNZIPPED_PATCH_LOCATION}/28828733/28870605EOF# 检查文件系统空间(可选)$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile ${UNZIPPED_PATCH_LOCATION}/patch_list_dbhome.txt$ORACLE_HOME/OPatch/opatch prereq CheckSystemCommandAvailable -phBaseFile ${UNZIPPED_PATCH_LOCATION}/patch_list_dbhome.txtsu - root. /home/grid/.bash_profile && export GI_HOME=${ORACLE_HOME} && export UNZIPPED_PATCH_LOCATION=/ups/soft${GI_HOME}/OPatch/opatchauto apply ${UNZIPPED_PATCH_LOCATION}/28828733 -analyzeOR${GI_HOME}/OPatch/opatchauto rollback ${UNZIPPED_PATCH_LOCATION}/28828733 -analyzeDuring 19.x GI installation, the file ‘oui-patch.xml‘ will be created under the central inventory directory on the OUI node (node where gridSetup.sh was invoked) but not on the other nodes
除节点外的其它节点因缺少oui-patch.xml文件引起PSU升级失败。
# 解决方案export REMOTE_NODE_NAME=‘o19c2,o19c3‘export INV_DIR="$(grep ‘inventory_loc‘ /etc/oraInst.loc|awk -F= ‘{print $NF}‘)/ContentsXML"for _host in $(echo ${REMOTE_NODE_NAME}|sed ‘s/,/ /g‘);do scp ${INV_DIR}/oui-patch.xml ${_host}:${INV_DIR}/oui-patch.xml ssh ${_host} "chmod 660 ${INV_DIR}/oui-patch.xml && chown grid:oinstall ${INV_DIR}/oui-patch.xml"done-- 备份oracle_home和inventory目录文件su - root# . /home/grid/.bash_profile && export GI_HOME=${ORACLE_HOME}# . /home/oracle/.bash_profile && export DB_HOME=${ORACLE_HOME}# tar -zcpf ora_app_$(date +%Y%m%d).tgz ${GI_HOME} ${DB_HOME} &cd /oracletar -czpf app.tgz app --exclude=*.trc --exclude=*.trm # 未接入备份expdp \‘/ as sysdba\‘ 所有节点依次顺序进行补丁应用
# 节点上的集群服务正常情况下# 1. 配置环境变量su - root. /home/grid/.bash_profileexport GI_HOME=${ORACLE_HOME}export UNZIPPED_PATCH_LOCATION=/ups/softexport PATH=${ORACLE_HOME}/OPatch:$PATH# 2. 应用补丁（GI补丁已包含DB）opatchauto apply ${UNZIPPED_PATCH_LOCATION}/28828733# 1. 关闭DB服务(oracle)# 1.1 语法：$ <ORACLE_HOME>/bin/srvctl stop home -o <ORACLE_HOME> -s <status file location> -n <node name># 1.2 执行命令关闭节点上的数据库实例su - oracle$ORACLE_HOME/bin/srvctl stop home -o $ORACLE_HOME -s /tmp/orc1.stats -n orcl# 2. 执行`rootcrs.pl -prepatch`命令关闭集群并解锁GI HOMEsu - root/ups/oracle/12.2/grid/perl/bin/perl /ups/oracle/12.2/grid/crs/install/rootcrs.pl -prepatch# 3. 应用补丁包内所有子集(OCW, ACFS, DBWLM and DB, ...)到GI HOME，详见readme中Table2-1$ <GI_HOME>/OPatch/opatch apply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%OCW TRACKING BUG%$ <GI_HOME>/OPatch/opatch apply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%ACFS TRACKING BUG%$ <GI_HOME>/OPatch/opatch apply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%DB WLM TRACKING BUG%$ <GI_HOME>/OPatch/opatch apply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%DB RU TRACKING BUG%$ <GI_HOME>/OPatch/opatch apply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%TOMCAT RU TRACKING BUG% su - grid# 3.1 应用db psu子包/ups/oracle/12.2/grid/OPatch/opatch apply -oh /ups/oracle/12.2/grid -local /ups/soft/28828733/28822515 -silent# 3.2 应用 OCW PSU /ups/oracle/12.2/grid/OPatch/opatch apply -oh /ups/oracle/12.2/grid -local /ups/soft/28828733/28870605 -silent# 3.3 ACFS PSU/ups/oracle/12.2/grid/OPatch/opatch apply -oh /ups/oracle/12.2/grid -local /ups/soft/28828733/28864846 -silent# 3.4 DBWLM PSU/ups/oracle/12.2/grid/OPatch/opatch apply -oh /ups/oracle/12.2/grid -local /ups/soft/28828733/26839277 -silent# 3.5 tomcat PSU/ups/oracle/12.2/grid/OPatch/opatch apply -oh /ups/oracle/12.2/grid -local /ups/soft/28828733/28566910 -silent# 4. 检查补丁更新详细日志tail -100f $(ls -lrt /ups/oracle/12.2/grid/cfgtoollogs/opatch/*.log|tail -1|awk ‘{print $NF}‘)# 5. 更新除 ACFS PSU到 DB HOME，详见readme Table2-2$ <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%OCW TRACKING BUG%/custom/scripts/prepatch.sh -dbhome <ORACLE_HOME>$ <ORACLE_HOME>/OPatch/opatch apply -oh <ORACLE_HOME> -local <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%OCW TRACKING BUG%$ <ORACLE_HOME>/OPatch/opatch apply -oh <ORACLE_HOME> -local <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%DB RU TRACKING BUG%$ <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%OCW TRACKING BUG%/custom/scripts/postpatch.sh -dbhome <ORACLE_HOME> # 5.1 执行prepatch.sh脚本保存db home的配置信息su - oracle/ups/soft/28828733/28870605/custom/scripts/prepatch.sh -dbhome /ups/oracle/database/product/12.2/db_1# 5.2 Apply DB PSU sub-patches/ups/oracle/database/product/12.2/db_1/OPatch/opatch apply -oh /ups/oracle/database/product/12.2/db_1 -local /ups/soft/28828733/28822515 -silent# 5.3 Apply OCW PSU sub-patches/ups/oracle/database/product/12.2/db_1/OPatch/opatch apply -oh /ups/oracle/database/product/12.2/db_1 -local /ups/soft/28828733/28870605 -silent# 5.4 补丁更新详细日志tail -100f $(ls -lrt /ups/oracle/database/product/12.2/db_1/cfgtoollogs/opatch/*.log|tail -1|awk ‘{print $NF}‘)# 5.5 执行 postpatch.sh 脚本还原db home配置信息/ups/soft/28828733/28870605/custom/scripts/postpatch.sh -dbhome /ups/oracle/database/product/12.2/db_1# 6. root启动集群服务su - root/ups/oracle/12.2/grid/rdbms/install/rootadd_rdbms.sh/ups/oracle/12.2/grid/perl/bin/perl /ups/oracle/12.2/grid/crs/install/rootcrs.pl -postpatch# 6.1 查看详细日志tail -100f /ups/oracle/grid/crsdata/$(hostname)/crsconfig/crspatch_*.log待验证
# 1. 关闭非本地外所有节点GI服务（即远端节点）su - root. /home/grid/.bash_profilecrsctl stop crs# 2. （本地节点GI要启动）应用补丁GI HOME(依次顺序更新所有节点)su - rootopatchauto apply /ups/soft/28828733 -oh /oracle/app/12.2/grid -nonrollingopatchauto apply ${UNZIPPED_PATCH_LOCATION}/28828733 -oh <GI_HOME># 1. 关闭集群服务su - root/ups/oracle/12.2/grid/perl/bin/perl /ups/oracle/12.2/grid/crs/install/rootcrs.pl -prepatch# 2. 应用补丁包内所有子集(OCW, ACFS, DBWLM and DB, ...)到GI HOME，详见readme中Table2-1su - grid# 2.1 应用db psu子包/ups/oracle/12.2/grid/OPatch/opatch apply -oh /ups/oracle/12.2/grid -local /ups/soft/28828733/28822515 -silent# 2.2 应用 OCW PSU /ups/oracle/12.2/grid/OPatch/opatch apply -oh /ups/oracle/12.2/grid -local /ups/soft/28828733/28870605 -silent# 2.3 ACFS PSU/ups/oracle/12.2/grid/OPatch/opatch apply -oh /ups/oracle/12.2/grid -local /ups/soft/28828733/28864846 -silent# 2.4 DBWLM PSU/ups/oracle/12.2/grid/OPatch/opatch apply -oh /ups/oracle/12.2/grid -local /ups/soft/28828733/26839277 -silent# 2.5 tomcat PSU/ups/oracle/12.2/grid/OPatch/opatch apply -oh /ups/oracle/12.2/grid -local /ups/soft/28828733/28566910 -silent# 3. 启动集群rootsu - root/ups/oracle/12.2/grid/rdbms/install/rootadd_rdbms.sh/ups/oracle/12.2/grid/perl/bin/perl /ups/oracle/12.2/grid/crs/install/rootcrs.pl -postpatchopatchauto apply ${UNZIPPED_PATCH_LOCATION}/28828733 -oh <oracle_home1_path>,<oracle_home2_path># 1. 关闭db服务并保存状态su - oracle$ORACLE_HOME/bin/srvctl stop home -o $ORACLE_HOME -s /tmp/orc1.stats -n orc1# 2. 更新除 ACFS PSU到 DB HOME，详见readme Table2-2# 2.1 执行prepatch.sh脚本保存db home的配置信息su - oracle/ups/soft/28828733/28870605/custom/scripts/prepatch.sh -dbhome /ups/oracle/database/product/12.2/db_1# 2.2 Apply DB PSU sub-patches/ups/oracle/database/product/12.2/db_1/OPatch/opatch apply -oh /ups/oracle/database/product/12.2/db_1 -local /ups/soft/28828733/28822515 -silent# 2.3 Apply OCW PSU sub-patches/ups/oracle/database/product/12.2/db_1/OPatch/opatch apply -oh /ups/oracle/database/product/12.2/db_1 -local /ups/soft/28828733/28870605 -silent# 2.4 补丁更新详细日志tail -100f $(ls -lrt /ups/oracle/database/product/12.2/db_1/cfgtoollogs/opatch/*.log|tail -1|awk ‘{print $NF}‘)# 3. 还原Db 配置su - oracle/ups/soft/28828733/28870605/custom/scripts/postpatch.sh -dbhome /ups/oracle/database/product/12.2/db_1# 4. 启动DB 服务su - oracle/ups/oracle/database/product/12.2/db_1/bin/srvctl start home -o /ups/oracle/database/product/12.2/db_1 -n orc1 -s /tmp/orc1.stats待所有节点都成功应用补丁软件后，选一DB实例执行数据字典更新操作。
# 启动orcl1服务su - oraclesrvctl start instance -d orcl -i orcl1# 连接并启动所有PDBsqlplus /nologconnect / as sysdbaalter pluggable database all open instances=all; quit;# 更新字典cd $ORACLE_HOME/OPatch./datapatch -verbose# 若存在失效对象，执行脚本修复cd $ORACLE_HOME/rdbms/adminsqlplus /nologCONNECT / AS SYSDBA@?/rdbms/admin/utlrp.sql-- # PDB 中执行alter session set container=PDB1;@?/rdbms/admin/utlrp.sql单独对指定的容器更新数据字典
cd ${ORACLE_HOME}/OPatchsqlplus / as sysdbaalter pluggable database all close instances = all;alter pluggable database all open read write instances= all;Execute datapatch individually in each of the containers, starting with CDB$ROOT :1. Apply datapatch is CDB$ROOT : =================================== ./datapatch -verbose -pdbs CDB\$ROOT2. Apply datapatch is PDB : =================================== $ ./datapatch -verbose -pdbs <pdbname>3. Apply datapatch is PDB$SEED : ================================== SQL> alter session set container=PDB$SEED; SQL> alter session set "_oracle_script"=TRUE; SQL> alter pluggable database pdb$seed close immediate instances=all; SQL> alter pluggable database pdb$seed OPEN READ WRITE; SQL> select open_mode from v$database; SQL> exit Execute: ./datapatch -verbose -pdbs PDB\$SEED SQL> alter session set "_oracle_script"=FALSE; SQL> alter pluggable database pdb$seed close immediate instances=all; SQL> alter pluggable database pdb$seed OPEN READ ONLY instances=all; 4. show pdbs -> If this shows the PDB$SEED in restricted mode, bounce the database once.5. Check for the relevant patch existence in the registry$sqlpatch fopr all the containers (CDB$ROOT, PDB$SEED and RCPDB) : set linesize 150 col logfile for a90 select patch_id, action, logfile, status from registry$sqlpatch;-- 检查版本sqlplus "/ as sysdba"set lines 168 pages 999col DESCRIPTION for a40col status for a10col version for a10select patch_id,PATCH_UID,STATUS,VERSION,to_char(ACTION_TIME,‘yyyy-mm-dd hh24:mi:ss‘) ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch; PATCH_ID PATCH_UID STATUS VERSION BUNDLE_ID ACTION_TIME DESCRIPTION---------- ---------- ---------- ------------ ---------- ------------------- ---------------------------------------- 26609817 21483023 SUCCESS 12.2.0.1 170814 2017-10-11 09:37:18 DATABASE RELEASE UPDATE 12.2.0.1.170814 set lines 168 pages 999col DESCRIPTION for a60col status for a10select INSTALL_ID,PATCH_ID,PATCH_UID,STATUS,TO_CHAR(ACTION_TIME,‘YYYY-MM-DD HH24:MI:SS‘) ACTION_TIME,DESCRIPTION from dba_registry_sqlpatch;INSTALL_ID PATCH_ID PATCH_UID STATUS ACTION_TIME DESCRIPTION---------- ---------- ---------- ---------- ------------------- ------------------------------------------------------------ 1 29517242 22862832 SUCCESS 2020-01-07 09:25:57 Database Release Update : 19.3.0.0.190416 (29517242) 2 30125133 23151502 SUCCESS 2020-01-07 15:13:04 Database Release Update : 19.5.0.0.191015 (30125133) 3 30128191 23093535 SUCCESS 2020-01-07 15:44:41 OJVM RELEASE UPDATE: 19.5.0.0.191015 (30128191)SQL>-- 检查失效对象情况$ sqlplus "/ as sysdba"set lines 168 pages 99col owner for a18col name for a18SELECT o.owner, c.name, o.con_id, COUNT(*) FROM cdb_objects o, v$containers c WHERE o.con_id = c.con_id AND o.status = ‘INVALID‘ GROUP BY o.owner, c.name, o.con_id ORDER BY 2, 1; col COMP_NAME for a40col schema for a18select r.con_id,r.comp_name,r.version,r.status,c.name from cdb_registry r, v$containers c where r.con_id = c.con_id and r.status != ‘VALID‘;select r.con_id,r.comp_name,r.version,r.status,c.name from cdb_registry r, v$containers c where r.con_id = c.con_id;-- 检查pdb是否存在异常错误信息set lines 200 pages 99col name for a12col time for a22col cause for a10col type for a8col message for a48col status for a8col action for a32col con_id for 9999col ERROR_NUMBER for 9999SELECT to_char(v.time,‘yyyy-mm-dd hh24:mi:ss‘) time, v.name, v.cause, v.type, v.error_number errcode, v.message, v.status, v.action, v.con_id FROM pdb_plug_in_violations v where v.type=‘ERROR‘;su - oraclecd /oracletar -xf app.tgz app# 1. 冲突检查cd <patch id>opatch prereq CheckConflictAgainstOHWithDetail -ph ./# 2. 关闭DB服务sqlplus / as sysdbashutdown immediate;# 3. 应用补丁cd <patch id>opatc apply# 4. 更新数据字典cd $ORACLE_HOME/OPatch./datapatch -verbose# 5. 检查确认opatch lsinv注意：需要全部停止数据库服务
su - oracleunzip -qo p25811364_122010_Linux-x86-64.zip -d /oracle/softsu - oracleexport PATH=${ORACLE_HOME}/OPatch:${PATH}cd 25811364opatch prereq CheckConflictAgainstOHWithDetail -ph ./su - oraclesrvctl stop database -d orcl su - root. /home/grid/.bash_profilecrsctl stop crssu - oraclecd 25811364opatch applyopatch lsinventory待所有节点软件更完成后，选一DB实例完成数据字典更新
-- Single/Multitenant (CDB/PDB) DBsqlplus /nologSQL> CONNECT / AS SYSDBASQL> STARTUPSQL> alter system set cluster_database=false scope=spfile; $ORACLE_HOME/bin/srvctl stop database -d orclsqlplus /nologSQL> CONNECT / AS SYSDBASQL> STARTUP UPGRADESQL> alter pluggable database all open upgrade;SQL> quitcd $ORACLE_HOME/OPatch./datapatch -verbosesqlplus /nologSQL> CONNECT / AS SYSDBASQL> alter system set cluster_database=true scope=spfile;SQL> SHUTDOWNSQL> quit$ORACLE_HOME/bin/srvctl start database -d orclsqlplus /nologSQL> CONNECT / AS SYSDBASQL> STARTUP UPGRADESQL> quitcd $ORACLE_HOME/OPatch./datapatch -verbosesqlplus /nologSQL> CONNECT / AS SYSDBASQL> shutdownSQL> startup# 若存在失效对象，重新编译cd $ORACLE_HOME/rdbms/adminsqlplus /nologSQL> CONNECT / AS SYSDBASQL> @$ORACLE_HOME/rdbms/admin/utlrp.sqlsqlplus "/ as sysdba"set lines 168 pages 999REM ‘invalid pdb‘col name for a12col time for a22col cause for a10col type for a8col message for a48col status for a8col action for a32col con_id for 9999col ERROR_NUMBER for 9999SELECT to_char(v.time,‘yyyy-mm-dd hh24:mi:ss‘) time, v.name, v.cause, v.type, v.error_number errcode, v.message, v.status, v.action, v.con_id FROM pdb_plug_in_violations v where v.type=‘ERROR‘;REM ‘invalid objs‘col owner for a18select owner,con_id,count(*) cnt from cdb_objects where status = ‘INVALID‘ group by owner,con_id, status order by 2,1;col COMP_NAME for a40col schema for a18REM ‘invalid comps‘select con_id,comp_name,version,status,schema from cdb_registry where status = ‘INVALID‘;-- 12.2col DESCRIPTION for a40col status for a10col version for a10REM ‘patch version‘SELECT patch_id ,patch_uid ,status ,version ,bundle_id ,to_char(action_time, ‘yyyy-mm-dd hh24:mi:ss‘) action_time ,description FROM dba_registry_sqlpatch;-- 19col ACTION for a8col DESCRIPTION for a32col SOURCE_BUILD_DESCRIPTION for a32col SOURCE_BUILD_TIMESTAMP for a24col TARGET_BUILD_DESCRIPTION for a24col TARGET_BUILD_TIMESTAMP for a24SELECT rsp.install_id ,rsp.patch_id ,rsp.patch_uid ,rsp.patch_type ,rsp.action ,rsp.status ,to_char(rsp.action_time, ‘yyyy-mm-dd hh24:mi:ss‘) action_time ,rsp.description ,rsp.source_version ,rsp.source_build_description -- ,rsp.source_build_timestamp ,rsp.target_version ,rsp.target_build_description -- ,rsp.target_build_timestamp FROM dba_registry_sqlpatch rsp;Oracle Patch Assurance - Data Guard Standby-First Patch Apply (Doc ID 1265700.1)
# 1. 回滚GI 补丁# GI HOME + DB HOMEsu - root<GI_HOME>/OPatch/opatchauto rollback <UNZIPPED_PATCH_LOCATION>/26610291# only GI HOMEopatchauto rollback ${UNZIPPED_PATCH_LOCATION}/28828733 -oh <path to GI home># only DB HOMEopatchauto rollback <UNZIPPED_PATCH_LOCATION>/26610291 -oh <oracle_home1_path>,<oracle_home2_path># 回滚数据字典su - oraclesqlplus /nologconnect / as sysdbastartupalter pluggable database all open; quit;cd $(orabasehome)/OPatch./datapatch -verbose# 若存在失效对象，执行脚本修复cd $ORACLE_HOME/rdbms/adminsqlplus /nologCONNECT / AS SYSDBA@?/rdbms/admin/utlrp.sqlGI HOME+DB HOME
# 1. 停DB服务su - oracle<ORACLE_HOME>/bin/srvctl stop home -o <ORACLE_HOME> -s <status file location> -n <node name># 2. 关闭集群su - root<GI_HOME>/crs/install/rootcrs.sh -prepatch -rollback# 3. 回滚GI HOME 补丁su - grid$ <GI_HOME>/OPatch/opatch nrollback -local -id %OCW TRACKING BUG%,%ACFS TRACKING BUG%,%DB RU TRACKING BUG%, %DB WLM TRACKING BUG%, %TOMCAT TRACKING BUG -oh <GI_HOME># 4. 回滚DB HOME 补丁su - oracle$ <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%OCW TRACKING BUG%/custom/scripts/prepatch.sh -dbhome <ORACLE_HOME>$ <ORACLE_HOME>/OPatch/opatch nrollback -local -id %OCW TRACKING BUG%,%DBRU TRACKING BUG% -oh <ORACLE_HOME> $ <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%OCW TRACKING BUG%/custom/scripts/postpatch.sh -dbhome <ORACLE_HOME> # 5. 启动集群服务su - root<GI_HOME>/rdbms/install/rootadd_rdbms.sh<GI_HOME>/crs/install/rootcrs.sh -postpatch -rollback# 6. 启动DB服务su - oracle<ORACLE_HOME>/bin/srvctl start home -o <ORACLE_HOME> -s <status file location> -n <node name># 7. 回滚数据字典# oracle用户执行$ <ORACLE_HOME>/OPatch/opatch nrollback -local -id %OCW TRACKING BUG%,%DB RU TRACKING BUG% -oh <ORACLE_HOME> # 停止所有服务，同2.4.4.4节内容# 软件回退su - oracleopatch rollback -id 25811364# 2. 回退数据字典，同2.4.4.6节## 3. 检查日志cd $ORACLE_HOME/sqlpatch/25811364/tail -100f <PATCH_ID>_rollback_<database SID>_<CDB name>_<timestamp>.log# 4. 启动服务[grid@o19c1 ~]$ asmca -silent -addDisk -diskGroupName DATA -disk ‘/dev/asm-data‘ [FATAL] [DBT-30007] Addition of disks to disk group DATA failed.ORA-15032: not all alterations performedORA-15137: The ASM cluster is in rolling patch state./**/可能原因是当前实际的 active version 不是一致的。
检查所有节点：
# 确认所有节点补丁相同 crsctl query crs softwarepatch[grid@o19c1 ~]$ crsctl query crs softwarepatch o19c1Oracle Clusterware patch level on node o19c1 is [724960844].[grid@o19c1 ~]$ crsctl query crs softwarepatch o19c2Oracle Clusterware patch level on node o19c2 is [724960844].[grid@o19c1 ~]$ # 以root在任一节点执行su - root. /home/grid/.bash_profilecrsctl stop rollingpatch<GI_HOME>/crs/install/rootcrs.sh -unlock<GI_HOME>/crs/install/rootcrs.sh -patchpublic权限问题引起组件失效-- 检查public默认权限set lines 168 pages 99select table_name from dba_tab_privs where grantee=‘PUBLIC‘ and privilege=‘EXECUTE‘ and table_name in (‘UTL_FILE‘, ‘UTL_TCP‘, ‘UTL_HTTP‘, ‘UTL_SMTP‘, ‘DBMS_RANDOM‘,‘DBMS_SQL‘,‘DBMS_JOB‘,‘DBMS_LOB‘);-- 重新授权grant execute on dbms_sql to PUBLIC;grant execute on dbms_lob to PUBLIC;grant execute on utl_file to PUBLIC;grant execute on dbms_job to PUBLIC;grant execute on dbms_scheduler to PUBLIC;grant execute on dbms_random to public;grant execute on utl_http to public;grant execute on utl_smtp to public;grant execute on UTL_FILE to MDSYS;[root@o19c1 ~]# crsctl start crsCRS-6706: Oracle Clusterware Release patch level (‘74762968‘) does not match Software patch level (‘724960844‘). Oracle Clusterware cannot be started.CRS-4000: Command Start failed, or completed with errors.[root@o19c1 ~]# crsctl query crs releasepatchPatching 12.2.0.1 Grid Infrastructure gives error CRS-6706: Oracle Clusterware Release Patch Level (‘748994161‘) Does Not Match Software Patch Level (文档 ID 2348013.1)
su - root# 1. Run the following command as the root user to complete the patching set up behind the scenes:cd /ups/oracle/19.2/grid/bin./clscfg -localpatch# 2. Run the following command as the root user to lock the GI homecd /ups/oracle/19.2/grid/crs/install/./rootcrs.sh -lock# 3. Run the following command as the root user to start the GIcrsctl start crs
