2023年11月｜ 2023年12月 ｜2024年01月 ブログトップ

OCI BaseDBの19cから23cへアップグレード(AutoUpgrade編)　[OCI]

この記事は、JPOUG Advent Calendar 2023 20日目の記事です。19日目は HiroyukiNakaie さんの記事 「クラウドのリソース一覧をSQLで取得できるようにする」でした。

１．はじめに


　2023年9月にOCIのBase Database Serviceで23cが提供開始された。シングル構成に限るという制約はあるものの、23cの新規インスタンスを構成できるようになった。しかし、OCIコンソールで既存の19cの環境を23cへアップグレードする機能は提供されておらず、AutoUpgradeも23cに対応していない状況である。既存の19c環境を23cにアップグレードするには、前回のBlog記事のように新規環境を構築してデータ移行するのが一般的だろう。
　そんな中、Oracle社のアップグレードを専門とするDaniel Overby Hansen氏のブログ記事（参考[1]）にAutoUpgradeを利用して19cのPDBを23cのBase Databaseへコピーする方法が紹介された。本稿では、この記事をもとに実際にOCIで19cのPDBを23cへアップグレードする検証をしてみたい。
　検証に用いた環境を下図に示す。左が移行元となる19cのソースDB(oradbvm1)で移行対象のPDB(oradb)を含む。PDBには、scottスキーマにEMP表100万件、DEPT表10万件が入っている。右が移行先となる23cのターゲットDB(oradbvm2)で、PDBはない状態。いずれもOCIのBaseDatabaseServiceを利用して環境を構築した。ターゲットDBからソースDBの移行対象PDBにDBLINKを作成し、AutoUpgradeを用いてPDBクローンによるアップグレードを行う。ターゲットDBのCDBには、23c用に必要な初期化パラメータや各種設定を行っておく。
20231220_upg23cTestEnv2.jpg

　アップグレードの基本的な手順は以下の通り。詳細は以下に述べていく。
３．準備
　AutoUpgradeツール・設定ファイルの配置
　ソースDBのDBLINKユーザ作成
　ターゲットDBのDBLINK作成
４．アナライズ・FIXUPS
　ソースDBのアナライズ（事前チェック）
　　java -jar ./autoupgrade.jar -config oradb.cfg -mode analyze
　ソースDBのFIXUPS（事前チェックで検出された一部の自動修正）
　　java -jar ./autoupgrade.jar -config oradb.cfg -mode fixups
５．アップグレード
　AutoupgradeへのTDEパスワード登録
　　java -jar ./autoupgrade.jar -config oradb.cfg -load_password
　デプロイ（アップグレード）
　　java -jar ./autoupgrade.jar -config oradb.cfg -mode deploy
６．事後作業
　ターゲットDBのPDBオープン
　サービス追加
　tnsnames.oraへの接続識別子追加
　接続確認

２．前提の理解


　本題に入る前にいくつか前提事項を整理しておきたい。まず、本手順で利用するAutoUpgradeツールは、ソースDBとターゲットDB両方に配置する必要がある。いずれの環境にも、もともと$ORACLE_HOMEにAutoUpgradeは含まれているが、19cのAutoUpgradeはターゲットDBとして23cはサポートしていない。そこで、ここでは23cのBase DatabaseのORACLE_HOMEに含まれるautoupgrade.jar（バージョンは23.3.230728）を19cのBase Databaseにコピーして使う。
　なお、AutoUpgradeの最新版はMOSからダウンロード可能である（参考[2]）が、ここには23.3.230728は公開されていないため、OCIのBase Databaseの23cに含まれているautoupgradeは暫定的なリリースなのかもしれない。
[oracle@oradbvm2 ~]$ java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -version
build.version 23.3.230728　★今回利用するAutoUpgradeのバージョン
build.date 2023/07/28 20:33:14 +0000
build.hash 58f81599
build.hash_date 2023/07/28 17:06:40 +0000
build.supported_target_versions 12.2,18,19,21,23
build.type production
build.label (HEAD, tag: v23.3, origin/devel, devel)

　AutoUpgradeを使うためには設定ファイルを用意する必要がある。今回作成したAutoUpgradeの設定ファイル（ここではoradb.cfgとした）は以下の通りである。パラメータの意味はAutoUpgradeマニュアル（参考[3]）を、実際の設定は参考[4]を参照されたい。
global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade・・・ツールのログ出力先
global.keystore=/u01/app/oracle/cfgtoollogs/keystore・・・ツールのキーストア（パスワード）格納先
upg1.source_home=/u01/app/oracle/product/19.0.0.0/dbhome_1・・・ソースDBのORACLE_HOME
upg1.target_home=/u01/app/oracle/product/23.0.0.0/dbhome_1・・・ターゲットDBのORACLE_HOME
upg1.sid=coradb　★・・・ソースDBのSID
upg1.pdbs=oradb・・・ソースDBのPDB名
upg1.target_cdb=dummy　★・・・ターゲットDBのCDB名
upg1.source_dblink.oradb=clonepdb 600　☆・・・ソースPDBへのDBLINK名とクローンのリフレッシュレート（600秒毎）
upg1.target_pdb_copy_option.oradb=file_name_convert=none・・・PDBのコピーオプション（ASMとOMFを利用）
upg1.target_version=23・・・ターゲットDBのバージョン
upg1.start_time=now　☆・・・アップグレードの実行タイミング（nowだと直ちに実行。時刻も指定可能）

