Playing with Flashback Data Archive on 11GR2

最新推荐文章于 2022-09-23 14:19:40 发布

conghuan4367

最新推荐文章于 2022-09-23 14:19:40 发布

阅读量208

点赞数

文章标签：数据库操作系统

此前所写的《11G Flashback Data Archive新特性的研究》是基于11.1.0.6@Soalris10 环境：

http://space.itpub.net/15415488/viewspace-615982

最近11GR2发布，于是赶快在自己的RedHat EL5上安装了，于是本文主要以一种天马行空撰文的方法，聊一聊自己Playing with FBDA的一段经历。

其中有一些是危险的操作，主要来满足个人对FBDA的好奇，希望不要在生产环境模仿。

一.11GR2 NF PPT号称的为FBDA tracked table增加了更多DDL支持其实在11.2.0.1@RedHat是（提前）忽悠大家的。

让我们来看看PPT里所讲的：
ALTER TABLE ADD COLUMN was supported in Oracle Database 11g Release 1.
In Oracle Database 11g Release 2, the following DDL operations are supported:
Dropping of columns and partitions
Modifying and renaming columns
Renaming tables
Truncating tables and partitions

那么在11.2.0.1上事实真是如此么？

1.1 drop column仍然不支持：
SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0
SQL> alter table haotest drop column object_id;
alter table haotest drop column object_id
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

1.2 drop partition仍然不支持：
SQL> create table haopar(id number,name varchar2(30))
2 partition by range (id)
3 (PARTITION part1 VALUES LESS THAN (100),
4 PARTITION part2 VALUES LESS THAN (maxvalue)
5 ) tablespace assmtbs;

Table created.

SQL> alter table haopar flashback archive haofbda;

Table altered.
SQL> alter table haopar drop partition part1;
alter table haopar drop partition part1
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

1.3 modify column仍然不支持
SQL> alter table haotest modify object_id2 number(1,1);
alter table haotest modify object_id2 number(1,1)
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

1.4 rename column仍然不支持

SQL> alter table haotest rename column object_id2 to object_id3;
alter table haotest rename column object_id2 to object_id3
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

1.5 rename table至少早在11.1.0.6就支持了，所以现在11.2支持也不算新特性。
1.6 truncate table仍然不支持
SQL> truncate table haotest;
truncate table haotest
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

1.7 truncate partition仍然不支持
SQL> ALTER TABLE haopar truncate PARTITION part1;
ALTER TABLE haopar truncate PARTITION part1
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

综上，至少在11.2.0.1 for linux on X86上，
new feture PPT所述的更多支持DDL仍然不支持。
但我更原因相信这些正在将来的11.2版本上会支持，
但目前我姑且认为是提前的忽悠。

二.FBDA bug全集

我很幸运在玩FBDA@11.2.0.1@RedHat的时候，几乎经历了所有的Bug。

Metalink上几乎没写任何的workaround，这让我感到不敢在11.2base version使用FBDA。

2.1 Bug:FLASHBACK DATA ARCHIVE COSUMES HIGH CPU
Bug No: 7028762

在history table建立前，将已经enable FBDA的table给disable FBDA后，FBDA后台进程占用大量CPU。

由于在我破本本是单核的，所以，常常看到它占用70%以上的CPU。

SQL> create flashback archive haofbda2
2 tablespace fbdatbs
3 quota 100m
4 retention 1 year;

SQL> alter table haotest flashback archive haofbda2;

Table altered.

SQL> select TABLE_NAME,STATUS,ARCHIVE_TABLE_NAME from dba_flashback_archive_tables;

TABLE_NAME STATUS ARCHIVE_TABLE_NAME
---------- -------- --------------------
HAOTEST ENABLED SYS_FBA_HIST_41909

SQL> update haotest set object_name='hao';

13093 rows updated.

SQL> commit;

Commit complete.

SQL> alter table haotest no flashback archive;

Table altered.

