Oracleのロック検証（TXロック、TMロック編）

今回はOracleのロックについて検証しました。

ロックとは、複数のセッションでデータを同時に変更できないようにする仕組み(排他制御)です。
Oracleに限らず、たいていのRDBMSはロックを使用してデータの整合性を確保します。
セッションが1つしか存在しないシステムならあまりロックを意識しなくても良いかもしれませんが、セッションが複数存在するシステムならロックを意識してアプリ設計しないとロック取得待ちやデッドロックの原因になります。

ロックモード
DBリソースへのアクセスを制御するロックの種類
共有ロック(読取りロック)
同じリソースを複数のセッションで同時にアクセスできるようにするためのロック
共有ロック取得後も別セッションから対象のリソースへアクセスできる
ただし、別セッションは表定義やデータを変更できない(共有ロックは取得可能、排他ロックは取得不可)
※PostgreSQLなどはSELECT時に共有ロックを取得しますが、Oracleは読取り一貫性(CR)を採用しているため、SELECT時に共有ロックは取得しません
排他ロック(占有ロック)
同じリソースを複数のセッションで同時にアクセスできない(共有できない)ようにするためのロック
排他ロック取得後は別セッションはリソースへアクセスできない(共有ロック・排他ロックともに取得不可)

ロック範囲
リソースをロックする範囲
表レベル
特定の表(テーブル)全体をロックする
行レベル
表(テーブル)の特定の行(レコード)をロックする

エンキュー(Oracleエンキュー)
データの変更をシリアライズするため、共有メモリ構造体(ロック)を提供するサービス
ロックはキューで管理される(FIFO型のロック)

自動ロック
OracleはDDL文やDML文でトランザクションを発行すると自動的にロックを取得する
DMLロック(データ・ロック)
DML文を実行した場合、自動的に取得されるロック
行レベルと表レベルの2種類のロックが自動的に取得される
データ保護が目的

・行レベルのロック(行ロック、TXロック、トランザクション)
表の特定の行に対するロック
INSERT/UPDATE/DELETE/MERGE/SELECT FOR UPDATE文を実行した場合、自動的に取得される
ロック取得タイミング：トランザクション開始時
ロック解放タイミング：トランザクションのコミット/ロールバック
※特定の行に対する表の表ロックも取得する(データ更新中のDDL操作(表定義変更など)を防ぐため)
・表レベルのロック(表ロック、TMロック、DMLエンキュー)
特定の表に対するロック
INSERT/UPDATE/DELETE/MERGE/SELECT FOR UPDATE文を実行した場合、自動的に取得される
ロック取得タイミング：トランザクション開始時
ロック解放タイミング：トランザクションのコミット/ロールバック
なお、表ロックには下記のモードがある

[ロックモード2]行共有ロック(RS/SS：ROW SHARE、SSは何の略か分かりません)
表のロックを保持しているトランザクションが該当行をロックしており、該当行を更新する予定であることを示すモード
[ロックモード3]行排他ロック(RX/SX：ROW EXCLUSIVE、SXは何の略か分かりません)
表のロックを保持しているトランザクションが該当行を更新/SELECT FOR UPDATEしたことを示すモード
[ロックモード4]共有表ロック(S：SHARE)
表のロックを保持しているトランザクションがいても別のトランザクションも同じ表へ問合せできることを示すモード
1トランザクションが共有表ロックを保持する場合、表更新できるが、複数トランザクションが共有表ロックを保持する場合、表更新できない
[ロックモード5]共有行排他表ロック(SRX：SHARE ROW EXCLUSIVE)
表のロックを保持しているトランザクションがいても別のトランザクションも同じ表へ問合せできることを示すモード
共有表ロックを保持するトランザクションは表更新できるが、別トランザクションは表更新できない
[ロックモード6]排他表ロック(X：EXCLUSIVE)
表のロックを保持しているトランザクション以外、表への問合せ・更新できないことを示すモード

※LOCK TABLE文で手動で明示的に表ロックを取得できる

DDLロック(データディクショナリ・ロック)
DDL文を実行した場合、自動的に取得されるロック
変更対象のオブジェクトに対するDMLロックも自動的に取得される
スキーマ・オブジェクトの構造(表定義やビュー定義など)保護が目的
※明示的にDDLロックを取得できない
※DDL文のロック解除待ち時間(秒)はDDL_LOCK_TIMEOUT初期化パラメータで設定できる(デフォルト(0)は待機しない＝NOWAIT)
・排他DDLロック
別トランザクションがDDLロック/DMLロックを取得できないようにするロック
別トランザクションのスキーマ・オブジェクト定義に対するDDL操作(ALTER TABLE、DROP TABLEなど)による変更(破壊)を防ぐ
・共有DDLロック
別トランザクションが同じスキーマ・オブジェクト定義を変更(破壊)しない場合、同じスキーマ・オブジェクトにアクセスできるようにするロック
スキーマ・オブジェクト定義を変更(破壊)しないDDL文としてCREATE PROCEDUREなどがある
・ブレーク可能解析ロック
ライブラリキャッシュ(共有SQL領域)のSQL文がスキーマ・オブジェクトを参照する場合、対応するSQL文に対して取得されるロック
参照しているスキーマ・オブジェクト定義が変更/削除された場合、対応する共有SQL領域のSQL文を無効化する
SQL文の解析フェーズ中に共有プール内で取得される

システムロック
データファイルなどの内部構造の保護が目的
・ラッチ
SGA内の共有データの保護(排他制御)に使用される
共有メモリ領域へのアクセス開始～終了まで保持されるロック
マイクロ秒～ミリ秒レベルで獲得と解放を繰り返すが獲得に失敗した場合、スピンして獲得できるのを待機する
※スピン後でも獲得できずスリープする場合、待機イベント「latch free」などで待機する
・共有プール上でのメモリの割り当てや解放
・バッファキャッシュ上でのバッファの探索
・ライブラリキャッシュ上でのオブジェクトの探索
・セッションの開始
・チェックポイントの開始
・ログバッファの割り当て
など
・mutex
ラッチに似ているが、ラッチは複数オブジェクトをグルーピングして保護するが、mutexは単一オブジェクトを保護する
・内部ロック
ディクショナリキャッシュ内のデータ保護
制御ファイル、REDOログファイル、アーカイブログファイル、データファイルなどのファイル保護
表領域とUNDOセグメントの保護

今回は特定のデータに対してロックモード1～6のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証しました。
結果は下記のようになりました。

■検証環境
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)

■前提
・非CDB

■設定内容
DDLロックタイムアウト時間
SQL> select inst_id, name, value from gv$parameter where name = 'ddl_lock_timeout';

INST_ID NAME                           VALUE
------- ------------------------------ ----------------------------------------
      1 ddl_lock_timeout               0
      2 ddl_lock_timeout               0

■検証パターン
①ロックモード1に対するロック取得検証
②ロックモード2に対するロック取得検証
③ロックモード3に対するロック取得検証
④ロックモード4に対するロック取得検証
⑤ロックモード5に対するロック取得検証
⑥ロックモード6に対するロック取得検証

■検証　※作業ログは全パターンを載せると膨大な量になるため、セッション2(HR)から一部DML(insert・update)、一部DDL(DROP TABLE)を実行したログのみ載せます
①ロックモード1に対するロック取得検証
特定のデータに対してロックモード1のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します

【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード1のロック取得
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得

【作業ログ】
別セッションからロックモード1～6のロックが取得できたため作業ログは割愛します

②ロックモード2に対するロック取得検証
特定のデータに対してロックモード2のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します

【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得(select for update)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
3. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得(LOCK TABLE)
4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得

【作業ログ】

1. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得
SQL> select * from emp where EMPNO = 7369 for update;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17        800                    20

2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       134      63853 HR
       141      35627 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       134 TX     X(排他ロック)                           2
       134 TM     RX/SX(行排他ロック)                     2
       141 TX     X(排他ロック)                           2
       141 TM     RX/SX(行排他ロック)                     2

※下記SQLでオブジェクトレベルでロック取得状況を確認できます
SQL> select o.owner, o.object_name, o.object_type, lo.SESSION_ID, decode(lo.LOCKED_MODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode from v$locked_object lo, dba_objects o where lo.OBJECT_ID = o.OBJECT_ID;

OWNER           OBJECT_NAME     OBJECT_TYPE     SESSION_ID LCK_MODE
--------------- --------------- --------------- ---------- ------------------------------
SCOTT           EMP             TABLE                  141 RX/SX(行排他ロック)
SCOTT           EMP             TABLE                  134 RX/SX(行排他ロック)

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       134      63853 HR                           141
       141      35627 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       134 TM     RX/SX(行排他ロック)                     2
       134 TX     NONE(ロックなし)                        0
       141 TX     X(排他ロック)                           1
       141 TM     RX/SX(行排他ロック)                     2

--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
                 *
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       134      63853 HR
       141      35627 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       141 TX     X(排他ロック)                           2
       141 TM     RX/SX(行排他ロック)                     2

3. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得(LOCK TABLE)
SQL> LOCK TABLE emp IN ROW SHARE MODE nowait;

表がロックされました。

4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
        12       3652 HR
       128       9275 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
        12 TX     X(排他ロック)                           2
        12 TM     RX/SX(行排他ロック)                     2
       128 TM     RS/SS(行共有ロック)                     2

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;

1行が更新されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
        12       3652 HR
       128       9275 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
        12 TX     X(排他ロック)                           2
        12 TM     RX/SX(行排他ロック)                     2
       128 TM     RS/SS(行共有ロック)                     2

--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
                 *
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
        12       3652 HR
       128       9275 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       128 TM     RS/SS(行共有ロック)                     2

③ロックモード3に対するロック取得検証
特定のデータに対してロックモード3のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します

【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(insert)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
3. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(update)
4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
5. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(delete)
6. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
7. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(LOCK TABLE)
8. セッション2(HR)で同じデータに対してロックモード1～6のロック取得

【作業ログ】

1. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       257      48116 SCOTT
       372      65105 HR                           257

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       257 TX     X(排他ロック)                           1
       257 TM     RX/SX(行排他ロック)                     2
       372 TX     X(排他ロック)                           2
       372 TM     RX/SX(行排他ロック)                     2
       372 TX     NONE(ロックなし)                        0

3. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;

1行が更新されました。

4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       257      48116 SCOTT
       372      65105 HR

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       257 TX     X(排他ロック)                           2
       257 TM     RX/SX(行排他ロック)                     2
       372 TX     X(排他ロック)                           2
       372 TM     RX/SX(行排他ロック)                     2

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       257      48116 SCOTT
       372      65105 HR

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       257 TX     X(排他ロック)                           1
       257 TM     RX/SX(行排他ロック)                     2
       372 TM     RX/SX(行排他ロック)                     2
       372 TX     NONE(ロックなし)                        0

--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
                 *
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       257      48116 SCOTT
       372      65105 HR

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       257 TX     X(排他ロック)                           2
       257 TM     RX/SX(行排他ロック)                     2

5. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(delete)
SQL> delete from scott.emp where EMPNO = 7369;

1行が削除されました。

6. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       257      48116 SCOTT
       372      65105 HR

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       257 TX     X(排他ロック)                           2
       257 TM     RX/SX(行排他ロック)                     2
       372 TX     X(排他ロック)                           2
       372 TM     RX/SX(行排他ロック)                     2

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       257      48116 SCOTT
       372      65105 HR                           257

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       257 TX     X(排他ロック)                           1
       257 TM     RX/SX(行排他ロック)                     2
       372 TM     RX/SX(行排他ロック)                     2
       372 TX     NONE(ロックなし)                        0

--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
                 *
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       257      48116 SCOTT
       372      65105 HR

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       257 TX     X(排他ロック)                           2
       257 TM     RX/SX(行排他ロック)                     2

7. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(LOCK TABLE)
SQL> LOCK TABLE emp IN ROW EXCLUSIVE MODE nowait;

表がロックされました。

8. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
        17      25120 HR
       355      64736 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
        17 TX     X(排他ロック)                           2
        17 TM     RX/SX(行排他ロック)                     2
       355 TM     RX/SX(行排他ロック)                     2

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;

1行が更新されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
        17      25120 HR
       355      64736 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
        17 TX     X(排他ロック)                           2
        17 TM     RX/SX(行排他ロック)                     2
       355 TM     RX/SX(行排他ロック)                     2

--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
                 *
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
        17      25120 HR
       355      64736 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       355 TM     RX/SX(行排他ロック)                     2

④ロックモード4に対するロック取得検証

特定のデータに対してロックモード4のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します

【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード4のロック取得(LOCK TABLE)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得

【作業ログ】

1. セッション1(SCOTT)で特定のデータに対してロックモード4のロック取得(LOCK TABLE)
SQL> LOCK TABLE emp IN SHARE MODE nowait;

表がロックされました。

2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       137       7809 SCOTT
       371      20677 HR

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       137 TM     S(共有ロック)                           1
       371 TM     NONE(ロックなし)                        0

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       137       7809 SCOTT
       371      20677 HR                           137

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       137 TM     S(共有ロック)                           1
       371 TM     NONE(ロックなし)                        0

--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
                 *
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       137       7809 SCOTT
       371      20677 HR

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       137 TM     S(共有ロック)                           2

⑤ロックモード5に対するロック取得検証
特定のデータに対してロックモード5のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します

【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード5のロック取得(LOCK TABLE)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得

【作業ログ】

1. セッション1(SCOTT)で特定のデータに対してロックモード5のロック取得(LOCK TABLE)
SQL> LOCK TABLE emp IN SHARE ROW EXCLUSIVE MODE nowait;

表がロックされました。

2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       137      61707 SCOTT
       371      28633 HR                           137

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       137 TM     SRX/SSX(共有行排他ロック)               1
       371 TM     NONE(ロックなし)                        0

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       137      61707 SCOTT
       371      28633 HR                           137

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       137 TM     SRX/SSX(共有行排他ロック)               1
       371 TM     NONE(ロックなし)                        0

--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
                 *
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

※別セッションでロック状況確認
SQL> --#ついでに
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
       137      61707 SCOTT
       371      28633 HR

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       137 TM     SRX/SSX(共有行排他ロック)               2

⑥ロックモード6に対するロック取得検証
特定のデータに対してロックモード6のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します

【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード6のロック取得(LOCK TABLE)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得

【作業ログ】

1. セッション1(SCOTT)で特定のデータに対してロックモード6のロック取得(LOCK TABLE)
SQL> LOCK TABLE emp IN EXCLUSIVE MODE nowait;

表がロックされました。

2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
        12      12189 HR                           377
       377      62284 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
        12 TM     NONE(ロックなし)                        0
       377 TM     X(排他ロック)                           1

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
        12      12189 HR                           377
       377      62284 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
        12 TM     NONE(ロックなし)                        0
       377 TM     X(排他ロック)                           1

--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
                 *
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

       SID    SERIAL# USERNAME        BLOCKING_SESSION
---------- ---------- --------------- ----------------
        12      12189 HR
       377      62284 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

       SID TYPE   LCK_MODE                            BLOCK
---------- ------ ------------------------------ ----------
       377 TM     X(排他ロック)                           2

■参考資料
データの同時実行性と整合性

Oracleの監査の検証（統合監査編）

前回の従来型監査で検証した項目を統合監査で検証した場合、実際の動きにどのような違いがあるのか検証しました。

統合監査についてざっくり復習します。

概要
12cから登場した新しい監査機能で、従来型監査(必須監査、DBA監査、標準監査(標準データベース監査)、ファイングレイン監査(FGA監査))の監査証跡(監査ログ)をDBの1つのビューに集約して管理します

メリット
・監査ログを1つのビューにしたことで監査ログを探さなくて済む
　→従来型監査は監査によって監査ログをOSファイルに出力したり、DBに出力したり管理が複雑になりやすいという管理上のデメリットがありました
・監査対象のDB操作に「OSユーザが○○だったら～」、「ホスト名が○○だったら～」など細かい条件を指定可能
　→例えば従来型監査はログインを監査するという設定はできますが、「OSユーザがoracleだったらログインを監査する」という設定はできませんでした
　　統合監査は「OSユーザがoracleだったらログインを監査する」という設定ができます
・Data PumpやRMANなどのツールも監査可能
・キュー書込み方式によるディスクI/O軽減(12.1)
　→従来型監査は監査が発生するたび、監査ログを実表に書き込んでいたため、その都度ディスクI/Oが発生していました
　　キュー書込み方式は監査ログをSGAに溜めておき、一定のタイミングで実表へ書き込むため、ディスクI/Oが減りパフォーマンス向上が期待できます
　　※ただし、インスタンス(メモリ)障害等で情報が欠落する可能性があるという理由で12.2で非推奨になりました (参考) 監査証跡の管理
・監査管理用ロール登場
　　→監査設定や参照は管理者権限(SYSDBA権限やDBAロール)が必要でしたが、監査管理用ロール(AUDIT_ADMIN、AUDIT_VIEWER)が登場したことで管理者じゃなくても監査設定や参照ができます
　　　AUDIT_ADMIN：監査ポリシー参照/作成/変更/削除/監査ログ参照など
　　　AUDIT_VIEWER：監査ログ参照

デメリット
・キュー書込み方式の場合、インスタンス(メモリ)障害等で情報が欠落する可能性がある
・DB破損してバックアップがない場合、監査ログを復旧できない可能性がある

【監査対象】
従来型監査の監査項目に加え、Data PumpやRMANなどのツールの操作(※1)
(※1) 監査の概要
【出力先】
UNIFIED_AUDIT_TRAILビュー(AUDSYS.CLI_SWP$xxx表)

■検証環境
OS：Oracle Linux 6.5
GI：Oracle Grid Infrastructure 12c Release 1 (12.1.0.2.0) Enterprise Edition
DB：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)

