Pickleball spielen

In this last part about Flashback, we will focus on flashback database, which is again based on a completely different architecture than the other two kinds of flashback discussed before. The main difference compared to the other flashback types is, that you move your entire database back to a point in the past, with exactly the same outcome as you were able to achieve long before version 10g with a Database Point In Time Recovery (DBPITR), but with a much faster (and even easier) method. But before you can take advantage of this much faster DBPITR, you must invest in form of writing flashback logs, which is not done by default. You may turn on flashback logging as follows:

SQL> select flashback_on from v$database;

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

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

Total System Global Area  373293056 bytes
Fixed Size                  1219496 bytes
Variable Size             134218840 bytes
Database Buffers          234881024 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.

SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select flashback_on from v$database;

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

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 2G
SQL> alter system set db_recovery_file_dest_size=10g;

System altered.

Please notice that you must be in Archive Mode before turning on flashback logging and that the flashback logs are being written into the Flash-Recovery-Area as well as the Archive Logs. You may need to increase the amount of space reserved in the Flash-Recovery-Area for your database, since the default reserved size of 2G is very small.

The amount of space needed for your flashback logs is based on two factors: The frequency in which blocks are being modified and the point in time, that you want to be able to flashback your database to. The latter is defined by the dynamic parameter db_flashback_retention_target. Also, you can see an estimation of the amount of space needed for your flashback logs with the dynamic performance view v$flashback_database_log:

SQL> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

SQL> select ESTIMATED_FLASHBACK_SIZE/1024/1024 as mb from v$flashback_database_log;

        MB
----------
157.289063

SQL> alter system set db_flashback_retention_target=60 -- unit is minutes ;

System altered.

SQL> select ESTIMATED_FLASHBACK_SIZE/1024/1024 as mb from v$flashback_database_log;

        MB
----------
   6.46875

Now let us assume, that you are just about doing some critical operation with your database, for example an application upgrade that changes contents of many tables. You want to be able to undo these changes if necessary as quick as possible on the database level. That is why you turned on flashback as described above. Additionally, you may create a restore point now (a label associated with the current SCN and stored in the Database Controlfile), to flashback to instead of having to provide a timestamp or even a SCN. The scenario might go like this:

SQL> create restore point before_critical_op;

Restore point created.

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
-------------------------------------------------
NAME
-------------------------------------------------
    618103                     2 NO             0
10-MAR-09 08.36.29.000000000 AM
BEFORE_CRITICAL_OP

SQL> drop user hr cascade -- oops! Mistake... ;

User dropped.

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

Total System Global Area  373293056 bytes
Fixed Size                  1219496 bytes
Variable Size             138413144 bytes
Database Buffers          230686720 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> flashback database to restore point before_critical_op;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select username from dba_users where username='HR';

USERNAME
------------------------------
HR

SQL> drop restore point  before_critical_op;

Restore point dropped.

Please notice that the flashback database is done in Status mount and ends with an open resetlogs as both would be the case with a regular DBPITR. But we are way faster now, because we do not have to do a complete restore of all our datafiles from backup! In fact, what has happend under the covers during this operation was a restore of older block versions from the flashback logs over the current blocks in the datafiles that were too new for the point in the past, we wanted the database to be flashed back to.

Because not every block change leads to the writing of that block to the flashback logs (that would not be performant), there is still something to be actualized to reach the exact point in the past. This is done internally in form of recovery with redo protocol from archive logs or online logs, which is why you can only use flashback database together with archiving.