オラクる。

oracle専門ブログにしてみようかな～っと

カテゴリ：oracle > 12c

12cの新機能Data Redactioのうち、列内容を部分的に変換する部分リダクションは、複数の列にリダクションを設定することが出来ます。
また、それぞれの列に異なるリダクション方式を指定できます。

BEGIN
 DBMS_REDACT.ADD_POLICY(
  policy_name    =>    'EMP_P',
  object_schema    =>    'SCOTT',
  object_name    =>    'EMP',
  column_name    =>    'HIREDATE',
  expression    =>    '1=1',
  function_type    =>    DBMS_REDACT.PARTIAL,
  function_parameters    =>    'Md1YHMS');
END;
/

まずは、scottスキマーのemp表hiredate列に対して、日付を「１」にリダクションするポリシーを作成します。

SQL> SELECT EMPNO,ENAME,HIREDATE,SAL FROM EMP;

     EMPNO ENAME      HIREDATE        SAL
---------- ---------- -------- ----------
      7369 SMITH      80-12-01        800
      7499 ALLEN      81-02-01       1600
      7521 WARD       81-02-01       1250
      7566 JONES      81-04-01       2975
      7654 MARTIN     81-09-01       1250
      7698 BLAKE      81-05-01       2850
      7782 CLARK      81-06-01       2450
      7839 KING       81-11-01       5000
      7844 TURNER     81-09-01       1500
      7900 JAMES      81-12-01        950
      7902 FORD       81-12-01       3000

     EMPNO ENAME      HIREDATE        SAL
---------- ---------- -------- ----------
      7934 MILLER     82-01-01       1300

12行が選択されました。

その時点での表のSELECT結果です。
HIREDATE列がリダクションされてます。

BEGIN
 DBMS_REDACT.ALTER_POLICY(
  policy_name    =>    'EMP_P',
  object_schema    =>    'SCOTT',
  object_name    =>    'EMP',
  action    =>    DBMS_REDACT.ADD_COLUMN,
  column_name    =>    'SAL',
  function_type    =>    DBMS_REDACT.PARTIAL,
  function_parameters    =>    '7,1,3');
END;
/

ALTER_POLICYでポリシーを追加します。
同じEMP表SAL列に対して、先頭の１桁目から３桁目を７にリダクションするポリシーです。

SQL> SELECT EMPNO,ENAME,HIREDATE,SAL FROM EMP;

     EMPNO ENAME      HIREDATE        SAL
---------- ---------- -------- ----------
      7369 SMITH      80-12-01        777
      7499 ALLEN      81-02-01       7770
      7521 WARD       81-02-01       7770
      7566 JONES      81-04-01       7775
      7654 MARTIN     81-09-01       7770
      7698 BLAKE      81-05-01       7770
      7782 CLARK      81-06-01       7770
      7839 KING       81-11-01       7770
      7844 TURNER     81-09-01       7770
      7900 JAMES      81-12-01        777
      7902 FORD       81-12-01       7770

     EMPNO ENAME      HIREDATE        SAL
---------- ---------- -------- ----------
      7934 MILLER     82-01-01       7770

12行が選択されました。

HIREDATEとSALの両方がリダクションされました。

前回を作成したルールを実行してみましょう。
評価の結果は必ず真になるので、表領域移動、圧縮は必ず実行されます。

var v_taskid NUMBER
exec DBMS_ILM.EXECUTE_ILM(task_id=>:v_taskid, -
owner=>'SCOTT',object_name=>'EMP2')

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

スキーマscott、表EMP2に対するルールが実行されました。
実行したルールのタスクIDはv_taskidに格納されます。

SQL> SELECT task_id, policy_name, selected_for_execution
  2  FROM dba_ilmevaluationdetails WHERE task_id=:v_taskid;

   TASK_ID POLICY_NAME                                                                                                                     SELECTED_FOR_EXECUTION
---------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------
        13 P4                                                                                                                              SELECTED FOR EXECUTION
        13 P3                                                                                                                              SELECTED FOR EXECUTION

