【Oracle Database】DMLリダイレクションで一歩進んだ Active Data Guard の使い方（19c新機能）

この記事は JPOUG Advent Calendar 2019 の24日目の記事です。

Oracle Database 19c では Active Data Guard のスタンバイデータベースからもDML文の実行可能なActive Data Guard DMLリダイレクションという機能が追加されました。

初期化パラメータ ADG_REDIRECT_DML を有効化した上で、スタンバイデータベースで実行された DML文はプライマリデータベースへ転送され、トランザクションの結果が REDO によりスタンバイへ反映されます。

・Oracle Data Guard概要および管理, 19c

Active Data Guardスタンバイ・データベースでのDML操作の実行

最終的にはプライマリ側で実行されるので負荷分散を目的とした機能ではありませんが、たとえば、BIツールなど参照処理がメインではあるもののデータベースに何らかのリポジトリを持ち、レポーティングの際にデータを投入するような製品もスタンバイデータベースで実行できるようになりました。

今回は弊社取り扱い製品の WebFOCUS を使用した検証と合わせてご紹介いたします。

Active Data Guard DMLリダイレクションの概要

通常、Active Data Guard では参照処理を行うことは可能ですが更新処理は行うことができません
　

　
　## 環境確認
　
　--プライマリ
　
　SQL> select BANNER_FULL from v$version;
　
　BANNER_FULL
　---------------------------------------------------------------------------
　Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
　Version 19.3.0.0.0
　
　SQL> select db_unique_name, database_role from v$database;
　
　DB_UNIQUE_NAME       DATABASE_ROLE
　-------------------- --------------------
　ora19c               PRIMARY
　
　--スタンバイ
　
　SQL> select db_unique_name, database_role from v$database;
　
　DB_UNIQUE_NAME       DATABASE_ROLE
　-------------------- --------------------
　ora19cs              PHYSICAL STANDBY
　
　## スタンバイをアクティブへ
　
　SQL> alter database open read only;
　
　データベースが変更されました。
　
　SQL> alter database recover managed standby database disconnect;
　
　データベースが変更されました。
　
　SQL> select db_unique_name, database_role, open_mode from v$database;
　
　DB_UNIQUE_NAME       DATABASE_ROLE        OPEN_MODE
　-------------------- -------------------- -----------------------------------
　ora19cs              PHYSICAL STANDBY     READ ONLY WITH APPLY
　
　
　## 通常、更新処理は行えない
　
　SQL> conn scott/tiger
　接続されました。
　SQL> select * from EMP;
　
　     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
　---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
　      7369 SMITH      CLERK            7902 80-12-17        800                    20
　      7499 ALLEN      SALESMAN         7698 81-02-20       1600        300         30
　　　：
　      7902 FORD       ANALYST          7566 81-12-03       3000                    20
　      7934 MILLER     CLERK            7782 82-01-23       1300                    10
　
　12行が選択されました。
　
　SQL> insert into EMP values (9999,'KNAKAGAKI','ENGINEER',7698,SYSDATE,1000,null,40);
　insert into EMP values (9999,'KNAKAGAKI','ENGINEER',7698,SYSDATE,1000,null,40)
　            *
　行1でエラーが発生しました。:
　ORA-16000: データベースまたはプラガブル・データベースは読取り専用アクセスでオープンされています

Oracle Database 19c の新機能 DMLリダイレクションでは、上述のような更新処理をスタンバイデータベースからも発行することができます。

実行には初期化パラメータ ADG_REDIRECT_DML を有効化することで可能です。このパラメータはセッション単位でも有効にすることができ、参照のみ実行させたいユーザや処理と区別させることもできます。

　
　--スタンバイでADG_REDIRECT_DMLを有効化
　
　SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;
　
　セッションが変更されました。
　
　SQL> insert into EMP values (9999,'KNAKAGAKI','ENGINEER',7698,SYSDATE,1000,null,40);
　
　1行が作成されました。
　
　SQL> select * from EMP;
　
　     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
　---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
　      7369 SMITH      CLERK            7902 80-12-17        800                    20
　      7499 ALLEN      SALESMAN         7698 81-02-20       1600        300         30
　      :
　      7902 FORD       ANALYST          7566 81-12-03       3000                    20
　      7934 MILLER     CLERK            7782 82-01-23       1300                    10
　      9999 KNAKAGAKI  ENGINEER         7698 19-05-25       1000                    40 
　
　13行が選択されました。
　
　SQL> commit;
　
　コミットが完了しました。
　
　
　--プライマリ
　
　SQL> select * from EMP;
　
　     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
　---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
　      7369 SMITH      CLERK            7902 80-12-17        800                    20
　      7499 ALLEN      SALESMAN         7698 81-02-20       1600        300         30
　      :
　      7902 FORD       ANALYST          7566 81-12-03       3000                    20
　      7934 MILLER     CLERK            7782 82-01-23       1300                    10
　      9999 KNAKAGAKI  ENGINEER         7698 19-05-25       1000                    40 
　
　13行が選択されました。

