【PSU】Oracle19c 单机升级最新Oracle RU-19.11

1.1 检查环境

1 检查OPatch版本
[oracle@oracle19c ~]$ opatch version
OPatch Version: 12.2.0.1.17

2 检查数据库软件版本
[oracle@oracle19c ~]$ opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)

OPatch succeeded.

3 检查数据库补丁情况

SET PAGESIZE 1000
SET SERVEROUT ON
sys@ORCL 22:16:50> SET LONG 2000000
 COLUMN action_time FORMAT A12
 COLUMN action FORMAT A10
 COLUMN comments FORMAT A30
 COLUMN description FORMAT A60
 COLUMN namespace FORMAT A20
 COLUMN status FORMAT A10N status FORMAT A10

SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
action,
status,
description,
patch_id
FROM sys.dba_registry_sqlpatch
  7  ORDER by action_time;

ACTION_TIME  ACTION     STATUS     DESCRIPTION                                                    PATCH_ID
------------ ---------- ---------- ------------------------------------------------------------ ----------
2021-07-17   APPLY      SUCCESS    Database Release Update : 19.3.0.0.190416 (29517242)           29517242

sys@ORCL 22:16:52>

4 检查组件情况

col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
sys@ORCL 22:17:46> select comp_id,comp_name,version,status from dba_registry;

COMP_ID    COMP_NAME                             VERSION     STATUS
---------- ------------------------------------- ----------- ----------
CATALOG    Oracle Database Catalog Views         19.0.0.0.0  VALID
CATPROC    Oracle Database Packages and Types    19.0.0.0.0  VALID
RAC        Oracle Real Application Clusters      19.0.0.0.0  OPTION OFF
JAVAVM     JServer JAVA Virtual Machine          19.0.0.0.0  VALID
XML        Oracle XDK                            19.0.0.0.0  VALID
CATJAVA    Oracle Database Java Packages         19.0.0.0.0  VALID
APS        OLAP Analytic Workspace               19.0.0.0.0  VALID
XDB        Oracle XML Database                   19.0.0.0.0  VALID
OWM        Oracle Workspace Manager              19.0.0.0.0  VALID
CONTEXT    Oracle Text                           19.0.0.0.0  VALID
ORDIM      Oracle Multimedia                     19.0.0.0.0  VALID
SDO        Spatial                               19.0.0.0.0  VALID
XOQ        Oracle OLAP API                       19.0.0.0.0  VALID
OLS        Oracle Label Security                 19.0.0.0.0  VALID
DV         Oracle Database Vault                 19.0.0.0.0  VALID

15 rows selected.

sys@ORCL 22:17:47>

1.2 升级最新的OPatch补丁

[oracle@oracle19c ~]$ 
[oracle@oracle19c ~]$ opatch version
OPatch Version: 12.2.0.1.17

[oracle@oracle19c sw]$ unzip p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME
[oracle@oracle19c sw]$ opatch version
OPatch Version: 12.2.0.1.25
OPatch succeeded.

1.3 解压缩软件

软件大概1.3G
unzip p32545013_190000_Linux-x86-64.zip
补丁解压缩后大概2.7G

1.4 检查Patch 是否冲突

[oracle@oracle19c ru]$ cd 32545013/
[oracle@oracle19c 32545013]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.25
Copyright (c) 2021, 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.25
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2021-07-19_22-27-03PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

1.5 应用补丁

1.如果是物理DG，那么主备库都需要安装Patch，可以先在备库安装，再安装主库，操作步骤可以参考：Document 278641.1。

2.对于RAC 环境，可以使用OPatch rolling 方式来安装，这样没有停机时间，具体操作可以参考：Document 244241.1。

3.对于单实例，必须关闭待升级ORACLE HOME关联的所有实例和监听，并且包括退出所有的sqlplus窗口。

1.5.1 先关闭数据库和监听

[oracle@oracle19c 32545013]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 19 22:28:22 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

sys@ORCL 22:28:23> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
sys@ORCL 22:28:25> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL 22:29:01>
sys@ORCL 22:29:04> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oracle19c 32545013]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-JUL-2021 22:29:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19c)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

1.5.2 应用补丁

[oracle@oracle19c 32545013]$ pwd
/home/oracle/sw/ru/32545013
[oracle@oracle19c 32545013]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.25
Copyright (c) 2021, 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.25
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2021-07-19_22-30-00PM_1.log

Verifying environment and performing prerequisite checks...

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

Oracle Interim Patch Installer version 12.2.0.1.25
Copyright (c) 2021, 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.25
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2021-07-19_22-31-18PM_1.log

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

Do you want to proceed? [y|n]
y
User Responded with: Y
........
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 32545013 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [32545013].
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/opatch2021-07-19_22-47-56PM_1.log

OPatch succeeded.

1.6 加载变化的SQL到数据库

sqlplus /nolog
SQL> Connect / as sysdba
SQL> startup
SQL> alter pluggable database all open;
SQL> quit
cd $ORACLE_HOME/OPatch
./datapatch -verbose

[oracle@oracle19c OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.11.0.0.0 Production on Mon Jul 19 23:17:35 2021
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_11516_2021_07_19_23_17_35/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...

升级用普通的移动硬盘需要40分钟

1.7 查看版本变化

datapatch 工具加载修改的SQL之后会将patch添加到dba_registry_sqlpatch试图中，以反应patch的应用情况。 查询试图可以看到patch的APPLY状态为”SUCCESS”
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;
SQL> col version for a25
SQL> col comments for a80
SQL> select ACTION_TIME,VERSION,COMMENTS from dba_registry_history;

opatch lspathes

  col status for a10
 col action for a10
 col action_time for a30
 col description for a60
 select patch_id,patch_type,action,status,action_time,description from dba_registry_sqlpatch;
 col version for a25
 col comments for a80

  PATCH_ID PATCH_TYPE ACTION     STATUS     ACTION_TIME                    DESCRIPTION
---------- ---------- ---------- ---------- ------------------------------ ------------------------------------------------------------
  29517242 RU         APPLY      SUCCESS    17-JUL-21 05.07.46.904032 PM   Database Release Update : 19.3.0.0.190416 (29517242)
  32545013 RU         APPLY      SUCCESS    19-JUL-21 11.53.14.450395 PM   Database Release Update : 19.11.0.0.210420 (32545013)

sys@ORCL 23:55:33> sys@ORCL 23:55:33> sys@ORCL 23:55:33>  select ACTION_TIME,VERSION,COMMENTS from dba_registry_history;

ACTION_TIME                    VERSION                   COMMENTS
------------------------------ ------------------------- --------------------------------------------------------------------------------
                               19                        RDBMS_19.11.0.0.0DBRU_LINUX.X64_210412
17-JUL-21 05.07.39.117079 PM   19.0.0.0.0                Patch applied on 19.3.0.0.0: Release_Update - 190410122720
19-JUL-21 11.32.16.881097 PM   19.0.0.0.0                Patch applied from 19.3.0.0.0 to 19.11.0.0.0: Release_Update - 210413004009

sys@ORCL 23:55:34>

[oracle@oracle19c OPatch]$ opatch lspatches
32545013;Database Release Update : 19.11.0.0.210420 (32545013)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.

1.8 编译失效对象

@?/rdbms/admin/utlrp.sql