オラクる。

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

2016年07月

12cの新機能、In Database ArchiveはOracleで表の論理削除を実現する機能です。

CREATE TABLE tab1 (
    id NUMBER,
    description VARCHAR2(50),
    CONSTRAINT tab1_pk PRIMARY KEY (id)
)
ROW ARCHIVAL;


テスト用に表を作成します。
「ROW ARCHIVAL;」をCREATE TABLE文に付与することで、その表のIn Database Archiveが有効になります。

SQL> ALTER TABLE tab1 NO ROW ARCHIVAL;

表が変更されました。

SQL> ALTER TABLE tab1 ROW ARCHIVAL;

表が変更されました。


ALTER文で既存の表のIn Database Archive有効、無効が設定できます。

COLUMN column_name FORMAT A20
COLUMN data_type FORMAT A20

SELECT column_id,
       column_name,
       data_type,
       data_length,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = 'TAB1'
ORDER BY column_id;


 COLUMN_ID COLUMN_NAME          DATA_TYPE            DATA_LENGTH HID
---------- -------------------- -------------------- ----------- ---
         1 ID                   NUMBER                        22 NO
         2 DESCRIPTION          VARCHAR2                      50 NO
           ORA_ARCHIVE_STATE    VARCHAR2                    4000 YES
           SYS_NC00003$         RAW                          126 YES


descでは表示されませんが、user_tab_colsビューを見ると「ORA_ARCHIVE_STATE」列が追加されていることがわかります。

INSERT /*+ APPEND */ INTO tab1
SELECT level,'Description of ' || level
FROM dual
CONNECT BY level <= 1000;

COMMIT;

SELECT count(*) FROM tab1;

  COUNT(*)
----------
      1000


テスト用にデータ1000件を挿入します。

COLUMN ORA_ARCHIVE_STATE FORMAT A20

SELECT ora_archive_state, COUNT(*)
FROM tab1
GROUP BY ora_archive_state
ORDER BY ora_archive_state;

ORA_ARCHIVE_STATE      COUNT(*)
-------------------- ----------
0                          1000


1000件のデータのORA_ARCHIVE_STATE列は、最初は全て0に設定されています。

UPDATE tab1
SET ora_archive_state = '1'
WHERE id BETWEEN 751 and 1000;
COMMIT;


751件目から1000件目のORA_ARCHIVE_STATEを「1」に更新します。

SELECT COUNT(*) FROM tab1;

  COUNT(*)
----------
       750


countで表示されるデータ件数が750件になりました。
751件目から1000件目が「論理削除」されたことになります。
「論理」削除なので実体は存在します。
見えなくなっただけです。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

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

SQL> SELECT COUNT(*) FROM tab1;

  COUNT(*)
----------
      1000


ROW ARCHIVAL VISIBILITY値をALLに設定することで、全てのデータが見えるようになります。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

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

SQL> SELECT COUNT(*) FROM tab1;

  COUNT(*)
----------
       750


ROW ARCHIVAL VISIBILITY値をACTIVEに設定することで、ORA_ARCHIVE_STATE列が1に設定されている行が見えなくなります。(デフォルト)

12cよりRMANを利用して表単位でのリカバリを行なうことが出来るようになりました。
これで、特定の表をある指定した時点に戻すということが出来ます。

[oracle@DB01 ~]$ sqlplus test/password@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on 月 7月 18 00:54:29 2016

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

最終正常ログイン時間: 日 7月  17 2016 01:14:32 +09:00


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options
に接続されました。

SQL> CREATE TABLE t1 (id NUMBER);

表が作成されました。

SQL> INSERT INTO t1 VALUES (1);

1行が作成されました。

SQL> COMMIT;

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


テスト用にt1という表を作成し、1件のデータを挿入しました。

[oracle@DB01 ~]$ sqlplus / as sysdba

SQL> SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 3163019


リカバリ前に現在のSCN番号を確認します。
この番号が重要です。

[oracle@DB01 ~]$ sqlplus test/password@pdb1

SQL> INSERT INTO t1 VALUES (2);

1行が作成されました。

SQL> COMMIT;

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

SQL> SELECT * FROM t1;

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


もう1件データを挿入しました。
この表を先ほど確認したSCN番号の地点まで戻せば、ここで挿入したデータは消えるはずです。

RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
UNTIL SCN 3163019
AUXILIARY DESTINATION '/u01/aux'
REMAP TABLE 'TEST'.'T1':'T1_PREV';

recoverが開始されました(開始時間: 16-07-18)



recoverが完了しました(完了時間: 16-07-18)

早速、リカバリ開始です。
rmanのRECOVERコマンドを利用します。
今回はPDB内の表をリカバリします。
作業用に/u01/auxを利用し、リカバリ後の表名はt1_prevとします。
相当な量のログが流れた後にリカバリが完了します。

[oracle@DB01 ~]$ sqlplus test/password@pdb1

SQL> SELECT * FROM t1;

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

SQL> SELECT * FROM t1_prev;

        ID
----------
         1


早速、PDBに接続し、表を確認します。
本来の表には2件データが挿入されています。
一方で、リカバリ後の表には1件しかデータが挿入されていません。
2件目のデータが挿入される前のSCN番号に戻ったからです。

12cのrmanではマルチテナントが導入されたことに伴って、コンテナごとのバックアップ、およびにPDBごとのリストアが出来るようになります。

[oracle@DB01 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on 日 7月 17 00:54:40 2016

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

ターゲット・データベース: CDB2 (データベースID=663406917)に接続されました

RMAN> backup database;

backupが開始されました(開始時間: 16-07-17)
リカバリ・カタログのかわりにターゲット・データベース制御ファイルを使用しています
チャネル: ORA_DISK_1が割り当てられました



チャネルORA_DISK_1: ピース1(16-07-17)を起動します
チャネルORA_DISK_1: ピース1(16-07-17)が完了しました
ピース・ハンドル=+FRA/CDB2/36D1F427301211C5E053C60911AC0FFB/BACKUPSET/2016_07_17/nnndf0_tag20160717t005453_0.462.917398655 タグ=TAG20160717T005453 コメント=NONE
チャネルORA_DISK_1: バックアップ・セットが完了しました。経過時間: 00:00:45
backupが完了しました(完了時間: 16-07-17)

Control File and SPFILE Autobackupが開始されました(開始時間: 16-07-17)
ピース・ハンドル=+FRA/CDB2/AUTOBACKUP/2016_07_17/s_917398699.459.917398703 コメント=NONE
Control File and SPFILE Autobackupが完了しました(完了時間: 16-07-17)


rmanでターゲットデータベースに接続して、backup databaseを実行すると、PDBも含めたコンテナ全体のバックアップが取得されます。

[oracle@DB01 ~]$ sqlplus test/password@pdb1
SQL> set linesize 100
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17        800                    20
      7499 ALLEN      SALESMAN        7698 81-02-20       1600        300         30
      7521 WARD       SALESMAN        7698 81-02-22       1250        500         30




SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
16-07-17 00:59:51.559666 +09:00

SQL> drop table emp;

表が削除されました。

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


バックアップを取得した後、試しにpdbに接続し、1つの表を削除してみます。
dropする前に時刻を確認しているので、drop前の時刻に戻る不完全リカバリを実行してみます。

[oracle@DB01 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on 日 7月 17 01:00:16 2016

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

ターゲット・データベース: CDB2 (データベースID=663406917)に接続されました

RMAN> alter pluggable database pdb1 close;

リカバリ・カタログのかわりにターゲット・データベース制御ファイルを使用しています
文が処理されました


まず、コンテナの管理者でrmanに接続し、リカバリ対象のPDBをクローズします。
12cよりrmanのコマンドラインより直接SQL文が実行できるようになりました。

RMAN> restore pluggable database pdb1;

restoreが開始されました(開始時間: 16-07-17)
チャネル: ORA_DISK_1が割り当てられました
チャネルORA_DISK_1: SID=36 デバイス・タイプ=DISK

チャネルORA_DISK_1: データファイル・バックアップ・セットのリストアを開始しています
チャネルORA_DISK_1: バックアップ・セットからリストアするデータファイルを指定しています
チャネルORA_DISK_1: データファイル00008を+DATA/CDB2/36D2894304212B74E053C60911AC5711/DATAFILE/system.298.916421277にリストアしています
チャネルORA_DISK_1: データファイル00009を+DATA/CDB2/36D2894304212B74E053C60911AC5711/DATAFILE/sysaux.297.916421277にリストアしています
チャネルORA_DISK_1: データファイル00010を+DATA/CDB2/36D2894304212B74E053C60911AC5711/DATAFILE/users.299.916421279にリストアしています
チャネルORA_DISK_1: データファイル00012を+DATA/test0706.dbfにリストアしています
チャネルORA_DISK_1: バックアップ・ピース+FRA/CDB2/36E03DC7689B77BBE053C60911AC5895/BACKUPSET/2016_07_17/nnndf0_tag20160717t005453_0.464.917398599から読取り中です
チャネルORA_DISK_1: ピース・ハンドル=+FRA/CDB2/36E03DC7689B77BBE053C60911AC5895/BACKUPSET/2016_07_17/nnndf0_tag20160717t005453_0.464.917398599 タグ=TAG20160717T005453
チャネルORA_DISK_1: バックアップ・ピース1がリストアされました
チャネルORA_DISK_1: リストアが完了しました。経過時間: 00:01:05
restoreが完了しました(完了時間: 16-07-17)


そして、対象のPDBをバックアップよりリストアします。

RMAN> recover pluggable database pdb1 until
2> time "to_timestamp('16-07-17 00:59:51','YY-MM-DD HH24:MI:SS')";


recoverが開始されました(開始時間: 16-07-17)
リカバリ・カタログのかわりにターゲット・データベース制御ファイルを使用しています
チャネル: ORA_DISK_1が割り当てられました
チャネルORA_DISK_1: SID=278 デバイス・タイプ=DISK
RMAN-05026: 警告: 次の表領域のセットは、指定したPoint-in-Timeへの適用が想定されています



スレッド1 (順序78)のアーカイブ・ログは、ファイル+FRA/CDB2/ARCHIVELOG/2016_07_17/thread_1_seq_78.458.917399165としてディスクに存在します
アーカイブ・ログ・ファイル名=+FRA/CDB2/ARCHIVELOG/2016_07_17/thread_1_seq_78.458.917399165 スレッド=1 順序=78
メディア・リカバリが完了しました。経過時間: 00:00:01
recoverが完了しました(完了時間: 16-07-17)

SQL文: alter database open read only



Oracleインスタンスがシャットダウンしました


自動インスタンスを削除しています
自動インスタンスが削除されました
補助インスタンス・ファイル+FRA/CDB2/DATAFILE/sysaux.451.917399399を削除しました
補助インスタンス・ファイル+FRA/CDB2/DATAFILE/current.453.917399385を削除しました
recoverが完了しました(完了時間: 16-07-17)


次に表削除前の時間を指定して、不完全リカバリを実行します。
これで、表はemp削除前の状態に戻りました。

RMAN> alter pluggable database pdb1 open resetlogs;

文が処理されました

resetlogsオプションでpdbをオープンします。


[oracle@DB01 ~]$ sqlplus test/password@pdb1

SQL> set linesize 100
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17        800                    20
      7499 ALLEN      SALESMAN        7698 81-02-20       1600        300         30
      7521 WARD       SALESMAN        7698 81-02-22       1250        500         30
      7566 JONES      MANAGER         7839 81-04-02       2975                    20
      7654 MARTIN     SALESMAN        7698 81-09-28       1250       1400         30




削除した表が復活していることがわかります。
これでリストア完了です。

unified auditの監査情報は、デフォルトではまずSGAに設けられた領域に書き込みを行ない、一定時間経過後にSGAからディスクへの書き込みを行ないます。

SQL> show parameter UNIFIED_AUDIT_SGA_QUEUE_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
unified_audit_sga_queue_size         integer     1048576


SGAの領域サイズはUNIFIED_AUDIT_SGA_QUEUE_SIZEで設定されています。
デフォルトは1MBです。
なお、SGAからディスクへの書き込みが行なわれる前にインスタンスクラッシュが発生した場合、監査情報が一部失われる恐れがあります。
それを防ぐために、監査情報を直接ディスクへ書き込む設定も可能です。
ただし、パフォーマンスに影響を与えないか見極める必要があります。

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.audit_trail_unified,
    audit_trail_property       => DBMS_AUDIT_MGMT.audit_trail_write_mode,
    audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_immediate_write
  );
END;
/

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


PL/SQLプロシージャを利用して、ディスクへ直接書き込みモードへ変更することが出来ます。

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.audit_trail_unified,
    audit_trail_property       => DBMS_AUDIT_MGMT.audit_trail_write_mode,
    audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_queued_write
  );
