Oracle Flashback Architecture

Hi,

In this article I will explain Flashback technology which is one of the perfect features of Oracle.

Flashback technology has introduced with oracle 10g. With this technology, data losses are tried to be prevented. With Flashback, we can view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. And we can query them in read only mode.

Oracle Flashback types are as follows.

• Flashback Database
• Flashback Table
• Flashback Query
• Flashback Version Query
• Flashback Transaction Query
• Flashback Data Archive

In order to use Flashback technology, our database must be in archivelog mode. We can find out whether our database is in archivelog mode with the following query.

Select log_mode from v$database;

If the result of the query is archivelog, our database is in archive mode. If noarchivelog exits, our archive mode is disabled. The following steps should be performed to convert database to archivelog mode..

Shutdown immediate;

Startup mount;

Alter database ARCHIVELOG;

Alter database open;

If we want to use flashback database technology we need to change the flashback_on parameter as Yes. You can find out the value of the parameter with following query.

Select flashback_on from v$database;

If Result is No, you need to execute following query in mount mode to change it to yes.

Alter database flashback ON;

You need to run this command in mount mode at Oracle 10g. But with 11g, it can be run in open mode.

Flashback parameter can take 3 kinds of values. These are yes, no and restore point only. Restore point only is mostly used in snapshot databases. We can determine the size of the fast recovery area where the flashback logs are kept by the following formula.

FRA=(REDO + FB_BUFFER) * FB_RETENTION + ARCH_ON_DISK + ARCH_BUFFER

REDO: 1 day Redo size

FB_RETENTION: Flashback retention for daily.

FB_BUFFER: Daily redo * %20

ARCH_ON_DISK: Daily Archivelog Size * how many logs are kept

ARCH_BUFFER: Daily Archivelog Size

You cand find this approximate size with the following query.

Select start_Date,

sum(   Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2)) AS Mbytes

 FROM (SELECT To_Char(Vlh.First_Time,

 'YYYY-MM-DD') AS Start_Date,

 To_Char(Vlh.First_Time,

 'HH24') || ':00' AS Start_Time,

 COUNT(Vlh.Thread#) Num_Logs

 FROM V$log_History Vlh

 GROUP BY To_Char(Vlh.First_Time,

 'YYYY-MM-DD'),

 To_Char(Vlh.First_Time,

 'HH24') || ':00') Log_Hist,

 V$log Vl,

 V$database Vdb

 WHERE Vl.Group# = 1

 GROUP BY start_date

 ORDER BY start_date

The sample output is as follows.

START_DATE     MBYTES
---------- ----------
2010-01-19      311002010-01-20      361002010-01-21      16500

if you need to calculate as an example

REDO: it is generated max 108G Redo.

FB_RETENTION: 7 days

FB_BUFFER: 108 * %20=22G

ARCH_ON_DISK=120G * 2 = 240G




FRA = (108G + 22G) * 7 + 240G + 240G = 1271G.

Let’s look at flashback types are using.

Flashback Database -> It uses flashback logs. The flashback retention parameter is important for this type.

Flashback Drop -> Recycle bin is used. Recycle bin size is important for this type.

Flashback Table -> It is related to undo size because it is related with historical data. For this, undo retention parameter is important.

Flashback Query -> Undo parameter is also important because it is related also historical data.

Flashback query is available for all editions. Other features (flashback table, flashback database, flashback transaction query) are valid only for enterprise edition.

In addition, flashback data archive feature requires an oracle advanced compression license for database before 11.2.0.4. There is no such license issue for Oracle 11.2.0.4 and later.

 Flashback Parameters

DB_FLASHBACK_RETENTION_TARGET: It is main parameter that Specifies upper limit (in minutes) on how far back in time the database may be flashed back. We can learn actual value with the following command.

Show parameter db_Flashback_Retention_Target

Or

Select name,value from v$parameter

where name like '%db_flashback_retention_target%';

To change the parameter, execute the following command.

Alter system set db_flashback_retention_target=9000;

With this parameter, we have a time of 9000 min namely 15 hours for flashed back.

DB_RECOVERY_FILE_DEST_SIZE: It specifies (in bytes)  hard limit on the total space to be used by target database recovery files created in the flash recovery area. You can find out actual value with the following command.

Show parameter Db_Recover_File_Dest_Size

Or

Select name,value from v$parameter

where name like '%db_recovery_file_dest_size%';

To change the parameter, execute the following command.

Alter system set db_recovery_file_dest_size=50G;

DB_RECOVERY_FILE_DEST: It specifies default location of the flash recovery area. You can find out actual value with the following command

show parameter DB_RECOVERY_FILE_DEST

Or

Select name,value from v$parameter

where name like '% Db_Recovery_File_Dest %';

To change the parameter, execute the following command.

Alter system set db_recovery_file_dest='/u01/oracle/recovery';

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/