Oracle DatabaseのRelease Update (RU) とRelease Update Revisions (RUR) を自由に行き来できるか試してみた

現在のOracle Databaseは四半期ごとの集積パッチをRelease Update (RU)、Release Update Revisions (RUR) という形で提供しています。
詳細は以下をご参照ください。
[Oracle Database 最新情報と年次リリースモデルに関して](https://speakerdeck.com/oracle4engineer/oracle-database-zui-xin-qing-bao-tonian-ci-ririsumoderuniguan-site?slide=13)
上記資料にもある通り、RUとRURは柔軟に行き来ができる仕様となっています。
と言うものの、実際に試したことがなかったのでやってみました。

## 前提条件

+ OS: Oracle Linux 7.9
+ DB構成: シングル・インスタンス
+ 初期DBバージョン: RUR 19.12.2

## 検証シナリオ

1. RUR 19.12.2 のDBを作成
2. RU 19.14 を適用
3. 再度RUR 19.12.2 にロールバック

## 実施結果

結論としては問題なくRUとRURを行き来できました。
以降は詳細手順を載せていきます。

### RUR 19.12.2 ⇒ RU 19.14 の適用

opatchで適用済みパッチがRUR 19.12.2 であることを確認。

```
$ $ORACLE_HOME/OPatch/opatch lspatches
33494256;Database Release Update Revision : 19.12.2.0.220118 (33494256)
32327208;DSTV36 UPDATE - TZDATA2020E - NEED OJVM FIX
32327201;RDBMS - DSTV36 UPDATE - TZDATA2020E
31732095;UPDATE PERL IN 19C DATABASE ORACLE HOME TO V5.32
32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)
32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)

OPatch succeeded.
```

conflictチェック。

```
$ $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/19c/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19c/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/19c/dbhome_1/cfgtoollogs/opatch/opatch2022-03-15_06-02-56AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
```

DBを停止。

```
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 15 05:40:52 2022
Version 19.12.2.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.2.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.2.0.0
```

リスナーを停止。

```
$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-MAR-2022 05:41:48

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19single.pub.emcc.oraclevcn.com)(PORT=1521)))
The command completed successfully
```

RU 19.14を適用。

```
$ $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/19c/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19c/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/19c/dbhome_1/cfgtoollogs/opatch/opatch2022-03-15_05-42-10AM_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/19c/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
...略...
Patch 33515361 successfully applied.
Sub-set patch [33494256] 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/19c/dbhome_1/cfgtoollogs/opatch/opatch2022-03-15_05-42-10AM_1.log

OPatch succeeded.
```

RU 19.14が適用できたことを確認。

```
$ $ORACLE_HOME/OPatch/opatch lspatches
33515361;Database Release Update : 19.14.0.0.220118 (33515361)
32327208;DSTV36 UPDATE - TZDATA2020E - NEED OJVM FIX
32327201;RDBMS - DSTV36 UPDATE - TZDATA2020E
31732095;UPDATE PERL IN 19C DATABASE ORACLE HOME TO V5.32
32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)
32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)

OPatch succeeded.
```

DBを起動。
```
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 15 05:49:39 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 4949277248 bytes
Fixed Size    9144896 bytes
Variable Size  889192448 bytes
Database Buffers 4043309056 bytes
Redo Buffers    7630848 bytes
Database mounted.
Database opened.
SQL> 
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED  READ ONLY  NO
 3 ORCLPDB  READ WRITE NO
SQL> 
SQL> exit
```

datapatchを適用。

```
$ ./datapatch -verbose
SQL Patching tool version 19.14.0.0.0 Production on Tue Mar 15 05:50:26 2022
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_1682_2022_03_15_05_50_26/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:
Interim patch 32876380 (OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)):
  Binary registry: Installed
  PDB CDB$ROOT: Applied successfully on 14-MAR-22 08.41.17.520536 AM
  PDB ORCLPDB: Applied successfully on 14-MAR-22 08.51.48.157949 AM
  PDB PDB$SEED: Applied successfully on 14-MAR-22 08.51.48.157949 AM

Current state of release update SQL patches:
  Binary registry:
    19.14.0.0.0 Release_Update 211225122123: Installed
  PDB CDB$ROOT:
    Applied 19.12.2.0.0 Release_Update_Revision 220113181912 successfully on 15-MAR-22 05.33.24.348488 AM
  PDB ORCLPDB:
    Applied 19.12.2.0.0 Release_Update_Revision 220113181912 successfully on 15-MAR-22 05.33.24.826366 AM
  PDB PDB$SEED:
    Applied 19.12.2.0.0 Release_Update_Revision 220113181912 successfully on 15-MAR-22 05.33.24.531427 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED ORCLPDB
    No interim patches need to be rolled back
    Patch 33494256 (Database Release Update Revision : 19.12.2.0.220118 (33494256)):
      Rollback from 19.12.2.0.0 Release_Update_Revision 220113181912 to 19.12.0.0.0 Release_Update 210716141810
    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: 6

Validating logfiles...done
Patch 33494256 rollback (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33494256/24593356/33494256_rollback_ORCL_CDBROOT_2022Mar15_05_51_11.log (no errors)
Patch 33515361 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33515361/24589353/33515361_apply_ORCL_CDBROOT_2022Mar15_05_51_12.log (no errors)
Patch 33494256 rollback (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33494256/24593356/33494256_rollback_ORCL_PDBSEED_2022Mar15_05_53_28.log (no errors)
Patch 33515361 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33515361/24589353/33515361_apply_ORCL_PDBSEED_2022Mar15_05_53_32.log (no errors)
Patch 33494256 rollback (pdb ORCLPDB): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33494256/24593356/33494256_rollback_ORCL_ORCLPDB_2022Mar15_05_53_28.log (no errors)
Patch 33515361 apply (pdb ORCLPDB): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33515361/24589353/33515361_apply_ORCL_ORCLPDB_2022Mar15_05_53_32.log (no errors)

Automatic recompilation incomplete; run utlrp.sql to revalidate.
  PDBs: ORCLPDB PDB$SEED

SQL Patching tool complete on Tue Mar 15 05:56:07 2022
```

このあとPDBに327個のINVALIDオブジェクトがあるのを確認したので、再コンパイルを実行。

```
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/utlrp_catcon_2662.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/utlrp*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/utlrp_*.lst] files for spool files, if any

catcon.pl: completed successfully
```

リスナーを起動。

```
$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-MAR-2022 06:01:51

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

Starting /u01/app/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ora19single/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19single)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19single.pub.emcc.oraclevcn.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-MAR-2022 06:01:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora19single/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19single)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
```

### RU 19.14 ⇒ RUR 19.12.2 にロールバック

ロールバックの際はopatchのrollbackコマンドで戻します。
試しにopatch applyでRUR 19.12.2の適用を試みたところ、すでに適用されているので何もしません、と返ってきました。
まぁ当たり前ですね。。

```
$ $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/19c/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19c/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/19c/dbhome_1/cfgtoollogs/opatch/opatch2022-03-15_06-06-12AM_1.log

Verifying environment and performing prerequisite checks...
Skip patch 33494256 from list of patches to apply: This patch is not needed.

After skipping patches with missing components, there are no patches to apply.
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/19c/dbhome_1/cfgtoollogs/opatch/opatch2022-03-15_06-06-12AM_1.log

OPatch completed with warnings.
```

DBとリスナーが停止されていることを確認し、ロールバック。

```
$ $ORACLE_HOME/OPatch/opatch rollback -id 33515361
Oracle Interim Patch Installer version 12.2.0.1.29
Copyright (c) 2022, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19c/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19c/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/19c/dbhome_1/cfgtoollogs/opatch/opatch2022-03-15_06-15-56AM_1.log


Patches will be rolled back in the following order: 
   33515361
The following patch(es) will be rolled back: 33515361  

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19c/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y

Rolling back patch 33515361...
...(略)...
Patching component oracle.jdk, 1.8.0.201.0...
RollbackSession removing interim patch '33515361' from inventory
Inactive sub-set patch [33494256] has become active due to the rolling back 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/19c/dbhome_1/cfgtoollogs/opatch/opatch2022-03-15_06-15-56AM_1.log

OPatch succeeded.
```

RUR 19.12.2 が適用されていることを確認。

```
$ $ORACLE_HOME/OPatch/opatch lspatches
33494256;Database Release Update Revision : 19.12.2.0.220118 (33494256)
32327208;DSTV36 UPDATE - TZDATA2020E - NEED OJVM FIX
32327201;RDBMS - DSTV36 UPDATE - TZDATA2020E
31732095;UPDATE PERL IN 19C DATABASE ORACLE HOME TO V5.32
32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)
32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)

OPatch succeeded.
```

あとは以下の手順を実行しますが、詳細は割愛します。

+ 全PDB含めDB起動
+ datapatch実行
+ INVALIDオブジェクトの再コンパイル
+ リスナー起動

## まとめ

以上の通り、RUとRURは柔軟に行き来できました。
｢当初はRURを適用して脆弱性対応やリグレッション修正のみ考慮していたけれども、やっぱりRU適用したくなった！」というケースに対して柔軟に対応できるので安心ですね。
ちなみに集積パッチの適用の仕方としては、RURではなく、定期的にRUを適用していくのがお勧めです（含まれるバグ修正の数が全然違いますので...）。
RUにはオプティマイザ修正も含まれますが、デフォルトで無効化されていますので、その点はご安心ください。