Thursday, April 23, 2009

Beware flashback bug in 10.2.0.3

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

# But the v$flashback_database_log table is confused

SQL> select * from v$flashback_database_log
2 ;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
0 60 4.1465E+10
1813890048

# Fix is to disable and re-enable

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
3.2802E+10 23-APR-09 60 15941632
0