Site icon IT Tutorial

Oracle Flashback Architecture

Mehmet Salih Deveci

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.

 

 

 

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/

	


	






		

			
				Exit mobile version