自动启用表的FLASHBACK ARCHIVE
时间：2015-05-14 02:16:20
收藏：0
阅读：255
Oracle的Flashback特性能让你找回先前的数据，因为这个特性依赖于UNDO，所以它的使用也是有很多限制的，比如当表的结构发生变化后, 如删除了一个字段(增加字段Flashback还能使用)，
那Flashback的特性就不能发挥作用了。当你使用Flashback时就会抛出ORA-01466的错误，提示表的定义已更改：
08:09:16 SQL> select timestamp_to_scn(to_timestamp(sysdate,‘yyyy-mm-dd hh24:mi:ss‘)) scn from dual;
SCN
-----------------------
6054997065323
08:10:00 SQL> alter table YKSOFT.USRA04_BAK20090814 add testcol VARCHAR2(30);
Table altered.
08:10:22 SQL> ALTER TABLE YKSOFT.USRA04_BAK20090814 DROP COLUMN testcol;
Table altered.
08:10:35 SQL> select count(*) from YKSOFT.USRA04_BAK20090814 as of scn 6054997065323;
select count(*) from YKSOFT.USRA04_BAK20090814 as of scn 6054997065323
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
针对这个不足，Oracle在11g中引入了一个新的特性Flashback Data Archive，也叫做Oracle Total Recall。该特性支持在ADD/DROP/MODIFY/RENAME表字段，或者ADD/DROP/RENAME表的约束，
或者TRUNCATE表后，仍然能正确使用Flashback Query，而不会报错:
08:09:16 SQL> select timestamp_to_scn(to_timestamp(sysdate,‘yyyy-mm-dd hh24:mi:ss‘)) scn from dual;
SCN
-----------------------
6054997065323
08:10:52 SQL> alter table yksoft.USRAZQ add testcol VARCHAR2(30);
Table altered.
08:11:16 SQL> alter table yksoft.USRAZQ drop COLUMN testcol;
Table altered.
08:11:37 SQL> select count(*) from yksoft.USRAZQ as of scn 6054997065323;
COUNT(*)
----------
132
1 row selected.
但是Flashback Archive是针对表的，也就是说如果你要启用Flashback Archive，你需要对每个表进行设置：ALTER TABLE XXX.XXXX FLASHBACK ARCHIVE [flashback_archive]，如果应用
的表基本不会增加/删除，那么一次初始化启用表的Flashback Archive也就可以了。但是有些应用，就比如我现在碰到的，时不时的就会CREATE一下表，时不时的就会DROP一下表，时不时的就
会ALTER一下表，然后还时不时的有XX用户邮件过来说不小心删错数据了，帮我恢复到之前的时间XXX，这时悲剧就发生了Flashback显然已经失效，又得花一番功夫去恢复了。此处省略1万字。。。
也就是基于这个原因，才会想到去实现一个自动启用表的FLASHBACK ARCHIVE的方案。该方案基于Oracle的System Trigger。
首先创建一个AFTER CREATE的Trigger：
CREATE OR REPLACE PROCEDURE SYS.ENABLE_TAB_FLA
(
object_owner IN VARCHAR2,
object_name IN VARCHAR2,
fla_name IN VARCHAR2
) IS
opsql VARCHAR2(128);
BEGIN
opsql := ‘ALTER TABLE ‘||object_owner||‘.‘||object_name||‘ FLASHBACK ARCHIVE ‘||fla_name;
EXECUTE IMMEDIATE opsql;
END;
/
CREATE OR REPLACE TRIGGER SYS.ENABLE_TAB_FLA_TRIG
AFTER CREATE ON DATABASE
DECLARE
operation VARCHAR2(30);
object_owner VARCHAR2(30);
object_name VARCHAR2(128);
object_type VARCHAR2(30);
sql_list ora_name_list_t;
sql_i binary_integer;
sql_text VARCHAR2(4000);
attempt_by VARCHAR2(30);
fired VARCHAR2(3);
comment_text VARCHAR2(2000);
attempt_dt DATE;
fla VARCHAR2(30);
BEGIN
operation := ora_sysevent;
object_owner := ora_dict_obj_owner;
object_name := ora_dict_obj_name;
object_type := ora_dict_obj_type;
sql_i := ora_sql_txt(sql_list);
FOR i IN 1..sql_i LOOP
sql_text := sql_text || sql_list(i);
END LOOP;
attempt_by := USER;
fired := ‘NO‘;
attempt_dt := SYSDATE;
IF (operation = ‘CREATE‘) AND (object_owner = ‘YKSOFT‘) AND (object_type = ‘TABLE‘) AND (SUBSTR(object_name, 1, 4) IN (‘USRA‘, ‘RETA‘)) THEN
fla := ‘YKFLA‘;
ENABLE_TAB_FLA(object_owner, object_name, fla);
fired := ‘YES‘;
comment_text := ‘Flashback Archive was enabled for ‘||object_owner||‘.‘||object_name||‘ on ‘||fla;
TRI_LOG (operation, object_owner, object_name, object_type, sql_text, attempt_by, fired, comment_text, attempt_dt); -- 记录日志
END IF;
EXCEPTION
WHEN OTHERS THEN
comment_text := sqlerrm;
TRI_LOG (operation, object_owner, object_name, object_type, sql_text, attempt_by, fired, comment_text, attempt_dt);
END;
/
我这里创建的是DATABASE级的Trigger，当然你也可以创建SCHEMA TRIGGER(但是SCHEMA TRIGGER不一定生效)。然后在Trigger根据实际的需要对表启用Flashback Archive。
到这里，其实已经完成自动启用表的FLASHBACK ARCHIVE的功能，但是还有一点也必须考虑到，就是在DROP表时。如果在一张已启用Flashback Archive的表上进行DROP操作，就会报ORA-55610错误
SQL> DROP TABLE YKSOFT.USRA01AT;
DROP TABLE YKSOFT.USRA01AT
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
也就是说在DROP表之前，先要禁用该表的Flashback Archive。所以这里创建一个BEFORE DROP的Trigger：
CREATE OR REPLACE PROCEDURE SYS.DISABLE_TAB_FLA
(
object_owner IN VARCHAR2,
object_name IN VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
opsql VARCHAR2(128);
BEGIN
opsql := ‘ALTER TABLE ‘||object_owner||‘.‘||object_name||‘ NO FLASHBACK ARCHIVE‘;
EXECUTE IMMEDIATE opsql;
COMMIT;
END;
/
CREATE OR REPLACE TRIGGER SYS.DISABLE_TAB_FLA_TRIG
BEFORE DROP ON DATABASE
DECLARE
operation VARCHAR2(30);
object_owner VARCHAR2(30);
object_name VARCHAR2(128);
object_type VARCHAR2(30);
sql_list ora_name_list_t;
sql_i binary_integer;
sql_text VARCHAR2(4000);
attempt_by VARCHAR2(30);
fired VARCHAR2(3);
comment_text VARCHAR2(2000);
attempt_dt DATE;
opsql VARCHAR2(128);
BEGIN
operation := ora_sysevent;
object_owner := ora_dict_obj_owner;
object_name := ora_dict_obj_name;
object_type := ora_dict_obj_type;
sql_i := ora_sql_txt(sql_list);
FOR i IN 1..sql_i LOOP
sql_text := sql_text || sql_list(i);
END LOOP;
attempt_by := USER;
fired := ‘NO‘;
attempt_dt := SYSDATE;
IF (operation = ‘DROP‘) AND (object_owner = ‘YKSOFT‘) AND (object_type = ‘TABLE‘) AND (SUBSTR(object_name, 1, 4) IN (‘USRA‘, ‘RETA‘)) THEN
DISABLE_TAB_FLA(object_owner, object_name);
fired := ‘YES‘;
comment_text := ‘Flashback Archive was disable for ‘||object_owner||‘.‘||object_name;
TRI_LOG (operation, object_owner, object_name, object_type, sql_text, attempt_by, fired, comment_text, attempt_dt);
END IF;
EXCEPTION
WHEN OTHERS THEN
comment_text := sqlerrm;
TRI_LOG (operation, object_owner, object_name, object_type, sql_text, attempt_by, fired, comment_text, attempt_dt);
END;
/
BEFORE DROP的Trigger里很重要的一点是需要创建一个自治事务(PRAGMA AUTONOMOUS_TRANSACTION)的过程来支持禁用Flashback Archive的操作，否则就会报ORA-30512的错误：
SQL> DROP TABLE YKSOFT.RETA01AT;
DROP TABLE YKSOFT.RETA01AT
*
ERROR at line 1:
ORA-30512: cannot modify YKSOFT.RETA01AT more than once in a transaction
而且过程中的COMMIT也是必须的，否则还是报错。至此，一个比较完整的自动启用表的FLASHBACK ARCHIVE方案也就实现了。当然还有一些操作，如ALTER TABLE MOVE/ALTER TABLE UPGRADE也会引发和DROP一样的错误，这可以根据实际需要再去完善了。
原文：http://blog.itpub.net/13885898/viewspace-1651394/
评论（0）