実行結果を確認します。
先ほどのv_taskidを条件に利用します。
「SELECTED FOR EXECUTION」で実行されたことを確認できました。

SELECT job_name,job_state,start_time,completion_time
FROM dba_ilmresults WHERE task_id=:v_taskid;

JOB_NAME                                                                                                                        JOB_STATE
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------
START_TIME                                                                  COMPLETION_TIME
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
ILMJOB16                                                                                                                        COMPLETED SUCCESSFULLY
16-08-19 01:00:44.183299                                                    16-08-19 01:01:06.404331

dba_ilmresultsビューでルールの実行結果を確認できます。
job_state列がCOMPLETES SUCCESSFULLYとなっていることを確認します。

SQL> select tablespace_name from dba_segments where segment_name='EMP2';

TABLESPACE_NAME
------------------------------
TBS1

ルールが実行されてemp2表のテーブルスペースが移動したことを確認できました。

ADOの評価の基準として、そのオブジェクトのアクセス頻度があります。
アクセス頻度を一定の条件で評価し、その条件が満たされれば、圧縮や表領域移動などのアクションを実行します。
また、カスタムの評価基準を作成することが出来ます。

CREATE OR REPLACE FUNCTION scott.ilm_rule1
(p_object_id IN NUMBER)
RETURN BOOLEAN
AS
BEGIN
    RETURN TRUE;
END;
/

ファンクションが作成されました。

カスタムの評価基準はPL/SQLで作成します。
今回は、評価すると必ず「真」で返す評価基準を作成しました。

ALTER TABLE scott.emp2 ILM ADD POLICY
ROW STORE COMPRESS ADVANCED
SEGMENT ON ilm_rule1;

表が変更されました。

ALTER TABLE scott.emp2 ILM ADD POLICY
TIER TO tbs1 ON ilm_rule1;

表が変更されました。

その評価基準を利用した圧縮と表領域移動のルールを作成します。
対象は表はインデックスなどのセグメントレベルです。

SELECT p.* FROM dba_ilmdatamovementpolicies p,dba_ilmobjects o
WHERE p.policy_name=o.policy_name AND o.object_name='EMP2';

POLICY_NAME                                                                                                                     ACTION_TYPE SCOPE   COMPRESSION_LEVEL
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------- ------------------------------
TIER_TABLESPACE                                                                                                                 TIER_STAT CONDITION_TYPE         CONDITION_DAYS
-------------------------------------------------------------------------------------------------------------------------------- --------- ---------------------- --------------
CUSTOM_FUNCTION
--------------------------------------------------------------------------------------------------------------------------------
P3                                                                                                                              COMPRESSION SEGMENT ADVANCED
                                                                                                                                          USER DEFINED                        0
ILM_RULE1

P4                                                                                                                              STORAGE     SEGMENT
TBS1                                                                                                                                      USER DEFINED                        0
ILM_RULE1

ポリシーが作成され、カスタムの評価基準が利用されていることを確認します。
次回はそのルールを実行してみて、どうなるかを確認してみます。

12cでは自動データ最適化（ADO）という機能が導入されました。
この機能によって、アクセス頻度に応じたデータの圧縮、表領域移動が自動化されます。

最適化を実行するにはアクセス頻度の収集（heat_map)と自動データ最適化を有効にする必要があります。

[oracle@DB01 ~]$ sqlplus / as sysdba

SQL> ALTER SYSTEM SET heat_map = ON;

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

SQL> show parameter heat_map

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      ON

インスタンスレベルでheat_mapが有効化されました。
ちなみにマルチテナント環境ではheat_mapを有効化することが出来ませんでした。
非マルチテナント環境のみ利用可能なようです。

SQL> ALTER TABLE SCOTT.emp ILM ADD POLICY
  2  COLUMN STORE COMPRESS FOR QUERY LOW
  3  GROUP AFTER 6 MONTHS OF LOW ACCESS;