SQL> select TABLE_NAME,STATUS,ARCHIVE_TABLE_NAME from dba_flashback_archive_tables;

TABLE_NAME STATUS ARCHIVE_TABLE_NAME
---------- -------- --------------------
HAOTEST DISABLED SYS_FBA_HIST_41909

SQL> desc SYS_FBA_HIST_41909
ERROR:
ORA-04043: object SYS_FBA_HIST_41909 does not exist

oracle@HaoRedHat: ~/data/bdump > ps -ef|grep 6147
oracle 6147 1 57 03:15 ? 00:07:34 ora_fbda_hao2

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6147 oracle 25 0 955m 67m 62m R 76.2 5.5 7:48.60 oracle

oracle@HaoRedHat: ~/data/bdump > strace -i -c -p 6147
Process 6147 attached - interrupt to quit
Process 6147 detached
% time     seconds usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
38.94    0.111114          24      4679           getrusage
35.75    0.102009          26      3953           gettimeofday
20.91    0.059670          25      2367           times
2.40    0.006848         129        53           semctl
1.07    0.003044          28       108           lseek
0.93    0.002645          24       108           read
------ ----------- ----------- --------- --------- ----------------
100.00    0.285330                 11268           total

经过strace，发现FBDA进程一直在getrusage和gettimeofday，没有干正事。

这时，我想，把FBDA进程干掉应该会被重启的，于是我就干了。

oracle@HaoRedHat: ~/data/bdump > kill -9 6147

果然在alert log里：

2009-10-13 03:36:42.918000 +08:00
Restarting dead background process FBDA
FBDA started with pid=36, OS id=6240

但是非常不幸的是，当它被重启之后，仍然占用高CPU。

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6240 oracle 25 0 955m 30m 28m R 77.7 2.4 0:36.01 oracle

oracle@HaoRedHat: ~/data/bdump > ps -ef|grep 6240
oracle 6240 1 76 03:36 ? 00:00:41 ora_fbda_hao2

这时，在alert log里有trace文件生成，打开一看：

Flashback Archive: Error ORA-942 in SQL select ROWID from HAOZHU_USER.SYS_FBA_HIST_41909 where ROWNUM = 1
Flashback Archive: Error ORA-942 in SQL select ROWID from HAOZHU_USER.SYS_FBA_DDL_COLMAP_41909 where ROWNUM = 1
Flashback Archive: Error ORA-903 in SQL select ROWID from .SYS_FBA_TCRV_41909 where ROWNUM = 1

（00942, 00000, "table or view does not exist"）

果然是跟没有生成history table有关。

如果你想知道怎么解决的，请往后接着看。

因为接下来就是另一个Bug。

2.2 Bug：DROP FLASHBACK ARCHIVE FAILS BY ORA-600[12811]
Bug No: 6958073

在metalink上这篇bug出现在11.0.1.6上，同样没有任何解决办法。

我之前那篇文章也提到过drop flashback archive会报600错误。

但现在在11.2.0.1上，会有什么不同的地方么？

SQL> drop flashback archive haofbda2;

Flashback archive dropped.
SQL> select FLASHBACK_ARCHIVE_NAME from dba_flashback_archive;

FLASHBACK_ARCHIVE_NA
--------------------
HAOFBDA2

SQL> drop flashback archive haofbda2;

Flashback archive dropped.

SQL> /

Flashback archive dropped.

SQL> /

Flashback archive dropped.

SQL> select FLASHBACK_ARCHIVE_NAME from dba_flashback_archive;

FLASHBACK_ARCHIVE_NA
--------------------
HAOFBDA2

果然，Oracle再次欺骗我说drop成功，但是在11.2上不会报出600错误来了。

但实际上FBDA仍然存在。

这时我看到tracked table跟FBDA仍然有关联：

SQL> select TABLE_NAME,FLASHBACK_ARCHIVE_NAME,STATUS from dba_flashback_archive_tables;

