10 April, 2016

FBDA -- 5 : Testing AutoPurging

Tracking data changes after one row added (ID_COLUMN=2000) on 06-Apr

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
06-APR-16 10.53.20.328132 PM +08:00

SQL> select scn_to_timestamp(startscn), scn_to_timestamp(endscn), count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4  order by 1,2;

SCN_TO_TIMESTAMP(STARTSCN)
---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ENDSCN)
---------------------------------------------------------------------------
  COUNT(*)
----------
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM
       450

02-APR-16 11.32.55.000000000 PM
03-APR-16 11.45.24.000000000 PM
       550

02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM
  5

02-APR-16 11.46.11.000000000 PM
03-APR-16 11.45.24.000000000 PM
       445

03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM
  5

03-APR-16 11.45.24.000000000 PM
04-APR-16 11.05.33.000000000 PM
      1000

06-APR-16 10.40.43.000000000 PM
06-APR-16 10.42.54.000000000 PM
  1


7 rows selected.

SQL> 
SQL> select count(*) from sys_fba_tcrv_93250;    

  COUNT(*)
----------
      1002

SQL> 


More changes on 07-Apr


SQL> insert into test_fbda
  2  select 3000, to_char(3000), trunc(sysdate)
  3  from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> update test_fbda
  2  set date_inserted=date_inserted
  3  where id_column=3000;

1 row updated.

SQL> delete test_fbda
  2  where id_column < 1001 ;

1000 rows deleted.

SQL> commit;

Commit complete.

SQL> 
SQL> l
  1  select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4* order by 1,2,3
SQL> /

STARTTIME    ENDTIME       COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM  02-APR-16 11.46.11.000000000 PM  450
02-APR-16 11.32.55.000000000 PM  03-APR-16 11.45.24.000000000 PM  550
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.41.33.000000000 PM    5
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.45.24.000000000 PM  445
03-APR-16 11.41.33.000000000 PM  03-APR-16 11.45.24.000000000 PM    5
03-APR-16 11.45.24.000000000 PM  04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM  06-APR-16 10.42.54.000000000 PM    1
07-APR-16 10.27.35.000000000 PM  07-APR-16 10.28.03.000000000 PM    1
07-APR-16 10.28.03.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000

10 rows selected.

SQL> 
SQL> l
  1  select id_column, trunc(date_inserted), count(*)
  2  from test_fbda
  3  group by id_column, trunc(date_inserted)
  4* order by 1
