Tracking data changes after one row added (ID_COLUMN=2000) on 06-Apr
More changes on 07-Apr
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:
As on the morning of 10-Apr (after leaving the database instance running overnight) :
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.
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.
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.
.
.
.
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:
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.
Post a Comment