TABLE_NAME FLASHBACK_ARCHIVE_NA STATUS
---------- -------------------- --------
HAOTEST HAOFBDA2 DISABLED

于是我打算先drop purge这个已经被我disable FBDA的tracked table：

SQL> drop table HAOTEST purge;

Table dropped.

SQL> select TABLE_NAME,FLASHBACK_ARCHIVE_NAME,STATUS from dba_flashback_archive_tables;

no rows selected

大喜，果然这个表和FBDA的关联被我取消了（其实没有，后面会有阐述）。

但是仍然drop不掉FBDA：
SQL> drop flashback archive haofbda2;

Flashback archive dropped.

SQL> select FLASHBACK_ARCHIVE_NAME from dba_flashback_archive;

FLASHBACK_ARCHIVE_NA
--------------------
HAOFBDA2

到这里，我终于被万恶的FBDA给激怒了，于是开始玩火（反正在自己的虚拟机上）。

我认为，这样的bug很有可能是后台进程没有合理地修改内部表所造成的，肯定有什么残留的信息。

首先，我找到所有%FBA%字样的内部表：

oracle@HaoRedHat: ~/data/arc > ora tablike FBA

OWNER                          TABLE_NAME                     TABLESPACE_NAME       INI_TRANS FREELISTS NEXT_EXTENT PCT_INCREASE
------------------------------ ------------------------------ -------------------- ---------- ---------- ----------- ------------
SYS                            SYS_FBA_BARRIERSCN             SYSTEM                        1          1     1048576
SYS                            SYS_FBA_DL                     SYSTEM                        1          1     1048576
SYS                            SYS_FBA_FA                     SYSTEM                        1          1     1048576
SYS                            SYS_FBA_PARTITIONS             SYSTEM                        1          1     1048576
SYS                            SYS_FBA_TRACKEDTABLES          SYSTEM                        1          1     1048576
SYS                            SYS_FBA_TSFA                   SYSTEM                        1          1     1048576
SYS                            SYS_FBA_USERS                  SYSTEM                        1          1     1048576
SYS                            SYS_MFBA_NCHANGE                                             1          1
SYS                            SYS_MFBA_NROW                                                1          1
SYS                            SYS_MFBA_NTCRV                                               1          1
SYS                            SYS_MFBA_STAGE_RID                                           1          1
SYS                            SYS_MFBA_TRACKED_TXN                                         1          1

对每一个select一把，终于发现，我前面drop table haotest purge命令其实并没有完全取消tracked table和FBDA的关联。信息还存在于FBDA内部表里。

SQL> select * from SYS_FBA_TRACKEDTABLES;

      OBJ#        FA#    DROPSCN OBJNAME                        OWNERNAME                           FLAGS      SPARE
---------- ---------- ---------- ------------------------------ ------------------------------ ---------- ----------
     41909          1    3092606 HAOTEST                        HAOZHU_USER                             0
        -1         -1          0                                                                        1

那么为什么dba_flashback_archive_tables为显示没有任何信息呢？

其实很简单，我们查看下dba_flashback_archive_tables的view的定义就知道了。

oracle@HaoRedHat: ~/products/11r2/rdbms/admin > ora vdesc dba_flashback_archive_tables

VIEW                           TEXT
------------------------------ --------------------------------------------------------------------------------
SYS.DBA_FLASHBACK_ARCHIVE_TABL select o.NAME, u.NAME, f.FANAME, 'SYS_FBA_HIST_'||o.obj#,
ES                                  decode(t.DROPSCN, 0, 'ENABLED', 'DISABLED')
                               from OBJ$ o, USER$ u, SYS_FBA_FA f, SYS_FBA_TRACKEDTABLES t
                               where t.FA# = f.FA# and t.OBJ# = o.OBJ# and o.OWNER# = u.USER# and
                                 /* user has system privileges */
                                 exists (select null from v$enabledprivs where priv_number = -350)

