I must thank my fellow DBA Franky Weber Faust for his publication in his blog.

Introduction: –  In this article, we have a quick journey on redo log during user-managed backup. RMAN is the most recommended tool for performing backup and recovery of an Oracle database, but we know that this is not always the case and many DBAs around the world still prefer to back up using the manual method. Of course, there are scenarios in which we can do via storage a snapshot of the disks/filesystems where the database is stored and this is usually much faster, but the discussion here is another. RMAN has been with Oracle since version 8 and it’s no longer an excuse to say you do not know the tool.

My intention with this article is to present information that for many is unknown. When we back up using the user-managed backup method we generate much more redo data than is necessary. In Oracle’s official documentation it is mentioned that when the database or tablespace is put into backup mode (begin backup) the first DML in a block takes the integer to the redo log buffer and not just the changes made.

What is redo log?

A redo log is a file that is part of an Oracle database. When a transaction is committed, the transaction’s details in the redo log buffer is written to a redo log file. The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

In backup mode, the database copies whole changed data blocks into the redo stream.

Let’s simulate the scenario then.

I’ll first create a table to test:

Now let’s enable the auto trace in our session to see the statistics generated in each operation and we will perform an update:

As we noted above no redo was generated. I will make a new update on object_id 1001:

Again no redo was generated, but the physical reads were zeroed, which means that we are talking about the same block of data that was now cached. Let’s go ahead…

I will disable the auto trace and put the USERS tablespace in backup mode:

I’ll enable autotrace again and re-run updates with different values just to show that we’re actually changing data:

Oops, now we have had a very large amount of redo generated, because I’m saying it’s great? Because the entire block was copied into the redo log buffer. Let’s run an update on the same block and see what happens:

Did you see? The value is low this time because the block has already been copied to the redo log buffer, so Oracle only writes the changes (changed vectors).
Let’s do the same thing just for another block of data, I will now update object_id = 35000:

Again a good amount of redo.
To finish we will update object_id 35001 and note that only the changes in the log buffer will be written:

I hope with help of this article you add some gems in your Oracle knowledge’s treasure. Thank you for giving your  valuable time to reading.

About The Author

Leave a Reply