重庆思庄Oracle、Redhat认证学习论坛
标题: 19c升级 [打印本页]
作者: denglj 时间: 2022-4-8 17:43
标题: 19c升级
本帖最后由 denglj 于 2022-4-8 17:45 编辑
数据库升级19.3—>19.9—>19.14
系统：CentOS7.9
数据库：19.3
SQL> selectbanner_full from v$version
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19cEnterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
1、升级至19.9说明：要升级19.9，OPatch版本需大于 12.2.0.1.19，1.1—1.8需在两个节点执行
1.1、软件包处理sftp>cd /tmp
sftp>lcd F:\BaiduNetdiskDownload\2201最新补丁集-文档2118136.2\19C-202201\OPatch
sftp>put p6880880_190000_Linux-x86-64.zip
[grid@hisdb1:/tmp]$unzip -q -o p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME
[oracle@hisdb1:/tmp]$unzip -q -o p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME
[grid@hisdb1:/home/grid]$ opatch version
OPatch Version: 12.2.0.1.28
OPatch succeeded.
[oracle@hisdb1:/home/oracle]$ opatch version
OPatch Version: 12.2.0.1.28
OPatch succeeded.
1.2、补丁一致性[grid@hisdb1:/home/grid]$ $ORACLE_HOME/OPatch/opatchlsinventory -detail -oh $ORACLE_HOME
[oracle@hisdb1:/home/oracle]$ $ORACLE_HOME/OPatch/opatchlsinventory -detail -oh $ORACLE_HOME
说明：此处输出为补丁应用前状态.
1.3、patch[root@hisdb1 /]# mkdir /opatch
[root@hisdb1 /]# chown -R grid:oinstall /opatch
sftp> lcd F:\BaiduNetdiskDownload
sftp> cd /home/grid
sftp> put p31750108_190000_Linux-x86-64.zip
[grid@hisdb1:/home/grid]$ unzip -qp31750108_190000_Linux-x86-64.zip -d /opatch
1.4、检查补丁冲突Grid Home：
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opatch/31750108/31771877
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opatch/31750108/31772784
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opatch/31750108/31773437
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opatch/31750108/
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opatch/31750108/31780966
DB Home：
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opatch/31750108/31771877
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opatch/31750108/31772784
此处存在以下异常：
[grid@hisdb1:/home/grid]$ $ORACLE_HOME/OPatch/opatch prereqCheckConflictAgainstOHWithDetail -phBaseDir /opatch/31750108/
Oracle Interim Patch Installer version 12.2.0.1.28
Copyright (c) 2022, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home :/u01/app/19.3.0/grid
Central Inventory : /u01/app/oraInventory
from : /u01/app/19.3.0/grid/oraInst.loc
OPatch version :12.2.0.1.28
OUI version :12.2.0.7.0
Log file location :/u01/app/19.3.0/grid/cfgtoollogs/opatch/opatch2022-04-02_14-44-27PM_1.log
This command doesn't support System Patch.
OPatch failed with error code 21
说明：根据opatchCheckSystemSpace Command For Grid Infrastructure RU Fails With: "Thiscommand doesn't support System Patch" (Doc ID 2634165.1) ，此不需检测。
1.5、系统空间检查运行opatchsystemspace检查。
1.5.1、GI主目录创建具有以下内容的/tmp/patch_list_gihome.txt文件：
[grid@hisdb1:/home/grid]$ vi /tmp/patch_list_gihome.txt
/opatch/31750108/31771877
/opatch/31750108/31772784
/opatch/31750108/31773437
/opatch/31750108/31780966
说明：oracle官方文档中包含/opatch/31750108/的检查，运行会报错，同上不需检测，此处将已将其取消.
1.5.2、检查GI主目录可用空间[grid@hisdb1:/home/grid]$ $ORACLE_HOME/OPatch/opatch prereqCheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt
1.5.3、DB主目录创建具有以下内容的/home/oracle/oracle_patch.txt文件
[oracle@hisdb1:/home/oracle]$ vi /tmp/patch_list_dbhome.txt
/opatch/31750108/31771877
/opatch/31750108/31772784
1.5.4、检查DB主目录可用空间[oracle@hisdb1:/home/oracle]$ $ORACLE_HOME/OPatch/opatchprereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt
1.6、一次性补丁冲突检测与解决[root@hisdb1 opatch]#/u01/app/19.3.0/grid/OPatch/opatchauto apply /opatch/31750108 -analyze
1.7、自动补丁安装说明：rac升级操作过程中关闭数据库，反而出现告警。
[grid@hisdb1:/home/oracle]$ srvctl stop database -d orcl
[grid@hisdb1:/home/grid]$ lsnrctl stop
[root@hisdb1opatch]# export PATH=$PATH:/u01/app/19.3.0/grid/OPatch
[root@hisdb1 opatch]# opatchauto apply/opatch/31750108
OPatchauto session is initiated at SatApr 2 15:38:27 2022
System initialization log file is/u01/app/19.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2022-04-02_03-38-30PM.log.
Session log file is/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2022-04-02_03-38-51PM.log
The id for this session is VBQ6
Executing OPatch prereq operations toverify patch applicability on home /u01/app/oracle/product/19.3.0/db
Executing OPatch prereq operations toverify patch applicability on home /u01/app/19.3.0/grid
Patch applicability verifiedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Patch applicability verifiedsuccessfully on home /u01/app/19.3.0/grid
Executing patch validation checks onhome /u01/app/19.3.0/grid
Patch validation checks successfullycompleted on home /u01/app/19.3.0/grid
Executing patch validation checks onhome /u01/app/oracle/product/19.3.0/db
Patch validation checks successfullycompleted on home /u01/app/oracle/product/19.3.0/db
Verifying SQL patch applicability onhome /u01/app/oracle/product/19.3.0/db
SQL patch applicability verifiedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Preparing to bring down database serviceon home /u01/app/oracle/product/19.3.0/db
Successfully prepared home/u01/app/oracle/product/19.3.0/db to bring down database service
Performing prepatch operations on CRS - bringingdown CRS service on home /u01/app/19.3.0/grid
Prepatch operation log file location:/u01/app/grid/crsdata/hisdb1/crsconfig/crs_prepatch_apply_inplace_hisdb1_2022-04-02_03-39-41PM.log
CRS service brought down successfully onhome /u01/app/19.3.0/grid
Performing prepatch operation on home/u01/app/oracle/product/19.3.0/db
Perpatch operation completedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Start applying binary patch on home/u01/app/oracle/product/19.3.0/db
Binary patch applied successfully onhome /u01/app/oracle/product/19.3.0/db
Performing postpatch operation on home/u01/app/oracle/product/19.3.0/db
Postpatch operation completedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Start applying binary patch on home/u01/app/19.3.0/grid
Binary patch applied successfully onhome /u01/app/19.3.0/grid
Performing postpatch operations on CRS -starting CRS service on home /u01/app/19.3.0/grid
Postpatch operation log file location:/u01/app/grid/crsdata/hisdb1/crsconfig/crs_postpatch_apply_inplace_hisdb1_2022-04-02_03-54-55PM.log
CRS service started successfully on home/u01/app/19.3.0/grid
Preparing home/u01/app/oracle/product/19.3.0/db after database service restarted
No step execution required.........
Trying to apply SQL patch on home/u01/app/oracle/product/19.3.0/db
SQL patch applied successfully on home/u01/app/oracle/product/19.3.0/db
OPatchAuto successful.
--------------------------------Summary--------------------------------
Patching is completed successfully.Please find the summary as follows:
Host:hisdb1
RACHome:/u01/app/oracle/product/19.3.0/db
Version:19.0.0.0.0
Summary:
==Following patches were SKIPPED:
Patch: /opatch/31750108/31773437
Reason: This patch is not applicable tothis specified target type - "rac_database"
Patch: /opatch/31750108/31780966
Reason: This patch is not applicable tothis specified target type - "rac_database"
==Following patches were SUCCESSFULLYapplied:
Patch: /opatch/31750108/31771877
Log: /u01/app/oracle/product/19.3.0/db/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_15-42-36PM_1.log
Patch: /opatch/31750108/31772784
Log:/u01/app/oracle/product/19.3.0/db/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_15-42-36PM_1.log
Host:hisdb1
CRS Home:/u01/app/19.3.0/grid
Version:19.0.0.0.0
Summary:
==Following patches were SUCCESSFULLYapplied:
Patch: /opatch/31750108/31771877
Log:/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_15-46-45PM_1.log
Patch: /opatch/31750108/31772784
Log:/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_15-46-45PM_1.log
Patch: /opatch/31750108/31773437
Log:/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_15-46-45PM_1.log
Patch: /opatch/31750108/31780966
Log:/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_15-46-45PM_1.log
Patching session reported followingwarning(s):
_________________________________________________
[WARNING] The database instance 'orcl1'from '/u01/app/oracle/product/19.3.0/db', in host'hisdb1' is not running. SQLchanges, if any, will not be applied.
To apply. the SQL changes, bring up thedatabase instance and run the command manually from any one node (run asoracle).
Refer to the readme to get the correctsteps for applying the sql changes.
OPatchauto session completed at SatApr 2 15:59:24 2022
Time taken to complete the session 20minutes, 57 seconds
1.8、检查grid补丁[grid@hisdb1:/home/grid]$ opatch lspatches
31780966;TOMCAT RELEASE UPDATE19.0.0.0.0 (31780966)
31773437;ACFS RELEASE UPDATE 19.9.0.0.0(31773437)
31772784;OCW RELEASE UPDATE 19.9.0.0.0(31772784)
31771877;Database Release Update :19.9.0.0.201020 (31771877)
OPatch succeeded.
[grid@hisdb1:/home/grid]$ sqlplus -version
SQL*Plus: Release 19.0.0.0.0 -Production
Version 19.9.0.0.0
以下贴出节点2的执行过程：
[root@hisdb2 /]# exportPATH=$PATH:/u01/app/19.3.0/grid/OPatch
[root@hisdb2 /]# opatchauto apply/opatch/31750108
OPatchauto session is initiated at SatApr 2 16:04:55 2022
System initialization log file is/u01/app/19.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2022-04-02_04-04-58PM.log.
Session log file is/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2022-04-02_04-05-20PM.log
The id for this session is NX44
Executing OPatch prereq operations toverify patch applicability on home /u01/app/oracle/product/19.3.0/db
Executing OPatch prereq operations toverify patch applicability on home /u01/app/19.3.0/grid
Patch applicability verifiedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Patch applicability verifiedsuccessfully on home /u01/app/19.3.0/grid
Executing patch validation checks onhome /u01/app/19.3.0/grid
Patch validation checks successfullycompleted on home /u01/app/19.3.0/grid
Executing patch validation checks onhome /u01/app/oracle/product/19.3.0/db
Patch validation checks successfullycompleted on home /u01/app/oracle/product/19.3.0/db
Verifying SQL patch applicability onhome /u01/app/oracle/product/19.3.0/db
SQL patch applicability verifiedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Preparing to bring down database serviceon home /u01/app/oracle/product/19.3.0/db
Successfully prepared home/u01/app/oracle/product/19.3.0/db to bring down database service
Performing prepatch operations on CRS -bringing down CRS service on home /u01/app/19.3.0/grid
Prepatch operation log file location:/u01/app/grid/crsdata/hisdb2/crsconfig/crs_prepatch_apply_inplace_hisdb2_2022-04-02_04-06-06PM.log
CRS service brought down successfully onhome /u01/app/19.3.0/grid
Performing prepatch operation on home/u01/app/oracle/product/19.3.0/db
Perpatch operation completedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Start applying binary patch on home/u01/app/oracle/product/19.3.0/db
Binary patch applied successfully onhome /u01/app/oracle/product/19.3.0/db
Performing postpatch operation on home/u01/app/oracle/product/19.3.0/db
Postpatch operation completedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Start applying binary patch on home/u01/app/19.3.0/grid
Failed while applying binary patches onhome /u01/app/19.3.0/grid
Execution of [OPatchAutoBinaryAction]patch action failed, check log for more details. Failures:
Patch Target : hisdb2->/u01/app/19.3.0/gridType[crs]
Details: [
---------------------------PatchingFailed---------------------------------
Command execution failed during patchingin home: /u01/app/19.3.0/grid, host: hisdb2.
Command failed: /u01/app/19.3.0/grid/OPatch/opatchauto apply /opatch/31750108 -oh/u01/app/19.3.0/grid -target_type cluster -binary -invPtrLoc/u01/app/19.3.0/grid/oraInst.loc -jre /u01/app/19.3.0/grid/OPatch/jre-persistresult/u01/app/19.3.0/grid/opatchautocfg/db/sessioninfo/sessionresult_hisdb2_crs_1.ser-analyzedresult/u01/app/19.3.0/grid/opatchautocfg/db/sessioninfo/sessionresult_analyze_hisdb2_crs_1.ser
Command failure output:
==Following patches FAILED in apply:
Patch: /opatch/31750108/31771877
Log:/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_16-12-56PM_1.log
Reason: Failed during Patching:oracle.opatch.opatchsdk.OPatchException: ApplySession failed in systemmodification phase... 'ApplySession::apply failed: java.io.IOException:oracle.sysman.oui.patch.PatchException: java.io.FileNotFoundException:/u01/app/oraInventory/ContentsXML/oui-patch.xml (Permission denied)'
After fixing the cause of failure Runopatchauto resume
OPATCHAUTO-68061: The orchestrationengine failed.
OPATCHAUTO-68061: The orchestrationengine failed with return code 1
OPATCHAUTO-68061: Check the log for moredetails.
OPatchAuto failed.
OPatchauto session completed at SatApr 2 16:18:17 2022
Time taken to complete the session 13minutes, 22 seconds
opatchauto failed with error code 42
异常解决：
观察节点1与节点2 /oracle/oraInventory/ContentsXML/oui-patch.xml权限差异，
将节点二修改成与节点一文件属性一致。
[grid@hisdb1:/home/grid]$ ll/u01/app/oraInventory/ContentsXML/
total 16
-rw-rw---- 1 grid oinstall 300 Apr 2 15:54 comps.xml
-rw-rw---- 1 grid oinstall 555 Mar 2111:15 inventory.xml
-rw-rw---- 1 grid oinstall 292 Apr 2 15:54 libs.xml
-rw-rw---- 1 grid oinstall 174 Apr 2 15:54 oui-patch.xml
[oracle@hisdb2:/home/oracle]$ ll/u01/app/oraInventory/ContentsXML/
total 20
-rw-rw---- 1 grid oinstall 300 Apr 2 16:12 comps.xml
-rw-rw---- 1 grid oinstall 555 Mar 21 11:19 inventory.xml
-rw-rw---- 1 grid oinstall 292 Apr 2 16:12 libs.xml
-rw-r--r-- 1 oracle oinstall 174Apr 2 16:12 oui-patch.xml
-rw-r--r-- 1 grid oinstall 174 Apr 2 16:12 oui-patch.xml.back
[oracle@hisdb2:/u01/app/oraInventory/ContentsXML]$chmod 660 oui-patch.xml
[root@hisdb2 ContentsXML]# chown -Rgrid:oinstall oui-patch.xml
[grid@hisdb2:/home/grid]$ opatchlsinventory
Oracle Interim Patch Installer version12.2.0.1.28
Copyright (c) 2022, OracleCorporation. All rights reserved.
Oracle Home : /u01/app/19.3.0/grid
Central Inventory :/u01/app/oraInventory
from :/u01/app/19.3.0/grid/oraInst.loc
OPatch version : 12.2.0.1.28
OUI version : 12.2.0.7.0
Log file location : /u01/app/19.3.0/grid/cfgtoollogs/opatch/opatch2022-04-02_16-37-03PM_1.log
Lsinventory Output file location :/u01/app/19.3.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2022-04-02_16-37-03PM.txt
--------------------------------------------------------------------------------
Inventory load failed... OPatch cannotload inventory for the given Oracle Home.
LsInventorySession failed: Unable tocreate patchObject
Possible causes are:
ORACLE_HOME/inventory/oneoffs/31771877 is corrupted. PatchObjectconstructor: Input file"/u01/app/19.3.0/grid/inventory/oneoffs/31771877/etc/config/actions"or"/u01/app/19.3.0/grid/inventory/oneoffs/31771877/etc/config/inventory"does not exist.
OPatch failed with error code 73
说明：此处查看opatch，也出现异常，将节点1的31771877传送到节点2.
[grid@hisdb1:/opatch/31750108]$ scp -r31771877/ hisdb2:/u01/app/19.3.0/grid/inventory/oneoffs/
节点2继续：
[root@hisdb2 ContentsXML]# opatchautoresume
OPatchauto session is initiated at SatApr 2 16:42:59 2022
Session log file is/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2022-04-02_04-42-59PM.log
Resuming existing session with id NX44
Start applying binary patch on home/u01/app/19.3.0/grid
Binary patch applied successfully onhome /u01/app/19.3.0/grid
Checking shared status of home.....
Performing postpatch operations on CRS -starting CRS service on home /u01/app/19.3.0/grid
Postpatch operation log file location:/u01/app/grid/crsdata/hisdb2/crsconfig/crs_postpatch_apply_inplace_hisdb2_2022-04-02_04-47-09PM.log
CRS service started successfully on home/u01/app/19.3.0/grid
Preparing home/u01/app/oracle/product/19.3.0/db after database service restarted
No step execution required.........
Trying to apply SQL patch on home/u01/app/oracle/product/19.3.0/db
SQL patch applied successfully on home /u01/app/oracle/product/19.3.0/db
OPatchAuto successful.
--------------------------------Summary--------------------------------
Patching is completed successfully.Please find the summary as follows:
Host:hisdb2
RAC Home:/u01/app/oracle/product/19.3.0/db
Version:19.0.0.0.0
Summary:
==Following patches were SKIPPED:
Patch: /opatch/31750108/31773437
Reason: This patch is not applicable tothis specified target type - "rac_database"
Patch: /opatch/31750108/31780966
Reason: This patch is not applicable tothis specified target type - "rac_database"
==Following patches were SUCCESSFULLYapplied:
Patch: /opatch/31750108/31771877
Log:/u01/app/oracle/product/19.3.0/db/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_16-08-51PM_1.log
Patch: /opatch/31750108/31772784
Log:/u01/app/oracle/product/19.3.0/db/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_16-08-51PM_1.log
Host:hisdb2
CRS Home:/u01/app/19.3.0/grid
Version:19.0.0.0.0
Summary:
==Following patches were SKIPPED:
Patch: /opatch/31750108/31771877
Reason: This patch is not applicable tothis specified target type - "cluster"
==Following patches were SUCCESSFULLYapplied:
Patch: /opatch/31750108/31772784
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_16-43-29PM_1.log
Patch: /opatch/31750108/31773437
Log:/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_16-43-29PM_1.log
Patch: /opatch/31750108/31780966
Log:/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_16-43-29PM_1.log
Patching session reported followingwarning(s):
_________________________________________________
[WARNING] The database instance 'orcl2'from '/u01/app/oracle/product/19.3.0/db', in host'hisdb2' is not running. SQLchanges, if any, will not be applied.
To apply. the SQL changes, bring up thedatabase instance and run the command manually from any one node (run asoracle).
Refer to the readme to get the correct stepsfor applying the sql changes.
OPatchauto session completed at SatApr 2 16:51:09 2022
Time taken to complete the session 8minutes, 10 seconds
[grid@hisdb2:/home/grid]$ opatchlspatches
31780966;TOMCAT RELEASE UPDATE19.0.0.0.0 (31780966)
31773437;ACFS RELEASE UPDATE 19.9.0.0.0(31773437)
31772784;OCW RELEASE UPDATE 19.9.0.0.0(31772784)
31771877;Database Release Update :19.9.0.0.201020 (31771877)
OPatch succeeded.
[grid@hisdb2:/home/grid]$ sqlplus-version
SQL*Plus: Release 19.0.0.0.0 -Production
Version 19.9.0.0.0
[grid@hisdb2:/home/grid]$ srvctl startdatabase -d orcl
1.9、加载SQL 说明：此步骤为加载变化的SQL到数据库，RAC环境只需在节点1执行。
[oracle@hisdb1:/u01/app/oracle/product/19.3.0/db/OPatch]$./datapatch -verbose
1.10、处理无效对象因之前datapatch命令会加载SQL，此过程可能会产生无效对象，执行
@utlrp.sql脚本处理无效对象
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-04-02 17:15:28
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE statusIN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROMUTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROMdba_scheduler_jobs
DOC> WHERE job_name like'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROMdba_scheduler_running_jobs
DOC> WHERE job_name like'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2022-04-02 17:15:30
DOC> The following query reports thenumber of invalid objects.
DOC>
DOC> If the number is higher thanexpected, please examine the error
DOC> messages reported with eachobject (using SHOW ERRORS) to see if they
DOC> point to system misconfigurationor resource constraints that must be
DOC> fixed before attempting torecompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports thenumber of exceptions caught during
DOC> recompilation. If this number isnon-zero, please query the error
DOC> messages in the tableUTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resourceconstraints that must be
DOC> fixed before objects can compilesuccessfully.
DOC> Note: Typical compilation errors(due to coding errors) are not
DOC> logged into this table: they go intoDBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
1.11、查看版本变化SQL> col status for a10
SQL> col action for a10
SQL> col action_time for a30
SQL> col desctiption for a60
SQL> set line 200
SQL> selectpatch_id,patch_type,action,status,action_time,description fromdba_registry_sqlpatch;
PATCH_ID PATCH_TYPE ACTION STATUS ACTION_TIME DESCRIPTION
---------- ---------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------
29517242 RU APPLY SUCCESS 21-MAR-22 11.30.57.513467 AM Database Release Update : 19.3.0.0.190416(29517242)
31771877 RU APPLY SUCCESS 02-APR-22 05.11.09.823188 PM Database Release Update : 19.9.0.0.201020 (31771877)
SQL> col version for a25
SQL> col comments for a80
SQL> select action_time,version,commentsfrom dba_registry_history;
ACTION_TIME VERSION COMMENTS
---------------------------------------------------------------------------------------------------------------------------------------
19 RDBMS_19.9.0.0.0DBRU_LINUX.X64_200930
21-MAR-22 11.30.52.564764 AM 19.0.0.0.0 Patch applied on 19.3.0.0.0:Release_Update - 190410122720
02-APR-22 05.10.27.383267 PM 19.0.0.0.0 Patch applied from 19.3.0.0.0 to 19.9.0.0.0:Release_Update - 200930183249
2、升级至19.14说明：要升级19.9，OPatch版本需大于 12.2.0.1.28，2.1—2.9需在两个节点执行.
2.1、当前DB版本[oracle@hisdb1:/home/oracle]$ sqlplus /as sysdba
SQL*Plus: Release 19.0.0.0.0 -Production on Sun Mar 20 09:55:49 2022
Version 19.9.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise EditionRelease 19.0.0.0.0 - Production
Version 19.9.0.0.0
2.2、软件包处理
说明：19.14 DB的RU编号是：33515361，GI的RU编号是：33509923
安装OPatch：
sftp> cd /tmp
sftp> lcd F:\installmedium\19c
sftp> putp6880880_122010_Linux-x86-64.zip
[grid@hisdb1:/tmp]$unzip -q -o p6880880_122010_Linux-x86-64.zip -d $ORACLE_HOME
[oracle@hisdb1:/tmp]$unzip -q -o p6880880_122010_Linux-x86-64.zip -d $ORACLE_HOME
[grid@hisdb1:/home/grid]$ opatch version
OPatch Version: 12.2.0.1.29
OPatch succeeded.
[oracle@hisdb1:/tmp]$ opatch version
OPatch Version: 12.2.0.1.29
OPatch succeeded.
2.3、补丁一致性检查DB Home和Grid Home的补丁列表里补丁的一致性
[grid@hisdb1:/home/grid]$ $ORACLE_HOME/OPatch/opatchlsinventory -detail -oh $ORACLE_HOME
[oracle@hisdb1:/home/oracle]$$ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
2.4、patch[root@hisdb1 /]# mkdir /patch
[root@hisdb1 /]# chown -R grid:oinstall/patch
sftp> lcd F:\BaiduNetdiskDownload
sftp> cd /home/grid
sftp> putp33509923_190000_Linux-x86-64.zip
[grid@hisdb1:/home/grid]$ unzip -qp33509923_190000_Linux-x86-64.zip -d /patch
2.5、检查补丁冲突Grid Home：
$ORACLE_HOME/OPatch/opatch prereqCheckConflictAgainstOHWithDetail -phBaseDir /patch/33509923/33515361
$ORACLE_HOME/OPatch/opatch prereqCheckConflictAgainstOHWithDetail -phBaseDir /patch/33509923/33529556
$ORACLE_HOME/OPatch/opatch prereqCheckConflictAgainstOHWithDetail -phBaseDir /patch/33509923/33534448
$ORACLE_HOME/OPatch/opatch prereqCheckConflictAgainstOHWithDetail -phBaseDir /patch/33509923/33239955
$ORACLE_HOME/OPatch/opatch prereqCheckConflictAgainstOHWithDetail -phBaseDir /patch/33509923/33575402
DB Home：
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail-phBaseDir /patch/33509923/33515361
$ORACLE_HOME/OPatch/opatch prereqCheckConflictAgainstOHWithDetail -phBaseDir /patch/33509923/33529556
2.6、系统空间检查运行opatch systemspace检查
2.6.1、GI主目录创建具有以下内容的/tmp/patch_lists_gihome.txt文件：
[grid@hisdb1:/home/grid]$ vi/tmp/patch_lists_gihome.txt
/patch/33509923/33515361
/patch/33509923/33529556
/patch/33509923/33534448
/patch/33509923/33239955
/patch/33509923/33575402
2.6.2、检查GI主目录可用空间[grid@hisdb1:/home/grid]$$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile/tmp/patch_lists_gihome.txt
2.6.3、DB主目录创建具有以下内容的/tmp/patch_lists_dbhome.txt文件：
[oracle@hisdb1:/home/oracle]$ vi/tmp/patch_lists_dbhome.txt
/patch/33509923/33515361
/patch/33509923/33529556
2.6.4、检查DB主目录可用空间
[oracle@hisdb1:/home/oracle]$$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile/tmp/patch_lists_dbhome.txt
2.7、一次性补丁冲突检测与解决[root@hisdb1 ~]#/u01/app/19.3.0/grid/OPatch/opatchauto apply /patch/33509923 -analyze
2.8、自动补丁安装apply前关闭所有节点数据库、监听、以及oracle相关进程
[root@hisdb1 ~]# exportPATH=$PATH:/u01/app/19.3.0/grid/OPatch
[root@hisdb1 ~]# opatchauto apply/patch/33509923
OPatchauto session is initiated at SatApr 2 22:37:47 2022
System initialization log file is/u01/app/19.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2022-04-02_10-37-50PM.log.
Session log file is/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2022-04-02_10-38-08PM.log
The id for this session is 6Z21
Executing OPatch prereq operations toverify patch applicability on home /u01/app/oracle/product/19.3.0/db
Executing OPatch prereq operations toverify patch applicability on home /u01/app/19.3.0/grid
Patch applicability verifiedsuccessfully on home /u01/app/19.3.0/grid
Patch applicability verifiedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Executing patch validation checks onhome /u01/app/19.3.0/grid
Patch validation checks successfullycompleted on home /u01/app/19.3.0/grid
Executing patch validation checks onhome /u01/app/oracle/product/19.3.0/db
Patch validation checks successfullycompleted on home /u01/app/oracle/product/19.3.0/db
Verifying SQL patch applicability onhome /u01/app/oracle/product/19.3.0/db
SQL patch applicability verifiedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Preparing to bring down database serviceon home /u01/app/oracle/product/19.3.0/db
Successfully prepared home/u01/app/oracle/product/19.3.0/db to bring down database service
Performing prepatch operations on CRS -bringing down CRS service on home /u01/app/19.3.0/grid
Prepatch operation log file location:/u01/app/grid/crsdata/hisdb1/crsconfig/crs_prepatch_apply_inplace_hisdb1_2022-04-02_10-39-51PM.log
CRS service brought down successfully onhome /u01/app/19.3.0/grid
Performing prepatch operation on home/u01/app/oracle/product/19.3.0/db
Perpatch operation completedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Start applying binary patch on home/u01/app/oracle/product/19.3.0/db
Binary patch applied successfully onhome /u01/app/oracle/product/19.3.0/db
Performing postpatch operation on home/u01/app/oracle/product/19.3.0/db
Postpatch operation completedsuccessfully on home /u01/app/oracle/product/19.3.0/db
Start applying binary patch on home/u01/app/19.3.0/grid
Binary patch applied successfully onhome /u01/app/19.3.0/grid
Performing postpatch operations on CRS -starting CRS service on home /u01/app/19.3.0/grid
Postpatch operation log file location:/u01/app/grid/crsdata/hisdb1/crsconfig/crs_postpatch_apply_inplace_hisdb1_2022-04-02_10-59-46PM.log
CRS service started successfully on home/u01/app/19.3.0/grid
Preparing home/u01/app/oracle/product/19.3.0/db after database service restarted
No step execution required.........
Trying to apply SQL patch on home/u01/app/oracle/product/19.3.0/db
SQL patch applied successfully on home/u01/app/oracle/product/19.3.0/db
OPatchAuto successful.
--------------------------------Summary--------------------------------
Patching is completed successfully.Please find the summary as follows:
Host:hisdb1
RACHome:/u01/app/oracle/product/19.3.0/db
Version:19.0.0.0.0
Summary:
==Following patches were SKIPPED:
Patch: /patch/33509923/33534448
Reason: This patch is not applicable tothis specified target type - "rac_database"
Patch: /patch/33509923/33575402
Reason: This patch is not applicable tothis specified target type - "rac_database"
Patch: /patch/33509923/33239955
Reason: This patch is not applicable tothis specified target type - "rac_database"
==Following patches were SUCCESSFULLYapplied:
Patch: /patch/33509923/33515361
Log:/u01/app/oracle/product/19.3.0/db/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_22-41-01PM_1.log
Patch: /patch/33509923/33529556
Log:/u01/app/oracle/product/19.3.0/db/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_22-41-01PM_1.log
Host:hisdb1
CRS Home:/u01/app/19.3.0/grid
Version:19.0.0.0.0
Summary:
==Following patches were SUCCESSFULLYapplied:
Patch: /patch/33509923/33239955
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_22-49-32PM_1.log
Patch: /patch/33509923/33515361
Log:/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_22-49-32PM_1.log
Patch: /patch/33509923/33529556
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_22-49-32PM_1.log
Patch: /patch/33509923/33534448
Log:/u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_22-49-32PM_1.log
Patch: /patch/33509923/33575402
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2022-04-02_22-49-32PM_1.log
OPatchauto session completed at SatApr 2 23:02:43 2022
Time taken to complete the session 24minutes, 56 seconds
2.9、检查 grid 补丁[grid@hisdb1:/home/grid]$ opatch lspatches
33575402;DBWLM RELEASE UPDATE 19.0.0.0.0(33575402)
33534448;ACFS RELEASE UPDATE 19.14.0.0.0(33534448)
33529556;OCW RELEASE UPDATE 19.14.0.0.0(33529556)
33515361;Database Release Update :19.14.0.0.220118 (33515361)
33239955;TOMCAT RELEASE UPDATE19.0.0.0.0 (33239955)
OPatch succeeded.
[grid@hisdb1:/home/grid]$ sqlplus-version
SQL*Plus: Release 19.0.0.0.0 -Production
Version 19.14.0.0.0
2.10、加载SQL 说明：此步骤为加载变化的SQL到数据库，RAC环境只需在节点1执行。
[oracle@hisdb1:/u01/app/oracle/product/19.3.0/db/OPatch]$./datapatch -verbose
2.11、处理无效对象因之前datapatch命令会加载SQL，此过程可能会产生无效对象，执行@utlrp.sql脚本处理无效对象
SQL> @?/rdbms/admin/utlrp.sql
2.12、查看版本变化SQL> col status for a10
SQL> col action for a10
SQL> col action_time for a30
SQL> col desctiption for a60
SQL> set line 200
SQL> selectpatch_id,patch_type,action,status,action_time,description fromdba_registry_sqlpatch;
PATCH_ID PATCH_TYPE ACTION STATUS ACTION_TIME DESCRIPTION
---------- ---------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------
29517242 RU APPLY SUCCESS 21-MAR-22 11.30.57.513467 AM Database Release Update : 19.3.0.0.190416(29517242)
31771877 RU APPLY SUCCESS 02-APR-22 05.11.09.823188 PM Database Release Update : 19.9.0.0.201020 (31771877)
33515361 RU APPLY SUCCESS 02-APR-22 11.47.50.965595 PM Database Release Update : 19.14.0.0.220118 (33515361)
SQL> col version for a25
SQL> col comments for a80
SQL> selectaction_time,version,comments from dba_registry_history;
ACTION_TIME VERSION COMMENTS
------------------------------------------------------- --------------------------------------------------------------------------------
19 RDBMS_19.14.0.0.0DBRU_LINUX.X64_211224.3
21-MAR-22 11.30.52.564764 AM 19.0.0.0.0 Patch applied on 19.3.0.0.0:Release_Update - 190410122720
02-APR-22 05.10.27.383267 PM 19.0.0.0.0 Patch applied from 19.3.0.0.0to 19.9.0.0.0: Release_Update - 200930183249
02-APR-2211.46.56.626133 PM 19.0.0.0.0 Patch applied from 19.9.0.0.0to 19.14.0.0.0: Release_Update – 211225122123
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |