How to manage Oracle Flashback Log size

Posted in: Oracle, Technical Track

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.

flashback logs

Flashback architecture overview

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.

 

 

email

Interested in working with Franky? Schedule a tech call.

About the Author

Senior Oracle Database Consultant
Franky works for Pythian as Senior Oracle Database Consultant. He has extensive knowledge in Oracle Exadata and High Availability technologies and in other databases like MySQL, Cassandra and SQL Server. He is always improving his skills focusing on researching Oracle performance and HA. Franky has been involved in some major implementations of multinode RAC in AIX, Linux and Exadata and multisite DataGuard environments. The guy is OCP 12c, OCE SQL, OCA 11g, OCS Linux 6 and RAC 12c and was nominated Oracle ACE in 2017. He is well known in the Brazilian community for his blog http://loredata.com.br/blog and for all the contribution he provides to the Oracle Brazilian community. Franky is also a frequent writer for OTN and speaker at some Oracle and database conferences around the world. Feel free to contact him in social media.

No comments

Leave a Reply

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