使用autoupgrade 升级 ORACLE Release Update 从19.16 到 19.19

郑全

环境：
source_home=/u01/app/oracle/product/19.16.0/db_1
target_home=/u01/app/oracle/product/19.19.0/db_1

需要升级的数据库：
    NONCDB:prod
  cdb:cdb1,cdb2,orclcdb

RU版本：
   源：19.16
   目标：19.19

前提：
    在升级之前，先要把 目标的ORACLE_HOME安装好。
    autoupgrade版本要 22.4.220712 or later
    下载最新的autoupgrade 版本，具体可以参见：Doc ID 2485457.1

安装步骤：

--1.查看autoupgrade版本
[oracle@dbserver admin]$ /u01/app/oracle/product/19.19.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.19.0/db_1/rdbms/admin/autoupgrade.jar -version

build.version 23.1.230224
build.date 2023/02/24 14:53:24 -0500
build.hash a1e2990e
build.hash_date 2023/02/24 14:44:39 -0500
build.supported_target_versions 12.2,18,19,21
build.type production
build.label (HEAD, tag: v23.1, origin/stable_devel, stable_devel)

--2.产生配置文件

/u01/app/oracle/product/19.19.0/db_1/jdk/bin/java -jar autoupgrade.jar -create_sample_file config /tmp/up.config

然后根据自己需要调整，由于是安装RU，配置中，只需要源和目标即可。

[oracle@dbserver admin]$ cat /tmp/up.config
#
# Sample config file for AutoUpgrade
#
# build version 23.1.230224
# build date    2023/02/24 14:53:24 -0500
#
#
# Global configurations
#
# This directory will include the following:
#   (1) AutoUpgrade''s global directory
#   (2) Any logs, not directly tied to a job
#   (3) Config files
#   (4) progress.json and status.json

global.autoupg_log_dir=/tmp/patchlogs
#
# Non-CDB patch to Non-CDB patch, source and target home
#
patch1.sid=prod
patch1.source_home=/u01/app/oracle/product/19.16.0/db_1
patch1.target_home=/u01/app/oracle/product/19.19.0/db_1
#
#
# CDB patch, Source and Target home
#
patch2.sid=cdb1
patch2.source_home=/u01/app/oracle/product/19.16.0/db_1
patch2.target_home=/u01/app/oracle/product/19.19.0/db_1

patch3.sid=cdb2
patch3.source_home=/u01/app/oracle/product/19.16.0/db_1
patch3.target_home=/u01/app/oracle/product/19.19.0/db_1

patch4.sid=orclcdb
patch4.source_home=/u01/app/oracle/product/19.16.0/db_1
patch4.target_home=/u01/app/oracle/product/19.19.0/db_1

--2.分析

/u01/app/oracle/product/19.19.0/db_1/jdk/bin/java -jar autoupgrade.jar -config /tmp/up.config -mode analyze

--开归档，设置快速恢复区

upg> status

Config

        User configuration file    [/tmp/up.config]
        General logs location      [/tmp/patchlogs/cfgtoollogs/upgrade/auto]
        Mode                       [ANALYZE]
Jobs Summary

        Total databases in configuration file [9]
        Total Non-CDB being processed         [1]
        Total Containers being processed      [8]

        Jobs finished successfully            [0]
        Jobs finished/aborted                 [0]
        Jobs in progress                      [3]

Progress
        +---+---------------------------------------------------------+
        |Job|                                                 Progress|
        +---+---------------------------------------------------------+
        |104|[|||||||||||||||||                                 ] 33 %|
        |105|[|||||||||||||||||                                 ] 33 %|
        |106|[|||||||||||||||||                                 ] 33 %|
        |107|[|                                                 ] 0  %|
        +---+---------------------------------------------------------+

upg> lsj
+----+-------+---------+---------+--------+---------------+-------+----------------------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|     START_TIME|UPDATED|                     MESSAGE|
+----+-------+---------+---------+--------+---------------+-------+----------------------------+
| 104|   cdb2|PRECHECKS|EXECUTING| RUNNING|       17:22:45| 0s ago|Loading database information|
| 105|   cdb1|PRECHECKS|EXECUTING| RUNNING|       17:22:45| 0s ago|Loading database information|
| 106|   prod|PRECHECKS|EXECUTING| RUNNING|       17:22:45| 2s ago|Loading database information|
| 107|orclcdb|    SETUP|PREPARING|FINISHED|May-16 17:22:45|       |  Scheduled, starts in 0 min|
+----+-------+---------+---------+--------+---------------+-------+----------------------------+
Total jobs 4

