签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19c 单实例 RU 从19.12.0 升级到19.14.0

2022-02-15 22:29 1664 0 原创 oracle
作者： hbhe0316
本文链接：http://www.cndba.cn/hbhe0316/article/106672

1.查看当前DB环境

http://www.cndba.cn/hbhe0316/article/106672 

[oracle@db02 ~]$ cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 7.9 (Maipo)
[oracle@db02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 21:37:59 2022
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> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HBHE                           READ WRITE NO

2.从MOS下载19.14 的RU 补丁
19.14 DB的RU 编号是：33515361，GI的RU编号是： 33509923http://www.cndba.cn/hbhe0316/article/106672

3.安装OPatch
要安装19.14的RU，OPatch 工具的版本必须大于 12.2.0.1.28 。 最新版本的OPatch 可以从MOS上下载，补丁号是：patch 6880880 。
详见文档:https://www.cndba.cn/hbhe0316/article/4706

http://www.cndba.cn/hbhe0316/article/106672
http://www.cndba.cn/hbhe0316/article/106672http://www.cndba.cn/hbhe0316/article/106672

4.检查Patch 是否冲突http://www.cndba.cn/hbhe0316/article/106672

[oracle@db02 tmp]$ unzip p33515361_190000_Linux-x86-64.zip
[oracle@db02 tmp]$ cd 33515361/
[oracle@db02 33515361]$ pwd
/tmp/33515361
[oracle@db02 33515361]$ $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-02-15_21-52-57PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

5.应用patch
关闭实例和监听，并退出sqlplus。

[oracle@db02 33515361]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-FEB-2022 21:54:10

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db02)(PORT=1521)))
The command completed successfully

[oracle@db02 33515361]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 21:54:35 2022
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> 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.12.0.0.0

6.应用patch：

[oracle@db02 33515361]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.29
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.29
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-02-15_21-56-13PM_1.log

Verifying environment and performing prerequisite checks...

--------------------------------------------------------------------------------
Start OOP by Prereq process.
Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.29
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.29
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-02-15_21-57-12PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   33515361  

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 '33515361' 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.options.olap.api, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.options.olap, 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.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.util, 19.0.0.0.0...

Patching component oracle.rdbms, 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.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.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.rdbms.crs, 19.0.0.0.0...

Patching component oracle.rdbms.drdaas, 19.0.0.0.0...

Patching component oracle.xdk.xquery, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...

Patching component oracle.odbc, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.lbac, 19.0.0.0.0...

Patching component oracle.oraolap.api, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

Patching component oracle.ldap.ssl, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.mgw.common, 19.0.0.0.0...

Patching component oracle.ldap.client, 19.0.0.0.0...

Patching component oracle.ons.ic, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...

Patching component oracle.ctx.rsf, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.rdbms.install.common, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.rdbms.hs_common, 19.0.0.0.0...

Patching component oracle.dbtoolslistener, 19.0.0.0.0...

Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 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 33515361 successfully applied.
Sub-set patch [32904851] has become inactive due to the application of a super-set patch [33515361].
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-02-15_21-57-12PM_1.log

OPatch succeeded.

7.加载变化的SQL到数据库
安装补丁之后，还需要将有变化的SQL加载到数据库中，这里可以直接运行Datapatch工具将这些修改的SQL重新加载到数据库中，如果是RAC环境，只需要在一个节点执行就可以了。http://www.cndba.cn/hbhe0316/article/106672

[oracle@db02 33515361]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 22:10:03 2022
Version 19.14.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            1224736768 bytes
Database Buffers         1275068416 bytes
Redo Buffers                7634944 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.14.0.0.0

8.运行datapatch工具：在我虚拟机上执行了大约 15 分钟。http://www.cndba.cn/hbhe0316/article/106672

http://www.cndba.cn/hbhe0316/article/106672 

[oracle@db02 33515361]$ cd $ORACLE_OPatch
[oracle@db02 ~]$ ./datapatch -verbose
-bash: ./datapatch: No such file or directory
[oracle@db02 ~]$ cd $ORACLE_HOME/OPatch
[oracle@db02 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.14.0.0.0 Production on Tue Feb 15 22:11:43 2022
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_11128_2022_02_15_22_11_43/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.14.0.0.0 Release_Update 211225122123: Installed
  PDB CDB$ROOT:
    Applied 19.12.0.0.0 Release_Update 210716141810 successfully on 04-OCT-21 04.59.15.387062 PM
  PDB HBHE:
    Applied 19.12.0.0.0 Release_Update 210716141810 successfully on 04-OCT-21 04.59.39.076329 PM
  PDB PDB$SEED:
    Applied 19.12.0.0.0 Release_Update 210716141810 successfully on 04-OCT-21 04.59.27.258491 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 33515361 (Database Release Update : 19.14.0.0.220118 (33515361)):
      Apply from 19.12.0.0.0 Release_Update 210716141810 to 19.14.0.0.0 Release_Update 211225122123
    No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 3

Validating logfiles...done
Patch 33515361 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33515361/24589353/33515361_apply_ORCL_CDBROOT_2022Feb15_22_13_17.log (no errors)
Patch 33515361 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33515361/24589353/33515361_apply_ORCL_PDBSEED_2022Feb15_22_17_43.log (no errors)
Patch 33515361 apply (pdb HBHE): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33515361/24589353/33515361_apply_ORCL_HBHE_2022Feb15_22_17_43.log (no errors)
SQL Patching tool complete on Tue Feb 15 22:22:41 2022

9.查看版本变化

[oracle@db02 OPatch]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 22:26:52 2022
Version 19.14.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.14.0.0.0

SQL> col status for a10
SQL>  col action for a10
SQL>  col action_time for a30
SQL> col description for a60
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)

  PATCH_ID PATCH_TYPE                     ACTION     STATUS
---------- ------------------------------ ---------- ----------
ACTION_TIME
------------------------------
DESCRIPTION
------------------------------------------------------------

  33515361 RU                             APPLY      SUCCESS
15-FEB-22 10.22.03.106989 PM
Database Release Update : 19.14.0.0.220118 (33515361)
SQL> select ACTION_TIME,VERSION,COMMENTS from dba_registry_history;

ACTION_TIME
------------------------------
VERSION
--------------------------------------------------------------------------------
COMMENTS
--------------------------------------------------------------------------------

19
RDBMS_19.14.0.0.0DBRU_LINUX.X64_211224.3

12-SEP-21 06.02.15.886321 PM
19.0.0.0.0
Patch applied on 19.3.0.0.0: Release_Update - 190410122720

ACTION_TIME
------------------------------
VERSION
--------------------------------------------------------------------------------
COMMENTS
--------------------------------------------------------------------------------

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

ACTION_TIME
------------------------------
VERSION
--------------------------------------------------------------------------------
COMMENTS
--------------------------------------------------------------------------------
Patch applied from 19.12.0.0.0 to 19.14.0.0.0: Release_Update - 211225122123

10.处理无效对象http://www.cndba.cn/hbhe0316/article/106672

SQL> @?/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2022-02-15 22:28: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-02-15 22:28:52

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.

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
hbhe0316

hbhe0316

关注

1.只有承认无知，才能装下新的东西； 2.进步来自一点点滴滴的积累； 3.广博让你更优秀，而专业让你无法替代； 4.挫折和失败能够转换为一种财富。

  • 889
    原创
  • 1
    翻译
  • 13
    转载
  • 24
    评论
  • 访问：1039147次
  • 积分：1523
  • 等级：核心会员
  • 排名：第6名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