読者です 読者をやめる 読者になる 読者になる

kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

Oracle Standard Editionでも使えたフラッシュバックデータアーカイブ

まず、フラッシュバックデータアーカイブとは

その存続期間中、表に対するすべてのトランザクションによる変更を追跡および格納できます。
レコード・ステージ・ポリシーと監査レポートのコンプライアンスに役立ちます。
Oracle® Databaseアドバンスト・アプリケーション開発者ガイド

つまり、過去の更新を指定した期間中、表領域に保持してくれる機能みたい。
ある時点のデータを問い合わせることは可能。

どうせEEしか使えないんだろうなと思っていたら、

Oracle Database 11gリリース2 (11.2.0.4)以降:
フラッシュバック・データ・アーカイブ履歴表の最適化を
使用するには、Oracle Advanced Compressionオプションのライセンスが必要です。
履歴表の最適化を行わない基本フラッシュバック・データ・アーカイブは、すべてのエディションで使用できます。
Oracle® Databaseライセンス情報

なんと11.2.0.4以降なら行の圧縮とはできないけどSEでも使用可能とのことで早速検証してみた。

アーキテクチャ

FBDA(バックグラウンドプロセス)が非同期(デフォルト5分)でUNDO領域の情報をフラッシュバックデータアーカイブ領域に書き込む。

フラッシュバックデータアーカイブの設定

サイズ1Gの保存期間1年で作成

1. 表領域の作成

create tablespace FBDA datafile '/opt/app/oracle/oradata/FBDA.dbf' size 1g;

2. フラッシュバックデータアーカイブ領域作成

FBDA_TS・・・フラッシュバックデータアーカイブ名
create flashback archive FBDA_TS tablespace FBDA quota 1g retention 1year;
ALTER USER ORAUSER QUOTA UNLIMITED ON FBDA ;

3. アクセス権限付与

GRANT FLASHBACK ARCHIVE ON FBDA_TS to ORAUSER ;

ここで権限付与を忘れてフラッシュバックデータアーカイブをテーブルに設定すると以下のようにFBDAバックグラウンドプロセスのトレースファイルにエラーがでる。。 ここで少しハマった。。

orcl_fbda_59131.trc
Flashback Archive: Error ORA-942 in SQL insert into ORAUSER.SYS_FBA_DDL_COLMAP_104277 (ENDSCN, COLUMN_NAME, TYPE, HISTORICAL_COLUMN_NAME)values (NULL, 'ID', 'NUMBER', 'ID')
Flashback Archive: Error ORA-942 in SQL "select count(*) from ORAUSER.SYS_FBA_DDL_COLMAP_104277"
Flashback Archive: Error ORA-942 in SQL "select count(*) from ORAUSER.SYS_FBA_DDL_COLMAP_104277"

4. テーブルにフラッシュバックデータアーカイブを設定する

  • 新しいテーブル
create table fbtest (id int,nm varchar2(20)) flashback archive FBDA_TS;
  • 既存テーブル
ALTER TABLE master_table FLASHBACK ARCHIVE FBDA_TS;

これでフラッシュバックデータアーカイブの設定は完了。

フラッシュバックデータアーカイブ確認方法

  • 情報は以下テーブルで確認可能
SELECT * FROM dba_FLASHBACK_ARCHIVE_TABLES;
SELECT * FROM DBA_FLASHBACK_ARCHIVE_TS;
  • フラッシュバックデータアーカイブ領域のサイズ確認方法
select owner,segment_name,BYTES,BLOCKS,EXTENTS 
           from dba_segments where tablespace_name='FBDA';

EGMENT_NAME                                  BYTES     BLOCKS    EXTENTS
---------------------------------------- ---------- ---------- ----------
SYS_FBA_DDL_COLMAP_104277                     65536          8          1
SYS_FBA_TCRV_104277                          131072         16          2
SYS_FBA_DDL_COLMAP_435180                     65536          8          1
SYS_FBA_TCRV_435180                        13631488       1664         28
SYS_FBA_HIST_104277                         8388608       1024          1
SYS_FBA_HIST_435180                         8388608       1024          1
SYS_FBA_TCRV_IDX_104277                      131072         16          2
SYS_FBA_TCRV_IDX_435180                    10485760       1280         25

はじめてアーカイブが生成されるタイミングでSYS_FBA_XXXX という表が複数作成される。
XXXX・・・セグメント名の最後の数字はオブジェクト番号。

SQL> select OBJECT_NAME,OBJECT_ID  
          from dba_objects where OBJECT_ID in (104277,435180);

OBJECT_NAME                               OBJECT_ID
---------------------------------------- ----------
MASTER_TABLE                                 104277
FBTEST                                       435180
  • 各セグメントの内容
セグメント名 内容
SYS_FBA_DDL_COLMAP カラム名(過去のも含む)を格納
SYS_FBA_HIST 変更前のすべてのカラムの行データを格納
SYS_FBA_TCRV rowidとDMLオペレーション(U I D)とSCNを格納
SYS_FBA_TCRV_IDX SYS_FBA_HISTを効率良く問い合わせるためのインデックス

とある一行の一カラムのアップデートを実行したとしてもSYS_FBA_HISTには全てのカラムの値が保持されるためカラム数の少ないテーブルの方がフラッシュバックデータアーカイブ領域の使用率は少なくなる。

問い合わせ方法

フラッシュバッククエリを使用して過去の時点の問い合わせができる。

 SELECT id, status FROM master_table where id='99999999';

        ID      STATUS
---------- ----------
  99999999          1

 SELECT id, status FROM master_table AS OF TIMESTAMP TO_TIMESTAMP ('2015-06-24 14:00:00', 'YYYY-MM-DD HH24:MI:SS') where id='99999999';

        ID      STATUS
---------- ----------
  99999999        777

その他

既存のテーブルに対して導入する際はdba_tab_modificationsを使用すれば大体の容量の見積もりは予想できそう。

select table_owner,table_name,inserts,updates,deletes,timestamp,truncated from sys.dba_tab_modifications where TABLE_OWNER='ORAUSER' and table_name = 'MASTER_TABLE';

TABLE_OWNER                    TABLE_NAME                        INSERTS    UPDATES    DELETES TIMESTAMP           TRU
------------------------------ ------------------------------ ---------- ---------- ---------- ------------------- ---
ORAUSER                        MASTER_TABLE                          154     494188          0 2015/08/07 00:17:28 NO