原来dba_flashback_archive_tables会join OBJ#和SYS_FBA_TRACKEDTABLES,所以当我把haotest drop purge之后，OBJ#没有了信息，但是FBDA基表却还有残留的信息。

于是一不做二不休，删掉基表！

SQL> delete from SYS_FBA_TRACKEDTABLES;

2 rows deleted.

SQL> commit;

Commit complete.

再尝试drop FBDA，依然不行。

SQL> drop flashback archive haofbda2;

Flashback archive dropped.

SQL> select FLASHBACK_ARCHIVE_NAME from dba_flashback_archive;

FLASHBACK_ARCHIVE_NA
--------------------
HAOFBDA2

于是怒了:

SQL> select * from SYS_FBA_FA;

FANAME
--------------------------------------------------------------------------------
FA# RETENTION CREATESCN PURGESCN FLAGS SPARE1 SPARE2
---------- ---------- ---------- ---------- ---------- ---------- ----------
OWNERNAME
------------------------------
HAOFBDA2
1 365 3092163 3092163 4
HAOZHU_USER

SQL> delete from SYS_FBA_FA;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select FLASHBACK_ARCHIVE_NAME from dba_flashback_archive;

no rows selected

但是即使如此，FBDA进程依然高CPU。

最后重启数据库，重新生成一个FBDA：

SQL> create flashback archive haofbda2
2 tablespace fbdatbs
3 quota 100m
4 retention 1 year;

tablespace fbdatbs
*
ERROR at line 2:
ORA-00001: unique constraint (SYS.SYS_C001424) violated

乱搞内部表的后遗症开始显现。。。

于是顺藤摸瓜，找到是哪张内部表有这个constraint：

SQL> select INDEX_NAME,TABLE_NAME from dba_constraints
2 where CONSTRAINT_NAME='SYS_C001424';

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_C001424 SYS_FBA_TSFA

SQL> select * from SYS_FBA_TSFA;

FA# TS# QUOTA FLAGS SPARE
---------- ---------- ---------- ---------- ----------
1 5 100 256

哦，原来FBDA和tablespace的关联我还没有删掉。

SQL> delete from SYS_FBA_TSFA;

1 row deleted.

SQL> commit;

Commit complete.

删掉这行之后，终于新建FBDA恢复正常！

SQL> create flashback archive haofbda2
2 tablespace fbdatbs
3 quota 100m
4 retention 1 year;

Flashback archive created.

于是再enable一个FBDA看看有没有其他后遗症：

SQL> alter table haotest flashback archive haofbda2;
alter table haotest flashback archive haofbda2
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktfa_lock_tt: fail], [0], [], [],
[], [], [], [], [], [], [], []

哈哈，我欣喜地发现了另一个后遗症，让我来解决它！

我突然想起当初在delete SYS_FBA_TRACKEDTABLES的时候删除了两行，莫非其中obj#=-1的那行是无辜的？

还好我保留了screen，于是insert back！

SQL> insert into SYS_FBA_TRACKEDTABLES values
2 (-1,-1,0,null,null,1,null);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table haotest flashback archive haofbda2;

Table altered.

哈哈，又一个后遗症被我解决！

最后，我自己总结出解决以上两个bug的非官方危险办法：

delete from SYS_FBA_TRACKEDTABLES where obj#<>-1;

delete from SYS_FBA_TSFA;

delete from SYS_FBA_FA;

然后重启数据库。

这样play with FBDA也会对自己了解FBDA是如何通过内部表产生关联有了更深的理解。

当然，不推荐大家在生产数据库玩。

三. 11GR2的新东东们

在聊了这么久FBDA的bug之后，聊点开心的吧。

3.1 新增的内部表们

oracle@HaoRedHat: ~/products/11r2/rdbms/admin > ora tablike 41925

我们可以看到，在我前一篇的文章中，只说了FBDA会产生两个内部表。

但从这里可以看出，FBDA有四个内部表了。

在这里，我可以看到了FBDA NF PPT里讲的会支持更多DDL的苗头了。

