GAGA LIFE.

インフラエンジニアブログ

スポンサーリンク

Oracleのヒント句の有効性を確認する Hint Report(DBMS_XPLAN)

概要

Oracle 18cまではヒント句の使用有無や記述の正誤を簡単に判断する方法はありませんでした。
これまでは、イベントの10053(CBOトレース)を取得し、出力されたトレース内の「Dumping Hints」というセクションで確認していました。
ただし、それも実際にヒントが有効になったかどうかの判断としては使えないケースがありました。
※従来バージョンでは、ヒントが有効であるため実行計画が決定されているのか、ヒントの指定は誤っているがCBOによりその実行計画になったのかを簡単に判別できなかった。

リリース19.3以降のDBMS_XPLANのDISPLAYやDISPLAY_CURSORでは、ヒントが有効にならなかった場合に、
「Hint Report」というセクションが表示され、この出力内容からヒントの利用有無が確認できるようになりました。

ヒント使用状況レポートは19cの新機能であり、実行計画の出力からオプティマイザーによって使用され、オプティマイザーによって使用されないすべてのヒントを判別するのに役立ちます。

ヒント句が無視される理由

  • 構文エラー:記述ミスや無効な引数を指定した場合
  • 未解決ヒント:構文エラー以外の理由。索引指定誤りなど
  • 競合するヒント:競合するヒントの同時指定。FULLヒントとINDEXヒントを同時に指定するなど
  • 問合せ変換の影響を受けるヒント:CBOによって内部で問合せ変換が発生した場合、ヒントが無効になる時がある

レポート出力可能なファンクション

  • DISPLAY(PLAN TABLE)
  • DISPLAY_CURSOR(カーソル・キャッシュ)
  • DISPLAY_WORKLOAD_REPOSITORY(AWR)
  • DISPLAY_SQL_PLAN_BASELINE(SQL計画ベースライン)
  • DISPLAY_SQLSET(SQLチューニング・セット)

ヒントレポートの出力制御(FORMATパラメータ)

Value Description
HINT_REPORT ヒントレポートを出力
HINT_REPORT_USED 使われたヒントレポートのみを出力
HINT_REPORT_UNUSED 使われなかったヒントのみ出力
TYPICAL(デフォルト) 使われなかったヒントのみ出力
ALL 使用/未使用の両方を出力

パラメータ指定例

SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'ALL'));
SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'TYPICAL'));
SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'BASIC +HINT_REPORT'));

レポート出力内容

Hint Reportでは、以下の出力がなされます。
- SQL文に対するヒント数(合計ヒント数と未使用ヒント数)を出力
- その後に、それぞれのヒントに対応する実行計画の行番号/問合せブロック名/オブジェクト別名を出力
- 同一オブジェクトでは、未使用のヒントを最初に表示し、その後に使用されたヒントを表示
- 未使用のヒント・テキストが出力されたときに、理由として以下のいずれかの注釈が表示される
(非使用ヒント数に、それぞれの注釈の数も出力されます)
- 正しくないヒントや最終計画に存在しない問合せブロックに対するヒントは、行番号が0

Value Description
E 構文エラー
N 未解決ヒント
U 未使用

実行例

SQL> select
    /*+ 
    INDEX(e emp_idx) 
    PUSH_PRED(dept) 
    FULL(@sel$2 l) 
    PARALLEL(2) 
    OPT_PARAM('_simple_view_merging','false')
    */
    e.first_name,
    e.last_name,
    dept_locs_v.street_address,
    dept_locs_v.postal_code
from
    employees e,
    (
        select
            /*+ FULL(l) FUL(d) */
            d.department_id,
            d.department_name,
            l.street_address,
            l.postal_code
        from
            depa  2  rtments d,
            locations l
        where
            d.location_id = l.location_id
    ) dept_locs_v
where
    dept_locs_v.department_id = e.department_id
and e.last_name = 'XXXXX'
;
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31  
レコードが選択されませんでした。

SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'ALL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3ghnkvfjbt5kz, child number 2
-------------------------------------
select     /*+     INDEX(e emp_idx)     PUSH_PRED(dept)     FULL(@sel$2
l)     PARALLEL(2)     OPT_PARAM('_simple_view_merging','false')     */
    e.first_name,     e.last_name,     dept_locs_v.street_address,
dept_locs_v.postal_code from     employees e,     (         select
       /*+ FULL(l) FUL(d) */             d.department_id,
d.department_name,             l.street_address,
l.postal_code         from             departments d,
locations l         where             d.location_id = l.location_id
) dept_locs_v where     dept_locs_v.department_id = e.department_id and
e.last_name = 'XXXXX'