SQL> /

 ID_COLUMN TRUNC(DAT   COUNT(*)
---------- --------- ----------
      2000 06-APR-16       1
      3000 07-APR-16       1

SQL> 


I see two new 1000 row sets (04-Apr and 07-Apr).  I should expect only one.

Now that rows for ID_COLUMN less than 1001 have been deleted on 07-Apr, we have to see if and when they get purged from the History table.


On 09-Apr:

SQL> insert into test_fbda
  2  select 4000, to_char(4000),trunc(sysdate)
  3  from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> update test_fbda
  2  set date_inserted=date_inserted
  3  where id_column=4000;

1 row updated.

SQL> commit;

Commit complete.

SQL> l
  1  select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4* order by 1,2,3
SQL> /

STARTTIME    ENDTIME       COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM  02-APR-16 11.46.11.000000000 PM  450
02-APR-16 11.32.55.000000000 PM  03-APR-16 11.45.24.000000000 PM  550
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.41.33.000000000 PM    5
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.45.24.000000000 PM  445
03-APR-16 11.41.33.000000000 PM  03-APR-16 11.45.24.000000000 PM    5
03-APR-16 11.45.24.000000000 PM  04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM  06-APR-16 10.42.54.000000000 PM    1
07-APR-16 10.27.35.000000000 PM  07-APR-16 10.28.03.000000000 PM    1
07-APR-16 10.28.03.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
09-APR-16 11.10.25.000000000 PM  09-APR-16 11.10.48.000000000 PM    1

11 rows selected.

SQL> 
SQL> select * from user_flashback_archive
  2  /

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------ -----------------
CREATE_TIME
---------------------------------------------------------------------------
LAST_PURGE_TIME
---------------------------------------------------------------------------
STATUS
-------
SYSTEM
FBDA
   1     3
02-APR-16 11.24.39.000000000 PM
02-APR-16 11.24.39.000000000 PM



SQL> 


As on the morning of 10-Apr (after leaving the database instance running overnight) :

SQL> select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4  order by 1,2,3
  5  /

STARTTIME    ENDTIME       COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM  02-APR-16 11.46.11.000000000 PM  450
02-APR-16 11.32.55.000000000 PM  03-APR-16 11.45.24.000000000 PM  550
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.41.33.000000000 PM    5
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.45.24.000000000 PM  445
03-APR-16 11.41.33.000000000 PM  03-APR-16 11.45.24.000000000 PM    5
03-APR-16 11.45.24.000000000 PM  04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM  06-APR-16 10.42.54.000000000 PM    1
07-APR-16 10.27.35.000000000 PM  07-APR-16 10.28.03.000000000 PM    1
07-APR-16 10.28.03.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
09-APR-16 11.10.25.000000000 PM  09-APR-16 11.10.48.000000000 PM    1

11 rows selected.

SQL> select systimestamp from dual
  2  /

SYSTIMESTAMP
---------------------------------------------------------------------------
10-APR-16 08.51.29.398107 AM +08:00

SQL> 
SQL> select * from user_flashback_archive
  2  /

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------ -----------------
CREATE_TIME
---------------------------------------------------------------------------
LAST_PURGE_TIME
---------------------------------------------------------------------------
STATUS
-------
SYSTEM
FBDA
   1     3
02-APR-16 11.24.39.000000000 PM
02-APR-16 11.24.39.000000000 PM



SQL> 


So auto-purge of the data as of earlier days (02-Apr to 06-Apr) hasn't yet kicked in ?  Let's try a manual purge.

SQL> alter flashback archive fbda purge before timestamp (sysdate-4);

Flashback archive altered.

SQL> select * from user_flashback_archive;

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
------------------ -----------------
CREATE_TIME
---------------------------------------------------------------------------
LAST_PURGE_TIME
---------------------------------------------------------------------------
STATUS
-------
SYSTEM
FBDA
   1     3

05-APR-16 11.52.16.000000000 PM



SQL> 
SQL> ! sleep 300
SQL> l
  1  select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4* order by 1,2,3
SQL> /

STARTTIME    ENDTIME       COUNT(*)
-------------------------------- -------------------------------- ----------
02-APR-16 11.32.55.000000000 PM  02-APR-16 11.46.11.000000000 PM  450
02-APR-16 11.32.55.000000000 PM  03-APR-16 11.45.24.000000000 PM  550
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.41.33.000000000 PM    5
02-APR-16 11.46.11.000000000 PM  03-APR-16 11.45.24.000000000 PM  445
03-APR-16 11.41.33.000000000 PM  03-APR-16 11.45.24.000000000 PM    5
03-APR-16 11.45.24.000000000 PM  04-APR-16 11.05.33.000000000 PM 1000
04-APR-16 11.09.43.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
06-APR-16 10.40.43.000000000 PM  06-APR-16 10.42.54.000000000 PM    1
07-APR-16 10.27.35.000000000 PM  07-APR-16 10.28.03.000000000 PM    1
07-APR-16 10.28.03.000000000 PM  07-APR-16 10.28.03.000000000 PM 1000
09-APR-16 11.10.25.000000000 PM  09-APR-16 11.10.48.000000000 PM    1

11 rows selected.

SQL> 


Although USER_FLASHBACK_ARCHIVE shows that a purge till 05-Apr (the 11:52pm timestamp is strange) has been done, I still see older rows in the History table.  The query on the active table does correctly exclude the rows that should not be available. 


SQL> select scn_to_timestamp(startscn) starttime, scn_to_timestamp(endscn) endtime, count(*)
  2  from sys_fba_hist_93250
  3  group by scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  4  order by 1,2,3;

STARTTIME      ENDTIME    COUNT(*)
---------------------------------- ---------------------------------- ----------
02-APR-16 11.32.55.000000000 PM    02-APR-16 11.46.11.000000000 PM      450
02-APR-16 11.32.55.000000000 PM    03-APR-16 11.45.24.000000000 PM      550
02-APR-16 11.46.11.000000000 PM    03-APR-16 11.41.33.000000000 PM        5
02-APR-16 11.46.11.000000000 PM    03-APR-16 11.45.24.000000000 PM      445
03-APR-16 11.41.33.000000000 PM    03-APR-16 11.45.24.000000000 PM        5
03-APR-16 11.45.24.000000000 PM    04-APR-16 11.05.33.000000000 PM     1000
04-APR-16 11.09.43.000000000 PM    07-APR-16 10.28.03.000000000 PM     1000
06-APR-16 10.40.43.000000000 PM    06-APR-16 10.42.54.000000000 PM        1
07-APR-16 10.27.35.000000000 PM    07-APR-16 10.28.03.000000000 PM        1
07-APR-16 10.28.03.000000000 PM    07-APR-16 10.28.03.000000000 PM     1000
09-APR-16 11.10.25.000000000 PM    09-APR-16 11.10.48.000000000 PM        1

11 rows selected.

SQL> select * from user_flashback_archive;

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME
------------------ ----------------- ---------------------------------------------------------------------------
LAST_PURGE_TIME            STATUS
--------------------------------------------------------------------------- -------
SYSTEM
FBDA
   1     3
05-APR-16 11.52.16.000000000 PM


SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
10-APR-16 10.52.12.361412 PM +08:00

SQL> select count(*) from test_fbda as of timestamp (sysdate-3);

  COUNT(*)
----------
  2

SQL> 
SQL> select partition_position, high_value
  2  from user_tab_partitions
  3  where table_name = 'SYS_FBA_HIST_93250'
  4  order by 1;

PARTITION_POSITION HIGH_VALUE
------------------ --------------------------------------------------------------------------------
   1 MAXVALUE

SQL> 



Support Document 16898135.1 states that if Oracle isn't maintaining partitions for the History table, it may not be purging data properly.  Even an ALTER FLASHBACK ARCHIVE ... PURGE doesn't purge data (unless PURGE ALL is issued).  I'd seen this behaviour in 11.2.0.4 . The bug is supposed to have been fixed in 12.1.0.2  but my 12.1.0.2 environment shows the same behaviour.   The fact that my test database has very little activity (very few SCNs being incremented) shouldn't matter. The "Workaround" is, of course, unacceptable.
.
.
.

1 comment:

Unknown said...

Thanks for posting this. I thought I was going crazy, not seeing auto-purging impacting sys_fba_hist tables based on retention, trying to manually purge, expecting sys_fba_hist tables to have fewer rows and only PURGE ALL working. I'm on 11.0.2.4.