upg> Job 106 completed

[oracle@dbserver admin]$ cat /tmp/patchlogs/cfgtoollogs/upgrade/auto/status/status.log
==========================================
          Autoupgrade Summary Report
==========================================
[Date]           Tue May 16 17:24:33 CST 2023
[Number of Jobs] 4
==========================================
[Job ID] 104
==========================================
[DB Name]                cdb2
[Version Before Upgrade] 19.16.0.0.0
[Version After Upgrade]  19.19.0.0.0
------------------------------------------
[Stage Name]    PRECHECKS
[Status]        SUCCESS
[Start Time]    2023-05-16 17:22:45
[Duration]      0:01:06
[Log Directory] /tmp/patchlogs/cdb2/104/prechecks
[Detail]        /tmp/patchlogs/cdb2/104/prechecks/cdb2_preupgrade.log
                Check passed and no manual intervention needed
------------------------------------------
==========================================
[Job ID] 105
==========================================
[DB Name]                cdb1
[Version Before Upgrade] 19.16.0.0.0
[Version After Upgrade]  19.19.0.0.0
------------------------------------------
[Stage Name]    PRECHECKS
[Status]        SUCCESS
[Start Time]    2023-05-16 17:22:47
[Duration]      0:01:05
[Log Directory] /tmp/patchlogs/cdb1/105/prechecks
[Detail]        /tmp/patchlogs/cdb1/105/prechecks/cdb1_preupgrade.log
                Check passed and no manual intervention needed
------------------------------------------
==========================================
[Job ID] 106
==========================================
[DB Name]                prod
[Version Before Upgrade] 19.16.0.0.0
[Version After Upgrade]  19.19.0.0.0
------------------------------------------
[Stage Name]    PRECHECKS
[Status]        SUCCESS
[Start Time]    2023-05-16 17:22:49
[Duration]      0:00:53
[Log Directory] /tmp/patchlogs/prod/106/prechecks
[Detail]        /tmp/patchlogs/prod/106/prechecks/prod_preupgrade.log
                Check passed and no manual intervention needed
------------------------------------------
==========================================
[Job ID] 107
==========================================
[DB Name]                orclcdb
[Version Before Upgrade] 19.16.0.0.0
[Version After Upgrade]  19.19.0.0.0
------------------------------------------
[Stage Name]    PRECHECKS
[Status]        SUCCESS
[Start Time]    2023-05-16 17:23:43
[Duration]      
[Log Directory] /tmp/patchlogs/orclcdb/107/prechecks
[Detail]        /tmp/patchlogs/orclcdb/107/prechecks/orclcdb_preupgrade.log
                Check passed and no manual intervention needed
------------------------------------------

--3.fixups Run the preupgrade fixups using Fixups mode.

/u01/app/oracle/product/19.19.0/db_1/jdk/bin/java -jar autoupgrade.jar -config /tmp/up.config -mode fixups

upg> tasks
+---+--------------+-------------+
| ID|          NAME|         Job#|
+---+--------------+-------------+
|  1|          main|      WAITING|
|218|    event_loop|      WAITING|
|219|       console|     RUNNABLE|
|220|  queue_reader|      WAITING|
|222|         cmd-0|      WAITING|
|223| job_manager-0|      WAITING|
|224| job_manager-1|      WAITING|
|226| job_manager-2|      WAITING|
|354|     exec_loop|TIMED_WAITING|
|879|prod-puifxl0-0|      WAITING|
|887|    sql-050144|     RUNNABLE|
|899|cdb2-puifxl0-0|      WAITING|
|909|    sql-E2A69B|     RUNNABLE|
|912|cdb1-puifxl0-0|      WAITING|
|917|    sql-094756|     RUNNABLE|
|960|    sql-4EF5C3|     RUNNABLE|
+---+--------------+-------------+
upg> lsj
+----+-------+---------+---------+--------+---------------+---------+---------------------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|     START_TIME|  UPDATED|                    MESSAGE|
+----+-------+---------+---------+--------+---------------+---------+---------------------------+
| 108|   cdb2|PREFIXUPS|EXECUTING| RUNNING|       17:27:29|!167s ago|                           |
| 109|   cdb1|PREFIXUPS|EXECUTING| RUNNING|       17:27:29|!166s ago|                           |
| 110|   prod|PREFIXUPS|EXECUTING| RUNNING|       17:27:29|!170s ago|                           |
| 111|orclcdb|    SETUP|PREPARING|FINISHED|May-16 17:27:29|         |Scheduled, starts in -3 min|
+----+-------+---------+---------+--------+---------------+---------+---------------------------+
Total jobs 4