WebFOCUS を使用した応用例

従来のActive Data Guard構成ではスタンバイデータベースはRead Onlyであるため、ツールから内部的に発行されるDMLに対応できず、スタンバイデータベースというリソースを十分に活用することができないケースもありました。
　
今回は、WebFOCUS を用いてWebFOCUS ClientのリポジトリをOracleへ格納したケースにて検証を行いました。

実際には以下のように、"ERROR_UOA_DB_UPDATE_OP_FAILED"のエラーが発生し、スタンバイ環境のデータベースに対してはWebFOCUS ClientのWEBコンソールからログインすらできませんでした。

そこで、WebFOCUSから接続するSCOTTユーザに対し、ログオン時にADG_REDIRECT_DMLを有効化するトリガーを作成し、スタンバイデータベースからもWebFOCUS Clientのリポジトリへ更新を可能としました。

　
　--プライマリ
　
　SQL> CREATE OR REPLACE TRIGGER SCOTT.logon_adg_redirect_dml AFTER LOGON ON SCOTT.SCHEMA
　  2  BEGIN
　  3    EXECUTE IMMEDIATE 'ALTER SESSION ENABLE ADG_REDIRECT_DML';
　  4  END;
　  5  /
　
　トリガーが作成されました。
　
　--スタンバイ
　
　SQL> select OWNER,TRIGGER_NAME,STATUS from DBA_TRIGGERS
　  2  where TRIGGER_NAME='LOGON_ADG_REDIRECT_DML';
　
　OWNER      TRIGGER_NAME              STATUS
　---------- ------------------------- ----------
　SCOTT      LOGON_ADG_REDIRECT_DML    ENABLED

DDL文はスタンバイデータベースでは実行ができないため、プライマリよりCREATE TRIGGERを実行し、スタンバイへ反映されていることを確認します。

これにより、スタンバイデータベースに接続するWebFOCUS Clientの操作で発生する内部的なDMLが実行できるようになり、ログイン、およびレポーティングが可能となりました。

▲REDOの転送／反映タイミングは保護モードに依存

▲ログイン後、データが参照できる状態に

　
BIツールを利用して分析処理をしていると「別の観点でグラフを追加したい」や「（年や地域など）デフォルトで選択されている値を変更したい」というようなちょっとした操作を行いたくなることもあります。しかし、こうした追加や変更を保存をするにはリポジトリの更新を伴うため、Read Only環境で行うことはできませんでした。
　
DMLリダイレクションが利用できることで、グラフの追加もスタンバイ側でできるようになります。実際にグラフの追加→保存を行った際のSQLトレースを採取し、リポジトリテーブルへのINSERT処理が行われていることを確認しています。

　
  SQL ID: fnnu4qsv741c4 Plan Hash: 0
　
  INSERT INTO WF_REPOSOBJ (HANDLE, APPNAME, CREATEDBY, CREATEDON, DEF_LNG, 
    EXP_DATE, EXT_ID, LASTACCESSBY, LASTACCESSON, LASTMODBY, LASTMODON, OBJNAME,
     OBJTYPE, PRT_PATH, RSNAME, SRT_ORDER, CLASSNAME) 
  VALUES
   (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , 
    :15 , :16 , :17 )
　
　
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.00       0.00          0          0          0           0
  Execute      1      0.01       0.39          0          0          3           1
  Fetch        0      0.00       0.00          0          0          0           0
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        2      0.01       0.39          0          0          3           1
　
  Misses in library cache during parse: 1
  Misses in library cache during execute: 1
  Optimizer mode: ALL_ROWS
  Parsing user id: 106  
　
  Elapsed times include waiting on following events:
    Event waited on                             Times   Max. Wait  Total Waited
    ----------------------------------------   Waited  ----------  ------------
    single-task message                             1        0.02          0.02
    SQL*Net message from dblink                    12        0.13          0.35
    SQL*Net message to dblink                      11        0.00          0.00
    SQL*Net vector data to dblink                   2        0.00          0.00
    asynch descriptor resize                        1        0.00          0.00
    Disk file operations I/O                        1        0.00          0.00
    standby query scn advance                       1        0.92          0.92
    SQL*Net message to client                       1        0.00          0.00
    SQL*Net message from client                     1        0.00          0.00