表が変更されました。

表に対して６ヶ月アクセスが少ないと圧縮するという最適化のポリシーを作成しました。

SQL> ALTER TABLE scott.emp ILM ADD POLICY
  2  TIER TO tbs1;

これはemp表に対する表領域の使用率が一定以上になったら別の表領域に移動するポリシーです。

SQL> column NAME format a20
SQL> SELECT * FROM dba_ilmparameters;

NAME                      VALUE
-------------------- ----------
ENABLED                       1
RETENTION TIME               30
JOB LIMIT                     2
EXECUTION MODE                2
EXECUTION INTERVAL           15
TBS PERCENT USED             85
TBS PERCENT FREE             25
POLICY TIME                   0

8行が選択されました。

デフォルトでは85%以上になったら上記のポリシーが作動します。

SQL> exec DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED,90);

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

SQL> exec DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,10);

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

SQL> SELECT * FROM dba_ilmparameters;

NAME                      VALUE
-------------------- ----------
ENABLED                       1
RETENTION TIME               30
JOB LIMIT                     2
EXECUTION MODE                2
EXECUTION INTERVAL           15
TBS PERCENT USED             90
TBS PERCENT FREE             10
POLICY TIME                   0

8行が選択されました。

DBMS_ILM_ADMIN.CUSTOMIZE_ILMプロシージャで85%という使用率を変更することも出来ます。

マルチテナント環境のフラッシュバックデータベースはCDB単位で行ないます。
特定のPDBやルートコンテナのみを戻すことは出来ません。

[oracle@DB01 ~]$ sqlplus / as sysdba

SQL> ALTER DATABASE FLASHBACK ON;

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

フラッシュバックデータベースを行なうには、まずルートコンテナに接続しフラッシュバックを有効化します。
アーカイブログモードであることが前提条件です。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2276329

検証時に戻したい時点を確認するため、現時点のSCNを表示します。

SQL> alter session set container = PDB1;

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

SQL> show con_name;

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

PDBに接続します。

SQL> create table test_table (id number);

表が作成されました。

SQL> insert into test_table values (1);

1行が作成されました。

SQL> insert into test_table values (2);

1行が作成されました。

SQL> insert into test_table values (3);

1行が作成されました。

SQL> commit;

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

SQL> select * from test_table;

        ID
----------
         1
         2
         3

PDB内に表作成、データをインサートし、コミットします。
これでSCNが進みます。

SQL> alter session set container = cdb$root;

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

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

再度、ルートコンテナに接続します。

SQL> shutdown immediate;
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。

SQL> startup mount;
ORACLEインスタンスが起動しました。

Total System Global Area 1.3522E+10 bytes
Fixed Size                  2939704 bytes
Variable Size            6912214216 bytes
Database Buffers         6576668672 bytes
Redo Buffers               30613504 bytes
データベースがマウントされました。

CDB全体を停止し、マウント状態で起動します。

SQL> FLASHBACK DATABASE TO SCN 2276329;

フラッシュバックが完了しました。

最初で確認したSCNにフラッシュバックします。
これでSCN確認以降に作成した表は無くなるはずです。

SQL> ALTER DATABASE OPEN READ ONLY;

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

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;

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

確認のため、cdb、pdbを読み取り専用でオープンします。

SQL> alter session set container = PDB1;

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

SQL> select * from test_table;
select * from test_table
              *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。

PDBに接続し、作成した表がフラッシュバックにより無くなっていることを確認します。

SQL> shutdown immediate;
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。

SQL> startup mount;
ORACLEインスタンスが起動しました。

Total System Global Area 1.3522E+10 bytes
Fixed Size                  2939704 bytes
Variable Size            6912214216 bytes
Database Buffers         6576668672 bytes
Redo Buffers               30613504 bytes
データベースがマウントされました。
SQL> alter database open resetlogs;

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

確認後、再度、CDBを停止、マウント状態で起動し、resetlogsオプション付きでオープンします。

このページのトップヘ