Writings on flashback log size

As you know, Flashback Database uses its own type of log files, they are usually called Flashback log files. These files are created when Oracle loads images of before modified data blocks into the flashback buffer and then RVWR (Recovery Writer) has the job to write flashback buffer to flashback log files on disk. It is an asynchronous process and you have very little control over it. Still, you can investigate how things work.

One of the things that DBA can’t control _directly_ is the size of these Flashback log files. By default they are almost the size of your redo log files and this makes perfect sense since the nature of redo logs and flashback logs. So what is forcing Oracle to create files the same size of the redo log files? The answer is the hidden parameter “_flashback_size_based_on_redo” that is set by default to TRUE.

Let’s make visual confirmation, generating some DML that will modify blocks:


SQL> select group#, bytes/1024 KB from v$log;

    GROUP#	   KB
---------- ----------
	 1	51200
	 2	51200
	 3	51200

SQL>select log#, bytes/1024 as KB from V$FLASHBACK_DATABASE_LOGFILE;

      LOG#	   KB
---------- ----------
	 1	51200
	 2	51200
	 3	51200

That is the easy way to check flashback log size, but you can dump the flashback log file and check how many blocks were allocated to flashback log:


SQL> alter system dump flashback logfile 1 logical

DUMP OF FLASHBACK LOG FILE (1) +DATA/BACODB/FLASHBACK/log_1.278.826239255
 FILE HEADER:
        Compatibility Vsn = 202375168=0xc100000
        Db ID=2672868488=0x9f50bc88, Db Name='BACODB'
        Activation ID=2672897670=0x9f512e86
        Control Seq=2099=0x833, File size=6400=0x1900
        File Number=1, Blksiz=8192, File Type=8 FLASH BACK

Since database block size is 8kb and the File Size is 6400 blocks, the math shows 51200 Kb, exactly the same as v$flashback_database_logfile.
Back to the hidden parameter _flashback_size_based_on_redo, which says that flashback log files are no more, no less than the size of your redo log files. So let’s turn it off and see what happens because we can 🙂

SQL> alter system set "_flashback_size_based_on_redo"=FALSE scope=spfile;
System altered.

Shutdown and startup your instance, generate some more heavy DML and check your V$FLASHBACK_DATABASE_LOGFILE again.

SQL> select log#, bytes, bytes/1024 as KB from V$FLASHBACK_DATABASE_LOGFILE;

      LOG#	BYTES	      KB
---------- ---------- ----------
	 1   52428800	   51200
	 2   52428800	   51200
	 3   52428800	   51200
	 4   26214400	   25600

We have now log file number 4, with a very different size from previous files. Something catch my attention while database was starting up, a line in alert.log shows:

Allocated 2784840 bytes in shared pool for flashback generation buffer

A quick query after database startup, shows exactly the same:

SQL> SELECT bytes FROM v$sgastat WHERE pool = 'shared pool'AND name = 'flashback generation buff';

     BYTES
----------
   2784840

The values are a lot similar, between the flashback log size and flashback generation buffer size. The size of flashback generation buffer is affected by shared pool size and we can affect shared pool and maybe generate flashback logs of different size.

SQL> alter system set shared_pool_size=10M scope=both;
System altered.

SQL> SELECT bytes FROM v$sgastat WHERE pool = 'shared pool'AND name = 'flashback generation buff';

     BYTES
----------
   4194304

We have now a very different value for flashback generation buffer so it is time to charge more DML and generate flashback log number 5, but first back to hidden parameters. Another hidden parameter defaults the size of flashback generation buffer “_flashback_generation_buffer_size” with the default value of 4194304 (granule size is 4MB).

SQL> select log#, bytes, bytes/1024 as KB from V$FLASHBACK_DATABASE_LOGFILE;

     LOG#	BYTES	      KB
---------- ---------- ----------
	 1   52428800	   51200
	 2   52428800	   51200
	 3   52428800	   51200
	 4   26214400	   25600
	 5   16777216	   16384
	 6   16777216	   16384
	 7   16777216	   16384


Dumping file 6 will show exactly the same (File Size = 2048 * 8K):

 FILE HEADER:
        Compatibility Vsn = 202375168=0xc100000
        Db ID=2672868488=0x9f50bc88, Db Name='BACODB'
        Activation ID=2672897670=0x9f512e86
        Control Seq=2248=0x8c8, File size=2048=0x800
        File Number=6, Blksiz=8192, File Type=8 FLASH BACK

We now have flashback file number 5,6,7 with 16777216 bytes, 4 times bigger that the current flashback generation buffer. Maybe, just maybe, Oracle is writing delayed chunks of 16MB each time generating a file with 16MB minimum (_flashback_format_chunk_mb_dwrite is 16MB).
The last test will be set another hidden parameter, “_flashback_max_log_size”. The chosen value is 10MB.

SQL> alter system set "_flashback_max_log_size"=10485760 scope=spfile;

After one more set of DML, files 8,9,10 and 11 are 15941632 bytes, more than the max_log_size that we have set.

SQL> select log#, bytes, bytes/1024 as KB from V$FLASHBACK_DATABASE_LOGFILE;

      LOG#	BYTES	      KB
---------- ---------- ----------
	 1   52428800	   51200
	 2   52428800	   51200
	 3   52428800	   51200
	 4   26214400	   25600
	 5   16777216	   16384
	 6   16777216	   16384
	 7   16777216	   16384
	 8   15941632	   15568
	 9   15941632	   15568
	10   15941632	   15568
	11   15941632	   15568

Conclusions:

1) As per default, your flashback log size is the same as redo log size.
2) Size is determined by flashback generation buffer in your shared pool if it is more than the default (set by parameter _flashback_generation_buffer_size).
3) Oracle decides if it will write 4MB or 16MB chunks to file, ignoring the minimum set by _flashback_generation_buffer_size.
4) Hidden parameter _flashback_max_log_size was set, but appears to be ignored.
5) db_flashback_retention_target will not affect the flashback log size, only the retention.

Notes:
1) alter system dump flashback logfile logical will skip the contents of blocks in the dump file, avoiding a very big file.
2) Tests were conducted in Oracle 12.1.0.1.0 64 bits

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s