　1点つまずいたのは、ソースDBとターゲットDBのSIDの設定を同じ（coradb）にすると、「The target_cdb parameter matches the sid parameter for coradb」というメッセージが出て、autoupgradeがエラーとなってしまう点である。これを回避するには、★部分をソースDBとターゲットDBで設定値を変更する必要がある。具体的には、ソースDBに配置する場合は、upg1.sidにソースDBのSIDを指定、upg1.target_cdbにはdummyを、逆にターゲットDBに配置する場合は、upg1.sidにdummyを指定、upg1.target_cdbにはターゲットDBのSIDを指定した。この点については、もっと良い方法があるのかもしれない。
　もう１点特筆すべきは、☆部分のstart_timeについてである。今回はnowを指定してアップグレードを即時実行することを指示した。実はここに特定の未来時刻を指定すると、その時刻にアップグレードを実行することができる。その際、source_dblink.oradbに指定するリフレッシュレートでアップグレード直近までPDBのリフレッシュが行われる。

３．準備


　ソースDBにて、作業ディレクトリ/home/oracle/autoupgを作成し、資材を配置する。oradb.cfgは参考[3]のソースDBの設定を使う。
[oracle@oradbvm1 ~]$ mkdir autoupg
[oracle@oradbvm1 ~]$ cd autoupg
[oracle@oradbvm1 autoupg]$ scp 10.0.1.12:/u01/app/oracle/product/23.0.0.0/dbhome
_1/rdbms/admin/autoupgrade.jar .
oracle@10.0.1.12's password:
autoupgrade.jar                               100% 5227KB 124.9MB/s   00:00
[oracle@oradbvm1 autoupg]$ vi oradb.cfg
[oracle@oradbvm1 autoupg]$ ll
total 5232
-rw-r----- 1 oracle oinstall 5352158 Nov 29 10:28 autoupgrade.jar
-rw-r--r-- 1 oracle oinstall     426 Nov 29 10:36 oradb.cfg

　ターゲットDBにて、作業ディレクトリ/home/oracle/autoupgを作成し、資材を配置する。
[oracle@oradbvm2 ~]$ mkdir autoupg
[oracle@oradbvm2 ~]$ cd autoupg
[oracle@oradbvm2 autoupg]$ vi oradb.cfg
[oracle@oradbvm2 autoupg]$ ll
total 5232
-rw-r----- 1 oracle oinstall 5352158 Nov 29 10:32 autoupgrade.jar
-rw-r--r-- 1 oracle oinstall     426 Nov 29 10:38 oradb.cfg
[oracle@oradbvm2 autoupg]$

　ソースDBにて、DBLINKの接続用ユーザを作成し、必要な権限を付与する。
SQL> create user dblinkuser identified by "XXX";
User created.

SQL> grant create session, create pluggable database, select_catalog_role to dblinkuser;
Grant succeeded.

SQL> grant read on sys.enc$ to dblinkuser;
Grant succeeded.

　ターゲットDBにて、DBLINKを作成するため、初期化パラメータglobal_namesをfalseに設定する。
SQL> alter system set global_names=FALSE scope=BOTH;
System altered.

SQL> show parameter global_names
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     FALSE

　ターゲットDBにて、DBLINKを作成する。ソースDBへのDBLINKの動作も確認しておく。
SQL> create database link clonepdb connect to dblinkuser identified by "XXX" using 'oradb_oradbvm1';
Database link created.
SQL> select banner_full from v$version@clonepdb;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

４．アナライズとFIXUPS


　ソースDBのアナライズを実施する。ジョブが正常終了すれば完了。
[oracle@oradbvm1 autoupg]$ java -jar ./autoupgrade.jar -config oradb.cfg -mode analyze
AutoUpgrade 23.3.230728 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 PDB(s) will be analyzed
Type 'help' to list console commands
upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]

Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
[oracle@oradbvm1 autoupg]$

　アナライズの結果のサマリレポートを確認する。ここで、ORADB TDE_PASSWORDS_REQUIREDとORADB TARGET_CDB_AVAILABILITYの2つは後続の手順で対処するため、無視する（参考[1]手順記載の通り）。[Detail]に記載されたログ(参考[5])に詳細な結果が記載されているので結果を確認する。ソースDBのデータベースの状態やコンポーネント、アップグレードに際して必要なアクション、推奨されるアクション、そのうち（この後実行する）FIXUPで修正される対象など重要な情報が記載されている。
[oracle@oradbvm1 autoupg]$ cat /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
==========================================
          Autoupgrade Summary Report
