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';

 

 

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

One thought on “Oracle Flashback Architecture

Leave a Reply

Your email address will not be published. Required fields are marked *