Oracle Database Discussions

{color:#0000ff}I pointed this out one year back when I was on 10.2.0.1 but it seems this issue is still here today with 10.2.0.4:
If I flashback much back in time, almost as back as the OLDEST_FLASHBACK_SCN I often fall in the scenario where the OLDEST_FLASHBACK_SCN > CHECKPOINT_CHANGE# .
{color}
h6. sys@DSSPRE> select (select oldest_flashback_scn from v$flashback_database_log) OLDEST_FLASHBACK_SCN, checkpoint_change#, controlfile_change#, current_scn from v$database;
h6. OLDEST_FLASHBACK_SCN CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CURRENT_SCN
-------------------- ------------------ ------------------- --------------
{color:#ff0000}7118887956839{color} 7118887954672 7118887955984 7118887956216
h6.
{color:#0000ff}In such cases upon attempting to flashback further, based on the OLDEST_FLASHBACK_SCN I get the obvious error:
{color}
sys@DSSPRE> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[sys@DSSPRE|mailto:sys@DSSPRE]> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2029008 bytes
Variable Size 121637424 bytes
Database Buffers 83886080 bytes
Redo Buffers 2162688 bytes
Database mounted.
[sys@DSSPRE|mailto:sys@DSSPRE]> flashback database to scn 7118887956840;
flashback database to scn 7118887956840
*
ERROR at line 1:
ORA-38743: Time/SCN is in the future of the database.


{color:#0000ff}Of course the SCN I want to flashback to must be older than CURRENT_SCN but then the OLDESTFLASHBACK_SCN appears to be no longer reliable._
{color}
sys@DSSPRE> flashback database to scn 7118887956200;
Flashback complete.
[sys@DSSPRE|mailto:sys@DSSPRE]> alter database open resetlogs;
Database altered.
[sys@DSSPRE|mailto:sys@DSSPRE]> select (select oldest_flashback_scn from v$flashback_database_log) OLDEST_FLASHBACK_SCN, checkpoint_change#, controlfile_change#, current_scn from v$database;
OLDEST_FLASHBACK_SCN CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CURRENT_SCN
-------------------- ------------------ ------------------- --------------
7118887957768 7118887956231 7118887956399 7118887956426

Any thoughts?

{color:#0000ff}Enrico
{color}