Pythian Blog: Technical Track

How to manage Oracle Flashback Log size

by Franky Faust on Oct 3, 2019 12:00:00 AM

Here we will learn how to manage the size of flashback logs generated in our environment. This is useful especially when you are hitting the error "ORA-00242: maximum allowed filename records used up in control file" which can be related to the bug 12661855 that you can find in My Oracle Support. There are ways to work around that bug other than the way presented here, but please do yourself a favor and read that note before applying any changes.

Please remember that in order to use this feature (flashback database) you must have Enterprise Edition licensed. 

First of all, we have to change the database to flashback mode on (remember that in order to have your database in flashback mode you must already have it in archive log mode and the FRA must be also set):

SQL> select name, open_mode, log_mode, flashback_on from v$database;
 
 NAME  OPEN_MODE  LOG_MODE FLASHBACK_ON
 --------- -------------------- ------------ ------------------
 CDB2  READ WRITE  ARCHIVELOG NO
 
 SQL> alter database flashback on;
 
 Database altered.
Checking the database alert.log we will see that the RVWR process, responsible for managing the flashback logs, was started: 
2018-03-18T17:05:40.963600+01:00
 alter database flashback on
 Starting background process RVWR
 2018-03-18T17:05:40.986988+01:00
 RVWR started with pid=63, OS id=6619
 2018-03-18T17:05:42.678759+01:00
 Allocated 15937344 bytes in shared pool for flashback generation buffer
 Flashback Database Enabled at SCN 2185106
 Completed: alter database flashback on
And our database turned flashback on: 
SQL> select name, open_mode, log_mode, flashback_on from v$database;
 
 NAME  OPEN_MODE  LOG_MODE FLASHBACK_ON
 --------- -------------------- ------------ ------------------
 CDB2  READ WRITE  ARCHIVELOG YES

Changing Flashback logs size

As we can see we have a 200MB flashback log: 
SQL> SELECT log# as "Log No", thread# as "Thread No", sequence# as "Seq No", name, bytes/1024/1024 as "Size(MB)", first_change# as "First Chg No", first_time from v$flashback_database_logfile order by first_time;
 
  Log No Thread No  Seq No NAME  Size(MB) First Chg No FIRST_TIME
 ---------- ---------- ---------- ---------------------------------------------------------------------- ---------- ------------ -----------------
 1  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb  200 2185105 03/18/18 17:05:42
 2  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb  200  0
 
 2 rows selected.
After running some activity in the database now we can see 7 flashback logs: 
SQL> SELECT log# as "Log No", thread# as "Thread No", sequence# as "Seq No", name, bytes/1024/1024 as "Size(MB)", first_change# as "First Chg No", first_time from v$flashback_database_logfile order by first_time;
 
  Log No Thread No  Seq No NAME  Size(MB) First Chg No FIRST_TIME
 ---------- ---------- ---------- ------------------------------------------------------------------------------------------ ---------- ------------ -----------------
 1  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb  200  2185105 03/18/18 17:05:42
 2  1  2 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb  200  2187758 03/18/18 17:32:58
 3  1  3 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx55wtr_.flb  200  2188056 03/18/18 17:35:25
 4  1  4 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5bh4s_.flb  200  2188418 03/18/18 17:39:01
 5  1  5 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5k6ns_.flb  200  2189289 03/18/18 17:42:04
 6  1  6 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5px1t_.flb  200  2189598 03/18/18 17:42:46
 7  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5r6sd_.flb  200  0
 
 7 rows selected.
If we look closer the flashback log size is the same as redo log size: 
SQL> select group#, bytes/1024/1024 size_mb from v$log;
 
  GROUP# SIZE_MB
 ---------- ----------
 1  200
 2  200
 3  200
 
 3 rows selected.
So let's change this. To change the flashback logs size we must change the hidden parameter "_db_flashback_log_min_size" which is set in bytes. Let us change this to 500MB: 
SQL> alter system set "_db_flashback_log_min_size"=524288000;
 
 System altered.
I have the same workload still running, so let's check again: 
SQL> SELECT log# as "Log No", thread# as "Thread No", sequence# as "Seq No", name, bytes/1024/1024 as "Size(MB)", first_change# as "First Chg No", first_time from v$flashback_database_logfile order by first_time;
 
  Log No Thread No  Seq No NAME  Size(MB) First Chg No FIRST_TIME
 ---------- ---------- ---------- ---------------------------------------------------------------------- ---------- ------------ -----------------
 1  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb  200 2185105 03/18/18 17:05:42
 2  1  2 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb  200 2187758 03/18/18 17:32:58
 3  1  3 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx55wtr_.flb  200 2188056 03/18/18 17:35:25
 4  1  4 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5bh4s_.flb  200 2188418 03/18/18 17:39:01
 5  1  5 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5k6ns_.flb  200 2189289 03/18/18 17:42:04
 6  1  6 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5px1t_.flb  200 2189598 03/18/18 17:42:46
 7  1  7 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5r6sd_.flb  200 2191323 03/18/18 17:43:29
 8  1  8 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5smmv_.flb  200 2214833 03/18/18 17:54:51
 9  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx6gxjt_.flb  400  0
 
 9 rows selected.
