techblog 

Oracle Flashback database is a new 10g feature that in theory allows you to “flash” the database back to a certain point in time, assuming you have the flashback logs covering the period you want to flashback to. While I was not keen on using this in production (too new & maybe too much overhead) I thought it seemed ideal for our testing & beta environments so that changes could be tested and then the test database flashed back to a consistent point. We had a reason for using this quite recently and it was not a happy experience. To setup flashback database is quite straightforward:

SQL> alter system set db_recovery_file_dest_size = SPACE;

SQL> alter system set db_recovery_file_dest = 'your flashback destination';

SQL> alter system set db_flashback_retention_target = 86400;

Where SPACE is an amount of space you want to allocate in your flashback destination - which is a filesystem/raw partition/ASM diskgroup. The retention target is the time in seconds that you want to be able to flash back to.

You now need to have the database in the mount state and run the following:

SQL> alter database flashback on;

You are now good to go and can open the database. You can check you have flashback enabled by running the following:

SQL> select flashback_on from v$database;

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

So there came a time when we actually wanted to flash back. I was using the following query to determine how far back the database could go:

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
916940214              27-DEC-06      172800     7368949760           3064135680

So this looked good and seemed to indicate we could flashback several months. So we tried going back a couple of weeks:

SQL> flashback database to timestamp to_date('01/02/2007 00:01:01', 'DD/MM/YYYY HH24:MI:SS');

flashback database to timestamp to_date('01/02/2007 00:01:01', 'DD/MM/YYYY HH24:MI:SS')

*

ERROR at line 1:

ORA-38726: Flashback database logging is not on.

We checked the flashback status from v$database again - we had not looked at this for a while:

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

It was at this point we realised that at some point earlier we had created a guaranteed restore point. To confuse things even further this restore point was created just before upgrading to 10.2.0.3 from 10.2.0.2 and we wanted to flashback to before the upgrade. You can check what restore points you have set with the following:

SQL> select name, scn, time, guarantee_flashback_database from v$restore_point;

NAME                SCN          TIME       GUA
--------------------------------------------------
BEFORE_UPGRADE      922353434    12-Feb-07   YES

We could attempt to flashback to the restore point but an open resetlogs failed.

We have now tested on both 10.2.0.2 and 10.2.0.3 and it seems like creating a guaranteed restore point DOES NOT set the flashback_on column to restore point only - it always remains at yes. The only thing I can conclude is that doing the upgrade screwed with the flashback database - I guess you can’t use flashback to undo a database upgrade! Beware though that with the guaranteed restore point, in theory the flash recovery area will keep filling up and could eventually cause you to run out of space - you can use the drop restore point command to get rid of your guaranteed restore points.