Oracle 19c RAC 19.3 升级到19.12
1.查看当前环境
[grid@rac01 ~]$ crsctl query crs softwareversion
Oracle Clusterware version on node [rac01] is [19.0.0.0.0]
[grid@rac01 ~]$ crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [19.0.0.0.0]
[grid@rac01 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[grid@rac01 ~]$ /u01/app/oracle/product/19.3.0/db_1/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517247;ACFS RELEASE UPDATE 19.3.0.0.0 (29517247)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)
29401763;TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)
OPatch succeeded.
2.升级OPatch,两边都需要升级
升级oracle用户OPatch
[oracle@rac01 32545013]$ cd $ORACLE_HOME/OPatch/
[oracle@rac01 OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.17
OPatch succeeded.
[oracle@rac01 32545013]$ cd $ORACLE_HOME
[oracle@rac01 dbhome_1]$ mv OPatch/ OPatchbak
[oracle@rac01 dbhome_1]$ unzip /tmp/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME
[oracle@rac01 OPatch]$ cd /u01/app/oracle/product/19.3.0/db_1/OPatch
[oracle@rac01 OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.27
OPatch succeeded.
升级grid用户OPatch
[root@rac01 grid]# cd /u01/app/19.3.0/grid
[root@rac01 grid]# mv OPatch OPatchbak
[root@rac01 grid]# unzip /tmp/p6880880_190000_Linux-x86-64.zip
[root@rac01 grid]# chown -R grid.oinstall OPatch
[root@rac01 grid]# cd OPatch
[root@rac01 OPatch]# ./opatch version
OPatch Version: 12.2.0.1.27
OPatch succeeded.
3.验证Oracle Inventory的有效性
[grid@rac01 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
[oracle@rac01 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
4.查看19.12 RU包
p32895426_190000_Linux-x86-64.zip为grid的升级包
p32904851_190000_Linux-x86-64.zip为oracle的升级包
root[@rac01 tmp]# chown -R grid.oinstall 32895426
root[@rac02 tmp]# chown -R grid.oinstall 32895426
5.检查Patch 是否冲突
[grid@rac01 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/32904851/
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2021, 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.17
OUI version : 12.2.0.7.0
Log file location : /u01/app/19.3.0/grid/cfgtoollogs/opatch/opatch2021-09-21_10-48-33AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
[oracle@rac01 tmp]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp/32904851/
Oracle Interim Patch Installer version 12.2.0.1.27
Copyright (c) 2021, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/19.3.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.3.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.27
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatch/opatch2021-09-21_10-53-28AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
查看空间是否满足
[grid@rac01 tmp]$ cat /tmp/patch_list_gihome.txt
/tmp/32895426/32585572
/tmp/32895426/32904851
/tmp/32895426/32915586
/tmp/32895426/32916816
/tmp/32895426/32918050
[grid@rac01 32585572]$ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2021, 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.17
OUI version : 12.2.0.7.0
Log file location : /u01/app/19.3.0/grid/cfgtoollogs/opatch/opatch2021-09-21_10-58-04AM_1.log
Invoking prereq "checksystemspace"
6.安装前分析
在analyze期间，有一个错误，提示没有fuser命令，所以需要安装psmisc补丁包。
[root@rac01 grid]# yum install -y psmisc
[root@rac01 ~]# /u01/app/19.3.0/grid/OPatch/opatchauto apply /tmp/32895426/ -analyze
OPatchauto session is initiated at Tue Sep 21 12:40:11 2021
System initialization log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2021-09-21_12-40-18PM.log.
Session log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2021-09-21_12-40-48PM.log
The id for this session is SQ3G
Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.3.0/grid
Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.3.0/db_1
Patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1
Patch applicability verified successfully on home /u01/app/19.3.0/grid
Executing patch validation checks on home /u01/app/19.3.0/grid
Patch validation checks successfully completed on home /u01/app/19.3.0/grid
Executing patch validation checks on home /u01/app/oracle/product/19.3.0/db_1
Patch validation checks successfully completed on home /u01/app/oracle/product/19.3.0/db_1
Verifying SQL patch applicability on home /u01/app/oracle/product/19.3.0/db_1
SQL patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1
OPatchAuto successful.
--------------------------------Summary--------------------------------
Analysis for applying patches has completed successfully:
Host:rac01
RAC Home:/u01/app/oracle/product/19.3.0/db_1
Version:19.0.0.0.0
==Following patches were SKIPPED:
Patch: /tmp/32895426/32915586
Reason: This patch is not applicable to this specified target type - "rac_database"
Patch: /tmp/32895426/32585572
Reason: This patch is not applicable to this specified target type - "rac_database"
Patch: /tmp/32895426/32918050
Reason: This patch is not applicable to this specified target type - "rac_database"
==Following patches were SUCCESSFULLY analyzed to be applied:
Patch: /tmp/32895426/32916816
Log: /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_12-41-16PM_1.log
Patch: /tmp/32895426/32904851
Log: /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_12-41-16PM_1.log
Host:rac01
CRS Home:/u01/app/19.3.0/grid
Version:19.0.0.0.0
==Following patches were SUCCESSFULLY analyzed to be applied:
Patch: /tmp/32895426/32916816
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_12-41-16PM_1.log
Patch: /tmp/32895426/32915586
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_12-41-16PM_1.log
Patch: /tmp/32895426/32585572
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_12-41-16PM_1.log
Patch: /tmp/32895426/32918050
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_12-41-16PM_1.log
Patch: /tmp/32895426/32904851
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_12-41-16PM_1.log
OPatchauto session completed at Tue Sep 21 12:43:26 2021
Time taken to complete the session 3 minutes, 16 seconds
Prereq "checkSystemSpace" passed.
OPatch succeeded.
7.执行opatchauto 工具应用RU
对GI HOME 进行patch：
(1) 升级过程会自动关闭和启动集群。
(2) 先升级节点1 grid，再升级节点2 grid。
[root@rac01 grid]# cd /u01/app/19.3.0/grid/OPatch
[root@rac01 OPatch]# ./opatchauto apply /tmp/32895426/ -oh /u01/app/19.3.0/grid
OPatchauto session is initiated at Tue Sep 21 14:26:05 2021
System initialization log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2021-09-21_02-26-15PM.log.
Session log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2021-09-21_02-26-32PM.log
The id for this session is 93CM
Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.3.0/grid
Patch applicability verified successfully on home /u01/app/19.3.0/grid
Executing patch validation checks on home /u01/app/19.3.0/grid
Patch validation checks successfully completed on home /u01/app/19.3.0/grid
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/rac01/crsconfig/crs_prepatch_apply_inplace_rac01_2021-09-21_02-27-44PM.log
CRS service brought down successfully on home /u01/app/19.3.0/grid
Start applying binary patch on home /u01/app/19.3.0/grid
Binary patch applied successfully on home /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/rac01/crsconfig/crs_postpatch_apply_inplace_rac01_2021-09-21_02-48-45PM.log
CRS service started successfully on home /u01/app/19.3.0/grid
OPatchAuto successful.
--------------------------------Summary--------------------------------
Patching is completed successfully. Please find the summary as follows:
Host:rac01
CRS Home:/u01/app/19.3.0/grid
Version:19.0.0.0.0
Summary:
==Following patches were SUCCESSFULLY applied:
Patch: /tmp/32895426/32585572
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_14-30-51PM_1.log
Patch: /tmp/32895426/32904851
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_14-30-51PM_1.log
Patch: /tmp/32895426/32915586
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_14-30-51PM_1.log
Patch: /tmp/32895426/32916816
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_14-30-51PM_1.log
Patch: /tmp/32895426/32918050
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_14-30-51PM_1.log
OPatchauto session completed at Tue Sep 21 14:54:40 2021
Time taken to complete the session 28 minutes, 35 seconds
[grid@rac01 ~]$ cd /u01/app/19.3.0/grid/OPatch
[grid@rac01 OPatch]$ ./opatch lspatches
32918050;TOMCAT RELEASE UPDATE 19.0.0.0.0 (32918050)
32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)
32915586;ACFS RELEASE UPDATE 19.12.0.0.0 (32915586)
32904851;Database Release Update : 19.12.0.0.210720 (32904851)
32585572;DBWLM RELEASE UPDATE 19.0.0.0.0 (32585572)
OPatch succeeded.
8.开始升级 DB RU (RELEASE UPDATE) 补丁
说明：由于 GI RU 包含 DB RU，所以 RAC 环境升级 DB 时，还将使用此 Patch 即可。
说明: (两个节点都要执行)
(1) 升级过程会自动关闭和启动集群。
(2) 先升级节点1 database，再升级节点2 database。
[root@rac01 OPatch]# cd /u01/app/oracle/product/19.3.0/db_1/OPatch
[root@rac01 OPatch]# ./opatchauto apply /tmp/32904851/ -oh /u01/app/oracle/product/19.3.0/db_1/
OPatchauto session is initiated at Tue Sep 21 15:35:34 2021
System initialization log file is /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchautodb/systemconfig2021-09-21_03-35-47PM.log.
Session log file is /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchauto/opatchauto2021-09-21_03-36-39PM.log
The id for this session is E13M
Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.3.0/db_1
Patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1
Executing patch validation checks on home /u01/app/oracle/product/19.3.0/db_1
Patch validation checks successfully completed on home /u01/app/oracle/product/19.3.0/db_1
Verifying SQL patch applicability on home /u01/app/oracle/product/19.3.0/db_1
SQL patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1
Preparing to bring down database service on home /u01/app/oracle/product/19.3.0/db_1
Successfully prepared home /u01/app/oracle/product/19.3.0/db_1 to bring down database service
Bringing down database service on home /u01/app/oracle/product/19.3.0/db_1
Database service successfully brought down on home /u01/app/oracle/product/19.3.0/db_1
Performing prepatch operation on home /u01/app/oracle/product/19.3.0/db_1
Perpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/db_1
Start applying binary patch on home /u01/app/oracle/product/19.3.0/db_1
Binary patch applied successfully on home /u01/app/oracle/product/19.3.0/db_1
Performing postpatch operation on home /u01/app/oracle/product/19.3.0/db_1
Postpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/db_1
Starting database service on home /u01/app/oracle/product/19.3.0/db_1
Database service successfully started on home /u01/app/oracle/product/19.3.0/db_1
Preparing home /u01/app/oracle/product/19.3.0/db_1 after database service restarted
No step execution required.........
Trying to apply SQL patch on home /u01/app/oracle/product/19.3.0/db_1
SQL patch applied successfully on home /u01/app/oracle/product/19.3.0/db_1
OPatchAuto successful.
--------------------------------Summary--------------------------------
Patching is completed successfully. Please find the summary as follows:
Host:rac01
RAC Home:/u01/app/oracle/product/19.3.0/db_1
Version:19.0.0.0.0
Summary:
==Following patches were SUCCESSFULLY applied:
Patch: /tmp/32904851
Log: /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_15-37-54PM_1.log
Patching session reported following warning(s):
_________________________________________________
[WARNING] The database instance 'cxmtdb1' from '/u01/app/oracle/product/19.3.0/db_1', in host'rac01' is not running. SQL changes, if any, will not be applied.
To apply. the SQL changes, bring up the database instance and run the command manually from any one node (run as oracle).
Refer to the readme to get the correct steps for applying the sql changes.
OPatchauto session completed at Tue Sep 21 16:05:12 2021
Time taken to complete the session 29 minutes, 39 seconds
在升级rac02节点遇到问题，查看日志
[root@rac02 lib]# chmod g+w /u01/app/oraInventory/ContentsXML/oui-patch.xml
[root@rac02 lib]# cd /u01/app/oracle/product/19.3.0/db_1/inventory/
[root@rac02 inventory]# cp -r oneoffs/ oneoffs.bak
[root@rac02 inventory]# cd oneoffs
[root@rac02 oneoffs]# ls
29517242 29585399
[root@rac02 oneoffs]# rm -rf 295*
从 节点1 拷贝到 节点2: 将第一个节点的补丁oneoffs目录拷贝到第二个节点
[oracle@rac01 ~]$ cd /u01/app/oracle/product/19.3.0/db_1/inventory/
[oracle@rac01 inventory]$ cd oneoffs/
[oracle@rac01 oneoffs]$ ls
29517242 29585399 32904851
[oracle@rac01 oneoffs]$ scp -r * rac02:/u01/app/oracle/product/19.3.0/db_1/inventory/oneoffs
actions.xml 100% 98KB 25.0MB/s 00:00
inventory.xml 100% 64KB 22.6MB/s 00:00
actions.xml 100% 347 417.0KB/s 00:00
inventory.xml 100% 18KB 8.1MB/s 00:00
inventory.xml 100% 646KB 67.3MB/s 00:00
actions.xml
[root@rac02 oneoffs]# cd /u01/app/oracle/product/19.3.0/db_1/OPatch
[root@rac02 OPatch]# ./opatchauto apply /tmp/32904851/ -oh /u01/app/oracle/product/19.3.0/db_1/
OPatchauto session is initiated at Tue Sep 21 17:56:32 2021
System initialization log file is /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchautodb/systemconfig2021-09-21_05-56-36PM.log.
Session log file is /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchauto/opatchauto2021-09-21_05-56-58PM.log
The id for this session is BIV5
Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.3.0/db_1
Patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1
Verifying SQL patch applicability on home /u01/app/oracle/product/19.3.0/db_1
SQL patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1
Preparing to bring down database service on home /u01/app/oracle/product/19.3.0/db_1
Successfully prepared home /u01/app/oracle/product/19.3.0/db_1 to bring down database service
Bringing down database service on home /u01/app/oracle/product/19.3.0/db_1
No step execution required.........
Starting database service on home /u01/app/oracle/product/19.3.0/db_1
No step execution required.........
Preparing home /u01/app/oracle/product/19.3.0/db_1 after database service restarted
No step execution required.........
OPatchAuto successful.
--------------------------------Summary--------------------------------
Patching is completed successfully. Please find the summary as follows:
Host:rac02
RAC Home:/u01/app/oracle/product/19.3.0/db_1
Version:19.0.0.0.0
Summary:
==Following patches were SKIPPED:
Patch: /tmp/32904851
Log: /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2021-09-21_17-57-21PM_1.log
Reason: /tmp/32904851 is not required to be applied to oracle home /u01/app/oracle/product/19.3.0/db_1
OPatchauto session completed at Tue Sep 21 17:58:25 2021
Time taken to complete the session 1 minute, 54 seconds
[oracle@rac02 OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 21 18:02:31 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to an idle instance.
SQL> quit
Disconnected
9.更新数字字典
oracle $ /u01/app/oracle/product/19.3.0/db_1/OPatch/datapatch -verbose。==注意== ：上面说了依次打减少了停机时间，但是停机时间还是需要的，就是在这里的运行datapatch的时间。这个步骤是升级数据字典，针对整个database的数据字典，因此只需在一个节点上跑就可以了。主要注意的是，如果是cdb模式，需要 alter pluggable database all open，打开所有的pdb之后，再运行datapatch。
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 21 18:05:31 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2516581448 bytes
Fixed Size 9141320 bytes
Variable Size 1124073472 bytes
Database Buffers 1375731712 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SQL> alter session set container = hbhe;
Session altered.
SQL> alter pluggable database open;
Pluggable database altered.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
[oracle@rac01 ~]$ cd $ORACLE_HOME/OPatch
[oracle@rac01 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.12.0.0.0 Production on Tue Sep 21 18:46:40 2021
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_11678_2021_09_21_18_46_40/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Warning: PDB HBHE is in mode MOUNTED and will be skipped.
Bootstrapping registry and package to current versions...done
Determining current state... done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.12.0.0.0 Release_Update 210716141810: Installed
PDB CDB$ROOT:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 20-SEP-21 12.41.09.423918 PM
PDB PDB$SEED:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 20-SEP-21 12.55.30.682713 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED
No interim patches need to be rolled back
Patch 32904851 (Database Release Update : 19.12.0.0.210720 (32904851)):
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.12.0.0.0 Release_Update 210716141810
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 2
Validating logfiles... done
Patch 32904851 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_CXMTDB_CDBROOT_2021Sep21_18_48_08.log (no errors)
Patch 32904851 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_CXMTDB_PDBSEED_2021Sep21_19_02_32.log (no errors)
Automatic recompilation incomplete; run utlrp.sql to revalidate.
PDBs: PDB$SEED
SQL Patching tool complete on Tue Sep 21 19:15:57 2021
10 处理无效对象
[oracle@rac01 OPatch]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rac01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 21 19:17:14 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> @utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-09-21 19:17:26
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 status IN (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(*) FROM UTL_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 FROM dba_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 FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2021-09-21 19:17:49
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
著作权归作者所有
如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！
赞0Ctrl+Enter 发表
作者其他文章
评论 1 · 赞 4
评论 0 · 赞 0
评论 1 · 赞 1
评论 0 · 赞 0
评论 0 · 赞 0
添加新评论0 条评论