==========================================
[Date]           Wed Nov 29 10:47:26 JST 2023
[Number of Jobs] 1
==========================================
[Job ID] 101
==========================================
[DB Name]                coradb_oradbvm1
[Version Before Upgrade] 19.20.0.0.0
[Version After Upgrade]  23
------------------------------------------
[Stage Name]    PRECHECKS
[Status]        FAILURE
[Start Time]    2023-11-29 10:46:34
[Duration]
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/101/prechecks
[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/101/prechecks/coradb_oradbvm1_preupgrade.log
                Check failed for ORADB, manual intervention needed for the below checks
                [TDE_PASSWORDS_REQUIRED, TARGET_CDB_AVAILABILITY]
Cause:The following checks have ERROR severity and no fixup is available or
the fixup failed to resolve the issue. Fix them manually before continuing:
ORADB TDE_PASSWORDS_REQUIRED
ORADB TARGET_CDB_AVAILABILITY
Reason:Database Checks has Failed details in /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/101/prechecks
Action:[MANUAL]
Info:Return status is ERROR
ExecutionError:No
Error Message:The following checks have ERROR severity and no fixup is available or
the fixup failed to resolve the issue. Fix them manually before continuing:
ORADB TDE_PASSWORDS_REQUIRED
ORADB TARGET_CDB_AVAILABILITY

------------------------------------------
[oracle@oradbvm1 autoupg]$

　次にFIXUPを実行する。ここでアップグレード前にソースDBに対する必要な修正が実行される。しかし、UPG-1321で異常終了してしまう。
[oracle@oradbvm1 autoupg]$ java -jar ./autoupgrade.jar -config oradb.cfg -mode fixups
AutoUpgrade 23.3.230728 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 PDB(s) will be processed
Type 'help' to list console commands
upg> "Database fixup failed with a runtime exception"  (conName="ORADB", stage="PREFIXUPS", checkName="NEW_TIME_ZONES_EXIST")

-------------------------------------------------
Errors in database [coradb]
Stage     [PREFIXUPS]
Operation [STOPPED]
Status    [ERROR]
Info    [
Error: UPG-1321
1 fixups with runtime errors
Cause: Runtime exception during Pre-Upgrade Fix-Up execution
For further details, see the log file located at /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/102/autoupgrade_20231129_user.log]

-------------------------------------------------
Logs: [/u01/app/oracle/cfgtoollogs/autoupgrade/coradb/102/autoupgrade_20231129_user.log]
-------------------------------------------------
upg>

　ログから以下のNEW_TIME_ZONES_EXISTのチェックの部分でエラーが発生していることを確認した。原因を探るため、failed_prefixups.logで詳細を確認する。
/u01/app/oracle/cfgtoollogs/autoupgrade/coradb/102/autoupgrade_20231129_user.log
...
2023-11-29 10:51:48.472 INFO Creating summary of the fixups which ran with errors in /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/102/prefixups/failed_prefixups.log ★
2023-11-29 10:51:48.473 ERROR "Database fixup failed with a runtime exception"  (conName="ORADB", stage="PREFIXUPS", checkName="NEW_TIME_ZONES_EXIST")　★

　ログを確認すると、ターゲットDBのzoneinfoディレクトリ配下のファイルを参照しようとしてNoSuchFileExceptionエラーになっているようである。これはORACLE_HOMEに含まれるファイルだが、当然ソースDBには23cのORACLE_HOMEディレクトリは存在しない。このロジックは、ソースDBとターゲットDBが同一DBサーバ上にあることを前提としているのかもしれない。
/u01/app/oracle/cfgtoollogs/autoupgrade/coradb/102/prefixups/failed_prefixups.log
...
2023-11-29 10:50:54.042 INFO Copying /u01/app/oracle/product/19.0.0.0/dbhome_1/oracore/zoneinfo/readme.txt to /u01/app/oracle/product/23.0.0.0/dbhome_1/oracore/zoneinfo/readme.txt - new_time_zones_exist.copyTimezone
2023-11-29 10:50:54.043 ERROR
============================ check info ============================
[ORADB][NEW_TIME_ZONES_EXIST][WARNING] ★
============================ check info ============================
=========================== trace start ==============================
Exception: NoSuchFileException ★
Err message: /u01/app/oracle/product/23.0.0.0/dbhome_1/oracore/zoneinfo/readme.txt
java.nio.file.NoSuchFileException: /u01/app/oracle/product/23.0.0.0/dbhome_1/oracore/zoneinfo/readme.txt ★

　仕方がないので、ターゲットDBのzoneinfo配下をソースDBにコピーする（readme.txtだけコピーではダメだったので、ディレクトリ配下のファイルを全てscpでコピー）。
[root@oradbvm1 ~]# mkdir -p /u01/app/oracle/product/23.0.0.0/dbhome_1/oracore/zoneinfo/
[root@oradbvm1 ~]# chown -R oracle:oinstall /u01/app/oracle/product/23.0.0.0/dbhome_1
[root@oradbvm1 ~]# cd /u01/app/oracle/product/23.0.0.0/dbhome_1/oracore/zoneinfo/
[oracle@oradbvm1 zoneinfo]$ scp -r 10.0.1.12:/u01/app/oracle/product/23.0.0.0/dbhome_1/oracore/zoneinfo/* .
oracle@10.0.1.12's password:
timezlrg_24.dat                               100%  759KB  96.8MB/s   00:00
timezone_22.dat                               100%  336KB  75.8MB/s   00:00
timezone_19.dat                               100%  335KB  94.4MB/s   00:00
...
timezone_8.dat                                100%  295KB  93.6MB/s   00:00
timezone_9.dat                                100%  343KB  98.4MB/s   00:00
[oracle@oradbvm1 zoneinfo]$ ll

　再度FIXUPを実行すると、ジョブは正常終了する。ログを確認すると、PRECHECKSはアナライズで出たエラーと同じもの（無視可能）が出るが、PREFIXUPSは成功している。ソースPDBへどのような変更がされたかが気になるところであるが、prefixups.htmlを見る限り、変更点は何もなかった。FIXUPSの手順自体不要だったのではという一抹の疑念はあるものの、一応手順は踏んでおいた方が良いのだろう。
[oracle@oradbvm1 autoupg]$ java -jar ./autoupgrade.jar -config oradb.cfg -mode fixups
AutoUpgrade 23.3.230728 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 PDB(s) will be processed
Type 'help' to list console commands
upg> Job 104 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]

Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
[oracle@oradbvm1 autoupg]$
[oracle@oradbvm1 autoupg]$ cat /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
==========================================
          Autoupgrade Summary Report
==========================================
[Date]           Wed Nov 29 11:55:14 JST 2023
[Number of Jobs] 1
==========================================
[Job ID] 104
==========================================
[DB Name]                coradb_oradbvm1
[Version Before Upgrade] 19.20.0.0.0
[Version After Upgrade]  23
------------------------------------------
[Stage Name]    PRECHECKS
[Status]        FAILURE
[Start Time]    2023-11-29 11:53:44
[Duration]      0:00:45
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/104/prechecks
[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/104/prechecks/coradb_oradbvm1_preupgrade.log
                Check failed for ORADB, manual intervention needed for the below checks
                [TDE_PASSWORDS_REQUIRED, TARGET_CDB_AVAILABILITY]
Cause:The following checks have ERROR severity and no fixup is available or
the fixup failed to resolve the issue. Fix them manually before continuing:
ORADB TDE_PASSWORDS_REQUIRED
ORADB TARGET_CDB_AVAILABILITY
Reason:Database Checks has Failed details in /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/104/prechecks
Action:[MANUAL]
Info:Return status is ERROR
ExecutionError:No
Error Message:The following checks have ERROR severity and no fixup is available or
the fixup failed to resolve the issue. Fix them manually before continuing:
ORADB TDE_PASSWORDS_REQUIRED
ORADB TARGET_CDB_AVAILABILITY

------------------------------------------
[Stage Name]    PREFIXUPS
[Status]        SUCCESS
[Start Time]    2023-11-29 11:54:29
[Duration]
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/104/prefixups
[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/104/prefixups/prefixups.html
------------------------------------------

　念のため、エラーの発生したNEW_TIME_ZONES_EXISTのチェックがパスしていることを確認。
　/u01/app/oracle/cfgtoollogs/autoupgrade/coradb/104/prechecks/prechecks_oradb.log
　...
  2538  2023-11-29 11:55:06.293 INFO Zone Directory /u01/app/oracle/product/23.0.0.0/dbhome_1/oracore/zoneinfo Defaulting TimeZone Value 36 - UpgradeUtilities.getTimeZoneReleasedVersion
  2539  2023-11-29 11:55:06.297 INFO TimeZone latest release value 42 from directory /u01/app/oracle/product/23.0.0.0/dbhome_1/oracore/zoneinfo - UpgradeUtilities.getTimeZoneReleasedVersion
  2540  2023-11-29 11:55:06.297 INFO End  - UpgradeUtilities.getTimeZoneReleasedVersion
  2541  2023-11-29 11:55:06.297 ★INFO Finished check [NEW_TIME_ZONES_EXIST][ORADB][PASSED] - CheckTrigger.call

５．アップグレード


　ターゲットDBにて、AutoUpgradeのキーストアにORACLE_SIDのTDEパスワードを設定する。本環境ではソースDBとターゲットDBのSIDは同一かつパスワードも同じなので、以下のように1つだけ登録する（元の手順ではソースDBとターゲットDB両方のパスワードを設定している）。
[oracle@oradbvm2 autoupg]$ java -jar ./autoupgrade.jar -config oradb.cfg -load_password
Processing config file ...

Starting AutoUpgrade Password Loader - Type help for available options
Creating new AutoUpgrade keystore - Password required
Enter password:
Enter password again:
AutoUpgrade keystore was successfully created

TDE> add coradb　★
Enter your secret/Password:
Re-enter your secret/Password:
TDE> save
Convert the AutoUpgrade keystore to auto-login [YES|NO] ? yes
TDE> list
+----------+---------------+------------------+-----------+------------------+
|ORACLE_SID|Action Required|      TDE Password|SEPS Status|Active Wallet Type|
+----------+---------------+------------------+-----------+------------------+
|    coradb|               |          Verified|   Inactive|               Any|
|     dummy|               |No password loaded|   Inactive|               Any|
+----------+---------------+------------------+-----------+------------------+
TDE> exit

AutoUpgrade Password Loader finished - Exiting AutoUpgrade
[oracle@oradbvm2 autoupg]$

　ターゲットDBにて、デプロイを実行する。ここで実際にDBLINK越しにPDBがクローンされ23cへのアップグレードが実行される。ログを見ると、AutoUpgradeのデプロイの各タスクでどの程度の時間がかかったかが確認できる。全体でかかった時間は概ね40分程度だが、うち30分はDBUPGRADE、10分はPOSTFIXUPSで占められていた（★部分）。
[oracle@oradbvm2 autoupg]$ java -jar ./autoupgrade.jar -config oradb.cfg -mode deploy
AutoUpgrade 23.3.230728 launched with default internal options
Processing config file ...
Loading AutoUpgrade keystore
AutoUpgrade keystore was successfully loaded
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 PDB(s) will be processed
Type 'help' to list console commands
upg> Copying remote database 'ORADB' as 'ORADB' for job 100
Remote database 'ORADB' created as PDB 'ORADB' for job 100
Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs restored                  [0]
Jobs pending                   [0]



Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
[oracle@oradbvm2 autoupg]$
[oracle@oradbvm2 autoupg]$ cat /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
==========================================
          Autoupgrade Summary Report
==========================================
[Date]           Wed Nov 29 14:31:42 JST 2023
[Number of Jobs] 1
==========================================
[Job ID] 100
==========================================
[DB Name]                coradb_oradbvm1
[Version Before Upgrade] 19.20.0.0.0
[Version After Upgrade]  23.3.0.23.09
------------------------------------------
[Stage Name]    PREUPGRADE
[Status]        SUCCESS
[Start Time]    2023-11-29 13:50:47
[Duration]      0:00:00
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/preupgrade
------------------------------------------
[Stage Name]    DRAIN
[Status]        SUCCESS
[Start Time]    2023-11-29 13:50:47
[Duration]      0:00:02
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/drain
------------------------------------------
[Stage Name]    CLONEPDB
[Status]        SUCCESS
[Start Time]    2023-11-29 13:50:50
[Duration]      0:00:24
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/clonepdb
------------------------------------------
[Stage Name]    REFRESHPDB
[Status]        SUCCESS
[Start Time]    2023-11-29 13:51:14
[Duration]      0:00:03
------------------------------------------
[Stage Name]    DBUPGRADE
[Status]        SUCCESS
[Start Time]    2023-11-29 13:51:21
[Duration]      0:29:48★
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/dbupgrade
------------------------------------------
[Stage Name]    UNPLUGWORK
[Status]        SUCCESS
[Start Time]    2023-11-29 14:21:10
[Duration]      0:00:07
------------------------------------------
[Stage Name]    POSTCHECKS
[Status]        SUCCESS
[Start Time]    2023-11-29 14:21:17
[Duration]      0:00:14
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/postchecks
[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/postchecks/coradb_oradbvm1_postupgrade.log
                Check passed and no manual intervention needed
------------------------------------------
[Stage Name]    POSTFIXUPS
[Status]        SUCCESS
[Start Time]    2023-11-29 14:21:32
[Duration]      0:10:09★
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/postfixups
[Detail]        /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/postfixups/postfixups.html
------------------------------------------
[Stage Name]    POSTUPGRADE
[Status]        SUCCESS
[Start Time]    2023-11-29 14:31:41
[Duration]      0:00:00
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/postupgrade
------------------------------------------
[Stage Name]    SYSUPDATES
[Status]        SUCCESS
[Start Time]    2023-11-29 14:31:42
[Duration]
[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/sysupdates
------------------------------------------
Summary: /u01/app/oracle/cfgtoollogs/autoupgrade/dummy/100/dbupgrade/upg_summary.log
[oracle@oradbvm2 autoupg]$

６．事後作業


　ターゲットDBにて、早速移行されたPDBの状態を確認する。確かにORADBが作成されていることがわかる。
[oracle@oradbvm2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Sat Dec 2 17:36:34 2023
Version 23.3.0.23.09

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


Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SQL> show pdbs

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

　PDBのリソースの確認を行い、LISTENERでサービスが認識されることを確認する。
[oracle@oradbvm2 ~]$
[oracle@oradbvm2 ~]$ lsnrctl status LISTENER
...
Service "coradb_oradb.paas.oracle.com" has 1 instance(s).
  Instance "coradb", status READY, has 2 handler(s) for this service...
...
[grid@oradbvm2 ~]$ crsctl stat res -t
...
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
...
ora.coradb_oradbvm2.db
      1        ONLINE  ONLINE       oradbvm2                 Open,HOME=/u01/app/o
                                                             racle/product/23.0.0
                                                             .0/dbhome_1,STABLE
ora.coradb_oradbvm2.oradb.pdb ★
      1        ONLINE  ONLINE       oradbvm2                 READ WRITE,STABLE
ora.cvu
      1        ONLINE  ONLINE       oradbvm2                 STABLE
ora.oradbvm2.vip
      1        ONLINE  ONLINE       oradbvm2                 STABLE
ora.scan1.vip
      1        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------
[grid@oradbvm2 ~]$

　tnsnames.oraへの接続識別子を追加する。
[oracle@oradbvm2 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
...
ORADB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradbvm2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = coradb_oradb.paas.oracle.com)
    )
  )

　追加した接続識別子で接続確認する。EMP、DEPT表にソースDBのオブジェクトが格納されており、正しい件数が確認できた。
[oracle@oradbvm2 ~]$ sqlplus scott/XXX@oradb

SQL*Plus: Release 23.0.0.0.0 - Production on Sat Dec 2 17:43:45 2023
Version 23.3.0.23.09

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

Last Successful login time: Fri Nov 24 2023 12:56:29 +09:00

Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SQL> select count(*) from emp;

  COUNT(*)
----------
   1000000

SQL> select count(*) from dept;

  COUNT(*)
----------
    100000

SQL>

７．まとめ


　本稿では、AutoUpgradeを利用して、OCIの19cのPDBを23cのBase Databaseへアップグレードを行った。参考にした手順はあっさりしていたので、簡単に検証できると思ったが、環境の違いによるAutoUpgradeの想定外のエラー対応や、そもそもAutoUpgradeへの理解の不足等で思ったより手こずってしまった。
　AutoUpgradeはいずれ23cに対応するので、OCIコンソール画面から23cへアップグレードもできるようになるのは時間の問題だろう。それでも、本手順はアップグレード後も元の環境を残しておける点は有用と感じる。複数PDBを面として利用している場合など、切り替えを段階的に実行したいケースもあるだろう。
　AutoUpgradeは設定ファイルや環境の事前準備・検証が必要であるが、一度仕組みができてしまえば移行対象のDB数が多くても効率的に移行が実現できる点も大きなメリットである。今回は移行対象は1PDBだったが、複数PDBだったとしても設定ファイルに追加するだけである。効果的に利用するためにも、AutoUpgradeの仕様の理解を深めたい。

参考


[1]Upgrade Base Database Cloud Service to Oracle Database 23c
[2]AutoUpgrade Tool (Doc ID 2485457.1)
[3]Using AutoUpgrade for Oracle Database Upgrades
[4]autoupgradeの設定ファイル
oradb.cfg
---ソースDB用
global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
global.keystore=/u01/app/oracle/cfgtoollogs/keystore
upg1.source_home=/u01/app/oracle/product/19.0.0.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/23.0.0.0/dbhome_1
upg1.sid=coradb
upg1.pdbs=oradb
upg1.target_cdb=dummy
upg1.source_dblink.oradb=clonepdb 600
upg1.target_pdb_copy_option.oradb=file_name_convert=none
upg1.target_version=23
upg1.start_time=now
---ターゲットDB用
global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
global.keystore=/u01/app/oracle/cfgtoollogs/keystore
upg1.source_home=/u01/app/oracle/product/19.0.0.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/23.0.0.0/dbhome_1
upg1.sid=dummy
upg1.pdbs=oradb
upg1.target_cdb=coradb
upg1.source_dblink.oradb=clonepdb 600
upg1.target_pdb_copy_option.oradb=file_name_convert=none
upg1.target_version=23
upg1.start_time=now

[5]アナライズのプレチェック結果サンプル
[oracle@oradbvm1 ~]$ cat /u01/app/oracle/cfgtoollogs/autoupgrade/coradb/101/prechecks/coradb_oradbvm1_preupgrade.log
Report generated by AutoUpgrade 23.3.230728 (#58f81599) on 2023-11-29 10:47:25

Upgrade-To version: 23.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  coradb
     Container Name:  ORADB
       Container ID:  3
            Version:  19.20.0.0.0
     DB Patch Level:  Database Release Update : 19.20.0.0.230718 (35320081)
         Compatible:  19.0.0.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  40
  Database log mode:  ARCHIVELOG
           Readonly:  false
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Server                          [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Multimedia                      [to be removed]   VALID
  Oracle Workspace Manager               [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

  *
  * ALL Components in This Database Registry:
  *
  Component   Current      Current      Original     Previous     Component
  CID         Version      Status       Version      Version      Schema
  ----------  -----------  -----------  -----------  -----------  ------------
  APS         19.20.0.0.0  VALID        19.18.0.0.0               SYS
  CATALOG     19.20.0.0.0  VALID        19.18.0.0.0               SYS
  CATJAVA     19.20.0.0.0  VALID        19.18.0.0.0               SYS
  CATPROC     19.20.0.0.0  VALID        19.18.0.0.0               SYS
  CONTEXT     19.20.0.0.0  VALID        19.18.0.0.0               CTXSYS
  DV          19.20.0.0.0  VALID        19.18.0.0.0               DVSYS
  JAVAVM      19.20.0.0.0  VALID        19.18.0.0.0               SYS
  OLS         19.20.0.0.0  VALID        19.18.0.0.0               LBACSYS
  ORDIM       19.20.0.0.0  VALID        19.18.0.0.0               ORDSYS
  OWM         19.20.0.0.0  VALID        19.18.0.0.0               WMSYS
  RAC         19.20.0.0.0  VALID        19.18.0.0.0               SYS
  SDO         19.20.0.0.0  VALID        19.18.0.0.0               MDSYS
  XDB         19.20.0.0.0  VALID        19.18.0.0.0               XDB
  XML         19.20.0.0.0  VALID        19.18.0.0.0               SYS
  XOQ         19.20.0.0.0  VALID        19.18.0.0.0               OLAPSYS

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  1.  Perform the specified action for each database in order to satisfy
      AutoUpgrade's TDE keystore requirements. This will involve adding the TDE
      keystore password for the database into either AutoUpgrade's keystore
      using the -load_password command line option or into a Secure External
      Password Store (SEPS) for the database. Once the upgrade has finished and
      there is no intention to use AutoUpgrade's system restore functionality
      to rerun the upgrade, the AutoUpgrade keystore file(s) can be removed
      from the directory or path referenced by the global.keystore
      configuration parameter.

      At this point, either (1) the TDE keystore password(s) required by
      AutoUpgrade have not been loaded into AutoUpgrade's keystore or a Secure
      External Password Store or (2) the auto-login keystore status of the
      database has not been modified. Review the required actions for each of
      the following databases:

      ORACLE_SID                      Action Required
      ------------------------------  ----------------------------------------
      dummy                           Open database

      For AutoUpgrade to upgrade a database using Oracle Transparent Data
      Encryption (TDE), the following conditions must be met:

      1. The TDE keystore password(s) required by AutoUpgrade must be loaded
      into AutoUpgrade's keystore or a Secure External Password Store for the
      database.

      When the source database uses TDE, AutoUpgrade requires TDE passwords for
      the databases listed below:
      * Both the source non-CDB and the target CDB of a non-CDB to PDB operation
      * Both the source CDB and the target CDB of an unplug-plug operation
      * Only the target CDB of an unplug-relocate operation

      2. The target CDB, if specified, must have an auto-login TDE keystore if
      its version is earlier than Oracle Database 19.11

      3. To upgrade a non-CDB or an entire CDB, the TDE keystore must be an
      auto-login keystore. This requirement also applies to a non-CDB to PDB
      operation, but only if the target CDB is at an Oracle Database Release
      earlier than 21c. If earlier than 21c, AutoUpgrade performs a standard
      upgrade of the non-CDB to the target version prior to creating the PDB in
      the target CDB.

  2.  Open the target CDB, dummy. Additionally, rerun the previous AutoUpgrade
      command as there are multiple checks that depend on the target CDB being
      available. Those checks have been temporarily marked as successful until
      the target CDB is open.

      The target CDB, dummy, is closed or unavailable.

      The target CDB, dummy, must be open in order to create a PDB during a
      non-CDB-to-PDB or unplug-plug operation.

  RECOMMENDED ACTIONS
  ===================
  3.  (AUTOFIXUP) Connect to the database as SYS to drop all Data Pump Advanced
      Queuing (AQ) tables prior to upgrading. Check MOS note 2789059.1 for
      details.

      There exists at least one Data Pump Advanced Queuing (AQ) table in the
      SYS schema which might prevent Data Pump AQ message types from getting
      re-created.

      The database needs to be free of Data Pump Advanced Queuing (AQ) tables
      in order for Data Pump AQ message types to be re-created during the
      database upgrade.

  4.  (AUTOFIXUP) Patch the new 23 $ORACLE_HOME/oracore/zoneinfo/ with the
      version 40 time zone data file from the 19.0.0.0.0
      $ORACLE_HOME/oracore/zoneinfo/.

      The database is using a time zone file version 40 that is newer than the
      version 36 in the target $ORACLE_HOME/oracore/zoneinfo directory.

      The time zone file version used in your database must exist in the new
      Oracle home before upgrading the database.

  5.  Convert your traditional audit configurations to unified audit policies
      and enable them. To continue using traditional audit in 23c, make sure
      initialization parameters AUDIT_TRAIL and AUDIT_SYS_OPERATIONS are set in
      the database after the upgrade process. This is intended as a temporary
      measure until you have time to convert to unified audit. Refer to MOS
      note 2909718.1 for more details on converting to unified audit.

      Traditional audit configuration is found in this database.

      Starting in 23c, Oracle unified audit is the auditing configuration for
      use in newly created databases. Support for traditional audit in 23c is
      limited to upgraded databases. On database upgrades, existent traditional
      audit settings are operational post upgrade for continued generation of
      audit records to the traditional audit trails. However, new traditional
      audit configurations cannot be created. Oracle strongly recommends to
      start using unified audit at the earliest opportunity.

  6.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 19.0.0.0
      Oracle Database Upgrade Guide.

  INFORMATION ONLY
  ================
  7.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSTEM                             540 MB       844 MB
      SYSAUX                             500 MB       511 MB

      Minimum tablespace sizes for upgrade are estimates.

  8.  Follow the instructions in the Oracle Multimedia README.txt file in <23
      ORACLE_HOME>/ord/im/admin/README.txt, or MOS note 2555923.1 to determine
      if Oracle Multimedia is being used. If Oracle Multimedia is being used,
      refer to MOS note 2347372.1 for suggestions on replacing Oracle
      Multimedia.

      Oracle Multimedia component (ORDIM) is installed.

      Starting in release 19c, Oracle Multimedia is desupported. Object types
      still exist, but methods and procedures will raise an exception. Refer to
      23 Oracle Database Upgrade Guide, the Oracle Multimedia README.txt file
      in <23 ORACLE_HOME>/ord/im/admin/README.txt, or MOS note 2555923.1 for
      more information.

  9.  Here are ALL the components in this database registry:

      Component Current     Current     Original    Previous    Component
      CID       Version     Status      Version     Version     Schema
      --------- ----------- ----------- ----------- ----------- -----------
      APS       19.20.0.0.0 VALID       19.18.0.0.0             SYS
      CATALOG   19.20.0.0.0 VALID       19.18.0.0.0             SYS
      CATJAVA   19.20.0.0.0 VALID       19.18.0.0.0             SYS
      CATPROC   19.20.0.0.0 VALID       19.18.0.0.0             SYS
      CONTEXT   19.20.0.0.0 VALID       19.18.0.0.0             CTXSYS
      DV        19.20.0.0.0 VALID       19.18.0.0.0             DVSYS
      JAVAVM    19.20.0.0.0 VALID       19.18.0.0.0             SYS
      OLS       19.20.0.0.0 VALID       19.18.0.0.0             LBACSYS
      ORDIM     19.20.0.0.0 VALID       19.18.0.0.0             ORDSYS
      OWM       19.20.0.0.0 VALID       19.18.0.0.0             WMSYS
      RAC       19.20.0.0.0 VALID       19.18.0.0.0             SYS
      SDO       19.20.0.0.0 VALID       19.18.0.0.0             MDSYS
      XDB       19.20.0.0.0 VALID       19.18.0.0.0             XDB
      XML       19.20.0.0.0 VALID       19.18.0.0.0             SYS
      XOQ       19.20.0.0.0 VALID       19.18.0.0.0             OLAPSYS

      Review the information before upgrading.

  10. Here is a count of invalid objects by Oracle-maintained users:

      Oracle-Maintained User Name                 Number of INVALID Objects
      ---------------------------                 -------------------------
      None                                        None

      Review the information before upgrading.

  11. Here is a count of invalid objects by Application users:

      Application User Name                       Number of INVALID Objects
      ---------------------------                 -------------------------
      None                                        None

      Review the information before upgrading.

  12. No action needed. Enterprise Manager Database Express will be removed by
      the upgrade process.

      Enterprise Manager Database Express is present. The database has EM
      Express files and objects.

      Starting with Oracle Database 23c, Enterprise Manager Database Express is
      de-supported. Any EM Express specific files and objects will removed from
      your database during the upgrade. EM Express ports will no longer be
      opened to accept any HTTP request. Roles EM_EXPRESS_BASIC and
      EM_EXPRESS_ALL as well as "EM Express Connect" privilege will be removed.
      If user is to downgrade to a release earlier than 23c, EM Express will be
      restored, including all of its files and objects, as well as the
      EM_EXPRESS_BASIC and EM_EXPRESS_ALL roles and "EM Express Connect"
      privilege. However, any specific non out-of-box user grants and audit
      policies of these roles and privilege will not be restored upon downgrade.

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  13. (AUTOFIXUP) Recompile the objects with timestamp mismatch. Refer to MOS
      note 781959.1 for more details.

      There are objects whose timestamp are mismatched with its parent objects.

      Timestamp of dependent objects must coincide with the timestamp of parent
      objects.

  14. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  15. Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      Oracle recommends gathering fixed object statistics after upgrade. This
      recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. Those statistics
      are specific to the Oracle Database release that generates them, and can
      be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 19.0.0.0
      Oracle Database Upgrade Guide.

  16. (AUTOFIXUP) Run $ORACLE_HOME/rdbms/admin/utlprpom.sql in order to
      recompile.

      There are invalid objects in Oracle-maintained schemas after upgrade.

      Invalid database objects need to be recompiled after the upgrade.

  INFORMATION ONLY
  ================
  17. In a CDB, you can recompile in N PDBs in parallel and have each PDB's
      recompilation use R degree of parallelism.
      For example:
        $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b recomp
      -d $ORACLE_HOME/rdbms/admin -n  -l /tmp utlprp.sql
      '--p'
      Note:
       a) If total number of PDBs to recompile in is more than cpu_count/2, you
      can start with N as cpu_count/2; else set N to the number of PDBs.
       b) You can start with R as 2, as in:  ..catcon.pl .. utlprp.sql '--p2'
       c) And you can adjust N and R as needed for your database and system.
       d) Note: cpu_count is an Oracle initialization parameter.
       e) Note: For 23c and higher use utlprpom.sql instead of utlprp.sql in
      the catcon.pl command above.

      This is an informational message on how you can run utlrp in a CDB.

      Recompiling invalid objects in a CDB can be done in multiple PDBs in
      parallel.

2023-12-20 07:27 

2023年11月｜2023年12月 ｜2024年01月 ブログトップ

Kazuhiro

データベーススペシャリスト
Oracle ACE
Oracle Community
Linked-in