END;
/

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


SGAに書き込むモードに戻す場合も、同じようにプロシージャを利用して設定します。

SQL> EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

なお、SGAに書き込むモードの場合でも、flush_unified_audit_trailプロシージャを利用することで、直ちにディスクに書き込ませる(flushする)ようにすることが出来ます。
上記例では現在接続しているコンテナのみをflushします。

[oracle@DB01 ~]$ sqlplus / as sysdba

SQL> EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail(container => DBMS_AUDIT_MGMT.container_all);

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


管理者ユーザーで接続し、flush_unified_audit_trailプロシージャをcontainer_allオプションで実行すると、全てのコンテナの監査情報がディスクにフラッシュされます。

12cのunified auditでは11gまでの監査機能では監査できなかったexpdp、impdpなどのユーティリティの実行を監査できます。

[oracle@DB01 ~]$ sqlplus sys/password@localhost/pdb1.test.local as sysdba

に接続されました。
SQL> CREATE AUDIT POLICY audit_dp_all_pol ACTIONS COMPONENT=DATAPUMP ALL;

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

SQL> AUDIT POLICY audit_dp_all_pol;

監査が成功しました。


監査ポリシーを作成し、有効にします。
「ACTIONS COMPONENT=DATAPUMP ALL」→全てのexpdp、impdpを監査するというポリシーです。

