CNDBA社区
19.16 DB的RU 编号是：34133642，GI的RU编号是： 34130714。
1.查看当前环境
[oracle@db02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 26 21:09:54 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HBHE READ WRITE NO
2.查看OPatch版本
[root@db02 ~]# su - oracle
Last login: Tue Jul 26 21:09:52 CST 2022 on pts/1
[oracle@db02 ~]$ cd $ORACLE_HOME/OPatch
[oracle@db02 OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.29
OPatch succeeded.
3.检查Patch 是否冲突
[root@db02 tmp]# unzip p34133642_190000_Linux-x86-64.zip
[root@db02 tmp]# chown -R oracle.oinstall 34133642/
[root@db02 tmp]# su - oracle
Last login: Tue Jul 26 21:12:09 CST 2022 on pts/2
[oracle@db02 ~]$ cd /tmp/34133642/
[oracle@db02 34133642]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.29
Copyright (c) 2022, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/19.3.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.3.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.29
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-07-26_21-17-04PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
应用patch
这里有几点注意事项：
1.如果是物理DG，那么主备库都需要安装Patch，可以先在备库安装，再安装主库，操作步骤可以参考：Document 278641.1。
2.对于RAC 环境，可以使用OPatch rolling 方式来安装，这样没有停机时间，具体操作可以参考：Document 244241.1。
3.对于单实例，必须关闭待升级ORACLE HOME关联的所有实例和监听，并且包括退出所有的sqlplus窗口。
关闭实例和监听，并退出sqlplus。
[oracle@db02 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2022 21:18:42
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db02)(PORT=1521)))
The command completed successfully
[oracle@db02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 26 21:18:47 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
升级Opatch，19.16最低要求Opatch为12.2.0.1.30 or later
[oracle@db01 32545013]$ cd $ORACLE_HOME/OPatch/
[oracle@db01 OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.17
OPatch succeeded.
[oracle@db01 32545013]$ cd $ORACLE_HOME
[oracle@db01 dbhome_1]$ mv OPatch/ OPatchbak
[oracle@db01 dbhome_1]$ unzip /tmp/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME
[oracle@db01 OPatch]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/OPatch
[oracle@db01 OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.32
OPatch succeeded.
应用patch：
[oracle@db02 34133642]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.32
Copyright (c) 2022, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/19.3.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.3.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.32
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-07-26_21-40-21PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 34133642
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.3.0/dbhome_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '34133642' to OH '/u01/app/oracle/product/19.3.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.bali.jewt, 11.1.1.6.0...
Patching component oracle.bali.ewt, 11.1.1.6.0...
Patching component oracle.help.ohj, 11.1.1.7.0...
Patching component oracle.perlint, 5.28.1.0.0...
Patching component oracle.rdbms.locator, 19.0.0.0.0...
Patching component oracle.perlint.expat, 2.0.1.0.4...
Patching component oracle.rdbms.rsf, 19.0.0.0.0...
Patching component oracle.rdbms, 19.0.0.0.0...
Patching component oracle.rdbms.util, 19.0.0.0.0...
Patching component oracle.assistants.acf, 19.0.0.0.0...
Patching component oracle.assistants.deconfig, 19.0.0.0.0...
Patching component oracle.assistants.server, 19.0.0.0.0...
Patching component oracle.buildtools.rsf, 19.0.0.0.0...
Patching component oracle.ctx, 19.0.0.0.0...
Patching component oracle.dbdev, 19.0.0.0.0...
Patching component oracle.dbjava.ic, 19.0.0.0.0...
Patching component oracle.dbjava.jdbc, 19.0.0.0.0...
Patching component oracle.dbjava.ucp, 19.0.0.0.0...
Patching component oracle.duma, 19.0.0.0.0...
Patching component oracle.javavm.client, 19.0.0.0.0...
Patching component oracle.ldap.owm, 19.0.0.0.0...
Patching component oracle.ldap.rsf, 19.0.0.0.0...
Patching component oracle.ldap.security.osdt, 19.0.0.0.0...
Patching component oracle.marvel, 19.0.0.0.0...
Patching component oracle.network.rsf, 19.0.0.0.0...
Patching component oracle.odbc.ic, 19.0.0.0.0...
Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patching component oracle.precomp.common.core, 19.0.0.0.0...
Patching component oracle.rdbms.crs, 19.0.0.0.0...
Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...
Patching component oracle.rdbms.deconfig, 19.0.0.0.0...
Patching component oracle.rdbms.oci, 19.0.0.0.0...
Patching component oracle.rhp.db, 19.0.0.0.0...
Patching component oracle.sdo, 19.0.0.0.0...
Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...
Patching component oracle.sqlplus, 19.0.0.0.0...
Patching component oracle.sqlplus.ic, 19.0.0.0.0...
Patching component oracle.wwg.plsql, 19.0.0.0.0...
Patching component oracle.ctx.rsf, 19.0.0.0.0...
Patching component oracle.ons, 19.0.0.0.0...
Patching component oracle.rdbms.rman, 19.0.0.0.0...
Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...
Patching component oracle.rdbms.scheduler, 19.0.0.0.0...
Patching component oracle.dbtoolslistener, 19.0.0.0.0...
Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...
Patching component oracle.rdbms.install.common, 19.0.0.0.0...
Patching component oracle.ons.ic, 19.0.0.0.0...
Patching component oracle.install.deinstalltool, 19.0.0.0.0...
Patching component oracle.oraolap, 19.0.0.0.0...
Patching component oracle.javavm.server, 19.0.0.0.0...
Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...
Patching component oracle.rdbms.hs_common, 19.0.0.0.0...
Patching component oracle.precomp.rsf, 19.0.0.0.0...
Patching component oracle.xdk.rsf, 19.0.0.0.0...
Patching component oracle.ldap.client, 19.0.0.0.0...
Patching component oracle.odbc, 19.0.0.0.0...
Patching component oracle.oraolap.api, 19.0.0.0.0...
Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...
Patching component oracle.ctx.atg, 19.0.0.0.0...
Patching component oracle.rdbms.drdaas, 19.0.0.0.0...
Patching component oracle.mgw.common, 19.0.0.0.0...
Patching component oracle.xdk.xquery, 19.0.0.0.0...
Patching component oracle.rdbms.dv, 19.0.0.0.0...
Patching component oracle.xdk.parser.java, 19.0.0.0.0...
Patching component oracle.ovm, 19.0.0.0.0...
Patching component oracle.xdk, 19.0.0.0.0...
Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...
Patching component oracle.network.client, 19.0.0.0.0...
Patching component oracle.rdbms.lbac, 19.0.0.0.0...
Patching component oracle.ldap.ssl, 19.0.0.0.0...
Patching component oracle.sdo.locator, 19.0.0.0.0...
Patching component oracle.network.listener, 19.0.0.0.0...
Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...
Patching component oracle.precomp.common, 19.0.0.0.0...
Patching component oracle.precomp.lang, 19.0.0.0.0...
Patching component oracle.jdk, 1.8.0.201.0...
Patch 34133642 successfully applied.
Sub-set patch [33806152] has become inactive due to the application of a super-set patch [34133642].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-07-26_21-40-21PM_1.log
OPatch succeeded.
5.加载变化的SQL到数据库
安装补丁之后，还需要将有变化的SQL加载到数据库中，这里可以直接运行Datapatch工具将这些修改的SQL重新加载到数据库中，如果是RAC环境，只需要在一个节点执行就可以了。
[oracle@db02 OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 26 21:51:58 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2516581456 bytes
Fixed Size 9141328 bytes
Variable Size 1140850688 bytes
Database Buffers 1342177280 bytes
Redo Buffers 24412160 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@db02 OPatch]$ cd $ORACLE_HOME/OPatch
[oracle@db02 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.16.0.0.0 Production on Tue Jul 26 21:53:38 2022
Copyright (c) 2012, 2022, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_8842_2022_07_26_21_53_38/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)
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.16.0.0.0 Release_Update 220703022223: Installed
PDB CDB$ROOT:
Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 20-APR-22 11.23.30.925558 PM
PDB HBHE:
Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 20-APR-22 11.23.36.653674 PM
PDB PDB$SEED:
Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 20-APR-22 11.23.33.783837 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED HBHE
No interim patches need to be rolled back
Patch 34133642 (Database Release Update : 19.16.0.0.220719 (34133642)):
Apply from 19.15.0.0.0 Release_Update 220331125408 to 19.16.0.0.0 Release_Update 220703022223
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...done
Patch 34133642 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34133642/24865470/34133642_apply_ORCL_CDBROOT_2022Jul26_21_55_27.log (no errors)
Patch 34133642 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34133642/24865470/34133642_apply_ORCL_PDBSEED_2022Jul26_22_00_10.log (no errors)
Patch 34133642 apply (pdb HBHE): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34133642/24865470/34133642_apply_ORCL_HBHE_2022Jul26_22_00_10.log (no errors)
Automatic recompilation incomplete; run utlrp.sql to revalidate.
PDBs: HBHE PDB$SEED
SQL Patching tool complete on Tue Jul 26 22:03:43 2022
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select patch_id,patch_type,action,status,action_time,description from dba_registry_sqlpatch;
PATCH_ID PATCH_TYPE ACTION STATUS ACTION_TIME DESCRIPTION
---------- ------------------------------ --------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
29517242 RU APPLY SUCCESS 12-SEP-21 06.02.25.516877 PM Database Release Update : 19.3.0.0.190416 (29517242)
32904851 RU APPLY SUCCESS 04-OCT-21 04.59.15.387062 PM Database Release Update : 19.12.0.0.210720 (32904851)
33515361 RU APPLY SUCCESS 15-FEB-22 10.22.03.106989 PM Database Release Update : 19.14.0.0.220118 (33515361)
33806152 RU APPLY SUCCESS 20-APR-22 11.23.30.925558 PM Database Release Update : 19.15.0.0.220419 (33806152)
34133642 RU APPLY SUCCESS 26-JUL-22 10.03.20.054932 PM Database Release Update : 19.16.0.0.220719 (34133642)
SQL> select ACTION_TIME,VERSION,COMMENTS from dba_registry_history;
ACTION_TIME VERSION COMMENTS
--------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
19 RDBMS_19.16.0.0.0DBRU_LINUX.X64_220701
12-SEP-21 06.02.15.886321 PM 19.0.0.0.0 Patch applied on 19.3.0.0.0: Release_Update - 190410122720
04-OCT-21 04.49.54.419451 PM 19.0.0.0.0 Patch applied from 19.3.0.0.0 to 19.12.0.0.0: Release_Update - 210716141810
15-FEB-22 10.16.31.981827 PM 19.0.0.0.0 Patch applied from 19.12.0.0.0 to 19.14.0.0.0: Release_Update - 211225122123
20-APR-22 11.19.21.191203 PM 19.0.0.0.0 Patch applied from 19.14.0.0.0 to 19.15.0.0.0: Release_Update - 220331125408
26-JUL-22 09.59.04.606843 PM 19.0.0.0.0 Patch applied from 19.15.0.0.0 to 19.16.0.0.0: Release_Update - 220703022223
6 rows selected.
处理无效对象
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-07-26 22:06:51
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 2022-07-26 22:06:54
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
-------------------
1
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.
版权声明：本文为博主原创文章，未经博主允许不得转载。
oracle
- 上一篇：AIX下安装SVN
- 下一篇：DB2 客户机自动重新路由的描述和设置