In this past sample, we see a 400MB flashback log. That was created in the transition to the new size, but all the next ones will be 500MB in size: 
SQL> SELECT log# as "Log No", thread# as "Thread No", sequence# as "Seq No", name, bytes/1024/1024 as "Size(MB)", first_change# as "First Chg No", first_time from v$flashback_database_logfile order by first_time;
 
  Log No Thread No  Seq No NAME  Size(MB) First Chg No FIRST_TIME
 ---------- ---------- ---------- ---------------------------------------------------------------------- ---------- ------------ -----------------
 1  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb  200 2185105 03/18/18 17:05:42
 2  1  2 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb  200 2187758 03/18/18 17:32:58
 3  1  3 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx55wtr_.flb  200 2188056 03/18/18 17:35:25
 4  1  4 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5bh4s_.flb  200 2188418 03/18/18 17:39:01
 5  1  5 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5k6ns_.flb  200 2189289 03/18/18 17:42:04
 6  1  6 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5px1t_.flb  200 2189598 03/18/18 17:42:46
 7  1  7 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5r6sd_.flb  200 2191323 03/18/18 17:43:29
 8  1  8 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5smmv_.flb  200 2214833 03/18/18 17:54:51
 9  1  9 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx6gxjt_.flb  400 2420878 03/18/18 18:04:34
 10  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7149f_.flb  500  0
 
 10 rows selected.
Checking one more time: 
SQL> SELECT log# as "Log No", thread# as "Thread No", sequence# as "Seq No", name, bytes/1024/1024 as "Size(MB)", first_change# as "First Chg No", first_time from v$flashback_database_logfile order by first_time;
 
  Log No Thread No  Seq No NAME  Size(MB) First Chg No FIRST_TIME
 ---------- ---------- ---------- ---------------------------------------------------------------------- ---------- ------------ -----------------
 1  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb  200 2185105 03/18/18 17:05:42
 2  1  2 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb  200 2187758 03/18/18 17:32:58
 3  1  3 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx55wtr_.flb  200 2188056 03/18/18 17:35:25
 4  1  4 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5bh4s_.flb  200 2188418 03/18/18 17:39:01
 5  1  5 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5k6ns_.flb  200 2189289 03/18/18 17:42:04
 6  1  6 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5px1t_.flb  200 2189598 03/18/18 17:42:46
 7  1  7 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5r6sd_.flb  200 2191323 03/18/18 17:43:29
 8  1  8 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5smmv_.flb  200 2214833 03/18/18 17:54:51
 9  1  9 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx6gxjt_.flb  400 2420878 03/18/18 18:04:34
 10  1  10 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7149f_.flb  500 2486224 03/18/18 18:06:51
 11  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx75f0m_.flb  500  0
 
 11 rows selected.
Cool! It is working as expected.

Pre-allocating the space for the Flashback logs

I want to show one more thing... we can pre-allocate the total flashback size we expect to have. Let's say we estimate we will have 10GB of flashback logs, then we can pre-allocate this space in order to optimize performance when we first enable flashback in our database so Oracle will not wait to allocate this space. Instead, it will allocate all at once. We only have to change the hidden parameter "_db_flashback_log_min_total_space" to the desired size: 
SQL> alter system set "_db_flashback_log_min_total_space"=10G;
 
 System altered.
Oracle will start to create new flashback log files until it reaches the specified size in the parameter: 
SQL> SELECT log# as "Log No", thread# as "Thread No", sequence# as "Seq No", name, bytes/1024/1024 as "Size(MB)", first_change# as "First Chg No", first_time from v$flashback_database_logfile order by first_time, log#;
 
  Log No Thread No  Seq No NAME  Size(MB) First Chg No FIRST_TIME
 ---------- ---------- ---------- ---------------------------------------------------------------------- ---------- ------------ -----------------
 1  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb  200 2185105 03/18/18 17:05:42
 2  1  2 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb  200 2187758 03/18/18 17:32:58
 3  1  3 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx55wtr_.flb  200 2188056 03/18/18 17:35:25
 4  1  4 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5bh4s_.flb  200 2188418 03/18/18 17:39:01
 5  1  5 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5k6ns_.flb  200 2189289 03/18/18 17:42:04
 6  1  6 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5px1t_.flb  200 2189598 03/18/18 17:42:46
 7  1  7 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5r6sd_.flb  200 2191323 03/18/18 17:43:29
 8  1  8 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx5smmv_.flb  200 2214833 03/18/18 17:54:51
 9  1  9 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx6gxjt_.flb  400 2420878 03/18/18 18:04:34
 10  1  10 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7149f_.flb  500 2486224 03/18/18 18:06:51
 11  1  11 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx75f0m_.flb  500 2666863 03/18/18 18:13:14
 12  1  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7jlo5_.flb  500  0
 13  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7jx9l_.flb  500  0
 14  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7k4rr_.flb  500  0
 15  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7kh1d_.flb  500  0
 16  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7kpdw_.flb  500  0
 17  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7kxo0_.flb  500  0
 18  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7l4w5_.flb  500  0
 19  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7lfgr_.flb  500  0
 20  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7lnok_.flb  500  0
 21  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7lvxp_.flb  500  0
 22  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7m366_.flb  500  0
 23  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7mbg1_.flb  500  0
 24  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7mksf_.flb  500  0
 25  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7ms2r_.flb  500  0
 26  0  1 /dt01/oracle/fra/CDB2/flashback/o1_mf_fbx7n0b6_.flb  500  0
 
 26 rows selected.
If we sum up all the flashback logs, we will have a total of 10GB. That is what I have for today! I hope you enjoyed this.
Topics: Oracle Big Data Technical Track Vertica

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.