Oracle Flashback Database explained and limitation

This post talks about What is Flashback Database in Oracle, How a flashback database works,How to configure flashback database

What is Flashback Database

• A new feature from 10.1 onwards, Uses past block images to back out changes to an oracle database
• During normal database operation, Oracle occasionally logs past block images in flashback logs
• Flashback logs are  written sequentially not archived
• Oracle automatically creates, resizes and deletes flashback logs in the flash recovery area
• DBA should be aware of flashback logs to monitor performance, to decide how much space to allocate to the flash recovery area
• Allows database to be recovered to a previous time to correct problems caused by logical data corruptions, user errors

How flashback database works

• Before images are used to restore the database to a point in the past
• Forward recovery is then used to bring the database to a consistent state
• So it both used the flashback logs and archive logs. Both should be available to flashback the database.
• past block images are created using the new process RVWR background process

How to configure flashback database

(a)Archiving must be enabled
(b)Flash recovery area must be configured using
DB_RECOVERY_FILE_DEST_SIZE – the size of flashback recovery area in bytes
DB_RECOVERY_FILE_DEST – location of the flashback recovery area

(c)Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:

SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;

(d) Set the Flashback Database retention target. The below query set it to 1 day

alter system set DB_FLASHBACK_RETENTION_TARGET=1440 scope=both;

(e) Check the status of Flashback

SQL> select flashback_on from  v$database;

(f)To disable Flashback Database, issue the following command

SQL> ALTER DATABASE FLASHBACK OFF;

(g)Monitor logging in the Flashback Database logs:

SQL> select begin_time, flashback_data,
db_data, redo_data, ESTIMATED_FLASHBACK_SIZE
from   v$flashback_database_stat;

(h)Monitor the Flashback Database retention target:

SQL> select * from   v$flashback_database_log;

Architecture of Flashback Database

(a)Flashback uses a flashback generation buffer
The size of the generation buffer is recorded in V$SGASTAT
Size is determined by _flashback_generation_buffer_size defaults to 4194304
(b) Flashback uses the recovery writer (RVWR) background process to copy flashback blocks from flashback generation buffer to flashback logs
(c)All blocks are logged when they first become dirty including data, indexes, undo, segment headers, bitmaps
(d)  Subsequent changes not necessarily logged
(e) Amount of flashback redo log generated roughly equivalent to the value of physical writes statistics
(f) Very similar design to LGWR  ie. Changes initially written to a memory buffer and RVWR subsequently flush flashback records to disk

Some important notes about Flashback
(1) With 10gR2, Flashback database across resetlogs
(2) With 10gR2, Flashback database across physical standby activation
(3) With 10gR2, Flashback database works across switch over or failover of physical and logical database
(4) With 10gR2, New feature restore point is created which is a convenient way to undo changes.

Internal working of Flashback database

• Oracle does not record before images of changed blocks in flashback logs after every change. It does record the before images but it at different intervals so the database can flashback to the limit said in the init ora file.
• Flashback database uses smartly both the flashback logs and archive logs to perform the flashback.
• When we execute the command Flashback database, Oracle first looks out for the before images of the block in the flashback logs. It will replace the changed block from images in the flashback logs. After that, it uses an archive log to recover the database to the point of flashback. So you will always find like this alert log
• So steps internally (1) Apply the flashback logs (2) Apply the archive logs to recover to the point of the flashback

Related Articles

how to check flashback retention period in oracle
Recover drop/truncate/delete table on primary using flashback on standby database
how to Flashback when we have dataguard
Flashback PDB in Oracle Database 12c Release 2
Oracle Flashback query