upg> status

Config

        User configuration file    [/tmp/up.config]
        General logs location      [/tmp/patchlogs/cfgtoollogs/upgrade/auto]
        Mode                       [FIXUPS]
Jobs Summary

        Total databases in configuration file [9]
        Total Non-CDB being processed         [1]
        Total Containers being processed      [8]

        Jobs finished successfully            [0]
        Jobs finished/aborted                 [0]
        Jobs in progress                      [3]

Progress
        +---+---------------------------------------------------------+
        |Job|                                                 Progress|
        +---+---------------------------------------------------------+
        |108|[|||||||||||||||||||||||||||||||                   ] 60 %|
        |109|[|||||||||||||||||||||||||||||||                   ] 60 %|
        |110|[|||||||||||||||||||||||||||||||                   ] 60 %|
        |111|[|                                                 ] 0  %|
        +---+---------------------------------------------------------+

upg>

--4.upgrade
/u01/app/oracle/product/19.19.0/db_1/jdk/bin/java -jar autoupgrade.jar -config /tmp/up.config -mode upgrade

[oracle@dbserver admin]$ /u01/app/oracle/product/19.19.0/db_1/jdk/bin/java -jar autoupgrade.jar -config /tmp/up.config -mode upgrade
AutoUpgrade 23.1.230224 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
3 CDB(s) plus 5 PDB(s) and 1 Non-CDB(s) will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+-----+---------+--------+---------------+-------+--------------------------+
|Job#|DB_NAME|STAGE|OPERATION|  STATUS|     START_TIME|UPDATED|                   MESSAGE|
+----+-------+-----+---------+--------+---------------+-------+--------------------------+
| 112|   cdb2|DRAIN|EXECUTING| RUNNING|       17:50:46| 0s ago|  Disabling RAC if present|
| 113|   cdb1|SETUP|PREPARING|FINISHED|May-16 17:50:46|       |Scheduled, starts in 0 min|
| 114|   prod|SETUP|PREPARING|FINISHED|May-16 17:50:46|       |Scheduled, starts in 0 min|
| 115|orclcdb|SETUP|PREPARING|FINISHED|May-16 17:50:47|       |Scheduled, starts in 0 min|
+----+-------+-----+---------+--------+---------------+-------+--------------------------+
Total jobs 4

upg>

upg> status

Config

        User configuration file    [/tmp/up.config]
        General logs location      [/tmp/patchlogs/cfgtoollogs/upgrade/auto]
        Mode                       [UPGRADE]
Jobs Summary

        Total databases in configuration file [9]
        Total Non-CDB being processed         [1]
        Total Containers being processed      [8]

        Jobs finished successfully            [3]
        Jobs finished/aborted                 [0]
        Jobs in progress                      [1]

Progress
        +---+----------------------------------------------------------+
        |Job|                                                  Progress|
        +---+----------------------------------------------------------+
        |112|[|||||||||||||||||||||||||||||||||||||||||||||||||||] 100%|
        |113|[|||||||||||||||||||||||||||||||||||||||||||||||||||] 100%|
        |114|[|||||||||||||||||||||||||||||||||||||||||||||||||||] 100%|
        |115| [||||||||||||||||||||||||||||||||||||||||||||||    ] 90 %|
        +---+----------------------------------------------------------+

upg> lsj
+----+-------+----------+---------+--------+----------+-------+----------------------------------------+
|Job#|DB_NAME|     STAGE|OPERATION|  STATUS|START_TIME|UPDATED|                                 MESSAGE|
+----+-------+----------+---------+--------+----------+-------+----------------------------------------+
| 112|   cdb2| COMPLETED|  STOPPED|FINISHED|  17:50:46|       |                                        |
| 113|   cdb1| COMPLETED|  STOPPED|FINISHED|  17:50:46|       |                                        |
| 114|   prod| COMPLETED|  STOPPED|FINISHED|  17:50:46|       |                                        |
| 115|orclcdb|SYSUPDATES|EXECUTING| RUNNING|  17:50:47|17s ago|The after-upgrade RAC configurations hav|
+----+-------+----------+---------+--------+----------+-------+----------------------------------------+
Total jobs 4

