重庆思庄oracle技术分享-19c升级

最新推荐文章于 2024-10-12 10:03:26 发布
最新推荐文章于 2024-10-12 10:03:26 发布
阅读量679 收藏 1
点赞数
分类专栏： oracle数据库 文章标签： oracle 数据库
原文链接：http://bbs.cqsztech.com/forum.php?mod=viewthread&tid=29867&extra=page%3D1
版权
本文详细记录了在CentOS7.9系统上，Oracle数据库从19.3版本升级到19.9，再到19.14的完整步骤。包括了补丁下载、OPatch升级、补丁一致性检查、系统空间检查、补丁安装、SQL加载、无效对象处理等关键环节，并在每个步骤中提供了执行命令及可能出现的问题和解决方案。
摘要由CSDN通过智能技术生成

数据库升级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 O R A C L E H O M E [ o r a c l e @ h i s d b 1 : / t m p ] ORACLE_HOME [oracle@hisdb1:/tmp] ORACLEH​OME[oracle@hisdb1:/tmp]unzip -q -o p6880880_190000_Linux-x86-64.zip -d O R A C L E H O M E [ g r i d @ h i s d b 1 : / h o m e / g r i d ] ORACLE_HOME [grid@hisdb1:/home/grid] ORACLEH​OME[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 O R A C L E H O M E [ o r a c l e @ h i s d b 1 : / h o m e / o r a c l e ] ORACLE_HOME [oracle@hisdb1:/home/oracle] ORACLEH​OME[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 © 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]KaTeX parse error: Expected 'EOF', got '#' at position 50: …b2 ContentsXML]#̲ chown -Rgrid:o… opatchlsinventory
Oracle Interim Patch Installer version12.2.0.1.28
Copyright © 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 © 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 O R A C L E H O M E [ o r a c l e @ h i s d b 1 : / t m p ] ORACLE_HOME [oracle@hisdb1:/tmp] ORACLEH​OME[oracle@hisdb1:/tmp]unzip -q -o p6880880_122010_Linux-x86-64.zip -d O R A C L E H O M E [ g r i d @ h i s d b 1 : / h o m e / g r i d ] ORACLE_HOME [grid@hisdb1:/home/grid] ORACLEH​OME[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 O R A C L E H O M E [ o r a c l e @ h i s d b 1 : / h o m e / o r a c l e ] ORACLE_HOME [oracle@hisdb1:/home/oracle] ORACLEH​OME[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

duanweifang
关注
专栏目录
Oracle Database 19c 安装详细过程及示例
CsmbPascal的博客
09-27 298
首先，访问 Oracle 官方网站并下载适用于您操作系统的 Oracle Database 19c 安装文件。在此步骤中，选择您希望安装 Oracle Database 19c 的位置。在本文中，我将为您提供 Oracle Database 19c 的安装详细过程，并提供相应的示例源代码。如果您选择安装了示例数据库和其他组件，则需要在此步骤中对它们进行配置。根据您的需求进行选择，并单击“下一步”。选择“自定义”以进行更详细的安装设置，并单击“下一步”。在此示例中，选择“单实例数据库安装”并单击“下一步”。
Oracle技术分享 oracle 19.14升级19.15
cqszpx的博客
06-24 691
系统：CentOS7.9 环境：oracle 19.14 + rac 说明：1-9步骤两个节点均需执行 1、备份目录 [root@hisdb1 ~]# tar -zcvPf oraclebase.tar.gz /u01/app/oracle >/dev/null [root@hisdb1 ~]# tar -zcvPf oraInventory.tar.gz /u01/app/oraInventory >/dev/null [root@hisdb1 ~]# tar -zcvPf gridbase.tar.gz
Oracle 19c 19.10DBRU 最新补丁升级看这一篇就够了
JiekeXu的博客
03-21 8557
作者 | JiekeXu来源 |JiekeXu DBA之路（ID: JiekeXu_IT）大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家聊聊 Oracle 19c RU...
oracle19c 容器数据库控制文件（重建控制文件）
m0_70247753的博客
01-02 1346
如果没有控制文件的自动备份，而且丢失了所有对控制文件的备份，那么我们可以通过重建控制文件来恢复数据库。-----将控制文件备份出来变成一个跟踪文件//这里面有很多文件需要一个个去找，哪一个是刚刚备份出来的控制文件，所以我们备份控制文件之后应该第一时间把这个文件保存下来，以免后面需要的时候找不到----------------
oracle12c（12.1.0.2）原地升级oracle19c（19.3.0.0）
最新发布
weixin_61212661的博客
10-12 818
本文主要介绍oracle12c升级19c的相关步骤及注意事项
centos7.9安装oracle19c服务端实战（保姆级步骤）
usersjx的博客
09-13 1386
我使用的centos7.9+X86的环境内存4G存储50G。
Oracle Database 19c (oracle-database-ee-19c.x86_64.part1.rar)
10-16
Oracle Database 19c 是最新的长期版本，支持期限最长； 19.3 - 企业版（也包括标准版 2） 适用于Linux x86-64系统。oracle-database-ee-19c文件分割成 三个 压缩包，必须集齐 三个 文件后才能一起解压一起使用： ...
oracle-database-preinstall-19c-1.0-1.el9.x86-64.rpm
04-20
oracle-database-preinstall-19c-1.0-1.el9.x86_64.rpm 预安装RPM： 访问Oracle Linux 9的软件源，如x86对应的是: https://yum.oracle.com/repo/OracleLinux/OL9/appstream/x86_64/， 找到对应Oracle 19c的预...
oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
08-25
oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
oracle-database-preinstall-19c-1.0-2.el7.x86_64 .rpm
08-21
oracle-database-preinstall-19c-1.0-2.el7.x86_64 .rpm,这是linux版本的，win请勿下载
Oracle Database 19c (oracle-database-ee-19c.x86_64.part2.rar)
10-16
Oracle Database 19c 是最新的长期版本，支持期限最长； 19.3 - 企业版（也包括标准版 2） 适用于Linux x86-64系统。oracle-database-ee-19c文件分割成 三个 压缩包，必须集齐 三个 文件后才能一起解压一起使用： ...
p6880880_112000_MSWIN-x86-64.zip
12-13
optach 版本更新 p6880880_112000_MSWIN-x86-64.zip
检查 Oracle 版本的 7 种方法
热门推荐
allway2的博客
05-05 3万+
以下是检查您正在运行的 Oracle 数据库版本的七种方法。 他们是： V$VERSION视图。 V$INSTANCE视图。 PRODUCT_COMPONENT_VERSION视图。 SQL 开发者图形用户界面。 SQLcl工具。 SQL*Plus工具。 DBMS_DB_VERSION包裹。 下面的例子。 V$VERSION观点_ 该V$VERSION视图返回版本和组件级别信息。 例子： SELECT BANNER_FULL FROM V$VERSION; 结果： BANNE
Redhat7安装oracle 12c补丁
三朝看客
10-17 801
系统：Red Hat Enterprise Linux Server release 7.4 (Maipo) 数据库：Oracle Release 12.1.0.1.0 oracle补丁：p6880880_122010_Linux-x86-64.zip oracle补丁：p29252035_122010_Linux-x86-64.zip 补丁的过程大概如下 cat /etc/issue una...
Oracle-expdp方式升级19c问题合集
sinat_36757755的博客
09-14 4913
最近在帮客户进行Oracle11g到19c版本的升级，使用expdp导用户的方式进行迁移，在升级过程中遇到了些错误问题，把当时的问题分析过程记录了下来，分享给大家。
Oracle12081,【Oracle介质】Oracle 12C Linux x86-64 最新OPatch patch 6880880 12.2.0.1.7
weixin_29541743的博客
04-09 471
天萃荷净Linux x86-64补丁程序6880880: OPatch patch of version 12.2.0.1.7 for Oracle software releases 12.1.0.x (installer) and 12.2.0.x (AUG 2016)上次更新时间 2016-8-26 上午1:48 (8 天前)产品 Orac...
记录Oracle rac 19C（19.15）补丁升级（OPatch 33803476）操作过程
狗剩儿的记忆博客
07-20 2234
提示以下是本篇文章正文内容，下面案例可供参考。
oracle 19c rac psu补丁安装方法
weixin_42329915的博客
07-13 543
ORACLE_HOME为oracle软件安装目录(/oracle/app/product/12.2.0/db_1)为psu安装包解压路径，本次实验改目录为/soft。$GRID_HOME为grid安装目录(/oracle/grid/crs_1)在双节点执行（grid或oracle用户都可以）1.1 root用户修改属组。1节点关集群 2节点开集群。一、解压opatch工具。查看opatch当前版本。1节点执行打grid补丁。2节点打grid补丁。
oracle grid12安装,Oracle 12c RAC安装PSU(12.1.0.2.161018)
weixin_39520013的博客
04-03 351
一、准备工作1，数据库环境操作系统版本 : Redhat 6.8 x64数据库版本 : Oracle 12.1.0.2 x64 RACGrid : 12.1.0.2Oracle database: 12.1.0.22，准备内容GI PSU : p24412235_121020_Linux-x86-64.zipOPatch : p6880880_122010_Linu...
Linux环境下详细安装Oracle-19c步骤解析
3. **数据库安装**：使用`yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm`命令安装Oracle 19c企业版。这将实际安装数据库软件到指定目录。 4. **配置文件更改**：接下来，你需要编辑`/etc/init.d/...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值