Playing Around with “Flashback Database”

“Flashback Database” is a new feature available in 10g databases and it can be used to rewind the content of the database to a previous moment in time. The key elements of this feature are:

• flashback logs
• restore points

By default the database runs with the “flashback database” feature disabled. I can always find out if this feature is activated or not by querying the V$DATABASE view.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO

There are some important requirements which must be fulfilled in order to start using this feature:

• a “Flash Recovery Area” must be setup;
• the database must run in ARCHIVELOG mode;

To enable flashback database the following steps should be followed:

1.clean shutdown of the database:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2.startup the database in mount state:

SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size      1218868 bytes
Variable Size     79693516 bytes
Database Buffers   180355072 bytes
Redo Buffers      2973696 bytes
Database mounted.

3. enable the flashback database feature:

SQL> alter database flashback on;

Database altered.

4.open the database:

SQL> alter database open;

Database altered.

Now, that the “Flashback Database” is enabled I can monitor and estimate the size of flashback logs by using the V$FLASHBACK_DATABASE_LOG and V$FLASH_RECOVERY_AREA_USAGE views. I can perform some massive updates/inserts and to query those two views afterwards to see how the effective and estimate flashback log sizes are affected.

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
            624609 19-DEC-06             1440        8192000                283582464

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                 .33                         0               1
ONLINELOG                  7.32                         0               3
ARCHIVELOG                  .61                         0               1
BACKUPPIECE               77.72                     25.84               8
IMAGECOPY                     0                         0               0
FLASHBACKLOG                .38                         0               1

6 rows selected.

The above listing reveals that currently I have a flashback log file of ~8M and the estimated flashback log size is ~270M.
OK, lets see this feature in action. I am going to create a restore point, to perform some inserts and to flashback the database to the restore point.

SQL> create restore point before_update;

Restore point created.

SQL> create table alek.fback_test as select * from all_objects;

Table created.

SQL> select count(1) from alek.fback_test;

COUNT(1)
----------
   49337

Now, I want to rewind the database back to the restore point. This will imply a shutdown, a startup in mount state, the corresponding FLASHBACK command and the opening of the database with the RESETLOGS clause.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> flashback database to restore point before_update;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(1) from alek.fback_test;
select count(1) from alek.fback_test
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

Everything went just perfect! The database was restored as it was when the restore point was defined. However, if my business requirements decide the need to restore the database to the defined restore point only and not between now and the time the restore point was defined then a better approach is to use guaranteed restore points with flashback database disabled. In this case the amount of generated flashback logs is smaller and the performance of an application which often modifies data is improved.
So, if I am going to use the above approach the first thing to be done is to disable the flashback database feature. This can be done with the database open.

SQL> alter database flashback off;

Database altered.

However, switching back is not possible when the database is in the OPEN state:

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

Another important thing to notice is that I cannot create the first guaranteed restore point with the database open. If trying to do so I'll get an error message as shown below:

SQL> create restore point before_delivery guarantee flashback database;
create restore point before_delivery guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_DELIVERY'.
ORA-38787: Creating the first guaranteed restore point requires mount mode 
when flashback database is off.

The error message is quite self explanatory therefore the next step would be to cleanly shutdown the database and to start it in the mount state.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> create restore point before_delivery guarantee flashback database;

Restore point created.

SQL> alter database open;

Database altered.

Now, lets simulate the same scenario as we did with the flashback database feature enabled.

SQL> create table alek.fback_test as select * from all_objects;

Table created.

SQL> select count(1) from alek.fback_test;

COUNT(1)
----------
   49337

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> flashback database to restore point before_delivery;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(1) from alek.fback_test;
select count(1) from alek.fback_test
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

Lovely, it works as expected! Now, I'm wondering what happen with my guaranteed restore point.

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

NAME                           TIME                                     GUA
------------------------------ ---------------------------------------- ---
BEFORE_DELIVERY                19-DEC-06 05.01.55.000000000 PM          YES
BEFORE_UPDATE                  19-DEC-06 03.54.19.000000000 PM          NO

It is still there. The guaranteed restore points are never automatically age out therefore the only option I have in order to delete it is to use the DROP RESTORE POINT command.

SQL> drop restore point before_delivery;

Restore point dropped.

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

NAME                           TIME                                     GUA
------------------------------ ---------------------------------------- ---
BEFORE_UPDATE                  19-DEC-06 03.54.19.000000000 PM          NO

Now, the ugly part of this is that I've end up with the previous situation when no guaranteed restore points were defined and the first one must be always created with the database started in the mount state.

SQL> create restore point before_delivery guarantee flashback database;
create restore point before_delivery guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_DELIVERY'.
ORA-38787: Creating the first guaranteed restore point requires mount mode 
when flashback database is off.

The workaround would be to create a second guaranteed restore point and to delete the previous one so that at least one guaranteed restore point to be defined. However, the drawback of this is that I have to maintain this guaranteed restore point if my goal is to maximize the availability of the database.

After the evaluation of this feature I can say what I like and what I don't like about it:

I like:

• the straightforward way of configuring and using it;
• the possibility to estimate the size of flashback logs through the V$FLASHBACK_DATABASE_LOG view;
• much easier to use than the classical database incomplete recovery;

I don't like:

• the potential performance issues on a productive system which performs a lot of changes in the underlying data.
• the fact that i cannot use it to rewind the content of just one or several tablespaces. If the database hosts more than one application then all of them will be affected therefore the remaining option will still remain the TPITR. The “Flashback Database” feature is closer to the “Database incomplete recovery” than the TPITR feature.
• From my point of view this feature suits well into test and/or developing environments were the likelihood of making mistakes is greater in comparison with the production environment which is supposed to be used in a restrictive and controlled way. The main benefit of using it in production systems could be seen from the application deployment strategy perspective. Before running an update script as part of the application delivery process the DBA can define a guaranteed restore point and if something nasty happens within that script then he/she can easily rewind the database as it was at the time the restore point was defined. However, the fact that the database should be shutdown doesn't sound very well to me as precious cached data from the SGA will be lost.