--5.验证所有数据库
--5.1.版本SQL> r
  1* select PATCH_ID,PATCH_TYPE,DESCRIPTION,ACTION,ACTION_TIME,STATUS,TARGET_VERSION  from dba_registry_sqlpatch

  PATCH_ID PATCH_TYPE DESCRIPTION                                        ACTION          ACTION_TIME                    STATUS                    TARGET_VERSION
---------- ---------- -------------------------------------------------- --------------- ------------------------------ ------------------------- ---------------
  34086870 INTERIM    OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)   APPLY           01-APR-23 05.23.31.649509 PM   SUCCESS                   19.1.0.0.0
  34133642 RU         Database Release Update : 19.16.0.0.220719 (341336 APPLY           01-APR-23 05.23.31.645131 PM   SUCCESS                   19.16.0.0.0
                      42)

  34086870 INTERIM    OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)   ROLLBACK        16-MAY-23 06.37.33.151642 PM   SUCCESS                   19.19.0.0.0
  35050341 INTERIM    OJVM RELEASE UPDATE: 19.19.0.0.230418 (35050341)   APPLY           16-MAY-23 06.37.41.283145 PM   SUCCESS                   19.16.0.0.0
  35042068 RU         Database Release Update : 19.19.0.0.230418 (350420 APPLY           16-MAY-23 06.37.41.271770 PM   SUCCESS                   19.19.0.0.0
                      68)

[oracle@dbserver ~]$ $ORACLE_HOME/OPatch/opatch lspatches
35050341;OJVM RELEASE UPDATE: 19.19.0.0.230418 (35050341)
35042068;Database Release Update : 19.19.0.0.230418 (35042068)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
[oracle@dbserver ~]$

--5.2 数据库网络配置信息

[oracle@dbserver ~]$ cd $ORACLE_HOME/network/admin
[oracle@dbserver admin]$ pwd
/u01/app/oracle/product/19.19.0/db_1/network/admin
[oracle@dbserver admin]$ ls -ltr
total 8
-rw-r--r-- 1 oracle oinstall 1536 Feb 14  2018 shrept.lst
drwxr-xr-x 2 oracle oinstall   64 Apr 17  2019 samples
-rw-r--r-- 1 oracle oinstall 1990 May 16 18:51 tnsnames.ora
-rw-r--r-- 1 oracle oinstall    0 May 16 18:51 sqlnet.ora
[oracle@dbserver admin]$ more tnsnames.ora
# start-delta prod
CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
    (CONNECT_DATA =
。。。

--5.3 参数文件

[oracle@dbserver admin]$ ls -ltr $ORACLE_HOME/dbs/spfile*
-rw-r----- 1 oracle oinstall 3584 May 16 19:13 /u01/app/oracle/product/19.19.0/db_1/dbs/spfilecdb1.ora
-rw-r----- 1 oracle oinstall 3584 May 16 19:17 /u01/app/oracle/product/19.19.0/db_1/dbs/spfilecdb2.ora

--5.4 口令文件

[oracle@dbserver admin]$ ls -ltr $ORACLE_HOME/dbs/orapw*
-rw-r----- 1 oracle oinstall 2560 May 16 17:53 /u01/app/oracle/product/19.19.0/db_1/dbs/orapwcdb2
-rw-r----- 1 oracle oinstall 3072 May 16 17:54 /u01/app/oracle/product/19.19.0/db_1/dbs/orapwcdb1
-rw-r----- 1 oracle oinstall 2048 May 16 17:55 /u01/app/oracle/product/19.19.0/db_1/dbs/orapwprod
-rw-r----- 1 oracle oinstall 2048 May 16 18:23 /u01/app/oracle/product/19.19.0/db_1/dbs/orapworclcdb

--5.4 环境

[oracle@dbserver admin]$ cat /etc/oratab |grep -v '^#'|grep -v '^$'
+ASM:/u01/app/19.16.0/grid:N
orcl:/u01/app/oracle/product/19.16.0/db_1:N
szdb:/u01/app/oracle/product/19.16.0/db_1:N
prod:/u01/app/oracle/product/19.19.0/db_1:N             # line added by Agent
cdb1:/u01/app/oracle/product/19.19.0/db_1:N             # line added by Agent
orclcdb:/u01/app/oracle/product/19.19.0/db_1:N          # line added by Agent
cdb2:/u01/app/oracle/product/19.19.0/db_1:N             # line added by Agent

郑全

登录/注册后可看大图