[oracle@DB01 ~]$ cd test_back/
[oracle@DB01 test_back]$ pwd
/home/oracle/test_back

[oracle@DB01 test_back]$ sqlplus sys/password@localhost/pdb1.test.local as sysdba

SQL> create or replace directory TEST_DIR as '/home/oracle/test_back';

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

SQL> grant read, write on directory TEST_DIR to test;

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

早速、expdpを実施してみましょう。
まずはディレクトリの準備から

[oracle@DB01 test_back]$ expdp test/password@localhost/pdb1.test.local dumpfile=
test_tables tables=emp,dept directory=TEST_DIR

Export: Release 12.1.0.2.0 - Production on 金 7月 15 00:42:57 2016

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

接続先: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options
"TEST"."SYS_EXPORT_TABLE_01"を起動しています: test/********@localhost/pdb1.test.local dumpfile=test_tables tables=emp,dept directory=TEST_DIR
BLOCKSメソッドを使用して見積り中です...
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 128 KB
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
. . "TEST"."DEPT"                               6.023 KB       4行がエクスポートされました
. . "TEST"."EMP"                                8.773 KB      14行がエクスポートされました
マスター表"TEST"."SYS_EXPORT_TABLE_01"は正常にロード/アンロードされました
******************************************************************************
TEST.SYS_EXPORT_TABLE_01に設定されたダンプ・ファイルは次のとおりです:
  /home/oracle/test_back/test_tables.dmp
ジョブ"TEST"."SYS_EXPORT_TABLE_01"が金 7月 15 00:43:20 2016 elapsed 0 00:00:18で正常に完了しました


そして、expdpを実行します。
このアクションが監査証跡に記録されます。

SQL> SELECT event_timestamp,dp_text_parameters1,dp_boolean_parameters1
  2  FROM unified_audit_trail WHERE dp_text_parameters1 IS NOT NULL;

EVENT_TIMESTAMP
---------------------------------------------------------------------------
DP_TEXT_PARAMETERS1
--------------------------------------------------------------------------------
DP_BOOLEAN_PARAMETERS1
--------------------------------------------------------------------------------
16-07-15 00:43:06.365530
MASTER TABLE:  "TEST"."SYS_EXPORT_TABLE_01" , JOB_TYPE: EXPORT, METADATA_JOB_MOD
E: TABLE_EXPORT, JOB VERSION: 12.1.0.2.0, ACCESS METHOD: AUTOMATIC, DATA OPTIONS
: 0, DUMPER DIRECTORY: NULL  REMOTE LINK: NULL, TABLE EXISTS: NULL, PARTITION OP
TIONS: NONE
MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE, DUMPFILE_PRESENT: TR
UE, JOB_RESTARTED: FALSE


unified_audit_trailビューを確認してみましょう。
先ほどのexpdpが記録されていることがわかります。

なお、unified_audit_trailにはdatapump用の列も用意されています。
それらは、DP_TEXT_PARAMETERS1やDP_BOOLEAN_PARAMETERS1などの列のことで、必ずdpが頭文字になっています。

このページのトップヘ