Hi fellows, here we will see how to manage the size of flashback logs generated in our environment. This is useful specially 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. This is not the only way to work around that bug, so do your self a favor and read that note before applying any changes. 🙂
Here I will show how to do this only for testing and learning purpose. Please remember that in order to use this feature (flashback database) you must have Enterprise Edition licensed.
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.
If we check 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 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb 200 2185105 03/18/18 17:05:42
2 1 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb 200 0
2 rows selected.
I will run a workload here to generate more flashback logs so we can compare and manage them properly.
Checking again we have now 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 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb 200 2185105 03/18/18 17:05:42
2 1 2 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb 200 2187758 03/18/18 17:32:58
3 1 3 /home/oracle/fra/CDB2/flashback/o1_mf_fbx55wtr_.flb 200 2188056 03/18/18 17:35:25
4 1 4 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5bh4s_.flb 200 2188418 03/18/18 17:39:01
5 1 5 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5k6ns_.flb 200 2189289 03/18/18 17:42:04
6 1 6 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5px1t_.flb 200 2189598 03/18/18 17:42:46
7 1 1 /home/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 us change this. To change the flashback logs size we must change the undocumented 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 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb 200 2185105 03/18/18 17:05:42
2 1 2 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb 200 2187758 03/18/18 17:32:58
3 1 3 /home/oracle/fra/CDB2/flashback/o1_mf_fbx55wtr_.flb 200 2188056 03/18/18 17:35:25
4 1 4 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5bh4s_.flb 200 2188418 03/18/18 17:39:01
5 1 5 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5k6ns_.flb 200 2189289 03/18/18 17:42:04
6 1 6 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5px1t_.flb 200 2189598 03/18/18 17:42:46
7 1 7 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5r6sd_.flb 200 2191323 03/18/18 17:43:29
8 1 8 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5smmv_.flb 200 2214833 03/18/18 17:54:51
9 1 1 /home/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 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb 200 2185105 03/18/18 17:05:42
2 1 2 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb 200 2187758 03/18/18 17:32:58
3 1 3 /home/oracle/fra/CDB2/flashback/o1_mf_fbx55wtr_.flb 200 2188056 03/18/18 17:35:25
4 1 4 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5bh4s_.flb 200 2188418 03/18/18 17:39:01
5 1 5 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5k6ns_.flb 200 2189289 03/18/18 17:42:04
6 1 6 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5px1t_.flb 200 2189598 03/18/18 17:42:46
7 1 7 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5r6sd_.flb 200 2191323 03/18/18 17:43:29
8 1 8 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5smmv_.flb 200 2214833 03/18/18 17:54:51
9 1 9 /home/oracle/fra/CDB2/flashback/o1_mf_fbx6gxjt_.flb 400 2420878 03/18/18 18:04:34
10 1 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7149f_.flb 500 0
10 rows selected.
Checking just 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 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb 200 2185105 03/18/18 17:05:42
2 1 2 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb 200 2187758 03/18/18 17:32:58
3 1 3 /home/oracle/fra/CDB2/flashback/o1_mf_fbx55wtr_.flb 200 2188056 03/18/18 17:35:25
4 1 4 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5bh4s_.flb 200 2188418 03/18/18 17:39:01
5 1 5 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5k6ns_.flb 200 2189289 03/18/18 17:42:04
6 1 6 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5px1t_.flb 200 2189598 03/18/18 17:42:46
7 1 7 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5r6sd_.flb 200 2191323 03/18/18 17:43:29
8 1 8 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5smmv_.flb 200 2214833 03/18/18 17:54:51
9 1 9 /home/oracle/fra/CDB2/flashback/o1_mf_fbx6gxjt_.flb 400 2420878 03/18/18 18:04:34
10 1 10 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7149f_.flb 500 2486224 03/18/18 18:06:51
11 1 1 /home/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
Ok, one more thing I want to show is that 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, it will allocate all at once.
We only have to change the undocumented 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 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lnz4_.flb 200 2185105 03/18/18 17:05:42
2 1 2 /home/oracle/fra/CDB2/flashback/o1_mf_fbx3lro0_.flb 200 2187758 03/18/18 17:32:58
3 1 3 /home/oracle/fra/CDB2/flashback/o1_mf_fbx55wtr_.flb 200 2188056 03/18/18 17:35:25
4 1 4 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5bh4s_.flb 200 2188418 03/18/18 17:39:01
5 1 5 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5k6ns_.flb 200 2189289 03/18/18 17:42:04
6 1 6 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5px1t_.flb 200 2189598 03/18/18 17:42:46
7 1 7 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5r6sd_.flb 200 2191323 03/18/18 17:43:29
8 1 8 /home/oracle/fra/CDB2/flashback/o1_mf_fbx5smmv_.flb 200 2214833 03/18/18 17:54:51
9 1 9 /home/oracle/fra/CDB2/flashback/o1_mf_fbx6gxjt_.flb 400 2420878 03/18/18 18:04:34
10 1 10 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7149f_.flb 500 2486224 03/18/18 18:06:51
11 1 11 /home/oracle/fra/CDB2/flashback/o1_mf_fbx75f0m_.flb 500 2666863 03/18/18 18:13:14
12 1 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7jlo5_.flb 500 0
13 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7jx9l_.flb 500 0
14 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7k4rr_.flb 500 0
15 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7kh1d_.flb 500 0
16 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7kpdw_.flb 500 0
17 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7kxo0_.flb 500 0
18 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7l4w5_.flb 500 0
19 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7lfgr_.flb 500 0
20 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7lnok_.flb 500 0
21 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7lvxp_.flb 500 0
22 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7m366_.flb 500 0
23 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7mbg1_.flb 500 0
24 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7mksf_.flb 500 0
25 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7ms2r_.flb 500 0
26 0 1 /home/oracle/fra/CDB2/flashback/o1_mf_fbx7n0b6_.flb 500 0
26 rows selected.
If we sum all the flashback logs we will have the total of 10GB.
That is what I have for today! Hope you enjoyed.
Franky