Plan hash value: 1795193073

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |       |       |     6 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000    |     1 |    61 |     6   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |             |     1 |    61 |     6   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     JOIN FILTER CREATE  | :BF0000     |     1 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  5 |      TABLE ACCESS FULL  | EMPLOYEES   |     1 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |     VIEW                |             |    27 |  1161 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  7 |      HASH JOIN          |             |    27 |  1026 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       PX BLOCK ITERATOR |             |    23 |   713 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  9 |        TABLE ACCESS FULL| LOCATIONS   |    23 |   713 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |       JOIN FILTER USE   | :BF0000     |    27 |   189 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 11 |        TABLE ACCESS FULL| DEPARTMENTS |    27 |   189 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   5 - SEL$1 / E@SEL$1
   6 - SEL$2 / DEPT_LOCS_V@SEL$1
   7 - SEL$2
   9 - SEL$2 / L@SEL$2
  11 - SEL$2 / D@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   5 - filter("E"."LAST_NAME"='XXXXX')
   7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
   9 - access(:Z>=:Z AND :Z<=:Z)
  11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"D"."DEPARTMENT_ID"))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   2 - (#keys=0) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   3 - (#keys=1; rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   4 - (rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   5 - (rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   6 - (rowset=256) "DEPT_LOCS_V"."DEPARTMENT_ID"[NUMBER,22], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12]
   7 - (#keys=1; rowset=256) "L"."POSTAL_CODE"[VARCHAR2,12], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "D"."DEPARTMENT_ID"[NUMBER,22]
   8 - (rowset=256) "L"."LOCATION_ID"[NUMBER,22], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "L"."POSTAL_CODE"[VARCHAR2,12]
   9 - (rowset=256) "L"."LOCATION_ID"[NUMBER,22], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "L"."POSTAL_CODE"[VARCHAR2,12]
  10 - (rowset=256) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]
  11 - (rowset=256) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 7 (U - Unused (2), N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------------------

   0 -  STATEMENT
           -  OPT_PARAM('_simple_view_merging','false')
           -  PARALLEL(2)

   1 -  SEL$1
         N -  PUSH_PRED(dept)

   5 -  SEL$1 / E@SEL$1
         U -  INDEX(e emp_idx) / index specified in the hint doesn't exist

   7 -  SEL$2
         E -  FUL

   9 -  SEL$2 / L@SEL$2
         U -  FULL(l) / hint overridden by another in parent query block
           -  FULL(@sel$2 l)

Note
-----
   - Degree of Parallelism is 2 because of hint

上記のHint Reportでは、オプティマイザーは以下のように判断しています。
- PARALLEL(2)、OPT_PARAM('_ simple_view_merging'、'false')およびFULL(@sel$2 l)ヒントのみを考慮
- U:指定されたインデックスが存在しないため、ヒントINDEX(e emp_idx)は使用しない
- U:同様のヒントが親クエリブロックで指定され、サブクエリブロックのクエリが上書きされるため、ヒントFULL(l)は使用しない
- E:FULヒントに構文エラーがあるため、考慮されない(※FULではなくFULL)
- N:ヒントPUSH_PRED(dept)は未解決(※「dept」という名前のビューはない)

参考資料

津島博士のパフォーマンス講座 第72回 SQLパッチとヒント使用状況レポートについて
https://blogs.oracle.com/otnjp/tsushima-hakushi-72

INDEX SKIP SCAN

INDEX SKIP SCAN

概要

INDEX SKIP SCANの概要について記載します。

INDEX SKIP SCAN

INDEX SKIP SCAN(9i~)により第1キーが抽出条件に無くてもインデックスを使用する機能がありますが、この実行計画が選択される条件として 第1キーのカーディナリティが非常に低い値である必要があります。
そうでない場合は、FULL SCANのほうが低コストと判断されます。

複合索引の付与列の順序の効率が良くないケース

  • 下記のような表に対して、①DEPTNO+②EMPNOの順で索引を作成している
    f:id:undercovergeek:20210227105454p:plain

複合索引の2列目だけの条件でクエリ実行

f:id:undercovergeek:20210227105521p:plain

複合索引をカーディナリティの高い列から付与することで改善

  • 複合索引を(①EMPNO + ②DEPTNOで再作成)
CREATE INDEX idx_comp
ON  emp2(
        empno,
        deptno
    )
;

f:id:undercovergeek:20210227105721p:plain

フラッシュバックOFF表領域の再作成

概要

フラッシュバックログが見積もりより大きい場合、フラッシュバックに時間を要することがあります。
事前にDatapumpでデータをExportしている場合、表領域再作成⇒IMPDPの方が早い場合があるため、再作成の手順を整理しておきます。

現在の状況確認(DBはフラッシュバックON)

SQL> conn / as sysdba
接続されました。
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
YES

SQL> alter session set container = pdb1;

セッションが変更されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3

6行が選択されました。

検証用の表領域作成とフラッシュバックOFF

SQL> create tablespace fb_test
datafile '+DATA'
size 20M
autoextend on
next 5M maxsize unlimited;
  2    3    4    5  

表領域が作成されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3
         6 FB_TEST              YES       NO        YES                          3 <---★

7行が選択されました。

SQL> alter tablespace fb_test flashback off;

表領域が変更されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3
         6 FB_TEST              YES       NO        NO                           3 <---★

7行が選択されました。

表領域とデータファイル削除

SQL> drop tablespace fb_test including contents and datafiles cascade constraints;

表領域が削除されました。

                          ▽アラートログ
                          2021-02-03T17:08:37.868433+09:00
                          PDB1(3):drop tablespace fb_test including contents and datafiles cascade constraints
                          2021-02-03T17:08:40.986229+09:00
                          PDB1(3):Deleted Oracle managed file +DATA/ORCL/B97C79C922B359DFE0536638A8C072ED/DATAFILE/fb_test.289.1063559079
                          PDB1(3):Completed: drop tablespace fb_test including contents and datafiles cascade constraints

SQL> create tablespace fb_test
datafile '+DATA'
size 20M
autoextend on
next 5M maxsize unlimited;
  2    3    4    5  
表領域が作成されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3
         6 FB_TEST              YES       NO        YES                          3 <---★

7行が選択されました。

結果

特に問題なく、同名表領域の作成とフラッシュバックONの設定が可能でした。
フラッシュバックに時間を要し、且つ事前に特定断面のダンプファイルがある場合は、フラッシュバックするのではなく、
表領域再作成後にIMPORTの方が早い可能性があります。

表領域レベルの圧縮

概要

表領域レベルの圧縮の動作を確認していきます。

マニュアル

Oracle® Database 管理者ガイド 19c

  13.2.4 デフォルト圧縮属性を持つ表領域

  表領域の作成時に、表領域に作成されるすべての表と索引またはそれらのパーティションをデフォルトで圧縮するように指定できます。
    - デフォルト圧縮属性を持つ表領域について
      表領域を作成する際に、表領域に作成されるすべての表および索引のデータのデフォルト圧縮を指定できます。
      デフォルト圧縮レベルは、表領域を構成するパーティションにも適用されます。このデータを圧縮するとディスク使用量を削減できます。
    - デフォルト圧縮属性を使用した表領域の作成
      表領域を作成するときに表圧縮のタイプを指定するには、DEFAULTキーワードを使用し、その後に圧縮タイプを含む表圧縮句を指定します。
      DEFAULTキーワードを使用して、その後に索引圧縮句と索引圧縮タイプを指定すると、索引圧縮のタイプを指定することもできます。

実行例

  • 圧縮表領域作成(COMP_TEST)
SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> create tablespace comp_test datafile '+DATA' DEFAULT ROW STORE COMPRESS ADVANCED;

表領域が作成されました。

SQL> select f.file_id,f.tablespace_name,f.file_name,f.bytes,f.blocks,f.autoextensible,t.compress_for
from dba_tablespaces t,dba_data_files f
where t.tablespace_name = f.tablespace_name 
order by f.file_id;
  2    3    4  
   FILE_ID TABLESPACE_NAME      FILE_NAME                                                                                       BYTES     BLOCKS AUTOEXTEN COMPRESS_FOR
---------- -------------------- ------------------------------------------------------------------------------------------ ---------- ---------- --------- --------------------
         9 SYSTEM               +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/system.284.1054420929                  241172480      29440 YES
        10 SYSAUX               +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/sysaux.282.1054420693                 1672478720     204160 YES
        11 UNDOTBS1             +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/undotbs1.283.1054420713                209715200      25600 YES
        12 USERS                +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/users.286.1054421063                    38010880       4640 YES
        13 UNDO_2               +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/undo_2.304.1054459615                  209715200      25600 YES
        14 EXAMPLES             +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/examples.329.1056635519               1073741824     131072 YES
        16 COMP_TEST            +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/comp_test.413.1056984971               104857600      12800 YES       ADVANCED
  • 比較用非圧縮表作成(EMP2)
SQL> create table emp2 as select * from emp where 0=1;

表が作成されました。

SQL> alter table emp2 modify (empno number(10));

表が変更されました。

SQL> begin
for i in 1..1000000 loop
insert into emp2 (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (i,DBMS_RANDOM.STRING('p',8),null,null,sysdate,null,null,null);
commit;
end loop;
end;
/
  2    3    4    5    6    7    8  
PL/SQLプロシージャが正常に完了しました。

SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
;
  2    3    4    5    6  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
DEPT                 USERS                DISABLED
EMP                  USERS                DISABLED
BONUS                USERS                DISABLED
SALGRADE             USERS                DISABLED
DUMMY                USERS                DISABLED
EMP2                 USERS                DISABLED

SQL> SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
  2    3    4    5    6    7  
SEGMENT_NAME         SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
-------------------- -------------------- -------------------- ----------
EMP                  TABLE                USERS                        64
EMP2                 TABLE                USERS                     33792
  • 圧縮表領域に表作成(EMP_COMP)
SQL> create table emp_comp tablespace comp_test as select * from emp where 0=1;

表が作成されました。

SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP                  USERS                DISABLED

SQL> alter table emp_comp modify (empno number(10));

表が変更されました。

SQL> begin
for i in 1..1000000 loop
insert into emp_comp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (i,DBMS_RANDOM.STRING('p',8),null,null,sysdate,null,null,null);
commit;
end loop;
end;
/
  2    3    4    5    6    7    8  

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

SQL> SQL> col SEGMENT_NAME for a40
col PARTITION_NAME for a50
SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
SQL> SQL>   2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552

SQL> select SEGMENT_NAME, MB, MB/decode(NOCOMP_SIZE, 0, MB, NOCOMP_SIZE)*100 as RATIO
  from (select SEGMENT_NAME, MB, (lag(MB, 1, 0) over (order by MB DESC)) NOCOMP_SIZE
          from (select SEGMENT_NAME, sum(BYTES)/1024/1024 MB
                  from USER_SEGMENTS
                 where SEGMENT_NAME like 'EMP%'
                 group by SEGMENT_NAME
               )
       );
  2    3    4    5    6    7    8  
SEGMENT_NAME                                     MB      RATIO
---------------------------------------- ---------- ----------
EMP2                                             33        100
EMP_COMP                                         23 69.6969697
EMP                                           .0625  .27173913
  • ダイレクトパスインサート(EMP_DIRECT)
SQL> create table emp_direct tablespace comp_test as select * from emp where 0=1;

表が作成されました。

SQL> alter table emp_direct modify (empno number(10));

表が変更されました。

SQL> begin
for i in 1..1000000 loop
insert /*+ APPEND */ into emp_direct (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (i,DBMS_RANDOM.STRING('p',8),null,null,sysdate,null,null,null);
commit;
end loop;
end;
/
  2    3    4    5    6    7    8  

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

SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP_DIRECT           COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP                  USERS                DISABLED

SQL> SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
  2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552
EMP_DIRECT                               TABLE                COMP_TEST                 23552
  • IMPDP(⇒EMP_IMPDP)
SQL> create table emp_impdp tablespace comp_test as select * from emp where 0=1;

表が作成されました。

SQL> alter table emp_impdp modify (empno number(10));

表が変更されました。

SQL> alter session set container = pdb1;

セッションが変更されました。

SQL> create or replace directory SCOTT_DIR as '/u01/app/oracle/admin/orcl/dpdump';

ディレクトリが作成されました。

SQL> grant read, write on directory SCOTT_DIR to SCOTT;

権限付与が成功しました。

SQL> grant exp_full_database to SCOTT;

権限付与が成功しました。

SQL> grant imp_full_database to SCOTT;

権限付与が成功しました。
$ expdp system/system@pdb1 directory=scott_dir tables=scott.emp2 logfile=emp2.log

Export: Release 19.0.0.0.0 - Production on 金 1120 17:14:48 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
"SYSTEM"."SYS_EXPORT_TABLE_01"を起動しています: system/********@pdb1 directory=scott_dir tables=scott.emp2 logfile=emp2.log 
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
. . "SCOTT"."EMP2"                              26.94 MB 1009999行がエクスポートされました
マスター表"SYSTEM"."SYS_EXPORT_TABLE_01"は正常にロード/アンロードされました
******************************************************************************
SYSTEM.SYS_EXPORT_TABLE_01に設定されたダンプ・ファイルは次のとおりです:
  /u01/app/oracle/admin/orcl/dpdump/expdat.dmp
ジョブ"SYSTEM"."SYS_EXPORT_TABLE_01"が金 1120 17:15:43 2020 elapsed 0 00:00:45で正常に完了しました

$ impdp scott/tiger@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test

Import: Release 19.0.0.0.0 - Production on 金 1120 17:35:39 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SCOTT"."SYS_IMPORT_FULL_01"は正常にロード/アンロードされました
"SCOTT"."SYS_IMPORT_FULL_01"を起動しています: scott/********@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test 
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."EMP_IMPDP"                         26.94 MB 1009999行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
ジョブ"SCOTT"."SYS_IMPORT_FULL_01"が金 1120 17:36:22 2020 elapsed 0 00:00:40で正常に完了しました
SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_IMPDP            COMP_TEST            DISABLED
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP_DIRECT           COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP                  USERS                DISABLED

5行が選択されました。

SQL_ID: 7w5qn4fjgpbq8
経過: 00:00:00.01
SQL> col SEGMENT_NAME for a40
col PARTITION_NAME for a50
SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
SQL> SQL>   2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552
EMP_DIRECT                               TABLE                COMP_TEST                 23552
EMP_IMPDP                                TABLE                COMP_TEST                 33792

SQL> drop table emp_impdp purge;

表が削除されました。
  • IMPDP(EMP_IMPDP compression_clause:\"row store compress advanced\"付与)
$ impdp scott/tiger@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test transform=table_compression_clause:\"row store compress advanced\"

Import: Release 19.0.0.0.0 - Production on 金 1120 17:51:23 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SCOTT"."SYS_IMPORT_FULL_01"は正常にロード/アンロードされました
"SCOTT"."SYS_IMPORT_FULL_01"を起動しています: scott/********@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test transform=table_compression_clause:"row store compress advanced" 
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."EMP_IMPDP"                         26.94 MB 1009999行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
ジョブ"SCOTT"."SYS_IMPORT_FULL_01"が金 1120 17:52:04 2020 elapsed 0 00:00:36で正常に完了しました
SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP_DIRECT           COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP_IMPDP            COMP_TEST            ENABLED                  ADVANCED
EMP                  USERS                DISABLED

5行が選択されました。

SQL_ID: 7w5qn4fjgpbq8
経過: 00:00:00.01
SQL> col SEGMENT_NAME for a40
col PARTITION_NAME for a50
SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
SQL> SQL>   2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552
EMP_DIRECT                               TABLE                COMP_TEST                 23552
EMP_IMPDP                                TABLE                COMP_TEST                 23552

表領域のフラッシュバックデータベースのON/OFF

概要

表領域のフラッシュバックデータベースのONとOFFについて検証してみました

マニュアル

Oracle® Database バックアップおよびリカバリ・ユーザーズ・ガイド 19c 7.5.1 フラッシュバック・データベースの有効化

ALTER DATABASEコマンドを使用して、フラッシュバック・データベースを有効にします

フラッシュバック・ロギングを有効にする手順

    1.「高速リカバリ領域の有効化」に説明されているとおりに、リカバリ領域を構成します。
    2. データベース・インスタンスがオープンまたはマウントされていることを確認します。
       インスタンスがマウントされている場合、フィジカル・スタンバイ・データベースでないかぎり、データベースが正しく停止されている必要があります。
       他のOracle Real Application Clusters (Oracle RAC)インスタンスはどのモードでもかまいません。
    3. オプションで、DB_FLASHBACK_RETENTION_TARGETに、フラッシュバックの期間の長さを分単位で設定します。

       ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH; # 3 days

    デフォルトでは、DB_FLASHBACK_RETENTION_TARGETは1日(1440分)に設定されます。

  注意:
  この設定は、データベースの起動および停止を通じて永続的にする必要があります。 

    4. データベース全体でフラッシュバック・データベース機能を有効にします。

       ALTER DATABASE FLASHBACK ON;

    5. 必要に応じて、特定の表領域のフラッシュバック・ロギングを無効にします。

    デフォルトでは、すべての永続表領域に対してフラッシュバック・ログが生成されます。
    特定の表領域のフラッシュバック・ロギングを無効にして、オーバーヘッドを軽減できます。次に例を示します。

       ALTER TABLESPACE tbs_3 FLASHBACK OFF;

    表領域のフラッシュバック・ロギングは、次のコマンドを使用して、後で再度有効にできます。

       ALTER TABLESPACE tbs_3 FLASHBACK ON;

    表領域に対してフラッシュバック・データベースを無効にする場合は、FLASHBACK DATABASEを実行する前に、データファイルをオフラインにする必要があります。 

動作確認

CDB

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
YES

SQL> ALTER TABLESPACE USERS FLASHBACK OFF;

表領域が変更されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          1
         0 SYSTEM               YES       NO        YES                          2
         1 SYSAUX               YES       NO        YES                          1
         1 SYSAUX               YES       NO        YES                          2
         2 UNDOTBS1             YES       NO        YES                          1
         2 UNDOTBS1             YES       NO        YES                          2
         3 TEMP                 NO        NO        YES                          1
         3 TEMP                 NO        NO        YES                          2
         4 UNDOTBS2             YES       NO        YES                          1
         5 USERS                YES       NO        NO                           1 <---★
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3

16行が選択されました。

SQL> select instance_name,status from v$instance;

INSTANCE_NAME                                    STATUS
------------------------------------------------ ------------------------------------
orcl1                                            OPEN

SQL> ALTER TABLESPACE USERS FLASHBACK ON;
ALTER TABLESPACE USERS FLASHBACK ON
*
行1でエラーが発生しました。:
ORA-01126: データベースはこのインスタンスでマウントし、どのインスタンスでもオープンしないでください

[grid@rac1 ~]$ srvctl stop database -db orcl
[grid@rac1 ~]$ srvctl start database -db orcl -o mount

SQL> select instance_name,status from v$instance;

INSTANCE_NAME                                    STATUS
------------------------------------------------ ------------------------------------
orcl1                                            MOUNTED

SQL> ALTER TABLESPACE USERS FLASHBACK ON;

表領域が変更されました。

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          1
         0 SYSTEM               YES       NO        YES                          2
         1 SYSAUX               YES       NO        YES                          1
         1 SYSAUX               YES       NO        YES                          2
         2 UNDOTBS1             YES       NO        YES                          1
         2 UNDOTBS1             YES       NO        YES                          2
         3 TEMP                 NO        NO        YES                          1
         3 TEMP                 NO        NO        YES                          2
         4 UNDOTBS2             YES       NO        YES                          1
         5 USERS                YES       NO        YES                          1 <---★
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3

PDB

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB                            READ WRITE NO
SQL> show con_name

CON_NAME
------------------------------
PDB

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3

SQL> ALTER TABLESPACE USERS FLASHBACK OFF;

表領域が変更されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        NO                           3 <---★

6行が選択されました。

SQL> ALTER TABLESPACE USERS FLASHBACK ON;
ALTER TABLESPACE USERS FLASHBACK ON
*
行1でエラーが発生しました。:
ORA-01126: データベースはこのインスタンスでマウントし、どのインスタンスでもオープンしないでください

SQL> shu immediate 
プラガブル・データベースがクローズされました。
SQL> conn /as sysdba
接続されました。
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED

SQL> alter session set container = pdb;

セッションが変更されました。

SQL> ALTER TABLESPACE USERS FLASHBACK ON;
ALTER TABLESPACE USERS FLASHBACK ON
*
行1でエラーが発生しました。:
ORA-01126: データベースはこのインスタンスでマウントし、どのインスタンスでもオープンしないでください

[grid@rac1 ~]$ srvctl stop database -db orcl
[grid@rac1 ~]$ srvctl start database -db orcl -o mount

SQL> conn /as sysdba
接続されました。
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB                            MOUNTED
SQL> alter session set container = pdb;

セッションが変更されました。

SQL> select * from v$tablespace;

       TS# NAME                 INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I     CON_ID
---------- -------------------- --------- --------- --------- --------- ----------
         0 SYSTEM               YES       NO        YES                          3
         1 SYSAUX               YES       NO        YES                          3
         2 UNDOTBS1             YES       NO        YES                          3
         3 TEMP                 NO        NO        YES                          3
         4 UNDO_2               YES       NO        YES                          3
         5 USERS                YES       NO        YES                          3 <---★

まとめ

▽表領域のflashback on/off実施の条件
フラッシュバックデータベースがONとなっている必要がある。

▽CDB/PDBの停止等が必要か
表領域のFLASHBACK ONはインスタンスレベルでMOUNT状態にする必要がある。 ※OFFはOPEN状態で可

PDBレベルでのフラッシュバックデータベースを実行

概要

12.2から導入されたPDB レベルでフラッシュバックを実行する方法について説明します。

前提

・本手順はローカルUNDOであることを前提にしています。 ローカル/共有の確認および有効化は下記で可能です。

UNDOモードの確認(TRUE:ローカルUNDO FALSE:共有UNDO)

SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='LOCAL_UNDO_ENABLED';

PROPERTY_VALUE
--------------------
TRUE

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

フラッシュバックON

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

・フラッシュバック・データベース実行に必要な権限は以下のいずれかです。
- SYSDBA権限
- SYSBACKUP権限

使用コマンド

  1. PDB レベルでのリストアポイント作成
    --PDBから
    特定の PDB に接続し、CREATE RESTORE POINT コマンドを実行:
SQL> alter session set container=PDB1;  
SQL> create restore point TEST1;  
SQL> create restore point TEST2 guarantee flashback database;  

--CDBから
CDB に接続し、CREATE RESTORE POINT コマンドの FOR PLUGGABLE DATABASE 句を使用:

SQL> alter session set container=CDB$ROOT;  
SQL> create restore point TEST1 for pluggable database PDB1;  
SQL> create restore point TEST2 for pluggable database PDB1 guarantee flashback database;  

※本手順ではCDBから実行する手順となります。

--リストアポイントへのフラッシュバック手順(※PDB がローカルUNDO を使用している場合)

SQL> alter pluggable database PDB1 close;
SQL> flashback pluggable database PDB1 to restore point TEST1;
SQL> alter pluggable database PDB1 open resetlogs;

実行例

--リストアポイントの作成

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> create restore point TEST1 for pluggable database PDB1;

Restore point created.

SQL> create restore point TEST2 for pluggable database PDB1 guarantee flashback database;

Restore point created.

--リストアポイントの情報の表示(※SQL*Plus または RMAN を使用)

SQL> SELECT NAME,TIME,SCN,PDB_RESTORE_POINT,GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;

NAME                 TIME                                    SCN PDB GUA
-------------------- -------------------------------- ---------- --- ---
TEST2                19-NOV-20 03.13.01.000000000 PM     9457194 YES YES
TEST1                19-NOV-20 03.12.53.000000000 PM     9457178 YES NO

RMAN> list restore point all;

リカバリ・カタログのかわりにターゲット・データベース制御ファイルを使用している

SCN              RSP Time Type       Time     Name
---------------- -------- ---------- -------- ----
9457178                              20-11-19 TEST1
9457194                   GUARANTEED 20-11-19 TEST2

--意図したリストアポイントに戻るか否かの確認

SQL> show user
USER is "SCOTT"
SQL> select * from tab;

TNAME                TABTYPE                                  CLUSTERID
-------------------- --------------------------------------- ----------
DEPT                 TABLE
EMP                  TABLE
BONUS                TABLE
SALGRADE             TABLE
DUMMY                TABLE

SQL> create table flbtbl(col1 number, col2 varchar(10));

Table created.

SQL> insert into flbtbl values(10,'a');

1 row created.

SQL> insert into flbtbl values(10,'a');

insert into flbtbl values(20,'b');

SQL> insert into flbtbl values(30,'c');

1 row created.

SQL> insert into flbtbl values(30,'c');

1 row created.

SQL> insert into flbtbl values(40,'d');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from flbtbl;

      COL1 COL2
---------- ------------------------------
        10 a
        10 a
        30 c
        30 c
        40 d

--フラッシュバックを実行 a. PDB がローカルUNDO を使用している場合:

SQL> alter pluggable database PDB1 close;
SQL> flashback pluggable database PDB1 to restore point TEST1;
SQL> alter pluggable database PDB1 open resetlogs;
SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL> flashback pluggable database PDB1 to restore point TEST1;
flashback pluggable database PDB1 to restore point TEST1
*
ERROR at line 1:
ORA-65025: Pluggable database  is not closed on all instances.

--Node2

  SQL> alter pluggable database PDB1 close;
 
  Pluggable database altered.

SQL> flashback pluggable database PDB1 to restore point TEST1;

Flashback complete.

SQL> alter pluggable database PDB1 open resetlogs;

Pluggable database altered.


SQL> conn scott/tiger@pdb1;
Connected.

SQL> select * from tab;

TNAME                TABTYPE                                  CLUSTERID
-------------------- --------------------------------------- ----------
DEPT                 TABLE
EMP                  TABLE
BONUS                TABLE
SALGRADE             TABLE
DUMMY                TABLE

Oracle RAC環境での oracle バイナリの権限(SGID oracle:asmadmin)

概要

ASM使用環境下でRMANでバックアップした場合などにバックアップセットのOSグループがasmadminグループとなります。
※oracleユーザーの所属するグループは、asmadminグループには属していない

# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba),54327(asmdba)
# id grid
uid=54322(grid) gid=54321(oinstall) groups=54321(oinstall),54330(racdba),54327(asmdba),54328(asmoper),54329(asmadmin)

原因

結論として、バックアップファイルのパーミッションが「oracle:asmadmin」となるのは"GI(ASM)環境"の仕様動作となります。

11.2 以降のRAC環境ででGrid InfrastructureとDatabaseを別々のOSユーザ(例:oracle/grid)でインストールしている場合、
relink処理直後のデータベースのoracleバイナリにはASMにアクセスするための権限が設定されていません。

DBCAでデータベースを作成した場合やsrvctlでデータベースを起動すると、
内部で上述のASMへのアクセス権限のあるグループで起動するようoracleバイナリの権限が設定されます(sビット)。
そのため起動にsrvctl使用していればrelinkが実行され、手動でもDBCAでもsビットが立つ動作です。

なお、create database文を直接実行してのデータベース作成直後は、追加で下記スクリプトを実行する必要がある。

  例
  ------------------------------
  # $GRID_HOME/bin/setasmgidwrap o=$ORACLE_HOME/bin/oracle
  ------------------------------
  ※上記の $GRID_HOME は Grid Infrastructure の $ORACLE_HOME
    $ORACLE_HOME はデータベースの $ORACLE_HOME を指しています

上記を実行することで次回relinkなどで権限が変更されるまでデータベースが ASM にアクセス可能な状態で起動される。

SGIDについて(Linux)

ファイルにSGIDを付与した場合、そのコマンドが所有グループの権限で実行される
Set Group ID
- 「SGID」とは「Set Group ID」の略。
- グループに対して設定される特殊パーミッション。
ポイント
- ファイルにSGIDが適応されていた場合、そのグループの権限でファイルが実行される
- ディレクトリにSGIDを指定すると、ディレクトリ内部で作成されたファイルは全てディレクトリのSGIDで指定したグループが割り振られる

動作確認

  • oracleユーザーでRMAN接続し、バックアップ
$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on 火 112 10:46:58 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
ターゲット・データベース: ORCL (DBID=1587538681)に接続されました
RMAN> backup current controlfile format '/home/oracle/work/backup/%U';
backupを21-01-12で開始しています
リカバリ・カタログのかわりにターゲット・データベース制御ファイルを使用しています
チャネル: ORA_DISK_1が割り当てられました
チャネルORA_DISK_1: SID=309 インスタンス=orcl1 デバイス・タイプ=DISK
チャネルORA_DISK_1: フル・データファイル・バックアップ・セットを開始しています
チャネルORA_DISK_1: バックアップ・セットにデータファイルを指定しています
現行の制御ファイルをバックアップ・セットに組み込んでいます
チャネルORA_DISK_1: ピース1 (21-01-12)を起動します
チャネルORA_DISK_1: ピース1 (21-01-12)が完了しました
ピース・ハンドル=/home/oracle/work/backup/03vjs2tp_1_1 タグ=TAG20210112T104720 コメント=NONE
チャネルORA_DISK_1: バックアップ・セットが完了しました。経過時間: 00:00:01
backupを21-01-12で終了しました
Control File and SPFILE Autobackupを21-01-05で開始しています
ピース・ハンドル=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/c-1587538681-20210112-00 コメント=NONE
Control File and SPFILE Autobackupを21-01-12で終了しました
RMAN> exit
Recovery Managerが完了しました。
  • バックアップファイルのパーミッション確認(oracle:asmadmin)
$ pwd
/home/oracle/work/backup
$ ls -la
合計 19328
drwxr-xr-x 2 oracle oinstall       26  112 10:47 .
drwxr-xr-x 3 oracle oinstall       20  112 10:46 ..
-rw-r----- 1 oracle asmadmin 19791872  112 10:47 03vjs2tp_1_1 <---★
  • ファイルを別ディレクトリにコピーし、再度パーミッション確認(oracle:oinstall)
$ ls -la
合計 19328
drwxr-xr-x 3 oracle oinstall       40  112 10:48 .
drwxr-xr-x 3 oracle oinstall       17  112 10:46 ..
-rw-r----- 1 oracle oinstall 19791872  112 10:48 03vjs2tp_1_1 <---★
drwxr-xr-x 2 oracle oinstall       26  112 10:47 backup
  • oracleバイナリから実行・出力されたファイルは以下のUID/GID
 $ ls -la $ORACLE_HOME/bin/oracle*
  -rwsr-s--x 1 oracle asmadmin 441253072 124 18:44 /u01/app/oracle/product/19.3.0/dbhome_1/bin/oracle

oracleバイナリをasmadminグループの権限で実行しています。
※$ORACLE_HOME/bin/oracleのoracle binaryがasmadminグループに所属するため、oracle ユーザで取得したRMANバックアップファイルの所属グループがasmadminとなる。

スポンサーリンク