How to manage Oracle Flashback Log size
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.
