本帖最后由 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认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2022-4-8 17:43:48
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对