DBのCPUサイジングについての一考察 [アーキテクチャ]
はじめに
DBサーバにどの程度のCPUが必要かを見積りしなければならない状況はよくある。
基盤更改の場合は、現行のシステムがあり、そのHWスペックもリソース状況もわかっているので、CPUの性能比から概ね精度の高い見積もりが可能である。しかし、全く新規業務の場合は、机上でどの程度のCPUが必要かを導くことは難しい。しかも、業務仕様や処理方式が決まっていない状況ではなおさらである。このような場合、想定する処理方式に対する性能の基礎数値が重要となる。
本稿では、DBの基礎的な処理（INSERT, SELECT, UPDATE, DELETE）の基礎数値を取得する簡単なモデルと処理（PL/SQL）を、OCIの環境で実測し、その結果を考察してみたい。なお、性能の結果は環境によって変わるので、あくまで参考程度と考えてほしい。
１．性能モデル
性能モデルとして、3つの表、b10tbl, b50tbl, b100tblを考える。
idカラムをnumber型の主キーとし、1からの連番を振る。dtカラムはdate型で、更新日時を入れる。col1～colnはvarchar2(10)のカラムで、b10tblは1個、b50tblは5個、b100tblは10個とし、ランダムな文字列を10バイト挿入する。初期データ件数は100万件とする。
この表に対して、以下のパタンで基礎性能値を確認する。1番目はselectのフルスキャン、2～5番目は索引アクセスのパタン（insert、select、update、delete）である。索引アクセスは10万回行う。
２．表（データ）の準備
表を作成するDDLは以下の通り。
drop table b10tbl cascade constraints; create table b10tbl( id number(7), dt date, col1 varchar2(10), constraint pk_b10tbl primary key (id) ); drop table b50tbl cascade constraints; create table b50tbl( id number(7), dt date, col1 varchar2(10), col2 varchar2(10), col3 varchar2(10), col4 varchar2(10), col5 varchar2(10), constraint pk_b50tbl primary key (id) ); drop table b100tbl cascade constraints; create table b100tbl( id number(7), dt date, col1 varchar2(10), col2 varchar2(10), col3 varchar2(10), col4 varchar2(10), col5 varchar2(10), col6 varchar2(10), col7 varchar2(10), col8 varchar2(10), col9 varchar2(10), col10 varchar2(10), constraint pk_b100tbl primary key (id) );
初期データを挿入する。
insert into b10tbl select
rownum id,
sysdate dt,
dbms_random.string('x',10) col1
from dual connect by level <=1000000;
commit;
insert into b50tbl select
rownum id,
sysdate dt,
dbms_random.string('x',10) col1,
dbms_random.string('x',10) col2,
dbms_random.string('x',10) col3,
dbms_random.string('x',10) col4,
dbms_random.string('x',10) col5
from dual connect by level <=1000000;
commit;
insert into b100tbl select
rownum id,
sysdate dt,
dbms_random.string('x',10) col1,
dbms_random.string('x',10) col2,
dbms_random.string('x',10) col3,
dbms_random.string('x',10) col4,
dbms_random.string('x',10) col5,
dbms_random.string('x',10) col6,
dbms_random.string('x',10) col7,
dbms_random.string('x',10) col8,
dbms_random.string('x',10) col9,
dbms_random.string('x',10) col10
from dual connect by level <=1000000;
commit;
統計情報を取得する。
exec dbms_stats.gather_table_stats('scott','b10tbl');
exec dbms_stats.gather_table_stats('scott','b50tbl');
exec dbms_stats.gather_table_stats('scott','b100tbl');
作成した表のサイズを確認する。
col segment_name for a20 select segment_name, sum(bytes)/1024/1024 mb from user_segments where segment_name like '%B%TBL' group by segment_name order by 1;
３．測定
フルスキャン性能については、シンプルにautotraceの処理時間を想定する。select *としたのは、フルスキャンに主キーの索引が使われないにするためである。以下はb10tblの例である。
set timi on set autotrace traceonly select * from b10tbl; set autotrace off
INSERT性能は、PL/SQLで100万件の状態から10万件を追加する。この際、dtには更新日時、colxにはdbms_randomでランダムな文字列を生成して設定する。以下の例はb10tblなので、col1カラムだけであるが、b50tblでは5カラム、b100tblでは10カラム分、ランダムな文字列を生成し設定する。また、コミットは1件毎に実施する。
declare
i number;
begin
for i in 1000001..1100000 loop
insert into b10tbl values(i,sysdate,dbms_random.string('x',10));
commit;
end loop;
end;
/
SELECT性能は、同様にPL/SQLで、INSERTで積まれた10万件に対して、主キー一本引きのSQLを発行する。以下の例はb10tblなので、col1カラムだけであるが、b50tblでは5カラム、b100tblでは10カラム分、カラムを取得する。
declare i number; l_col1 varchar2(10); begin for i in 1000001..1100000 loop select col1 into l_col1 from b10tbl where id=i; end loop; end; /
SELECT性能は、同様にPL/SQLで、INSERTで積まれた10万件に対して、主キー一本引きでUPDATEを発行する。この際、dtには更新日時、colxにはdbms_randomでランダムな文字列を生成して設定する。以下の例はb10tblなので、col1カラムだけであるが、b50tblでは5カラム、b100tblでは10カラム分、ランダムな文字列を生成し設定する。コミットは1件毎に実施する。
declare
i number;
begin
for i in 1..100000 loop
update b10tbl set dt=sysdate,col1=dbms_random.string('u',10) where id=1000000+i;
commit;
end loop;
end;
/
DELETE性能は、同様にPL/SQLで、INSERTで積まれた10万件に対して、主キー一本引きのDELETEを発行する。コミットは1件毎に実施する。
declare i number; begin for i in 1000001..1100000 loop delete from b10tbl where id=i; commit; end loop; end; /
４．測定環境
上記のモデルを実際にOCIのBaseDBを利用して測定する。環境は以下の通り。
Shape: VM.Standard.E4.Flex CPU core count: 2 Oracle Database software edition: Enterprise Edition High Performance Storage management software: Oracle Grid Infrastructure Available data storage: 256 GB Recovery area storage: 256 GB Total storage size: 712 GB Theoretical max IOPS: 19.2K DB system version: 19.20.0.0.0
メモリの状態は以下の通り。SGAは14.5GB、そのうちバッファキャッシュは12GBである。
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
------------ ------------
Host Mem (MB): 31,893.9 31,893.9
SGA use (MB): 14,848.0 14,848.0
PGA use (MB): 1,084.4 1,106.1
% Host Mem used for SGA+PGA: 49.95 50.02
Database Resource Limits
~~~~~~~~~~~~~~~~~~~~~~~~ Begin End
---------------- ----------------
CPUs: 4 4
SGA Target: 15,569,256,448 15,569,256,448
PGA Target: 3,892,314,112 3,892,314,112
Memory Target: 0 0
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 12,352M 12,352M Std Block Size: 8K
Shared Pool Size: 1,405M 1,404M Log Buffer: 154,872K
In-Memory Area: 0M 0M
上記の方法でデータを作成したところ、以下のサイズ（表と索引）となった。合計でたかだか338MBなので、全てバッファキャッシュに十分収まる。
SEGMENT_NAME MB -------------------- ---------- B100TBL 157 B10TBL 36 B50TBL 94 PK_B100TBL 17 PK_B10TBL 17 PK_B50TBL 17
５．測定結果
下表に測定結果を示す。rowsは処理件数、elapsed(ms)は処理の経過時間を示す。1番目のsel_fullはautotraceの経過時間、その他2～5番目はSQLレポート上の経過時間を示す。ms/rowは、1件あたりの処理時間を示す。例えばsel_fullであれば100万件の処理時間を100万で割った数値、sel_idxであれば、10万件の処理時間を10万で割った数値を示す。tpsは1をms/rowで割った値で、単位時間（1秒）あたりの処理件数を示す。
なお、参考[1]に、測定結果とあわせて、AWRレポート、SQLレポートをまとめたexcelシートを載せているので、詳細に興味があればそちらを確認頂きたい。
６．考察
フルスキャンはカラム長が長くなるほどに処理時間が延びる。カラム長が長いと参照ブロック数が増えることになるので、当然だろう。表と索引はバッファキャッシュに乗っているため、処理時間にIOは含まれていない数字と理解する必要がある。
SELECTはカラム数が増えるとじわりと処理時間が伸びていることがわかる。SQLレポートのGetsを見る限り、SELECTは4ブロック/件の論理ブロック読み込みをしていた。索引に3ブロック(BLEVEL=2)、表に1ブロックの読み込みをしていると推測される。通常の索引はBLEVEL=3位になっていることが多いので、少し軽めな索引アクセスと理解する必要がある。
INSERTとUPDATEはほぼおなじ程度の処理単価となっている。カラム増加に対する処理時間の傾向もほぼ同じである。SQLレポートのGetsを見る限り、INSERTは5ブロック/件、UPDATEは6ブロック/件の論理ブロック読み込みをしていた。このうち4ブロックは更新ブロックの特定に使われているはずなので、残りが純粋な更新に必要なUNDO関連のブロック数と思われる。カラム増加による処理時間の伸びが顕著なのは、dbms_randomの呼び出し回数の影響が大きいかもしれない。純粋なDMLの処理時間を確認する意味では、関数を含めない方が良かったかもしれない。
DELETEはカラム数の増加の影響はかなり限定的に見える。SQLレポートのGetsを見る限り、8ブロック/件の論理ブロック読み込みをしていた。これも4ブロックは更新ブロックの特定に使われているはずで、残りがDELETEに必要なUNDO関連のブロックなのだろう。更新よりブロック数が多いのは、索引の更新に伴うものかもしれない（削除に伴い、索引のB*Treeおよびリーフブロックの更新が必要）。
単位時間あたりのトランザクション数（tps）については、総じてSQLの性能は、DMLより一桁は速い。INSERTやUPDATEで、b10tblで18倍、b100tblで40倍もの差がある。DELETEは比較的軽いが、それでも10倍程度の差がある。
実際のシステムにおいては、一定の比率で参照、更新トランザクションが発生するので、上記を基礎数値として1コアあたりにさばけるトランザクション数が導き出せる。例えば、b100tblのモデルでselectが80%、insertが10%、updateが7%、deleteが3%の比率とすると、196tps/コアとなる。このような考え方で、必要な単位時間あたりのトランザクションに応じて、コア数を見積もることができる。実際に使うコアのスペックが異なる場合は、ここからspec int等のベンチマーク結果から、コア性能を考慮した見積もりを作れば良いだろう。
７．まとめ
DBサーバのCPUの見積もりについて、モデル（データ、処理モデル）から、OCIで実測した結果をもとに基礎数値を求めて、コア数を見積もる考え方について述べた。また、測定結果について、SQL、DMLの性能の傾向、カラム数（平均行長）に対する性能の傾向について考察した。
性能測定結果は環境によって変わるので、あくまで参考程度にしかならないとしても、実際の業務処理ロジックに近いモデルを作ることで、見積もりの精度を上げることができるだろう。
ありがたいことに、OCIのようなパブリッククラウドが使えるようになり、実機による測定ができる環境を実に簡単に準備できるようになった。いくばくかの利用料は発生するかもしれないが、コア数を比較的精度高く見積もることは、Oracleライセンス費の適正化につながるので、サイジング用にOCI使ったとしても十分にその価値はあるのではないかと感じる。
◆参考
[1]測定結果詳細：20240211_性能テスト結果_v2.xlsx
2024-02-11 14:03
Oracle19cでメンテナンスウィンドウのCPU高騰をチューニングした話 [アーキテクチャ]
OCIのBaseDB（Oracle19c）でメンテナンスウィンドウ時間帯のCPU高騰をチューニングしたので、メモを残しておく。結論から言うと、統計情報取得処理の中で、カラム統計の保留統計を削除するdelete文(5hud5urmn39bx)の実行計画が非効率だったので、SQLパッチを適用して回避したという話である。以下、事象、原因、対処について簡単に記載する。
OCIのBaseDB（19.11）で、メンテナンス時間帯にDBサーバのCPU使用率がかなり上がっていることが問題になっていた。メンテナンスウィンドウは平日7時～11時までに設定されていたが、メンテナンス時間中に複数のPDBで同時に以下のSQLが長時間化しており、CPUをほぼ使い切ってしまっている状況であった。
wri$_optstat_histhead_history表は、日次のパーティション表で、カラム統計のバックアップを保持する。通常はSAVTIMEはバックアップ日時が入るが、保留統計を使っている場合は、テーブル毎、カラム毎にSAVTIME=3000年12月1日で記録される。つまり、保留統計は全て同じパーティションに入る。
このdelete文は、savtime, obj#, intcol#の3つのプレディケートで削除対象を特定し、（保留）統計情報取得の際に、不要となった保留統計情報を削除していると思われる。様々な表のカラム統計が全く同じSAVTIMEで記録されるため、ある表の保留統計のレコードを識別するためにSAVTIMEでは絞り込めない。このため、表の数が増えてくると、保留統計取得に伴う過去カラム統計の削除に時間がかかるようになるのである。
SQLの実行計画は以下の通り。
◆非効率な実行計画
グローバルファンクション索引I_WRI$_OPTSTAT_HH_STでsavtimeで絞り込み、obj#とintcol#でフィルタをかけて削除対象のレコードを特定していることがわかる。この実行計画だと、同じSAVTIMEを持つレコードの件数が多いと、フィルタの絞り込みに時間がかる。
AWRレポートから、SQLの実行回数は6,571で、平均実行時間は0.48秒、論理読み込みブロック数は8,196.7である。保留統計の削除は全てsavtimeが同一であることから、パーティションのフルスキャンを繰り返し、結果としてバッファキャッシュ上のブロック読み込みでCPUを使ってしまう。そして、この処理が複数PDB（メンテナンス時間が一緒）で並列実行されるため、DBサーバのCPUが上昇してしまう、という状況を作り出していたのだろう。
他に有効な索引がないか確認したところ、以下のようにI_WRI$_OPTSTAT_HH_OBJ_ICOL_STという索引があった。これはobj#、intcol#、SYS_NC00027$で構成されているので、savtimeだけの絞り込みより効果がありそうである。
ちなみにSYS_NC00027$は仮想列で、TIMESTAMP WITH TIME ZONE型のsavtimeから、UTC時間を取り出していることが確認できる。
このdelete文は統計情報取得のプロシージャ内部で発行されているため、直接ヒントは使えない。SQLパッチで直接SQL_IDを指定して、I_WRI$_OPTSTAT_HH_OBJ_ICOL_STの索引を使うように誘導する。
SQLパッチ作成には、SQL_IDがv$sqlに存在している必要がある。遅延が発生している状況であれば、メンテナンス時間帯またはその直後ならライブラリキャッシュに確認できるはずである。統計情報を手動で取得すればこのdelete文も内部から発行されるはずである。当該SQLパッチは、PDB毎に設定する必要がある点に注意が必要である（CDBに設定してもPDBには効果がない）。
SQLパッチを適用した後、実行計画を確認したところ、期待通りI_WRI$_OPTSTAT_HH_OBJ_ICOL_STの索引が使われている。こちらの実行計画では索引でobj#とsavtimeを絞り込んでから、savtimeとintcol#でフィルターする形になる（索引だけではsavtimeを絞り込み切れないため、フィルタにも現れていると思われる）。
◆改善後の実行計画
上記の状態で、後日メンテナンスウィンドウ時間帯のリソースを確認したところ、このDMLによるCPU張り付きの事象は解消することができた。AWRのSQL ordered by...に現れない状態になった。
今回の事象を簡単に図に示す。遅延は(1)のI_WRI$_OPTSTAT_HH_STの索引を使ったとき、索引でsavtimeで絞り込んだ後、行をobj#（およびintcol#）で絞り込む部分で発生していたと考えられる。実際は保留統計の場合、savtimeは全て同じ値になっているので、全く絞り込みは効かない。(2)は、I_WRI$_OPTSTAT_HH_OBJ_ICOL_STの索引でobj#とsavtimeで絞り込み、行をsavtime（およびintcol#）で絞り込むため、効率が良い。
根本的な問いは、SQL_ID:5hud5urmn39bxにおいてI_WRI$_OPTSTAT_HH_OBJ_ICOL_ST ではなくI_WRI$_OPTSTAT_HH_ST が利用されていた要因は何か、という点である。10053トレース確認したところ、オプティマイザは両者の索引を比較して、I_WRI$_OPTSTAT_HH_STの方がコストが少ないと判断している。実際のトレースを見てみよう。
オプティマイザは★aでI_WRI$_OPTSTAT_HH_OBJ_ICOL_STのコスト計算を開始している。★bで索引の選択率を0.005435と推定し、★cでコストを4と推定している。その後、★dでI_WRI$_OPTSTAT_HH_STのコスト計算を開始し、★eで索引の選択率を7.3638e-04=0.00073と推定し、コストを2.001352と推定している。以上の結果から★fでI_WRI$_OPTSTAT_HH_STの方がコストが低いため、★gでこの索引を使う実行計画（非効率な実行計画）を選択している。問題は★eの部分において、保留統計の場合はsavtimeが全て同じ値になってしまう点が考慮されていないことではないかと感じている。col#27のヒストグラムが取得されており、バインドピークが有効になっていれば、適切な実行計画が選択されるかもしれない、という期待はあるが、本環境ではバインドピークは無効化しているため、そこまでの確認は行っていない。
本稿では、統計情報履歴削除のdelete文（5hud5urmn39bx）遅延の原因と対処方法について記載した。これは過去に保留統計でヒストグラムの統計履歴削除で遅延した事象（参考[1]）と類似事象である。今回はヒストグラム統計は取得しない設定にしていたため、この問題には該当しなかった。
本件をサポートに問い合わせたところ、対処方法についてはSQLパッチが妥当とのことであった。改善要望として、当該回避方法を公開ドキュメント化していただけるようなので、そのうち、公になるかもしれない。
保留統計を使って、マルチテナントでPDBを多く抱えているようなDBでは、このCPU高騰の事象が顕在化しやすいので留意した方が良いかもしれない。
[1]保留統計との闘い～統計取得遅延問題
[2]WRI$_OPTSTAT_HISTHEAD_HISTORYの構成情報
・表・パーティション構成
・索引の構成
以上
１．事象
OCIのBaseDB（19.11）で、メンテナンス時間帯にDBサーバのCPU使用率がかなり上がっていることが問題になっていた。メンテナンスウィンドウは平日7時～11時までに設定されていたが、メンテナンス時間中に複数のPDBで同時に以下のSQLが長時間化しており、CPUをほぼ使い切ってしまっている状況であった。
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
3,148.5 6,571 0.48 36.4 94.2 .0 5hud5urmn39bx
PDB: XXX
delete /* QOSH:PURGE_OLD_STS *//*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history
where :1 = savtime and obj# = :2 and intcol# = nvl(:3, intcol#)
Buffer Gets Elapsed
Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id
----------- ----------- ------------ ------ ---------- ----- ----- -------------
53,860,231 6,571 8,196.7 30.6 3,148.5 94.2 .0 5hud5urmn39bx
PDB: XXX
delete /* QOSH:PURGE_OLD_STS *//*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history
where :1 = savtime and obj# = :2 and intcol# = nvl(:3, intcol#)
２．解析
wri$_optstat_histhead_history表は、日次のパーティション表で、カラム統計のバックアップを保持する。通常はSAVTIMEはバックアップ日時が入るが、保留統計を使っている場合は、テーブル毎、カラム毎にSAVTIME=3000年12月1日で記録される。つまり、保留統計は全て同じパーティションに入る。
このdelete文は、savtime, obj#, intcol#の3つのプレディケートで削除対象を特定し、（保留）統計情報取得の際に、不要となった保留統計情報を削除していると思われる。様々な表のカラム統計が全く同じSAVTIMEで記録されるため、ある表の保留統計のレコードを識別するためにSAVTIMEでは絞り込めない。このため、表の数が増えてくると、保留統計取得に伴う過去カラム統計の削除に時間がかかるようになるのである。
SQLの実行計画は以下の通り。
◆非効率な実行計画
Plan hash value: 120054584
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 85 | 4 (0)| 00:00:01 | | |
| 1 | DELETE | WRI$_OPTSTAT_HISTHEAD_HISTORY | | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| WRI$_OPTSTAT_HISTHEAD_HISTORY | 1 | 85 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | I_WRI$_OPTSTAT_HH_ST | 19 | | 3 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJ#"=TO_NUMBER(:2) AND "INTCOL#"=NVL(:3,"INTCOL#"))
3 - access(SYS_EXTRACT_UTC("SAVTIME")=SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ(:1)))
グローバルファンクション索引I_WRI$_OPTSTAT_HH_STでsavtimeで絞り込み、obj#とintcol#でフィルタをかけて削除対象のレコードを特定していることがわかる。この実行計画だと、同じSAVTIMEを持つレコードの件数が多いと、フィルタの絞り込みに時間がかる。
AWRレポートから、SQLの実行回数は6,571で、平均実行時間は0.48秒、論理読み込みブロック数は8,196.7である。保留統計の削除は全てsavtimeが同一であることから、パーティションのフルスキャンを繰り返し、結果としてバッファキャッシュ上のブロック読み込みでCPUを使ってしまう。そして、この処理が複数PDB（メンテナンス時間が一緒）で並列実行されるため、DBサーバのCPUが上昇してしまう、という状況を作り出していたのだろう。
他に有効な索引がないか確認したところ、以下のようにI_WRI$_OPTSTAT_HH_OBJ_ICOL_STという索引があった。これはobj#、intcol#、SYS_NC00027$で構成されているので、savtimeだけの絞り込みより効果がありそうである。
SQL> select index_name, column_name from user_ind_columns where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY' order by index_name, column_position; INDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST OBJ# I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INTCOL# I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST SYS_NC00027$ I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST COLNAME I_WRI$_OPTSTAT_HH_ST SYS_NC00027$
ちなみにSYS_NC00027$は仮想列で、TIMESTAMP WITH TIME ZONE型のsavtimeから、UTC時間を取り出していることが確認できる。
SQL> select index_name, column_position, column_expression from user_ind_expressions where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';
INDEX_NAME COLUMN_POSITION COLUMN_EXPRESSION
------------------------------ --------------- --------------------------------------------------------------------------------
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 3 SYS_EXTRACT_UTC("SAVTIME")
I_WRI$_OPTSTAT_HH_ST 1 SYS_EXTRACT_UTC("SAVTIME")
３．対処（チューニング）
このdelete文は統計情報取得のプロシージャ内部で発行されているため、直接ヒントは使えない。SQLパッチで直接SQL_IDを指定して、I_WRI$_OPTSTAT_HH_OBJ_ICOL_STの索引を使うように誘導する。
SQLパッチ作成には、SQL_IDがv$sqlに存在している必要がある。遅延が発生している状況であれば、メンテナンス時間帯またはその直後ならライブラリキャッシュに確認できるはずである。統計情報を手動で取得すればこのdelete文も内部から発行されるはずである。当該SQLパッチは、PDB毎に設定する必要がある点に注意が必要である（CDBに設定してもPDBには効果がない）。
declare patch_name varchar2(20); begin patch_name := dbms_sqldiag.create_sql_patch( sql_id=>'5hud5urmn39bx', hint_text=>'index(@DEL$1 WRI$_OPTSTAT_HISTHEAD_HISTORY@DEL$1 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST)', name=>'5hud5urmn39bx_patch'); end; /
SQLパッチを適用した後、実行計画を確認したところ、期待通りI_WRI$_OPTSTAT_HH_OBJ_ICOL_STの索引が使われている。こちらの実行計画では索引でobj#とsavtimeを絞り込んでから、savtimeとintcol#でフィルターする形になる（索引だけではsavtimeを絞り込み切れないため、フィルタにも現れていると思われる）。
◆改善後の実行計画
Plan hash value: 3378320514
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 72 | 4 (0)| 00:00:01 | | |
| 1 | DELETE | WRI$_OPTSTAT_HISTHEAD_HISTORY | | | | | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| WRI$_OPTSTAT_HISTHEAD_HISTORY | 1 | 72 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST | 1 | | 3 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJ#"=TO_NUMBER(:2) AND SYS_EXTRACT_UTC("SAVTIME")=SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ(:1)))
filter("INTCOL#"=NVL(:3,"INTCOL#") AND SYS_EXTRACT_UTC("SAVTIME")=SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ(:1)))
上記の状態で、後日メンテナンスウィンドウ時間帯のリソースを確認したところ、このDMLによるCPU張り付きの事象は解消することができた。AWRのSQL ordered by...に現れない状態になった。
４．考察
今回の事象を簡単に図に示す。遅延は(1)のI_WRI$_OPTSTAT_HH_STの索引を使ったとき、索引でsavtimeで絞り込んだ後、行をobj#（およびintcol#）で絞り込む部分で発生していたと考えられる。実際は保留統計の場合、savtimeは全て同じ値になっているので、全く絞り込みは効かない。(2)は、I_WRI$_OPTSTAT_HH_OBJ_ICOL_STの索引でobj#とsavtimeで絞り込み、行をsavtime（およびintcol#）で絞り込むため、効率が良い。
根本的な問いは、SQL_ID:5hud5urmn39bxにおいてI_WRI$_OPTSTAT_HH_OBJ_ICOL_ST ではなくI_WRI$_OPTSTAT_HH_ST が利用されていた要因は何か、という点である。10053トレース確認したところ、オプティマイザは両者の索引を比較して、I_WRI$_OPTSTAT_HH_STの方がコストが少ないと判断している。実際のトレースを見てみよう。
****** Costing Index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST ★a (2)I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST索引のコスト計算（効率的な実行計画）
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Estimated selectivity: 0.005435 , col: #1 →obj#の選択率
Estimated selectivity: 7.3638e-04 , col: #27 →savtime(仮想列)の選択率：7.3638e-04=0.00073
Estimated selectivity: 0.009434 , col: #2 →intcol#の選択率
Access Path: index (RangeScan)
Index: I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
resc_io: 4.000000 resc_cpu: 57356
ix_sel: 0.005435 ix_sel_with_filters: 3.7755e-08 ★b (2)の索引の検索率(ix_sel)はobj#と同じ
***** Virtual column Adjustment ******
Column name SYS_NC00027$
Adjusted cost_cpu 250
Adjusted cost_io 0.000000
***** End virtual column Adjustment ******
Cost: 4.004604 Resp: 4.004604 Degree: 1 ★c (2)のコストは4
****** Costing Index I_WRI$_OPTSTAT_HH_ST★d (1)I_WRI$_OPTSTAT_HH_ST索引のコスト計算（非効率な実行計画）
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Estimated selectivity: 7.3638e-04 , col: #27 →savtime(仮想列)の選択率
Access Path: index (AllEqRange)
Index: I_WRI$_OPTSTAT_HH_ST
resc_io: 2.000000 resc_cpu: 29484
ix_sel: 7.3638e-04 ix_sel_with_filters: 7.3638e-04 ★e (1)の索引の検索率(ix_sel)はsavtime
Cost: 2.001352 Resp: 2.001352 Degree: 1
Best:: AccessPath: IndexRange ★f (1)の方が良い（いままでのうちベスト）と判定している
Index: I_WRI$_OPTSTAT_HH_ST
Cost: 2.001352 Degree: 1 Resp: 2.001352 Card: 0.000983 Bytes: 0.000000 ★g (1)の索引の実行計画を選定
オプティマイザは★aでI_WRI$_OPTSTAT_HH_OBJ_ICOL_STのコスト計算を開始している。★bで索引の選択率を0.005435と推定し、★cでコストを4と推定している。その後、★dでI_WRI$_OPTSTAT_HH_STのコスト計算を開始し、★eで索引の選択率を7.3638e-04=0.00073と推定し、コストを2.001352と推定している。以上の結果から★fでI_WRI$_OPTSTAT_HH_STの方がコストが低いため、★gでこの索引を使う実行計画（非効率な実行計画）を選択している。問題は★eの部分において、保留統計の場合はsavtimeが全て同じ値になってしまう点が考慮されていないことではないかと感じている。col#27のヒストグラムが取得されており、バインドピークが有効になっていれば、適切な実行計画が選択されるかもしれない、という期待はあるが、本環境ではバインドピークは無効化しているため、そこまでの確認は行っていない。
５．まとめ
本稿では、統計情報履歴削除のdelete文（5hud5urmn39bx）遅延の原因と対処方法について記載した。これは過去に保留統計でヒストグラムの統計履歴削除で遅延した事象（参考[1]）と類似事象である。今回はヒストグラム統計は取得しない設定にしていたため、この問題には該当しなかった。
本件をサポートに問い合わせたところ、対処方法についてはSQLパッチが妥当とのことであった。改善要望として、当該回避方法を公開ドキュメント化していただけるようなので、そのうち、公になるかもしれない。
保留統計を使って、マルチテナントでPDBを多く抱えているようなDBでは、このCPU高騰の事象が顕在化しやすいので留意した方が良いかもしれない。
参考
[1]保留統計との闘い～統計取得遅延問題
[2]WRI$_OPTSTAT_HISTHEAD_HISTORYの構成情報
・表・パーティション構成
SQL> select internal_column_id, column_name, nullable, data_type, virtual_column from user_tab_cols where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY' order by 1;
INTERNAL_COLUMN_ID COLUMN_NAME N DATA_TYPE VIR
------------------ -------------------- - ------------------------------ ---
1 OBJ# N NUMBER NO
2 INTCOL# N NUMBER NO
3 SAVTIME Y TIMESTAMP(6) WITH TIME ZONE NO
4 FLAGS Y NUMBER NO
5 NULL_CNT Y NUMBER NO
6 MINIMUM Y NUMBER NO
7 MAXIMUM Y NUMBER NO
8 DISTCNT Y NUMBER NO
9 DENSITY Y NUMBER NO
10 LOWVAL Y RAW NO
11 HIVAL Y RAW NO
12 AVGCLN Y NUMBER NO
13 SAMPLE_DISTCNT Y NUMBER NO
14 SAMPLE_SIZE Y NUMBER NO
15 TIMESTAMP# Y DATE NO
16 EXPRESSION Y CLOB NO
17 COLNAME Y VARCHAR2 NO
18 SAVTIME_DATE Y DATE YES
19 SPARE1 Y NUMBER NO
20 SPARE2 Y NUMBER NO
21 SPARE3 Y NUMBER NO
22 SPARE4 Y VARCHAR2 NO
23 SPARE5 Y VARCHAR2 NO
24 SPARE6 Y TIMESTAMP(6) WITH TIME ZONE NO
25 MINIMUM_ENC Y RAW NO
26 MAXIMUM_ENC Y RAW NO
27 SYS_NC00027$ Y TIMESTAMP(6) YES
SQL> select partition_name, high_value from user_tab_partitions where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';
PARTITION_NAME HIGH_VALUE
---------------- --------------------------------------------------------------------------------
P_PERMANENT TO_DATE(' 1900-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P1907 TO_DATE(' 2024-01-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P1922 TO_DATE(' 2024-01-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P1931 TO_DATE(' 2024-01-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P1942 TO_DATE(' 2024-01-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
・索引の構成
SQL> select index_name, column_name from user_ind_columns where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY' order by index_name, column_position;
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST OBJ#
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INTCOL#
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST SYS_NC00027$
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST COLNAME
I_WRI$_OPTSTAT_HH_ST SYS_NC00027$
SQL> select index_name, column_position, column_expression from user_ind_expressions where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';
INDEX_NAME COLUMN_POSITION COLUMN_EXPRESSION
------------------------------ --------------- --------------------------------------------------------------------------------
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 3 SYS_EXTRACT_UTC("SAVTIME")
I_WRI$_OPTSTAT_HH_ST 1 SYS_EXTRACT_UTC("SAVTIME")
以上
2024-01-28 20:46
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用に必要な初期化パラメータや各種設定を行っておく。
アップグレードの基本的な手順は以下の通り。詳細は以下に述べていく。
３．準備
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は暫定的なリリースなのかもしれない。
AutoUpgradeを使うためには設定ファイルを用意する必要がある。今回作成したAutoUpgradeの設定ファイル（ここではoradb.cfgとした）は以下の通りである。パラメータの意味はAutoUpgradeマニュアル（参考[3]）を、実際の設定は参考[4]を参照されたい。
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の設定を使う。
ターゲットDBにて、作業ディレクトリ/home/oracle/autoupgを作成し、資材を配置する。
ソースDBにて、DBLINKの接続用ユーザを作成し、必要な権限を付与する。
ターゲットDBにて、DBLINKを作成するため、初期化パラメータglobal_namesをfalseに設定する。
ターゲットDBにて、DBLINKを作成する。ソースDBへのDBLINKの動作も確認しておく。
ソースDBのアナライズを実施する。ジョブが正常終了すれば完了。
アナライズの結果のサマリレポートを確認する。ここで、ORADB TDE_PASSWORDS_REQUIREDとORADB TARGET_CDB_AVAILABILITYの2つは後続の手順で対処するため、無視する（参考[1]手順記載の通り）。[Detail]に記載されたログ(参考[5])に詳細な結果が記載されているので結果を確認する。ソースDBのデータベースの状態やコンポーネント、アップグレードに際して必要なアクション、推奨されるアクション、そのうち（この後実行する）FIXUPで修正される対象など重要な情報が記載されている。
次にFIXUPを実行する。ここでアップグレード前にソースDBに対する必要な修正が実行される。しかし、UPG-1321で異常終了してしまう。
ログから以下のNEW_TIME_ZONES_EXISTのチェックの部分でエラーが発生していることを確認した。原因を探るため、failed_prefixups.logで詳細を確認する。
ログを確認すると、ターゲットDBのzoneinfoディレクトリ配下のファイルを参照しようとしてNoSuchFileExceptionエラーになっているようである。これはORACLE_HOMEに含まれるファイルだが、当然ソースDBには23cのORACLE_HOMEディレクトリは存在しない。このロジックは、ソースDBとターゲットDBが同一DBサーバ上にあることを前提としているのかもしれない。
仕方がないので、ターゲットDBのzoneinfo配下をソースDBにコピーする（readme.txtだけコピーではダメだったので、ディレクトリ配下のファイルを全てscpでコピー）。
再度FIXUPを実行すると、ジョブは正常終了する。ログを確認すると、PRECHECKSはアナライズで出たエラーと同じもの（無視可能）が出るが、PREFIXUPSは成功している。ソースPDBへどのような変更がされたかが気になるところであるが、prefixups.htmlを見る限り、変更点は何もなかった。FIXUPSの手順自体不要だったのではという一抹の疑念はあるものの、一応手順は踏んでおいた方が良いのだろう。
念のため、エラーの発生したNEW_TIME_ZONES_EXISTのチェックがパスしていることを確認。
ターゲットDBにて、AutoUpgradeのキーストアにORACLE_SIDのTDEパスワードを設定する。本環境ではソースDBとターゲットDBのSIDは同一かつパスワードも同じなので、以下のように1つだけ登録する（元の手順ではソースDBとターゲットDB両方のパスワードを設定している）。
ターゲットDBにて、デプロイを実行する。ここで実際にDBLINK越しにPDBがクローンされ23cへのアップグレードが実行される。ログを見ると、AutoUpgradeのデプロイの各タスクでどの程度の時間がかかったかが確認できる。全体でかかった時間は概ね40分程度だが、うち30分はDBUPGRADE、10分はPOSTFIXUPSで占められていた（★部分）。
ターゲットDBにて、早速移行されたPDBの状態を確認する。確かにORADBが作成されていることがわかる。
PDBのリソースの確認を行い、LISTENERでサービスが認識されることを確認する。
tnsnames.oraへの接続識別子を追加する。
追加した接続識別子で接続確認する。EMP、DEPT表にソースDBのオブジェクトが格納されており、正しい件数が確認できた。
本稿では、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の設定ファイル
[5]アナライズのプレチェック結果サンプル
１．はじめに
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用に必要な初期化パラメータや各種設定を行っておく。
アップグレードの基本的な手順は以下の通り。詳細は以下に述べていく。
３．準備
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