■前提
・非CDB
・統合監査のみ

■設定情報
事前に下記のとおり設定しています

統合監査設定
SQL> select inst_id, parameter, value from gv$option where parameter = 'Unified Auditing';

INST_ID PARAMETER            VALUE
------- -------------------- --------------------
      1 Unified Auditing     TRUE
      2 Unified Auditing     TRUE

【参考】統合監査有効化
1. 統合監査設定確認
SQL> select inst_id, parameter, value from gv$option where parameter = 'Unified Auditing';

INST_ID PARAMETER            VALUE
------- -------------------- --------------------
      1 Unified Auditing     FALSE
      2 Unified Auditing     FALSE

2. DB停止
[oracle@node1 ~]$ srvctl stop database -db orcl

3. 統合監査実行可能ファイル有効化
[oracle@node1 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@node1 lib]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib
[oracle@node1 lib]$ ls -l libknlopt.a
-rw-r--r-- 1 oracle oinstall  1771642 11月 18 18:24 2022 libknlopt.a
[oracle@node1 lib]$ make -f ins_rdbms.mk uniaud_on ioracle
-rw-r--r-- 1 oracle oinstall  1771858 11月 26 14:03 2022 libknlopt.a
※RACの場合、全ノードでmakeが必要です(Doc ID 2371837.1)

4. DB起動
[oracle@node1 lib]$ srvctl start database -db orcl

5. 統合監査設定確認
SQL> select inst_id, parameter, value from gv$option where parameter = 'Unified Auditing';

INST_ID PARAMETER            VALUE
------- -------------------- --------------------
      1 Unified Auditing     TRUE
      2 Unified Auditing     TRUE

ASMの監査設定
SQL> --監査ログ(OSファイル)の出力先(デフォルト)
SQL> select inst_id, name, value from gv$parameter where name = 'audit_file_dest';

INST_ID NAME                           VALUE
------- ------------------------------ ----------------------------------------
      1 audit_file_dest                /u01/app/12.1.0/grid/rdbms/audit
      2 audit_file_dest                /u01/app/12.1.0/grid/rdbms/audit

DBの監査設定
SQL> --監査ログ(OSファイル)の出力先(デフォルト)
SQL> select inst_id, name, value from gv$parameter where name = 'audit_file_dest';

INST_ID NAME                           VALUE
------- ------------------------------ ----------------------------------------
      1 audit_file_dest                /u01/app/oracle/admin/orcl/adump
      2 audit_file_dest                /u01/app/oracle/admin/orcl/adump

SQL> --DBA監査設定(デフォルト)
SQL> select inst_id, name, value from gv$parameter where name = 'audit_sys_operations';

INST_ID NAME                           VALUE
------- ------------------------------ ----------------------------------------
      1 audit_sys_operations           TRUE
      2 audit_sys_operations           TRUE

SQL> --標準監査設定(前回の従来型監査検証の設定値)
SQL> select inst_id, name, value from gv$parameter where name = 'audit_trail';

INST_ID NAME                           VALUE
------- ------------------------------ ----------------------------------------
      1 audit_trail                    DB, EXTENDED
      2 audit_trail                    DB, EXTENDED

※統合監査が有効の場合、audit_file_dest、audit_sys_operations、audit_trail初期化パラメータは無効です
　DBインスタンス停止中の監査ログ(OSファイル)は「/u01/app/oracle/admin/orcl/adump」ではなく、「/u01/app/oracle/audit/<インスタンス名>」配下に出力されます

■検証パターン
①必須監査
②DBA監査
③標準監査(標準データベース監査)
④ファイングレイン監査(FGA監査)
※従来型監査で検証した値ベース監査は事前に定義された機能ではなく、統合監査との動きを比較できないため検証は割愛

■検証
①必須監査
特権ユーザの必須監査対象の操作が監査されるか検証します
検証手順は従来型監査の検証と同じです

【検証手順】
1. ASMインスタンス停止
2. ASMインスタンス起動
3. ASMインスタンス接続
4. DBインスタンス停止
5. DBインスタンス起動
6. DBインスタンス接続

【想定】
必須監査対象の操作が監査されること

【検証結果】
・必須監査対象の操作のうち、下記は監査された
- ASMインスタンス起動/停止、接続の監査ログはOSファイルに出力された(ビューには出力されなかった)
- DBインスタンス起動/停止の監査ログはビューに出力された
・必須監査対象の操作のうち、下記は監査されなかった
- DBインスタンス接続の監査ログはOSファイル、ビューどちらにも出力されなかった(※1)

(※1)
(DBインスタンス起動/停止以外の)SYSDBA権限の操作は監査されないとのことでした(Doc ID 2024280.1)
全操作を監査する監査ポリシー作成後、SYSDBA権限を持つ特権ユーザ(特権アカウント)に監査ポリシーを適用して対応します

【作業ログ】

1. ASMインスタンス停止
[root@node1 ~]# /u01/app/12.1.0/grid/bin/crsctl stop crs

#監査ログ確認
[root@node1 ~]# cat /u01/app/12.1.0/grid/rdbms/audit/+ASM1_ora_667_20221126152211413052143795.aud
Sat Nov 26 15:22:14 2022 +09:00
LENGTH : '154'
ACTION :[18] 'SHUTDOWN IMMEDIATE'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSASM'
CLIENT USER:[4] 'grid'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[0] ''
★SYSASM権限でASMインスタンス停止した監査ログが出力された

2. ASMインスタンス起動
[root@node1 ~]# /u01/app/12.1.0/grid/bin/crsctl start crs -wait

#監査ログ確認
[root@node1 ~]# cat /u01/app/12.1.0/grid/rdbms/audit/+ASM1_ora_6003_20221126152518149265143795.aud
Sat Nov 26 15:25:18 2022 +09:00
LENGTH : '142'
ACTION :[7] 'STARTUP'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSASM'
CLIENT USER:[4] 'grid'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[0] ''
★SYSASM権限でASMインスタンス起動した監査ログが出力された

3. ASMインスタンス接続
[grid@node1 ~]$ sqlplus / as sysasm

#監査ログ確認
[root@node1 ~]# cat /u01/app/12.1.0/grid/rdbms/audit/+ASM1_ora_9982_20221126153242132738143795.aud
Sat Nov 26 15:32:42 2022 +09:00
LENGTH : '147'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSASM'
CLIENT USER:[4] 'grid'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[0] ''
★SYSASM権限でASMインスタンス接続した監査ログが出力された

4. DBインスタンス停止
[oracle@node1 ~]$ srvctl stop database -db orcl

#監査ログ確認
[root@node1 ~]# ls -ltr /u01/app/oracle/audit/orcl1
-rw-r----- 1 oracle asmadmin 1536 11月 26 15:37 2022 ora_audit_1138.bin
-rw-r----- 1 oracle asmadmin 1536 11月 26 15:37 2022 ora_audit_0138.bin
★中身は見れないがSYSDBA権限でDBインスタンス停止と同時刻の監査ログが出力された

5. DBインスタンス起動
[oracle@node1 ~]$ srvctl start database -db orcl

#監査ログ確認(OSファイル)
[root@node1 ~]# ls -ltr /u01/app/oracle/audit/orcl1
-rw-r----- 1 oracle asmadmin  2560 11月 26 15:42 2022 ora_audit_00.bin
-rw-r----- 1 oracle asmadmin 10752 11月 26 15:42 2022 ora_audit_1355.bin
-rw-r----- 1 oracle asmadmin  1536 11月 26 15:42 2022 ora_audit_116.bin
★中身は見れないがSYSDBA権限でDBインスタンス起動と同時刻の監査ログが出力された

