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領域の情報をフラッシュバックデータアーカイブ領域に書き込む。
フラッシュバックデータアーカイブの設定
サイズ１Gの保存期間１年で作成
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