このようにDMLリダイレクションを用いることで、スタンバイデータベースを参照しているBIツールから発行される内部的なDMLをカバーすることができ、スタンバイのリソースを有効に活用できます。
　
ただし、動作としてはデータベースリンクにて処理をプライマリ側にリダイレクトしています。BIツールからのログインやグラフの追加などに伴うリポジトリ更新のような低負荷／低頻度な処理であれば問題ありませんが、バッチ処理をスタンバイ側で実行するなど、大量更新処理の実行は想定されていません。
　

まとめ

これまではActive Data Guard のスタンバイデータベースは参照のみのアプリケーションでしか有効利用できませんでした。しかし、厳密に参照のみが行われるアプリケーションは、実際にはそう多くありません。今回の調査を通して、DMLリダイレクションによりスタンバイデータベースを活用できるアプリケーションが大幅に増え、Active Data Guard活用の幅がより一層広がることを実感しました。

また、活用の幅が増えたActive Data Guardですが、DMLリダイレクションで実行できないSQLが無いか、など検証しきれていない点が残っています。引き続き様々な角度から検証をすすめていきたいと思います。

JPOUG Advent Calender執筆記事

2021年 18日目
Oracle Databaseのサポート対応で依頼することの多いファイルとコマンド
https://www.ashisuto.co.jp/db_blog/article/jpoug-oracledb-logfiles-command.html

2020年 17日目
Oracle Databaseバージョンアップ後の性能劣化で試したい暫定対処
https://www.ashisuto.co.jp/db_blog/article/jpoug-vup-temp-solution.html

2019年 20日目
【Oracle Database Cloud】"超"現実的に考える！Oracle CloudへのDB移行ガイド
https://www.ashisuto.co.jp/db_blog/article/20191220_cloud.html

2017年 14日目
Oracle Database 12cR2へのアップグレード後に発生するORA-01017
https://www.ashisuto.co.jp/db_blog/article/201712-ora-01017.html

2016年 9日目
【Oracle Database】2016年にサポートにお問い合わせをいただいたORAエラー TOP5
https://www.ashisuto.co.jp/db_blog/article/2016_oerr_rank.html

2015年 15日目
パフォーマンスダウンを「再現待ち」にしないための準備
https://www.ashisuto.co.jp/db_blog/article/20151215_oracle_pfmdwn.html

筆者情報

中垣佳祐

中垣佳祐（なかがきけいすけ）

アシスト北海道

2016年アシスト北海道へ入社後、Oracle Databaseのサポート業務に従事。入社2年目より夜間休日帯など営業時間外の緊急対応を主に担当。現在は通常時間帯のサポート業務を担当しており、第一線で日々奮闘中。...show more

検証協力

高畠明希

高畠明希（たかばたけあき）

アシスト北海道

2016年にアシスト北海道へ入社し、WebFOCUSのサポートを担当。コア機能のサポートに加え、オプション製品（Resource Analyzer等）のサポートを行っている。

■本記事の内容について
　本記事に記載されている製品およびサービス、定義及び条件は、特段の記載のない限り本記事執筆時点のものであり、予告なく変更になる可能性があります。あらかじめご了承ください。

■商標に関して
　・Oracle®、Java及びMySQLは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
　・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
　　文中の社名、商品名等は各社の商標または登録商標である場合があります。

【Oracle AI Database 26ai】同時リフレッシュでON COMMITマテリアライズド・ビューのenqueue待ちにサヨナラ！OLTP／DWHスループットを実測検証

本記事では、従来のON COMMIT MViewが抱えてきたこのようなスループット低下や待機イベントのボトルネックを振り返りつつ、Oracle AI Database 26aiが提供する「同時リフレッシュ（Concurrent Refresh）」によって、OLTP／DWHそれぞれのユースケースでどのような改善が見込めるのかを検証していきます。