就是SYS_FBA_DDL_COLMAP_41925，它记录了当前tracked table的column的名字和类型信息。

SQL> select count(*) from SYS_FBA_DDL_COLMAP_41925;

COUNT(*)
----------
15

SQL> alter table haotest add id number;

Table altered.

SQL> select count(*) from SYS_FBA_DDL_COLMAP_41925;

COUNT(*)
----------
16

当然现在只支持add column，我想有了这个表，将来确是可能支持例如drop column、modify column的DDL。

至于SYS_MFBA_NHIST_41925，当我再次查询时，他却没有了踪影：

oracle@HaoRedHat: ~/products/11r2/rdbms/admin > ora tablike 41925

于是我怀疑他是一个temporary table，并且只有在干活的时候才出现。

SQL> update haotest set object_name='hao2';

13093 rows updated.

SQL> select TABLE_NAME,TABLESPACE_NAME,COMPRESSION,READ_ONLY,TEMPORARY from dba_Tables where table_name='SYS_MFBA_NHIST_41925';

TABLE_NAME TABLESPACE_NAME COMPRESS REA T
------------------------------ ------------------------------ -------- --- -
SYS_MFBA_NHIST_41925 DISABLED NO Y

果然，他只在FBDA进程干活的时候生成。具体做什么的，估计是填充hist table时做的一个中间临时表。

3.2 DISASSOCIATE_FBA和REASSOCIATE_FBA

由于FBDA自己知道现在无法支持很多DDL语句，所以我想这是一个临时的办法。

他们就是：

SQL> desc dbms_flashback_archive
PROCEDURE DISASSOCIATE_FBA
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER_NAME                     VARCHAR2                IN
TABLE_NAME                     VARCHAR2                IN
PROCEDURE REASSOCIATE_FBA
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER_NAME                     VARCHAR2                IN
TABLE_NAME                     VARCHAR2                IN

当我们disassociate某个tracked table时，就可以对他做想要的DDL。然后再reassociate。

这让我不禁想起了logical standby中的alter database guard none(all);

呵呵，用法很简单，但是必须注意一点：

你的DDL不能更改column的名字或type。

SQL> exec dbms_flashback_archive.DISASSOCIATE_FBA(user,'HAOTEST');

PL/SQL procedure successfully completed.

SQL> alter table haotest modify id char;

Table altered.

SQL> exec dbms_flashback_archive.REASSOCIATE_FBA(user,'HAOTEST');
BEGIN dbms_flashback_archive.REASSOCIATE_FBA(user,'HAOTEST'); END;

*
ERROR at line 1:
ORA-55636: Flashback Data Archive enabled table "SYS"."HAOTEST" has different
definition from its history table
ORA-06512: at "SYS.DBMS_FLASHBACK_ARCHIVE", line 17
ORA-06512: at line 1

如果我打算手工同步hist table，不行：

SQL> alter table SYS_FBA_HIST_41925 modify id char;
alter table SYS_FBA_HIST_41925 modify id char
*
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on
table "SYS"."SYS_FBA_HIST_41925"

我只得将id column改回来。但这种方法给了我们truncate table等不影响column结构的DDL的机会，这些DDL在原来的版本中是无法执行的。

这算是FBDA的一大进步！

SQL> exec dbms_flashback_archive.DISASSOCIATE_FBA(user,'HAOTEST');

PL/SQL procedure successfully completed.

SQL> alter table haotest modify id number;

Table altered.

SQL> truncate table haotest;

Table truncated.

SQL> exec dbms_flashback_archive.REASSOCIATE_FBA(user,'HAOTEST');

PL/SQL procedure successfully completed.

好了，写了这么多，其目的就是让大家更好的理解FBDA和使用FBDA，虽然现在FBDA仍然有很多我们无法容忍的bug和缺点，但相信不久之后的11.2会有更大的提高和更完善的修复。

--Hao 2009.10.12 21：00pm 写于office