#監査ログ確認(ビュー)
SQL> select TO_CHAR(EVENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as EVT_TS,AUDIT_TYPE,USERHOST,DBUSERNAME,ACTION_NAME,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT from UNIFIED_AUDIT_TRAIL order by EVENT_TIMESTAMP desc;

EVT_TS               AUDIT_TYPE           USERHOST             DBUSERNAME      ACTION_NAME     OBJECT_SCHEMA   OBJECT_NAME     SQL_TEXT
-------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
2022/11/26 15:42:21  Standard             Not Available        SYS             STARTUP                                         STARTUP
2022/11/26 15:42:21  Standard             Not Available        SYS             STARTUP                                         STARTUP
2022/11/26 15:38:11  Standard             node2.oracle12c.jp   SYS             SHUTDOWN                                        SHUTDOWN IMMEDIATE
2022/11/26 15:37:49  Standard             node1.oracle12c.jp   SYS             SHUTDOWN                                        SHUTDOWN IMMEDIATE
★「4. DBインスタンス停止」、「5. DBインスタンス起動」の監査ログが出力された

6. DBインスタンス接続
[oracle@node1 ~]$ sqlplus / as sysdba

#監査ログ確認
★監査ログはOSファイル、ビューどちらにも出力されなかった
理由は上記「【検証結果】」で書いた通りです

【参考】全操作を監査する監査ポリシー作成後、SYS(SYSDBA権限)に監査ポリシー適用
1. 全操作を監査する監査ポリシー作成
SQL> CREATE AUDIT POLICY POL_ALL_ACTIONS
  2  ACTIONS ALL
  3  WHEN 'SYS_CONTEXT(''USERENV'', ''OS_USER'') = ''oracle'' AND (SYS_CONTEXT(''USERENV'', ''HOST'') = ''node1.oracle12c.jp'' OR SYS_CONTEXT(''USERENV'', ''HOST'') = ''node2.oracle12c.jp'')'
  4  EVALUATE PER STATEMENT;

監査ポリシーが作成されました。

★WHEN句で監査ポリシーに対する監査条件を指定できます
　本検証では「OSユーザがoracle」で「ホスト名がnode1.oracle12c.jpかnode2.oracle12c.jp」の場合、全操作を監査するという条件を指定しています

SQL> select POLICY_NAME,AUDIT_CONDITION,AUDIT_OPTION,OBJECT_SCHEMA,OBJECT_NAME from audit_unified_policies where POLICY_NAME like 'POL%' order by POLICY_NAME,AUDIT_OPTION,OBJECT_SCHEMA,OBJECT_NAME;

POLICY_NAME          AUDIT_CONDITION                                                   AUDIT_OPTION    OBJECT_SCHEMA   OBJECT_NAME
-------------------- ----------------------------------------------------------------- --------------- --------------- ---------------
POL_ALL_ACTIONS      SYS_CONTEXT('USERENV', 'OS_USER') = 'oracle' AND (SYS_CONTEXT('US ALL             NONE            NONE
                     ERENV', 'HOST') = 'node1.oracle12c.jp' OR SYS_CONTEXT('USERENV',
                     'HOST') = 'node2.oracle12c.jp')

2. 全操作を監査する監査ポリシー適用
SQL> audit policy POL_ALL_ACTIONS by sys;

監査が成功しました。

SQL> select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME          ENABLED_OPT              SUCCESS   FAILURE
--------------- -------------------- ------------------------ --------- ---------
SYS             POL_ALL_ACTIONS      BY                       YES       YES

★全操作を監査する監査ポリシー適用後、SYS(SYSDBA権限)でDBインスタンス接続したら監査ログが出力された

SQL> select TO_CHAR(EVENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as EVT_TS,AUDIT_TYPE,USERHOST,DBUSERNAME,ACTION_NAME,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT from UNIFIED_AUDIT_TRAIL order by EVENT_TIMESTAMP desc;

EVT_TS               AUDIT_TYPE           USERHOST             DBUSERNAME      ACTION_NAME     OBJECT_SCHEMA   OBJECT_NAME     SQL_TEXT
-------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
2022/11/26 16:04:47  Standard             node1.oracle12c.jp   SYS             LOGON

②DBA監査
特権ユーザのDBA監査対象の操作が監査されるか検証します
検証手順は従来型監査の検証と同じです

【検証手順】
1. データディクショナリ問合せ
2. データ更新

【想定】
DBA監査対象の操作が監査されること

【検証結果】
DBA監査対象の操作が監査された

【作業ログ】

1. データディクショナリ問合せ
SQL> select count(*) from dba_users;

  COUNT(*)
----------
        42

#監査ログ確認
SQL> select TO_CHAR(EVENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as EVT_TS,AUDIT_TYPE,USERHOST,DBUSERNAME,ACTION_NAME,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT from UNIFIED_AUDIT_TRAIL order by EVENT_TIMESTAMP desc;

EVT_TS               AUDIT_TYPE           USERHOST             DBUSERNAME      ACTION_NAME     OBJECT_SCHEMA   OBJECT_NAME          SQL_TEXT
-------------------- -------------------- -------------------- --------------- --------------- --------------- -------------------- --------------------------------------------------------------------------------
2022/11/26 16:09:15  Standard             node1.oracle12c.jp   SYS             SELECT          SYS             from$_subquery$_004  select count(*) from dba_users
★SYSDBA権限でデータディクショナリアクセスした監査ログが出力された

2. データ更新
SQL> update scott.emp set SAL = 800 where EMPNO = 7369;

1行が更新されました。

#監査ログ確認
SQL> select TO_CHAR(EVENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as EVT_TS,AUDIT_TYPE,USERHOST,DBUSERNAME,ACTION_NAME,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT from UNIFIED_AUDIT_TRAIL order by EVENT_TIMESTAMP desc;

EVT_TS               AUDIT_TYPE           USERHOST             DBUSERNAME      ACTION_NAME     OBJECT_SCHEMA   OBJECT_NAME     SQL_TEXT
-------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
2022/11/26 16:10:43  Standard             node1.oracle12c.jp   SYS             UPDATE          SCOTT           EMP             update scott.emp set SAL = 800 where EMPNO = 7369
★SYSDBA権限でデータ更新した監査ログが出力された

③標準監査(標準データベース監査)
一般ユーザの標準監査対象の操作が監査されるか検証します
検証手順は従来型監査の検証と同じです

【検証手順】
1. 監査ポリシー作成
2. 監査ポリシー適用
3. ノード1からSCOTTでDBログイン(間違ったパスワード)
4. ノード2からSCOTTでDBログイン(間違ったパスワード)
5. SCOTTでテーブル作成
6. SCOTTでテーブルにデータ挿入

【想定】
標準監査対象の操作が監査されること

【検証結果】
標準監査対象の操作が監査された

【作業ログ】

1. 監査ポリシー作成
SQL> --ログイン失敗検証用
SQL> CREATE AUDIT POLICY POL_LOGON_FAIL
  2  ACTIONS LOGON
  3  WHEN 'SYS_CONTEXT(''USERENV'', ''OS_USER'') = ''oracle'' AND (SYS_CONTEXT(''USERENV'', ''HOST'') = ''node1.oracle12c.jp'' OR SYS_CONTEXT(''USERENV'', ''HOST'') = ''node2.oracle12c.jp'')'
  4  EVALUATE PER STATEMENT;

監査ポリシーが作成されました。

SQL> --テーブル作成検証用
SQL> CREATE AUDIT POLICY POL_CRE_TBL
  2  ACTIONS CREATE TABLE
  3  WHEN 'SYS_CONTEXT(''USERENV'', ''OS_USER'') = ''oracle'' AND (SYS_CONTEXT(''USERENV'', ''HOST'') = ''node1.oracle12c.jp'' OR SYS_CONTEXT(''USERENV'', ''HOST'') = ''node2.oracle12c.jp'')'
  4  EVALUATE PER STATEMENT;

監査ポリシーが作成されました。

SQL> --データ操作検証用
SQL> CREATE AUDIT POLICY POL_OPERATION
  2  ACTIONS INSERT on scott.emp
  3  WHEN 'SYS_CONTEXT(''USERENV'', ''OS_USER'') = ''oracle'' AND (SYS_CONTEXT(''USERENV'', ''HOST'') = ''node1.oracle12c.jp'' OR SYS_CONTEXT(''USERENV'', ''HOST'') = ''node2.oracle12c.jp'')'
  4  EVALUATE PER STATEMENT;

監査ポリシーが作成されました。

SQL> select POLICY_NAME,AUDIT_CONDITION,AUDIT_OPTION,OBJECT_SCHEMA,OBJECT_NAME from audit_unified_policies where POLICY_NAME like 'POL%' order by POLICY_NAME,AUDIT_OPTION,OBJECT_SCHEMA,OBJECT_NAME;

POLICY_NAME          AUDIT_CONDITION                                                   AUDIT_OPTION    OBJECT_SCHEMA   OBJECT_NAME
-------------------- ----------------------------------------------------------------- --------------- --------------- ---------------
POL_ALL_ACTIONS      SYS_CONTEXT('USERENV', 'OS_USER') = 'oracle' AND (SYS_CONTEXT('US ALL             NONE            NONE
                     ERENV', 'HOST') = 'node1.oracle12c.jp' OR SYS_CONTEXT('USERENV',
                     'HOST') = 'node2.oracle12c.jp')

POL_CRE_TBL          SYS_CONTEXT('USERENV', 'OS_USER') = 'oracle' AND (SYS_CONTEXT('US CREATE TABLE    NONE            NONE
                     ERENV', 'HOST') = 'node1.oracle12c.jp' OR SYS_CONTEXT('USERENV',
                     'HOST') = 'node2.oracle12c.jp')

POL_LOGON_FAIL       SYS_CONTEXT('USERENV', 'OS_USER') = 'oracle' AND (SYS_CONTEXT('US LOGON           NONE            NONE
                     ERENV', 'HOST') = 'node1.oracle12c.jp' OR SYS_CONTEXT('USERENV',
                     'HOST') = 'node2.oracle12c.jp')

POL_OPERATION        SYS_CONTEXT('USERENV', 'OS_USER') = 'oracle' AND (SYS_CONTEXT('US INSERT          SCOTT           EMP
                     ERENV', 'HOST') = 'node1.oracle12c.jp' OR SYS_CONTEXT('USERENV',
                     'HOST') = 'node2.oracle12c.jp')

2. 監査ポリシー適用
SQL> --ログイン失敗検証用
SQL> audit policy POL_LOGON_FAIL by scott whenever not successful;

監査が成功しました。

SQL> --テーブル作成検証用
SQL> audit policy POL_CRE_TBL by scott;

監査が成功しました。

SQL> --データ操作検証用
SQL> audit policy POL_OPERATION;

監査が成功しました。

SQL> select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME          ENABLED_OPT              SUCCESS   FAILURE
--------------- -------------------- ------------------------ --------- ---------
SYS             POL_ALL_ACTIONS      BY                       YES       YES
SCOTT           POL_LOGON_FAIL       BY                       NO        YES
SCOTT           POL_CRE_TBL          BY                       YES       YES
ALL USERS       POL_OPERATION        BY                       YES       YES

3. ノード1からSCOTTでDBログイン(間違ったパスワード)
[oracle@node1 ~]$ sqlplus scott/machigai

SQL*Plus: Release 12.1.0.2.0 Production on 土 11月 26 16:17:57 2022

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

ERROR:
ORA-01017: ユーザー名/パスワードが無効です。ログオンは拒否されました。

#監査ログ確認
SQL> select TO_CHAR(EVENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as EVT_TS,AUDIT_TYPE,USERHOST,DBUSERNAME,ACTION_NAME,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT from UNIFIED_AUDIT_TRAIL order by EVENT_TIMESTAMP desc;

EVT_TS               AUDIT_TYPE           USERHOST             DBUSERNAME      ACTION_NAME     OBJECT_SCHEMA   OBJECT_NAME     SQL_TEXT
-------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
2022/11/26 16:17:58  Standard             node1.oracle12c.jp   SCOTT           LOGON
★ノード1でSCOTTがログイン失敗した監査ログが出力された

4. ノード2からSCOTTでDBログイン(間違ったパスワード)
[oracle@node2 ~]$ sqlplus scott/machigai

SQL*Plus: Release 12.1.0.2.0 Production on 土 11月 26 16:20:39 2022

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

ERROR:
ORA-01017: ユーザー名/パスワードが無効です。ログオンは拒否されました。

#監査ログ確認
SQL> select TO_CHAR(EVENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as EVT_TS,AUDIT_TYPE,USERHOST,DBUSERNAME,ACTION_NAME,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT from UNIFIED_AUDIT_TRAIL order by EVENT_TIMESTAMP desc;

EVT_TS               AUDIT_TYPE           USERHOST             DBUSERNAME      ACTION_NAME     OBJECT_SCHEMA   OBJECT_NAME     SQL_TEXT
-------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
2022/11/26 16:20:40  Standard             node2.oracle12c.jp   SCOTT           LOGON
★ノード2でSCOTTがログイン失敗した監査ログが出力された

5. SCOTTでテーブル作成
SQL> create table test_table(id number, text varchar(10), create_time date);

表が作成されました。

#監査ログ確認
SQL> select TO_CHAR(EVENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as EVT_TS,AUDIT_TYPE,USERHOST,DBUSERNAME,ACTION_NAME,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT from UNIFIED_AUDIT_TRAIL order by EVENT_TIMESTAMP desc;

EVT_TS               AUDIT_TYPE           USERHOST             DBUSERNAME      ACTION_NAME     OBJECT_SCHEMA   OBJECT_NAME     SQL_TEXT
-------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
2022/11/26 16:23:14  Standard             node1.oracle12c.jp   SCOTT           CREATE TABLE    SCOTT           TEST_TABLE      create table test_table(id number, text varchar(10), create_time date)
★SCOTTがCREATE TABLE権限を使用した監査ログが出力された

6. SCOTTでテーブルにデータ挿入
SQL> insert into emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

#監査ログ確認
SQL> select TO_CHAR(EVENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as EVT_TS,AUDIT_TYPE,USERHOST,DBUSERNAME,ACTION_NAME,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT from UNIFIED_AUDIT_TRAIL order by EVENT_TIMESTAMP desc;

EVT_TS               AUDIT_TYPE           USERHOST             DBUSERNAME      ACTION_NAME     OBJECT_SCHEMA   OBJECT_NAME     SQL_TEXT
-------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
2022/11/26 16:24:36  Standard             node1.oracle12c.jp   SCOTT           INSERT          SCOTT           EMP             insert into emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(99
★SCOTTがINSERT権限を使用した監査ログが出力された

④ファイングレイン監査(FGA監査)
ユーザのファイングレイン監査対象の操作が監査されるか検証します
検証手順は従来型監査の検証と同じです

【検証手順】
1. ファイングレイン監査ポリシー作成
2. 監査ポリシーに該当するパターン1
3. 監査ポリシーに該当するパターン2
4. 監査ポリシーに該当しないパターン1
5. 監査ポリシーに該当しないパターン2

【想定】
ファイングレイン監査対象の操作が監査されること

【検証結果】
ファイングレイン監査対象の操作が監査された

【作業ログ】

1. ファイングレイン監査ポリシー作成
SQL> exec dbms_fga.add_policy(object_schema => 'SCOTT', object_name => 'EMP', policy_name => 'fga_audit_emp_sal', audit_condition => 'DEPTNO=10', audit_column => 'SAL', enable => TRUE, statement_types => 'SELECT');

PL/SQLプロシージャが正常に完了しました。
★DEPTNOが10のレコードのSAL列をSELECTしたら監査するように設定

2. 監査ポリシーに該当するパターン1
SQL> select empno, ename, sal, deptno from emp;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            9999         10
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7839 KING             5000         10
      7844 TURNER           1500         30
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10

#監査ログ確認
SQL> select TO_CHAR(EVENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as EVT_TS,AUDIT_TYPE,USERHOST,DBUSERNAME,ACTION_NAME,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT from UNIFIED_AUDIT_TRAIL order by EVENT_TIMESTAMP desc;

EVT_TS               AUDIT_TYPE           USERHOST             DBUSERNAME      ACTION_NAME     OBJECT_SCHEMA   OBJECT_NAME     SQL_TEXT
-------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
2022/11/26 16:32:37  FineGrainedAudit     node1.oracle12c.jp   SCOTT           SELECT          SCOTT           EMP             select empno, ename, sal, deptno from emp
★EMPNO7369・7782・7839・7934のレコードのDEPTNOが10で、SAL列をSELECTしているため、監査ログが出力された

3. 監査ポリシーに該当するパターン2
SQL> select empno, ename, sal, deptno from emp where deptno = 10;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            9999         10
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10

#監査ログ確認
SQL> select TO_CHAR(EVENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as EVT_TS,AUDIT_TYPE,USERHOST,DBUSERNAME,ACTION_NAME,OBJECT_SCHEMA,OBJECT_NAME,SQL_TEXT from UNIFIED_AUDIT_TRAIL order by EVENT_TIMESTAMP desc;

EVT_TS               AUDIT_TYPE           USERHOST             DBUSERNAME      ACTION_NAME     OBJECT_SCHEMA   OBJECT_NAME     SQL_TEXT
-------------------- -------------------- -------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
2022/11/26 16:33:42  FineGrainedAudit     node1.oracle12c.jp   SCOTT           SELECT          SCOTT           EMP             select empno, ename, sal, deptno from emp where deptno = 10
★DEPTNOを10で絞って、SAL列をSELECTしているため、監査ログが出力された

4. 監査ポリシーに該当しないパターン1
SQL> select empno, ename, sal, deptno from emp where deptno = 20;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7566 JONES            2975         20
      7902 FORD             3000         20

#監査ログ確認
★SAL列をSELECTしているがDEPTNOを20で絞っているため、監査ログが出力されない

5. 監査ポリシーに該当しないパターン2
SQL> select empno, ename, job, deptno from emp where deptno = 10;

     EMPNO ENAME      JOB            DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH      CLERK              10
      7782 CLARK      MANAGER            10
      7839 KING       PRESIDENT          10
      7934 MILLER     CLERK              10

#監査ログ確認
★SAL列をSELECTしていないため、監査ログが出力されない

■参考資料
https://www.oracle.com/webfolder/technetwork/jp/ondemand/ddd2013/F-2.pdf
CREATE AUDIT POLICY (統合監査)
AUDIT (統合監査)
SYS_CONTEXT

■おわりに
ざっくり整理するとこんな感じでしょうか

従来型監査は監査によって監査ログの出力先が違っていましたが、統合監査は1つのビューで全監査ログを確認できるため、便利だと思います。
インスタンス起動/停止以外のSYSDBA権限の操作が監査されないのは焦りましたが…

Oracleの監査の検証（従来型監査編）

この記事は、JPOUG Advent Calendar 2022 16日目の記事です。
15日目はNAKASHOU06さんの記事「～クラウド1年生に贈るクラウド利用Tips～ - Qiita」でした。

今回はOracleの監査について検証しました。
監査はシステムのセキュリティ(機密性)を確保する手段の1つで、DBに対する操作を監視(記録)する機能です。
主に「誰が」、「いつ」、「何をしたか」を記録し、DBに対して不正なアクセスや操作がないか監視するのが目的です。

12c以降の場合、監査は2種類(従来型監査・統合監査)使用できますが、今回は従来型監査を検証しました。
今さら従来型監査の検証…という声も聞こえそうですが、統合監査との動きを比較するため、先ず従来型監査の動きを復習しました。
※個人の検証結果に基づく見解のため、正確性に欠ける可能性があります
※20c以降の場合、従来型監査は非推奨となっているため、設計する際はご注意ください (参考) AUDIT (従来型監査)

従来型監査は、必須監査、DBA監査、標準監査(標準データベース監査)、ファイングレイン監査(FGA監査)の監査証跡(監査ログ)をOSファイルやDBに出力します。

必須監査
特権ユーザ(SYSDBA/SYSOPER/SYSASM権限など)の操作を監査します
※必ず実施されるため、無効化できません

【監査対象】
・インスタンス起動/停止
・特権ユーザによるインスタンス接続
【出力先】
・UNIX系
OSファイル(AUDIT_FILE_DEST初期化パラメータで設定したパス/<インスタンス名>_ora_<プロセスID>_<監査日時>.aud)
※DBインスタンス停止中は$ORACLE_HOME/rdbms/audit/<インスタンス名>_ora_<プロセスID>_<監査日時>.audに出力されます
・Windows
イベントビューワ(未検証)

DBA監査
特権ユーザ(SYSDBA/SYSOPER権限など)の操作を監査します
必須監査はインスタンス起動/停止や接続を監査しますが、DBA監査は特権ユーザのDB内の操作を監査します
AUDIT_SYS_OPERATIONS初期化パラメータがTRUEの場合、有効になります

【監査対象】
・特権ユーザによるDB内の操作
【出力先】
・UNIX系
OSファイル(AUDIT_FILE_DEST初期化パラメータで設定したパス/<インスタンス名>_ora_<プロセスID>_<監査日時>.aud)
・Windows
イベントビューワ(未検証)

標準監査(標準データベース監査)
一般ユーザのDBの操作を監査します
表に影響を与えるDDL文(CREATE TABLE/DROP TABLE/TRUNCATE TABLEなど)のSQL文や権限(システム権限、オブジェクト権限)の使用を監査します
SQL文の監査は成功(WHENEVER SUCCESSFUL)、失敗(WHENEVER NOT SUCCESSFUL)で監査対象の操作を絞れます

【監査対象】
・一般ユーザによるDBインスタンスへの接続
・一般ユーザによる特定のDDL文(DB構造の変更)の操作　(例)テーブル作成
・一般ユーザによるシステム権限を使用した操作　(例)CREATE ANY TABLE権限の使用
・一般ユーザによるオブジェクト権限を使用した操作　(例)SCOTT.EMP表へのSELECT
【出力先】
AUDIT_TRAIL初期化パラメータの設定値によって出力先が異なります
AUDIT_TRAIL=os、xml、xml,extendedのいずれか
OSファイル(AUDIT_FILE_DEST初期化パラメータで設定したパス/<インスタンス名>_ora_<プロセスID>_<監査日時>.aud)
※AUDIT_TRAIL=xml,extendedの場合、SQL文、バインド変数も出力します
※WindowsでAUDIT_TRAIL=osの場合、イベントビューワ(未検証)

AUDIT_TRAIL=db、db,extendedのいずれか
DBA_AUDIT_TRAILビュー(SYS.AUD$表)
※AUDIT_TRAIL=db,extendedの場合、SQL文、バインド変数も出力します

値ベース監査
特権/一般ユーザのDB内の操作のうち、値の変化を監査します
例えばUPDATE文の場合、標準監査でSQL文は監査できますが、変更前後の値は監査できません
必須監査みたいに事前に組み込まれた監査機能ではないため、事前に変更前後の値を書き込むテーブルとUPDATE後に変更前後の値をテーブルに書き込むトリガーの作成が必要です
UPDATE文を実行するたびトリガーも実行される(DBへの負荷も増える)ため、注意が必要です

【監査対象】
UPDATE文などによる変更前後の値
【出力先】
ユーザが作成した値ベース監査用のテーブル

ファイングレイン監査(FGA監査)
特権/一般ユーザのデータベース内の操作(DML)を監査します
特定のテーブルのレコードの検索条件や監査対象の列、SQL文などの監査条件を指定して、標準監査よりさらに細かく監査します
※Enterprise Editionのみ

【監査対象】
監査ポリシーに合致したデータへの操作(DML)
【出力先】
DBA_FGA_AUDIT_TRAILビュー(SYS.FGA_LOG$表)
※AUDIT_TRAIL=db,extendedの場合、標準監査とファイングレイン監査の監査ログをDBA_COMMON_AUDIT_TRAILビューで参照可能

■検証環境
OS：Oracle Linux 6.5
GI：Oracle Grid Infrastructure 12c Release 1 (12.1.0.2.0) Enterprise Edition
DB：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)

■前提
・非CDB
・従来型監査のみ

■設定内容
事前に下記のとおり設定しています

統合監査設定
SQL> select inst_id, parameter, value from gv$option where parameter = 'Unified Auditing';

INST_ID PARAMETER            VALUE
------- -------------------- --------------------
      1 Unified Auditing     FALSE
      2 Unified Auditing     FALSE

【参考】統合監査(混合モード)無効化
1. 有効な監査ポリシー確認
SQL> select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME          ENABLED_OPT              SUCCESS   FAILURE
--------------- -------------------- ------------------------ --------- ---------
ALL USERS       ORA_SECURECONFIG     BY                       YES       YES
ALL USERS       ORA_LOGON_FAILURES   BY                       YES       YES

2. 監査ポリシー取り消し
SQL> noaudit policy ORA_SECURECONFIG;

監査取消しが成功しました。

SQL> noaudit policy ORA_LOGON_FAILURES;

監査取消しが成功しました。

3. 有効な監査ポリシー確認
SQL> select * from audit_unified_enabled_policies;

レコードが選択されませんでした。

ASMの監査設定
SQL> --監査ログ(OSファイル)の出力先(デフォルト)
SQL> select inst_id, name, value from gv$parameter where name = 'audit_file_dest';

INST_ID NAME                           VALUE
------- ------------------------------ ----------------------------------------
      1 audit_file_dest                /u01/app/12.1.0/grid/rdbms/audit
      2 audit_file_dest                /u01/app/12.1.0/grid/rdbms/audit

DBの監査設定
SQL> --監査ログ(OSファイル)の出力先(デフォルト)
SQL> select inst_id, name, value from gv$parameter where name = 'audit_file_dest';

INST_ID NAME                           VALUE
------- ------------------------------ ----------------------------------------
      1 audit_file_dest                /u01/app/oracle/admin/orcl/adump
      2 audit_file_dest                /u01/app/oracle/admin/orcl/adump

SQL> --DBA監査設定(デフォルト)
SQL> select inst_id, name, value from gv$parameter where name = 'audit_sys_operations';

INST_ID NAME                           VALUE
------- ------------------------------ ----------------------------------------
      1 audit_sys_operations           TRUE
      2 audit_sys_operations           TRUE

■検証パターン
①必須監査
②DBA監査
③標準監査(標準データベース監査)
④値ベース監査
⑤ファイングレイン監査(FGA監査)

■検証
①必須監査
特権ユーザの必須監査対象の操作が監査されるか検証します
検証環境はRACなのでASMとDBの起動/停止、接続を検証します
本検証では
・ASM操作はSYS(SYSASM権限)
・DB操作はSYS(SYSDBA権限)
を使用します

【検証手順】
1. ASMインスタンス停止
2. ASMインスタンス起動
3. ASMインスタンス接続
4. DBインスタンス停止
5. DBインスタンス起動
6. DBインスタンス接続

【想定】
必須監査対象の操作が監査されること

【検証結果】
必須監査対象の操作が監査された

【作業ログ】

1. ASMインスタンス停止
[root@node1 ~]# /u01/app/12.1.0/grid/bin/crsctl stop crs

#監査ログ確認
[root@node1 ~]# cat /u01/app/12.1.0/grid/rdbms/audit/+ASM1_ora_14288_20221124204627247146143795.aud
Thu Nov 24 20:46:30 2022 +09:00
LENGTH : '154'
ACTION :[18] 'SHUTDOWN IMMEDIATE'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSASM'
CLIENT USER:[4] 'grid'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[0] ''
★SYSASM権限でASMインスタンス停止した監査ログが出力された

2. ASMインスタンス起動
[root@node1 ~]# /u01/app/12.1.0/grid/bin/crsctl start crs -wait

#監査ログ確認
[root@node1 ~]# cat /u01/app/12.1.0/grid/rdbms/audit/+ASM1_ora_19856_20221124204915718819143795.aud
Thu Nov 24 20:49:15 2022 +09:00
LENGTH : '142'
ACTION :[7] 'STARTUP'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSASM'
CLIENT USER:[4] 'grid'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[0] ''
★SYSASM権限でASMインスタンス起動した監査ログが出力された

3. ASMインスタンス接続
[grid@node1 ~]$ sqlplus / as sysasm

#監査ログ確認
[root@node1 ~]# cat /u01/app/12.1.0/grid/rdbms/audit/+ASM1_ora_22562_20221124205356529751143795.aud
Thu Nov 24 20:53:56 2022 +09:00
LENGTH : '147'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSASM'
CLIENT USER:[4] 'grid'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[0] ''
★SYSASM権限でASMインスタンス接続した監査ログが出力された

4. DBインスタンス停止
[oracle@node1 ~]$ srvctl stop database -db orcl

#監査ログ確認
[root@node1 ~]# cat /u01/app/oracle/admin/orcl/adump/orcl1_ora_22996_20221124205518173254143795.aud
Thu Nov 24 20:55:34 2022 +09:00
LENGTH : '156'
ACTION :[18] 'SHUTDOWN IMMEDIATE'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[0] ''
★SYSDBA権限でDBインスタンス停止した監査ログが出力された

5. DBインスタンス起動
[oracle@node1 ~]$ srvctl start database -db orcl

#監査ログ確認
[root@node1 ~]# cat /u01/app/oracle/admin/orcl/adump/orcl1_ora_24752_20221124205952460023143795.aud
Thu Nov 24 20:59:52 2022 +09:00
LENGTH : '144'
ACTION :[7] 'STARTUP'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[0] ''
★SYSDBA権限でDBインスタンス起動した監査ログが出力された

【補足】
DBインスタンス起動/停止はクラスタを跨ぐ操作かどうかで監査ログの出力先が変わります
・クラスタを跨ぐ場合：全DBサーバに監査ログを出力(※1)
・クラスタを跨がない(ノード単位)場合：操作対象のDBサーバに監査ログを出力(※2)
(※1) 例. srvctl start database -db orcl
(※2) 例. srvctl start instance -db orcl -node node1

6. DBインスタンス接続
[oracle@node1 ~]$ sqlplus / as sysdba

#監査ログ確認
[root@node1 ~]# cat /u01/app/oracle/admin/orcl/adump/orcl1_ora_31455_20221124211423662296143795.aud
Thu Nov 24 21:14:23 2022 +09:00
LENGTH : '160'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1646447705'
★SYSDBA権限でDBインスタンス接続した監査ログが出力された

②DBA監査
特権ユーザのDBA監査対象の操作が監査されるか検証します
本検証ではSYS(SYSDBA権限)を使用します

【検証手順】
1. データディクショナリ問合せ
2. データ更新

【想定】
DBA監査対象の操作が監査されること

【検証結果】
DBA監査対象の操作が監査された

【作業ログ】

1. データディクショナリ問合せ
SQL> select count(*) from dba_users;

  COUNT(*)
----------
        42

#監査ログ確認
[root@node1 ~]# cat /u01/app/oracle/admin/orcl/adump/orcl1_ora_31987_20221124211615877436143795.aud
Thu Nov 24 21:16:17 2022 +09:00
LENGTH : '184'
ACTION :[30] 'select count(*) from dba_users'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1646447705'
★SYSDBA権限でデータディクショナリアクセスした監査ログが出力された

2. データ更新
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;

1行が更新されました。

#監査ログ確認
[root@node1 ~]# cat /u01/app/oracle/admin/orcl/adump/orcl1_ora_32177_20221124211651203243143795.aud
Thu Nov 24 21:18:06 2022 +09:00
LENGTH : '204'
ACTION :[50] 'update scott.emp set SAL = 1000 where EMPNO = 7369'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1646447705'
★SYSDBA権限でデータ更新した監査ログが出力された

③標準監査(標準データベース監査)
一般ユーザの標準監査対象の操作が監査されるか検証します
本検証ではSCOTT(サンプルユーザ)を使用します

【検証手順】
1. AUDIT_TRAIL初期化パラメータ設定
2. 監査オプション設定
3. ノード1からSCOTTでDBログイン(間違ったパスワード)
4. ノード2からSCOTTでDBログイン(間違ったパスワード)
5. SCOTTでテーブル作成
6. SCOTTでデータ挿入

【想定】
標準監査対象の操作が監査されること

【検証結果】
標準監査対象の操作が監査された

【作業ログ】

1. AUDIT_TRAIL初期化パラメータ設定
SQL> alter system set audit_trail = db, extended scope=spfile sid='*';

システムが変更されました。

SQL> select inst_id, name, value from gv$parameter where name = 'audit_trail';

INST_ID NAME                           VALUE
------- ------------------------------ ----------------------------------------
      1 audit_trail                    DB, EXTENDED
      2 audit_trail                    DB, EXTENDED
※反映させるため、DB再起動しています

2. 監査オプション設定
SQL> --ログイン失敗検証用
SQL> audit SESSION by SCOTT by access whenever not successful;

監査が成功しました。

SQL> --テーブル作成検証用
SQL> audit TABLE by SCOTT by access;

監査が成功しました。

SQL> --データ操作検証用
SQL> audit INSERT on scott.emp by access;

監査が成功しました。

SQL> select user_name, audit_option, success, failure from dba_stmt_audit_opts order by audit_option;

USER_NAME            AUDIT_OPTION         SUCCESS         FAILURE
-------------------- -------------------- --------------- ---------------
SCOTT                CREATE SESSION       NOT SET         BY ACCESS
SCOTT                TABLE                BY ACCESS       BY ACCESS
★オブジェクト権限の監査オプションは表示されなかった…

3. ノード1からSCOTTでDBログイン(間違ったパスワード)
[oracle@node1 ~]$ sqlplus scott/machigai

SQL*Plus: Release 12.1.0.2.0 Production on 木 11月 24 22:17:58 2022

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

ERROR:
ORA-01017: ユーザー名/パスワードが無効です。ログオンは拒否されました。

#監査ログ確認
SQL> select TO_CHAR(TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as TS, USERNAME, USERHOST, OWNER, OBJ_NAME, ACTION_NAME, SQL_TEXT from dba_audit_trail order by TIMESTAMP desc;

TS                   USERNAME        USERHOST             OWNER           OBJ_NAME        ACTION_NAME          SQL_TEXT
-------------------- --------------- -------------------- --------------- --------------- -------------------- --------------------------------------------------------------------------------
2022/11/24 22:17:59  SCOTT           node1.oracle12c.jp                                   LOGON
★ノード1でSCOTTがログイン失敗した監査ログが出力された

4. ノード2からSCOTTでDBログイン(間違ったパスワード)
[oracle@node2 ~]$ sqlplus scott/machigai

SQL*Plus: Release 12.1.0.2.0 Production on 木 11月 24 22:19:29 2022

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

ERROR:
ORA-01017: ユーザー名/パスワードが無効です。ログオンは拒否されました。

#監査ログ確認
SQL> select TO_CHAR(TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as TS, USERNAME, USERHOST, OWNER, OBJ_NAME, ACTION_NAME, SQL_TEXT from dba_audit_trail order by TIMESTAMP desc;

TS                   USERNAME        USERHOST             OWNER           OBJ_NAME        ACTION_NAME          SQL_TEXT
-------------------- --------------- -------------------- --------------- --------------- -------------------- --------------------------------------------------------------------------------
2022/11/24 22:19:30  SCOTT           node2.oracle12c.jp                                   LOGON
★ノード2でSCOTTがログイン失敗した監査ログが出力された

5. SCOTTでテーブル作成
SQL> create table test_table(id number, text varchar(10), create_time date);

表が作成されました。

#監査ログ確認
SQL> select TO_CHAR(TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as TS, USERNAME, USERHOST, OWNER, OBJ_NAME, ACTION_NAME, SQL_TEXT from dba_audit_trail order by TIMESTAMP desc;

TS                   USERNAME        USERHOST             OWNER           OBJ_NAME        ACTION_NAME          SQL_TEXT
-------------------- --------------- -------------------- --------------- --------------- -------------------- --------------------------------------------------------------------------------
2022/11/24 22:20:10  SCOTT           node1.oracle12c.jp   SCOTT           TEST_TABLE      CREATE TABLE         create table test_table(id number, text varchar(10), create_time date)
★SCOTTがCREATE TABLE権限を使用した監査ログが出力された

6. SCOTTでデータ挿入
SQL> insert into emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

#監査ログ確認
SQL> select TO_CHAR(TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as TS, USERNAME, USERHOST, OWNER, OBJ_NAME, ACTION_NAME, SQL_TEXT from dba_audit_trail order by TIMESTAMP desc;

TS                   USERNAME        USERHOST             OWNER           OBJ_NAME        ACTION_NAME          SQL_TEXT
-------------------- --------------- -------------------- --------------- --------------- -------------------- --------------------------------------------------------------------------------
2022/11/24 22:24:44  SCOTT           node1.oracle12c.jp   SCOTT           EMP             INSERT               insert into emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999
                                                                                                               , 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10)
★SCOTTがINSERT権限を使用した監査ログが出力された

④値ベース監査
ユーザがデータ更新した際、変更前後の値が監査されるか検証します
本検証ではSCOTT(サンプルユーザ)を使用します

【検証手順】
1. 値ベース監査用テーブル作成
2. 値ベース監査用トリガー作成
3. SCOTTでデータ更新

【想定】
変更前後の値が監査されること

【検証結果】
変更前後の値が監査された

【作業ログ】

1. 値ベース監査用テーブル作成
SQL> create table audit_emp_sal(username varchar2(30), create_date date, empno number, new_sal number, old_sal number);

表が作成されました。

2. 値ベース監査用トリガー作成
SQL> create or replace trigger trg_audit_emp_sal after update of sal on scott.emp referencing new as new old as old for each row
  2  begin
  3    if :old.sal != :new.sal then
  4      insert into audit_emp_sal values(sys_context('userenv', 'session_user'), sysdate, :new.empno, :new.sal, :old.sal);
  5    end if;
  6  end;
  7  /

トリガーが作成されました。
★SCOTTのEMP表のSAL列を更新(UPDATE)後、変更前後の値をAUDIT_EMP_SAL表に書き込むように設定

3. SCOTTでデータ更新
SQL> select * from emp where EMPNO = 7369;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17       1000                    10

SQL> update emp set SAL = 9999 where EMPNO = 7369;

1行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select * from emp where EMPNO = 7369;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17       9999                    10

#監査ログ確認
SQL> select USERNAME, TO_CHAR(CREATE_DATE, 'YYYY/MM/DD HH24:MI:SS') as CREATE_DATE, EMPNO, NEW_SAL, OLD_SAL from audit_emp_sal;

USERNAME        CREATE_DATE               EMPNO    NEW_SAL    OLD_SAL
--------------- -------------------- ---------- ---------- ----------
SCOTT           2022/11/24 22:30:38        7369       9999       1000
★SCOTTのUPDATE文による変更前(OLD_SAL)、変更後(NEW_SAL)の値が監査ログに出力された

⑤ファイングレイン監査(FGA監査)
ユーザのファイングレイン監査対象の操作が監査されるか検証します
本検証ではSCOTT(サンプルユーザ)を使用します

【検証手順】
1. ファイングレイン監査ポリシー作成
2. 監査ポリシーに該当するパターン1
3. 監査ポリシーに該当するパターン2
4. 監査ポリシーに該当しないパターン1
5. 監査ポリシーに該当しないパターン2

【想定】
ファイングレイン監査対象の操作が監査されること

【検証結果】
ファイングレイン監査対象の操作が監査された

【作業ログ】

1. ファイングレイン監査ポリシー作成
SQL> exec dbms_fga.add_policy(object_schema => 'SCOTT', object_name => 'EMP', policy_name => 'fga_audit_emp_sal', audit_condition => 'DEPTNO=10', audit_column => 'SAL', enable => TRUE, statement_types => 'SELECT');

PL/SQLプロシージャが正常に完了しました。
★DEPTNOが10のレコードのSAL列をSELECTしたら監査するように設定

2. 監査ポリシーに該当するパターン1
SQL> select empno, ename, sal, deptno from emp;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            9999         10
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7839 KING             5000         10
      7844 TURNER           1500         30
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10

#監査ログ確認
SQL> select TO_CHAR(TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as TS, DB_USER, USERHOST, OBJECT_NAME, SQL_TEXT, STATEMENT_TYPE from dba_fga_audit_trail order by TS desc;

TS                   DB_USER         USERHOST             OBJECT_NAME     SQL_TEXT                                                                         STATEMENT_TYPE
-------------------- --------------- -------------------- --------------- -------------------------------------------------------------------------------- ---------------
2022/11/24 22:32:35  SCOTT           node1.oracle12c.jp   EMP             select empno, ename, sal, deptno from emp                                        SELECT
★EMPNO7369・7782・7839・7934のレコードのDEPTNOが10で、SAL列をSELECTしているため、監査ログが出力された

3. 監査ポリシーに該当するパターン2
SQL> select empno, ename, sal, deptno from emp where deptno = 10;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            9999         10
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10

#監査ログ確認
SQL> select TO_CHAR(TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') as TS, DB_USER, USERHOST, OBJECT_NAME, SQL_TEXT, STATEMENT_TYPE from dba_fga_audit_trail order by TS desc;

TS                   DB_USER         USERHOST             OBJECT_NAME     SQL_TEXT                                                                         STATEMENT_TYPE
-------------------- --------------- -------------------- --------------- -------------------------------------------------------------------------------- ---------------
2022/11/24 22:32:42  SCOTT           node1.oracle12c.jp   EMP             select empno, ename, sal, deptno from emp where deptno = 10                      SELECT
★DEPTNOを10で絞って、SAL列をSELECTしているため、監査ログが出力された

4. 監査ポリシーに該当しないパターン1
SQL> select empno, ename, sal, deptno from emp where deptno = 20;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7566 JONES            2975         20
      7902 FORD             3000         20

#監査ログ確認
★SAL列をSELECTしているがDEPTNOを20で絞っているため、監査ログが出力されない

5. 監査ポリシーに該当しないパターン2
SQL> select empno, ename, job, deptno from emp where deptno = 10;

     EMPNO ENAME      JOB            DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH      CLERK              10
      7782 CLARK      MANAGER            10
      7839 KING       PRESIDENT          10
      7934 MILLER     CLERK              10

#監査ログ確認
★SAL列をSELECTしていないため、監査ログが出力されない

■参考資料
AUDIT (従来型監査)
https://www.oracle.com/jp/a/tech/docs/technical-resources/db-audit-100831.pdf

■おわりに
監査ログを自動的に削除する機能はないため、削除運用も設計が必要です。
監査は機密性を確保する手段でしたが、完全性や可用性は同時実行性やバックアップ・リカバリなどで確保します。
次回は従来型監査で監査した項目を統合監査で監査した場合、どのように監査されるのか検証しようと思います。

最後までお読みいただきありがとうございました。
17日目の記事もお楽しみに！

LogMiner検証

OracleユーティリティのLogMinerについて、机上の知識はあるものの、実務経験が無いため、実際の動きを検証しました。

LogMinerは、REDOログファイルやアーカイブログファイルに記録された更新情報(SQLなど)を分析できるツールです。
ただし、LogMinerを使えばすぐ分析できるわけではなく、事前にサプリメンタル・ロギング(REDOログファイルに分析用の追加情報を出力する機能)を有効にする必要があります。

なお、サプリメンタル・ロギングを有効にする場合、有効範囲とロギングレベルを指定する必要があります。

有効範囲
データベースレベルか表レベルで指定できます。
データベースレベルの場合、DB全体に対して有効になります。
表レベルの場合、特定の表に対して有効になります。

データベースレベルで指定する場合
alter database [add | drop] supplemental log data [(ALL | PRIMARY KEY | UNIQUE | FOREIGN KEY) columns];

表レベルで指定する場合
alter <スキーマ名>.<表名> [add | drop] supplemental log data (ALL | PRIMARY KEY | UNIQUE | FOREIGN KEY) columns;

※本検証はデータベースレベルで検証します

ロギングレベル
最小レベルか識別キーレベルで指定できます。
ロギングレベルに応じてREDOログファイルに追加される情報も増えます。

最小サプリメンタル・ロギング
LogMiner(LogMinerの仕組みを使用している製品)を使用するにあたり、必要最低限の追加情報(ログ情報)をREDOログファイルに出力します。
表レベルでは設定できません。

識別キー・ロギング
PRIMARY KEY
主キーを持つ表に対して、更新されるたびにすべての主キー列をREDOログファイルに出力します。
データベースレベル、表レベルともに設定できます。(データベースレベルで有効した場合、同時に最小レベルも有効になります)

UNIQUE
一意キーを持つ表に対して、一意キー列のいずれかが更新されるたびにその一意キーに属するすべての列をREDOログファイルに出力します。
データベースレベル、表レベルともに設定できます。(データベースレベルで有効した場合、同時に最小レベルも有効になります)

FOREIGN KEY
外部キーを持つ表に対して、外部キー列のいずれかが更新されるたびにその外部キーに属するすべての列をREDOログファイルに出力します。
データベースレベル、表レベルともに設定できます。(データベースレベルで有効した場合、同時に最小レベルも有効になります)

ALL
行が更新されるたびにすべての列をREDOログファイルに出力します。
データベースレベル、表レベルともに設定できます。(データベースレベルで有効した場合、同時に最小レベルも有効になります)

余談ですが、サプリメンタル・ロギングはOracle GoldenGate(LogMinerの仕組みを使用している製品)などDB間でデータ連携するときにも使用しています。

LogMinerを使用する目的は公式マニュアルではざっくり下記のように記載されています。
・アプリケーションレベルのエラー(不適切な行削除や行更新、オブジェクトの削除など)など、論理破損が発生した時期やリカバリSQLの特定
・依存関係のあるトランザクションのリカバリSQLの特定
・SQLの実行履歴から実行したSQLの傾向分析(どの表で更新や挿入が頻発しているかなど)
・事後監査(DBで実行されたDMLやDDL、実行順序、実行者などの追跡など)

特にアプリケーションレベルのエラーはフラッシュバック操作すればいいんじゃない？という疑問もありますが、高速リカバリ領域を構成していない(構成できない)場合などはLogMinerが使えそうですね。

■検証環境
OS：Oracle Linux 6.5
GI:Oracle Grid Infrastructure 12c Release 1 (12.1.0.2.0) Enterprise Edition
DB:Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)

■前提
なし

■検証パターン
①最小サプリメンタル・ロギング
②識別キー(PRIMARY KEY)サプリメンタル・ロギング
③識別キー(FOREIGN KEY)サプリメンタル・ロギング
④識別キー(ALL)サプリメンタル・ロギング
※UNIQUEは手元に手ごろな表がなかったので未検証

■検証
①最小サプリメンタル・ロギング
ロギングレベルを最小サプリメンタル・ロギングにした場合、REDOログファイルに出力される内容を検証します

【検証手順】
1. 最小サプリメンタルロギング設定
2. 分析対象のREDOログファイル特定
3. LogMinerディクショナリ・ファイル作成
4. 分析対象のREDOログファイル追加
5. LogMiner起動
6. テストデータ更新
7. 分析
8. LogMiner終了
9. 最小サプリメンタルロギング削除

【作業ログ】

1. 最小サプリメンタルロギング設定
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
NO                        NO                        NO                        NO                        NO

SQL> alter database add supplemental log data;

データベースが変更されました。

SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
YES                       NO                        NO                        NO                        NO

2. 分析対象のREDOログファイル特定
SQL> select
  2    l.GROUP#, l.THREAD#, l.SEQUENCE#, l.STATUS, lf.MEMBER
  3  from
  4    v$log l, v$logfile lf
  5  where
  6    l.GROUP#  = lf.GROUP#
  7  order by
  8    l.GROUP#, l.THREAD#;

    GROUP#    THREAD#  SEQUENCE# STATUS          MEMBER
---------- ---------- ---------- --------------- ----------------------------------------
         1          1         43 INACTIVE        +DATA/ORCL/ONLINELOG/redo01.log
         2          1         44 CURRENT         +DATA/ORCL/ONLINELOG/redo02.log
         3          2         39 CURRENT         +DATA/ORCL/ONLINELOG/redo03.log
         4          2         38 INACTIVE        +DATA/ORCL/ONLINELOG/redo04.log

3. LogMinerディクショナリ・ファイル作成
LogMinerディクショナリは、REDOログファイルのREDOデータをユーザに表示する際、オブジェクトIDをオブジェクト名に変換するためのファイルです(マップみたいなイメージ)
LogMinerディクショナリには3種類ありますが、本検証ではオンライン・カタログを使用するため、LogMinerディクショナリ・ファイル作成は割愛します

-オンライン・カタログ
-REDOログファイル埋め込み
-フラットファイル(非推奨)

4. 分析対象のREDOログファイル追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo02.log', OPTIONS => DBMS_LOGMNR.NEW);

PL/SQLプロシージャが正常に完了しました。

★RACなのでスレッド2の方も追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo03.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

★ログスイッチ等でREDOログファイルが切り替わっても対応できるよう、CURRENT以外のREDOログ・ファイルも追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo01.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo04.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

5. LogMiner起動
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQLプロシージャが正常に完了しました。

6. テストデータ更新(別セッション)
別TeraTermを立ち上げ、別セッションでテストデータを更新します

SQL> select * from emp where EMPNO = 7369;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17        800                    20

SQL> update emp set SAL = 9999 where EMPNO = 7369;

1行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select * from emp where EMPNO = 7369;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17       9999                    20

【参考】
一回ハマったのですが、DBMS_LOGMNR.START_LOGMNR()したセッションを終了してしまうと後続のv$logmnr_contentsへ問い合わせた際、下記エラーが発生します
------------------------------------------------------------
行8でエラーが発生しました。:
ORA-01306: dbms_logmnr.start_logmnr()は、v$logmnr_contentsから選択する前に起動する必要があります。
------------------------------------------------------------
※上記が発生した場合、ADD_LOGFILE()からやり直す必要があります

7. 分析
SQL> SELECT
  2    SCN, TO_CHAR(TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') AS TM_STAMP, OPERATION, SQL_REDO, SQL_UNDO
  3  FROM
  4    V$LOGMNR_CONTENTS
  5  WHERE
  6        USERNAME = 'SCOTT'
  7  ORDER BY
  8    SCN DESC;

       SCN TM_STAMP             OPERATION  SQL_REDO                                                     SQL_UNDO
---------- -------------------- ---------- ------------------------------------------------------------ ------------------------------------------------------------
   6727554 2022/11/11 10:28:46  UPDATE     update "SCOTT"."EMP" set "SAL" = '9999' where "SAL" = '800'  update "SCOTT"."EMP" set "SAL" = '800' where "SAL" = '9999'
                                           and ROWID = 'AAAWwdAAGAAAADFAAA';                            and ROWID = 'AAAWwdAAGAAAADFAAA';
★SQL_REDO列のWHERE句がEMPNOではなく、SALとROWIDに変わっていました

8. LogMiner終了
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQLプロシージャが正常に完了しました。

9. 最小サプリメンタルロギング削除
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
YES                       NO                        NO                        NO                        NO

SQL> alter database drop supplemental log data;

データベースが変更されました。

SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
NO                        NO                        NO                        NO                        NO

②識別キー(PRIMARY KEY)サプリメンタル・ロギング
ロギングレベルを識別キー(PRIMARY KEY)サプリメンタル・ロギングにした場合、REDOログファイルに出力される内容を検証します

【検証手順】
1. 識別キー(PRIMARY KEY)サプリメンタル・ロギング設定
2. 分析対象のREDOログファイル特定
3. LogMinerディクショナリ・ファイル作成
4. 分析対象のREDOログファイル追加
5. LogMiner起動
6. テストデータ更新
7. 分析
8. LogMiner終了
9. 識別キー(PRIMARY KEY)サプリメンタル・ロギング削除

【作業ログ】

1. 識別キー(PRIMARY KEY)サプリメンタル・ロギング設定
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
NO                        NO                        NO                        NO                        NO

SQL> alter database add supplemental log data (primary key) columns;

データベースが変更されました。

SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
IMPLICIT                  YES                       NO                        NO                        NO
★識別キーサプリメンタル・ロギングを有効にした際、同時に最小サプリメンタル・ロギングも有効になっているため、SUPPLEMENTAL_LOG_DATA_MIN列もIMPLICITになっています

2. 分析対象のREDOログファイル特定
SQL> select
  2    l.GROUP#, l.THREAD#, l.SEQUENCE#, l.STATUS, lf.MEMBER
  3  from
  4    v$log l, v$logfile lf
  5  where
  6    l.GROUP#  = lf.GROUP#
  7  order by
  8    l.GROUP#, l.THREAD#;

    GROUP#    THREAD#  SEQUENCE# STATUS          MEMBER
---------- ---------- ---------- --------------- ----------------------------------------
         1          1         43 INACTIVE        +DATA/ORCL/ONLINELOG/redo01.log
         2          1         44 CURRENT         +DATA/ORCL/ONLINELOG/redo02.log
         3          2         39 CURRENT         +DATA/ORCL/ONLINELOG/redo03.log
         4          2         38 INACTIVE        +DATA/ORCL/ONLINELOG/redo04.log

3. LogMinerディクショナリ・ファイル作成
本検証ではオンライン・カタログを使用するため、LogMinerディクショナリ・ファイル作成は割愛します

4. 分析対象のREDOログファイル追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo02.log', OPTIONS => DBMS_LOGMNR.NEW);

PL/SQLプロシージャが正常に完了しました。

★RACなのでスレッド2の方も追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo03.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

★ログスイッチ等でREDOログファイルが切り替わっても対応できるよう、CURRENT以外のREDOログ・ファイルも追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo01.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo04.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

5. LogMiner起動
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQLプロシージャが正常に完了しました。

6. テストデータ更新(別セッション)
別TeraTermを立ち上げ、別セッションでテストデータを更新します
PRIMARY KEY制約(EMPNO列)があるレコードを更新してみます

SQL> select * from emp where EMPNO = 7369;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17       9999                    20

SQL> update emp set SAL = 10000 where EMPNO = 7369;

1行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select * from emp where EMPNO = 7369;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17      10000                    20

7. 分析
SQL> SELECT
  2    SCN, TO_CHAR(TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') AS TM_STAMP, OPERATION, SQL_REDO, SQL_UNDO
  3  FROM
  4    V$LOGMNR_CONTENTS
  5  WHERE
  6        USERNAME = 'SCOTT'
  7  ORDER BY
  8    SCN DESC;

       SCN TM_STAMP             OPERATION  SQL_REDO                                                     SQL_UNDO
---------- -------------------- ---------- ------------------------------------------------------------ ------------------------------------------------------------
   6729463 2022/11/11 10:39:21  UPDATE     update "SCOTT"."EMP" set "SAL" = '10000' where "EMPNO" = '73 update "SCOTT"."EMP" set "SAL" = '9999' where "EMPNO" = '736
                                           69' and "SAL" = '9999' and ROWID = 'AAAWwdAAGAAAADFAAA';     9' and "SAL" = '10000' and ROWID = 'AAAWwdAAGAAAADFAAA';
★最小サプリメンタル・ロギングではEMPNOはWHERE句に含まれていませんでしたが、SQL_REDO列のWHERE句がEMPNOとSALとROWIDに変わっていました

8. LogMiner終了
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQLプロシージャが正常に完了しました。

9. 識別キー(PRIMARY KEY)サプリメンタル・ロギング削除
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
IMPLICIT                  YES                       NO                        NO                        NO

SQL> alter database drop supplemental log data (primary key) columns;

データベースが変更されました。

SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
NO                        NO                        NO                        NO                        NO

③識別キー(FOREIGN KEY)サプリメンタル・ロギング
ロギングレベルを識別キー(FOREIGN KEY)サプリメンタル・ロギングにした場合、REDOログファイルに出力される内容を検証します

【検証手順】
1. 識別キー(FOREIGN KEY)サプリメンタル・ロギング設定
2. 分析対象のREDOログファイル特定
3. LogMinerディクショナリ・ファイル作成
4. 分析対象のREDOログファイル追加
5. LogMiner起動
6. テストデータ更新
7. 分析
8. LogMiner終了
9. 識別キー(FOREIGN KEY)サプリメンタル・ロギング削除

【作業ログ】

1. 識別キー(FOREIGN KEY)サプリメンタル・ロギング設定
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
NO                        NO                        NO                        NO                        NO

SQL> alter database add supplemental log data (foreign key) columns;

データベースが変更されました。

SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
IMPLICIT                  NO                        NO                        YES                       NO

2. 分析対象のREDOログファイル特定
SQL> select
  2    l.GROUP#, l.THREAD#, l.SEQUENCE#, l.STATUS, lf.MEMBER
  3  from
  4    v$log l, v$logfile lf
  5  where
  6    l.GROUP#  = lf.GROUP#
  7  order by
  8    l.GROUP#, l.THREAD#;

    GROUP#    THREAD#  SEQUENCE# STATUS          MEMBER
---------- ---------- ---------- --------------- ----------------------------------------
         1          1         43 INACTIVE        +DATA/ORCL/ONLINELOG/redo01.log
         2          1         44 CURRENT         +DATA/ORCL/ONLINELOG/redo02.log
         3          2         39 CURRENT         +DATA/ORCL/ONLINELOG/redo03.log
         4          2         38 INACTIVE        +DATA/ORCL/ONLINELOG/redo04.log

3. LogMinerディクショナリ・ファイル作成
本検証ではオンライン・カタログを使用するため、LogMinerディクショナリ・ファイル作成は割愛します

4. 分析対象のREDOログファイル追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo02.log', OPTIONS => DBMS_LOGMNR.NEW);

PL/SQLプロシージャが正常に完了しました。

★RACなのでスレッド2の方も追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo03.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

★ログスイッチ等でREDOログファイルが切り替わっても対応できるよう、CURRENT以外のREDOログ・ファイルも追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo01.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo04.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

5. LogMiner起動
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQLプロシージャが正常に完了しました。

6. テストデータ更新(別セッション)
別TeraTermを立ち上げ、別セッションでテストデータを更新します
外部キー制約(DEPTNO列)がある列の値を更新してみます

SQL> select * from emp where EMPNO = 7369;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17      10000                    20

SQL> update emp set DEPTNO = 10 where EMPNO = 7369;

1行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select * from emp where EMPNO = 7369;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17      10000                    10

7. 分析
SQL> SELECT
  2    SCN, TO_CHAR(TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') AS TM_STAMP, OPERATION, SQL_REDO, SQL_UNDO
  3  FROM
  4    V$LOGMNR_CONTENTS
  5  WHERE
  6        USERNAME = 'SCOTT'
  7  ORDER BY
  8    SCN DESC;

       SCN TM_STAMP             OPERATION  SQL_REDO                                                     SQL_UNDO
---------- -------------------- ---------- ------------------------------------------------------------ ------------------------------------------------------------
   6733768 2022/11/11 11:00:45  UPDATE     update "SCOTT"."EMP" set "DEPTNO" = '10' where "DEPTNO" = '2 update "SCOTT"."EMP" set "DEPTNO" = '20' where "DEPTNO" = '1
                                           0' and ROWID = 'AAAWwdAAGAAAADFAAA';                         0' and ROWID = 'AAAWwdAAGAAAADFAAA';
★SQL_REDO列のWHERE句がEMPNOではなく、DEPTNOとROWIDに変わっていました

8. LogMiner終了
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQLプロシージャが正常に完了しました。

9. 識別キー(FOREIGN KEY)サプリメンタル・ロギング削除
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
IMPLICIT                  NO                        NO                        YES                       NO

SQL> alter database drop supplemental log data (foreign key) columns;

データベースが変更されました。

SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
NO                        NO                        NO                        NO                        NO

④識別キー(ALL)サプリメンタル・ロギング
ロギングレベルを識別キー(ALL)サプリメンタル・ロギングにした場合、REDOログファイルに出力される内容を検証します

【検証手順】
1. 識別キー(ALL)サプリメンタル・ロギング設定
2. 分析対象のREDOログファイル特定
3. LogMinerディクショナリ・ファイル作成
4. 分析対象のREDOログファイル追加
5. LogMiner起動
6. テストデータ更新
7. 分析
8. LogMiner終了
9. 識別キー(ALL)サプリメンタル・ロギング削除

【作業ログ】

1. 識別キー(ALL)サプリメンタル・ロギング設定
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
NO                        NO                        NO                        NO                        NO

SQL> alter database add supplemental log data (all) columns;

データベースが変更されました。

SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
IMPLICIT                  NO                        NO                        NO                        YES

2. 分析対象のREDOログファイル特定
SQL> select
  2    l.GROUP#, l.THREAD#, l.SEQUENCE#, l.STATUS, lf.MEMBER
  3  from
  4    v$log l, v$logfile lf
  5  where
  6    l.GROUP#  = lf.GROUP#
  7  order by
  8    l.GROUP#, l.THREAD#;

    GROUP#    THREAD#  SEQUENCE# STATUS          MEMBER
---------- ---------- ---------- --------------- ----------------------------------------
         1          1         43 INACTIVE        +DATA/ORCL/ONLINELOG/redo01.log
         2          1         44 CURRENT         +DATA/ORCL/ONLINELOG/redo02.log
         3          2         39 CURRENT         +DATA/ORCL/ONLINELOG/redo03.log
         4          2         38 INACTIVE        +DATA/ORCL/ONLINELOG/redo04.log

3. LogMinerディクショナリ・ファイル作成
本検証ではオンライン・カタログを使用するため、LogMinerディクショナリ・ファイル作成は割愛します

4. 分析対象のREDOログファイル追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo02.log', OPTIONS => DBMS_LOGMNR.NEW);

PL/SQLプロシージャが正常に完了しました。

★RACなのでスレッド2の方も追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo03.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

★ログスイッチ等でREDOログファイルが切り替わっても対応できるよう、CURRENT以外のREDOログ・ファイルも追加
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo01.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATA/ORCL/ONLINELOG/redo04.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQLプロシージャが正常に完了しました。

5. LogMiner起動
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQLプロシージャが正常に完了しました。

6. テストデータ更新(別セッション)
別TeraTermを立ち上げ、別セッションでテストデータを更新します

SQL> select * from emp where EMPNO = 7369;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17      10000                    10

SQL> update emp set SAL = 800 where EMPNO = 7369;

1行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select * from emp where EMPNO = 7369;

     EMPNO ENAME      JOB               MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK            7902 80-12-17        800                    10

7. 分析
SQL> SELECT
  2    SCN, TO_CHAR(TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') AS TM_STAMP, OPERATION, SQL_REDO, SQL_UNDO
  3  FROM
  4    V$LOGMNR_CONTENTS
  5  WHERE
  6        USERNAME = 'SCOTT'
  7  ORDER BY
  8    SCN DESC;

       SCN TM_STAMP             OPERATION  SQL_REDO                                                     SQL_UNDO
---------- -------------------- ---------- ------------------------------------------------------------ ------------------------------------------------------------
   6736256 2022/11/11 11:11:53  UPDATE     update "SCOTT"."EMP" set "SAL" = '800' where "EMPNO" = '7369 update "SCOTT"."EMP" set "SAL" = '10000' where "EMPNO" = '73
                                           ' and "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MGR" = '790 69' and "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MGR" = '7
                                           2' and "HIREDATE" = TO_DATE('80-12-17', 'RR-MM-DD') and "SAL 902' and "HIREDATE" = TO_DATE('80-12-17', 'RR-MM-DD') and "S
                                           " = '10000' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID AL" = '800' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID
                                            = 'AAAWwdAAGAAAADFAAA';                                      = 'AAAWwdAAGAAAADFAAA';
★SQL_REDO列のWHERE句がEMP表のすべての列とROWIDに変わっていました

8. LogMiner終了
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQLプロシージャが正常に完了しました。

9. 識別キー(ALL)サプリメンタル・ロギング削除
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
IMPLICIT                  NO                        NO                        NO                        YES

SQL> alter database drop supplemental log data (all) columns;

データベースが変更されました。

SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
NO                        NO                        NO                        NO                        NO

■参考資料
使用例1: LogMinerを使用した特定のユーザーによる変更の追跡
LogMinerディクショナリ・オプション
DBMS_LOGMNR
データベース・レベルのサプリメンタル・ロギング

Data Guardの保護モード

Data Guardの保護モードについて調査しました。

製品バージョンによって、保護モードの設定方法が変わるものもありますが、今回は12cR1を調査対象としています。

保護モードには3つあります。
・最大保護モード
・最大可用性モード
・最大パフォーマンスモード

保護モードはデータ保護、システム性能など優先する要件によって使い分けます。
・データ保護(プライマリDBとスタンバイDBのデータ整合性など)
・システム性能(プライマリDBに対するスタンバイDBからの応答速度など)

最大保護モード
プライマリDBとスタンバイDBでデータ整合性(プライマリDB障害時、スタンバイDBはデータロストなし)が保証されている状態です
プライマリDBでデータコミットし、REDOデータ転送後、プライマリDBはスタンバイDB(RFSプロセス)からコミット応答を待ちます
コミット応答受信後、プライマリDBもコミット確定します

障害等でスタンバイDBから応答がない場合、プライマリDBは停止します
一般的にはプライマリDBの停止を回避するため、マルチスタンバイ構成にし、1つのスタンバイDBが壊れてもプライマリDBが停止しないようにします
データ保護を優先するモードです

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE                PROTECTION_LEVEL
------------------------------ ------------------------------
MAXIMUM PROTECTION             MAXIMUM PROTECTION

最大可用性モード
プライマリDBとスタンバイDBでデータ整合性(プライマリDB障害時、スタンバイDBはデータロストなし)が(通常時は)保証されている状態です
プライマリDBでデータコミットし、REDOデータ転送後、プライマリDBはスタンバイDB(RFSプロセス)からコミット応答を待ちます
コミット応答受信後、プライマリDBもコミット確定します
ただし、プライマリDBから受信したREDOデータをスタンバイDBで確認するタイミングによって、コミット応答のタイミングが変わります

確認タイミングがAFFIRMの場合、プライマリDBから受信したREDOデータをスタンバイREDOログへ書き込んだ後にプライマリDBへコミット応答します(データ書込み完了まで待って応答する)
確認タイミングがNOAFFIRMの場合、プライマリDBから受信したREDOデータをスタンバイREDOログへ書き込む前にプライマリDBへコミット応答します(データ書き込み完了を待たず応答する)
※11gR2では、確認タイミングに指定できるのはAFFIRMだけでした

障害等でスタンバイDBから応答がない場合、保護モードを最大パフォーマンスモードへ遷移して稼働を継続します
通常時はデータ保護、障害時はシステム性能を優先するモードです

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE                PROTECTION_LEVEL
------------------------------ ------------------------------
MAXIMUM AVAILABILITY           MAXIMUM AVAILABILITY

最大パフォーマンスモード
プライマリDBとスタンバイDBでデータ整合性(プライマリDB障害時、スタンバイDBはデータロストあり)が保証されない状態です
プライマリDBでデータコミット、REDOデータ転送後、プライマリDBはスタンバイDB(RFSプロセス)からコミット応答を待たずにコミット確定します
デフォルトの保護モードです
システム性能を優先するモードです

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE                PROTECTION_LEVEL
------------------------------ ------------------------------
MAXIMUM PERFORMANCE            MAXIMUM PERFORMANCE

データの保護モード
保護モードの動きを整理しました。

同期モード(SYNC AFFIRM)

準同期モード(SYNC NOAFFIRM)

非同期モード(ASYNC NOAFFIRM)

REDOデータ転送プロセス
NSS (Network Server Sync)
プライマリDBのREDOデータを同期形式でスタンバイDBのRFSプロセスへ転送するプロセス
プライマリDBはデータコミットし、REDOデータ転送後、スタンバイDBのRFSプロセスからの応答を待つ

NSA (Network Server Async)
プライマリDBのREDOデータを非同期形式でスタンバイDBのRFSプロセスへ転送するプロセス
プライマリDBはデータコミットし、REDOデータ転送後、スタンバイDBのRFSプロセスからの応答を待たない

REDOデータ転送モード
SYNC
プライマリDBはデータコミットし、REDOデータ転送後、スタンバイDBのRFSプロセスからの応答を待つ

ASYNC
プライマリDBはデータコミットし、REDOデータ転送後、スタンバイDBのRFSプロセスからの応答を待たない

受信したREDOデータの確認タイミング(コミット応答タイミング)
AFFRIM
プライマリDBから受信したREDOデータをスタンバイREDOログへ書き込んだ後に確認する(スタンバイREDOログに書き込み完了したらコミット応答する)

NOAFFIRM
プライマリDBから受信したREDOデータをスタンバイREDOログへ書き込む前に確認する(スタンバイREDOログに書き込む前にコミット応答する)

【参考】
プライマリDBで保護モードを変更する際、プライマリDBがOPENモードだと下記エラーが発生しました
MOUNTモードで再起動後、alter文を再実行したらコマンド正常終了しました

------------------------------------------------------------
SQL> alter database set standby database to maximize PROTECTION;
alter database set standby database to maximize PROTECTION
*
行1でエラーが発生しました。:
ORA-01126: データベースはこのインスタンスでマウントし、どのインスタンスでもオープンしないでください
------------------------------------------------------------

■参考文献
Oracle Data Guardの保護モード
LOG_ARCHIVE_DEST_nパラメータの属性
Active Data Guard diffs from 11gR2 to 19c - Speaker Deck

Data GuardのMRPプロセス障害検証（RAC構成の場合）

Data GuardのスタンバイサイトがRAC構成だった場合、片ノードで稼働しているMRPプロセスがリアルタイム適用中に異常終了したときの動きを検証してみました。

■検証環境、前提、設定情報
Data Guardのスナップショット・スタンバイ検証 - 忘れかけのIT備忘録 と同様

■検証パターン
①リアルタイム適用中にノード1のMRPプロセスkill
リアルタイム適用中にスタンバイ側のノード1で稼働しているMRPプロセスkillした場合の動きを検証します

【検証手順】
1. [スタンバイDB(ノード1)]適用モード確認
2. [スタンバイDB(両ノード)]MRPプロセス確認
3. [スタンバイDB(ノード1)]MRPプロセスkill
4. [スタンバイDB(両ノード)]MRPプロセス確認
5. [スタンバイDB(ノード1)]MRP起動

【想定】
MRPプロセスがノード2にフェイルオーバーし、リアルタイム適用が継続されること

【検証結果】
MRPプロセスがノード2にフェイルオーバーし、リアルタイム適用が継続されなかった
→ノード1のMRPプロセスkill後、ノード1、ノード2ともにMRPプロセスはいなかった

【作業ログ】

1. [スタンバイDB(ノード1)]適用モード確認
SQL> select client_process, process, thread# ,sequence# ,status from v$managed_standby where process = 'MRP0';

CLIENT_PROCESS       PROCESS                 THREAD#  SEQUENCE# STATUS
-------------------- -------------------- ---------- ---------- --------------------
N/A                  MRP0                          1         15 APPLYING_LOG

2. [スタンバイDB(両ノード)]MRPプロセス確認
#ノード1
[root@dr-node1 ~]# ps -ef|grep mrp|grep -v grep
oracle   15828     1  0 14:07 ?        00:00:00 ora_mrp0_orcldr1
[root@dr-node1 ~]#

#ノード2
[root@dr-node2 ~]# ps -ef|grep mrp|grep -v grep
[root@dr-node2 ~]#

3. [スタンバイDB(ノード1)]MRPプロセスkill
[root@dr-node1 ~]# kill -9 15828

4. [スタンバイDB(両ノード)]MRPプロセス確認
#ノード1
[root@dr-node1 ~]# ps -ef|grep mrp|grep -v grep
[root@dr-node1 ~]#

#ノード2
[root@dr-node2 ~]# ps -ef|grep mrp|grep -v grep
[root@dr-node2 ~]#

★両ノードともにMRPプロセスはいなかった(自動復旧しなかった)

5. [スタンバイDB(ノード1)]適用モード確認
SQL> select client_process, process, thread# ,sequence# ,status from v$managed_standby where process = 'MRP0';

レコードが選択されませんでした。

SQL> alter database recover managed standby database disconnect;

データベースが変更されました。

SQL> select client_process, process, thread# ,sequence# ,status from v$managed_standby where process = 'MRP0';

CLIENT_PROCESS       PROCESS                 THREAD#  SEQUENCE# STATUS
-------------------- -------------------- ---------- ---------- --------------------
N/A                  MRP0                          2         13 APPLYING_LOG

6. [スタンバイDB(両ノード)]MRP起動
#ノード1
[root@dr-node1 ~]# ps -ef|grep mrp|grep -v grep
oracle   23009     1  0 14:25 ?        00:00:00 ora_mrp0_orcldr1
[root@dr-node1 ~]#

#ノード2
[root@dr-node2 ~]# ps -ef|grep mrp|grep -v grep
[root@dr-node2 ~]#

★リアルタイム適用開始後、ノード1でMRPプロセスが動いていた

【参考】
スタンバイ側のMRPプロセスkillしたときの各ノードのDBアラートログも確認してみました

プライマリ側(ノード1)
プロセスkill付近の時間帯は出力なし

プライマリ側(ノード2)
プロセスkill付近の時間帯は出力なし

スタンバイ側(ノード1)
Sat Nov 05 14:18:27 2022
MRP0: Background Media Recovery terminated with error 448
Sat Nov 05 14:18:27 2022
Recovery coordinator died, shutting down parallel recovery
Sat Nov 05 14:18:27 2022
Errors in file /u01/app/oracle/diag/rdbms/orcldr/orcldr1/trace/orcldr1_pr00_15842.trc:
ORA-00448: バックグラウンド・プロセスが正常終了しました。
Sat Nov 05 14:18:27 2022
Managed Standby Recovery not using Real Time Apply
Sat Nov 05 14:18:27 2022
Recovery interrupted!
Recovered data files to a consistent state at change 6007961
Sat Nov 05 14:18:27 2022
Errors in file /u01/app/oracle/diag/rdbms/orcldr/orcldr1/trace/orcldr1_pr00_15842.trc:
ORA-00448: バックグラウンド・プロセスが正常終了しました。

スタンバイ側(ノード2)
Sat Nov 05 14:18:27 2022
Managed Standby Recovery not using Real Time Apply

■おわりに
RAC構成でも片ノードのMRPプロセスが異常終了した場合、別ノードにフェイルオーバーされるわけではありませんでした。

Data Guardのフェイルオーバー／フェイルバック検証（その2）

今回はバックアップからのリストア/不完全リカバリで旧プライマリDBを復旧してみようと思います。

■検証環境、前提、設定情報
Data Guardのスナップショット・スタンバイ検証 - 忘れかけのIT備忘録 と同様

■検証パターン
①手動フェイルオーバー
②フェイルバック(リストア/不完全リカバリ)

■検証
①手動フェイルオーバー
プライマリDBでインスタンス障害が発生した場合を想定し、手動フェイルオーバーして現行のスタンバイDBがプライマリロールに昇格できるか検証します
インスタンス障害は本検証ではインスタンス異常停止(abort)させます
※プライマリDBが稼働していないパターン

【検証手順】
1. [現プライマリDB]RMANバックアップ
2. [現プライマリDB]インスタンス障害発生
3. [現スタンバイDB]アーカイブギャップ取得
4. [現スタンバイDB]MRP停止
5. [現スタンバイDB]手動フェイルオーバー
6. [新プライマリDB]OCRのロール、開始オプション変更
7. [新プライマリDB]DB再起動

【想定】
フェイルオーバー後、現スタンバイDB(orcldr)のロールがプライマリロールになること

【検証結果】
フェイルオーバー後、現スタンバイDB(orcldr)のロールがプライマリロールになった

【作業ログ】

1. [現プライマリDB]RMANバックアップ
現プライマリDBのデータファイルとアーカイブログのバックアップを取得します
本検証では旧プライマリDBをリストア/不完全リカバリする際、制御ファイルは現行のを使いますが、業務では制御ファイルやSPFILEもバックアップ取得することをお勧めします

RMAN> backup database plus archivelog;

backupが開始されました(開始時間: 22-11-03)
(略)
backupが完了しました(完了時間: 22-11-03)

2. [現プライマリDB]インスタンス障害発生
ABORTで疑似的にインスタンス障害を発生させます

[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行中です。
インスタンスorcl2はノードnode2で実行中です。
[oracle@node1 ~]$ srvctl stop database -db orcl -stopoption abort
[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行されていません。
インスタンスorcl2はノードnode2で実行されていません。

3. [現スタンバイDB]アーカイブギャップ取得
スタンバイDBでアーカイブログのギャップ(不足)が発生しているか確認します
ギャップが発生している場合、プライマリDBのアーカイブログをスタンバイDBにコピー・登録します

SQL> select * from v$archive_gap;

レコードが選択されませんでした。

※今回は発生していないため、スタンバイDBにコピー・登録は割愛します

4. [現スタンバイDB]MRP停止
SQL> select client_process, process, thread# ,sequence# ,status from v$managed_standby where process = 'MRP0';

CLIENT_PROCESS       PROCESS                 THREAD#  SEQUENCE# STATUS
-------------------- -------------------- ---------- ---------- --------------------
N/A                  MRP0                          1        125 APPLYING_LOG

SQL> alter database recover managed standby database cancel;

データベースが変更されました。

SQL> select client_process, process, thread# ,sequence# ,status from v$managed_standby where process = 'MRP0';

レコードが選択されませんでした。

5. [現スタンバイDB]手動フェイルオーバー
SQL> select db_unique_name, database_role from v$database;

DB_UNIQUE_NAME       DATABASE_ROLE
-------------------- ------------------------------------------------
orcldr               PHYSICAL STANDBY

SQL> alter database failover to orcldr;

データベースが変更されました。

SQL> select db_unique_name, database_role from v$database;

DB_UNIQUE_NAME       DATABASE_ROLE
-------------------- ------------------------------------------------
orcldr               PRIMARY

6. [新プライマリDB]OCRのロール、開始オプション変更
OCRに登録されている開始オプションとロールはスタンバイ時の状態のため、手動でプライマリ用に変更します

[oracle@dr-node1 ~]$ srvctl config database -db orcldr
一意のデータベース名: ORCLDR
データベース名: ORCLDR
Oracleホーム: /u01/app/oracle/product/12.1.0/dbhome_1
Oracleユーザー: oracle
spfile: +DATA/ORCLDR/PARAMETERFILE/spfileORCLDR.ora
パスワード・ファイル: +DATA/ORCLDR/PASSWORD/orapwORCLDR
ドメイン: 
開始オプション: mount
停止オプション: immediate
データベース・ロール: PHYSICAL_STANDBY
管理ポリシー: AUTOMATIC
サーバー・プール: 
ディスク・グループ: DATA
マウント・ポイントのパス: 
サービス: 
タイプ: RAC
開始の同時実行性: 
停止の同時実行性: 
OSDBAグループ: dba
OSOPERグループ: oper
データベース・インスタンス: orcldr1,orcldr2
構成されたノード: dr-node1,dr-node2
データベースは管理者によって管理されています
[oracle@dr-node1 ~]$ srvctl modify database -db orcldr -role PRIMARY -startoption open
[oracle@dr-node1 ~]$ srvctl config database -db orcldr
一意のデータベース名: ORCLDR
データベース名: ORCLDR
Oracleホーム: /u01/app/oracle/product/12.1.0/dbhome_1
Oracleユーザー: oracle
spfile: +DATA/ORCLDR/PARAMETERFILE/spfileORCLDR.ora
パスワード・ファイル: +DATA/ORCLDR/PASSWORD/orapwORCLDR
ドメイン: 
開始オプション: open
停止オプション: immediate
データベース・ロール: PRIMARY
管理ポリシー: AUTOMATIC
サーバー・プール: 
ディスク・グループ: DATA
マウント・ポイントのパス: 
サービス: 
タイプ: RAC
開始の同時実行性: 
停止の同時実行性: 
OSDBAグループ: dba
OSOPERグループ: oper
データベース・インスタンス: orcldr1,orcldr2
構成されたノード: dr-node1,dr-node2
データベースは管理者によって管理されています

7. [新プライマリDB]DB再起動
MOUNT状態のため、DB再起動します

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME                                    STATUS
------------------------------------------------ ------------------------------------
orcldr1                                          MOUNTED
orcldr2                                          MOUNTED

[oracle@dr-node1 ~]$ srvctl status database -db orcldr
インスタンスorcldr1はノードdr-node1で実行中です。
インスタンスorcldr2はノードdr-node2で実行中です。
[oracle@dr-node1 ~]$ srvctl stop database -db orcldr
[oracle@dr-node1 ~]$ srvctl status database -db orcldr
インスタンスorcldr1はノードdr-node1で実行されていません。
インスタンスorcldr2はノードdr-node2で実行されていません。
[oracle@dr-node1 ~]$ srvctl start database -db orcldr
[oracle@dr-node1 ~]$ srvctl status database -db orcldr
インスタンスorcldr1はノードdr-node1で実行中です。
インスタンスorcldr2はノードdr-node2で実行中です。

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME                                    STATUS
------------------------------------------------ ------------------------------------
orcldr1                                          OPEN
orcldr2                                          OPEN

●[新プライマリDB]SCNを進める
しばらく新プライマリDBで運用するという想定でデータ更新します

SQL> update emp set SAL = 1111 where EMPNO = 7698;

1行が更新されました。

SQL> commit;

コミットが完了しました。
SQL> update emp set SAL = 2222 where EMPNO = 7782;

1行が更新されました。

SQL> commit;

コミットが完了しました。
SQL> update emp set SAL = 3333 where EMPNO = 7839;

1行が更新されました。

SQL> commit;

コミットが完了しました。

②フェイルバック(リストア／不完全リカバリ)
旧プライマリDBをバックアップからリストア／不完全リカバリ後、スイッチオーバーでロールを入れ替えられるか検証します

【検証手順】
1. [新プライマリ]旧スタンバイDBが新プライマリDBに変換された時点のSCN取得
2. [旧プライマリ]リストア／不完全リカバリ
3. [旧プライマリ]インスタンス1のみMOUNTモードで起動
4. [旧プライマリ]旧プライマリDBのロールをフィジカル・スタンバイに変換
5. [旧プライマリDB]OCRのロール、開始オプション変更
6. [旧プライマリ]全インスタンスを一時的にREAD ONLYモードで起動
7. [旧プライマリ]MOUNTモードでDB再起動
8. [新プライマリ]REDO転送サービス開始
9. [新スタンバイ]MRP起動
10. スイッチオーバー手順でロールを入れ替える

【想定】
旧プライマリDBをバックアップからリストア／不完全リカバリ後、スイッチオーバーでロールを入れ替えられること
・現プライマリDB(orcldr)がスタンバイロールになること
・現スタンバイDB(orcl)がプライマリロールになること

【検証結果】
旧プライマリDBをバックアップからリストア／不完全リカバリ後、スイッチオーバーでロールを入れ替えらた
・現プライマリDB(orcldr)がスタンバイロールになった
・現スタンバイDB(orcl)がプライマリロールになった

【作業ログ】

1. [新プライマリ]旧スタンバイDBが新プライマリDBに変換された時点のSCN取得
SQL> select to_char(standby_became_primary_scn) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
------------------------------------------------------------------------------------------------------------------------
5674786

2. [旧プライマリ]リストア／不完全リカバリ
フェイルオーバー検証前に取得したバックアップを使用して、データベースをリストアします
リストア完了後、不完全リカバリで「1.」で取得したSCNまでリカバリします

本検証ではインスタンス異常停止でインスタンス停止している状態なのでMOUNTモードで起動します
障害状況によっては制御ファイルをリストアしないといけないケースもあると思いますが、そのときはNOMOUNTモードで起動して制御ファイルをリストアします

[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行されていません。
インスタンスorcl2はノードnode2で実行されていません。
[oracle@node1 ~]$ srvctl start database -db orcl -startoption mount
[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行中です。
インスタンスorcl2はノードnode2で実行中です。

RMAN> run
2> {
3>   set until scn 5674787;
4>   restore database;
5>   recover database;
6>  }

実行コマンド: SET until clause

restoreが開始されました(開始時間: 22-11-03)
(略)
recoverが完了しました(完了時間: 22-11-03)
★set until scn のSCNは「STANDBY_BECAME_PRIMARY_SCN + 1」なのでご注意ください(set until scnは指定したSCNは含まれません)

3. [旧プライマリ]インスタンス1のみMOUNTモードで起動
全インスタンスが起動している場合、後続の「4.」の「alter database convert to physical standby;」が失敗するため、インスタンス1のみMOUNTモードで起動します

[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行中です。
インスタンスorcl2はノードnode2で実行中です。
[oracle@node1 ~]$ srvctl stop instance -db orcl -instance orcl2
[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行中です。
インスタンスorcl2はノードnode2で実行されていません。

4. [旧プライマリ]旧プライマリDBのロールをフィジカル・スタンバイに変換
SQL> select db_unique_name, database_role, open_mode from v$database;

DB_UNIQUE_NAME       DATABASE_ROLE                                    OPEN_MODE
-------------------- ------------------------------------------------ ------------------------------------------------------------
orcl                 PRIMARY                                          MOUNTED

SQL> alter database convert to physical standby;

データベースが変更されました。

SQL> select db_unique_name, database_role, open_mode from v$database;

DB_UNIQUE_NAME       DATABASE_ROLE                                    OPEN_MODE
-------------------- ------------------------------------------------ ------------------------------------------------------------
orcl                 PHYSICAL STANDBY                                 MOUNTED

【参考】
1つのインスタンス以外が起動しているとエラー
SQL> alter database convert to physical standby;
alter database convert to physical standby;
*
行1でエラーが発生しました。:
ORA-38777: 他のインスタンスでデータベースを起動しないでください

5. [旧プライマリDB]OCRのロール、開始オプション変更
OCRに登録されている開始オプションとロールはプライマリ時の状態のため、手動でスタンバイ用に変更します

[oracle@node1 ~]$ srvctl config database -db orcl
一意のデータベース名: orcl
データベース名: orcl
Oracleホーム: /u01/app/oracle/product/12.1.0/dbhome_1
Oracleユーザー: oracle
spfile: +DATA/ORCL/PARAMETERFILE/spfileORCL.ora
パスワード・ファイル: +DATA/ORCL/PASSWORD/orapwORCL
ドメイン: 
開始オプション: open
停止オプション: immediate
データベース・ロール: PRIMARY
管理ポリシー: AUTOMATIC
サーバー・プール: 
ディスク・グループ: DATA
マウント・ポイントのパス: 
サービス: 
タイプ: RAC
開始の同時実行性: 
停止の同時実行性: 
OSDBAグループ: dba
OSOPERグループ: oper
データベース・インスタンス: orcl1,orcl2
構成されたノード: node1,node2
データベースは管理者によって管理されています
[oracle@node1 ~]$ srvctl modify database -db orcl -role PHYSICAL_STANDBY -startoption mount
[oracle@node1 ~]$ srvctl config database -db orcl
一意のデータベース名: orcl
データベース名: orcl
Oracleホーム: /u01/app/oracle/product/12.1.0/dbhome_1
Oracleユーザー: oracle
spfile: +DATA/ORCL/PARAMETERFILE/spfileORCL.ora
パスワード・ファイル: +DATA/ORCL/PASSWORD/orapwORCL
ドメイン: 
開始オプション: mount
停止オプション: immediate
データベース・ロール: PHYSICAL_STANDBY
管理ポリシー: AUTOMATIC
サーバー・プール: 
ディスク・グループ: DATA
マウント・ポイントのパス: 
サービス: 
タイプ: RAC
開始の同時実行性: 
停止の同時実行性: 
OSDBAグループ: dba
OSOPERグループ: oper
データベース・インスタンス: orcl1,orcl2
構成されたノード: node1,node2
データベースは管理者によって管理されています

6. [旧プライマリ]全インスタンスを一時的にREAD ONLYモードで起動
OPENすることでディクショナリ・チェックを使用して制御ファイルとデータベースを同期させます(整合性のチェック)
DBアラートログにエラーがなければ特に問題ありません
マニュアルでは「通常、フェイルオーバー中に古いプライマリがデータファイルを追加または削除している途中でなければ、ユーザー・アクションは必要ありません」とありますが、検証という意味で実施しました
※スタンバイDBをREAD ONLYモードでデータ同期する場合、Oracle Active Data Guardライセンスが必要になるのでご注意ください

[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行中です。
インスタンスorcl2はノードnode2で実行されていません。
[oracle@node1 ~]$ srvctl stop database -db orcl
[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行されていません。
インスタンスorcl2はノードnode2で実行されていません。
[oracle@node1 ~]$ srvctl start database -db orcl -startoption "read only"
[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行中です。
インスタンスorcl2はノードnode2で実行中です。

7. [旧プライマリ]MOUNTモードでDB再起動
[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行中です。
インスタンスorcl2はノードnode2で実行中です。
[oracle@node1 ~]$ srvctl stop database -db orcl
[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行されていません。
インスタンスorcl2はノードnode2で実行されていません。
[oracle@node1 ~]$ srvctl start database -db orcl -startoption mount
[oracle@node1 ~]$ srvctl status database -db orcl
インスタンスorcl1はノードnode1で実行中です。
インスタンスorcl2はノードnode2で実行中です。

8. [新プライマリ]REDO転送サービス開始
SQL> select inst_id, name, value from gv$parameter where name = 'log_archive_dest_3';

INST_ID NAME                           VALUE
------- ------------------------------ --------------------------------------------------
      1 log_archive_dest_3             service=ORCL ASYNC NOAFFIRM delay=0 OPTIONAL compr
                                       ession=DISABLE max_failure=0 max_connections=1 reo
                                       pen=300 db_unique_name=ORCL net_timeout=30 valid_f
                                       or=(ONLINE_LOGFILE,PRIMARY_ROLE)

      2 log_archive_dest_3             service=ORCL ASYNC NOAFFIRM delay=0 OPTIONAL compr
                                       ession=DISABLE max_failure=0 max_connections=1 reo
                                       pen=300 db_unique_name=ORCL net_timeout=30 valid_f
                                       or=(ONLINE_LOGFILE,PRIMARY_ROLE)

SQL> select inst_id, name, value from gv$parameter where name = 'log_archive_dest_state_3';

INST_ID NAME                           VALUE
------- ------------------------------ --------------------------------------------------
      1 log_archive_dest_state_3       ENABLE
      2 log_archive_dest_state_3       ENABLE

★必要に応じて下記コマンドで有効化します
alter system set log_archive_dest_state_n = enable;

9. [新スタンバイ]MRP起動
SQL> select client_process,process,thread#,sequence#,status from v$managed_standby where process = 'MRP0';

レコードが選択されませんでした。

SQL> alter database recover managed standby database disconnect;

データベースが変更されました。

SQL> select client_process,process,thread#,sequence#,status from v$managed_standby where process = 'MRP0';

CLIENT_PROCESS       PROCESS                 THREAD#  SEQUENCE# STATUS
-------------------- -------------------- ---------- ---------- --------------------
N/A                  MRP0                          1          3 APPLYING_LOG

10. スイッチオーバー手順でロールを入れ替える
スイッチオーバーでロールを入れ替えて障害発生前の状態に戻します
スイッチオーバー手順につきましてはData Guardのスイッチオーバー／スイッチバック検証 - 忘れかけのIT備忘録をご参照ください

■参考資料
ロールの推移
Oracle Data Guardの使用例