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:
SQL> create table teste tablespace users as select * from dba_objects; Table created.
Now let’s enable the auto trace in our session to see the statistics generated in each operation and we will perform an update:
SQL> set autotrace traceonly statistics SQL> update teste set object_name='tablelore' where object_id=1000; 1 row updated. Statistics ---------------------------------------------------------- 9 recursive calls 3 db block gets 1610 consistent gets 1529 physical reads 0 redo size 865 bytes sent via SQL*Net to client 870 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
As we noted above no redo was generated. I will make a new update on object_id 1001:
SQL> update teste set object_name='tablelore' where object_id=1001; 1 row updated. Statistics ---------------------------------------------------------- 5 recursive calls 1 db block gets 1603 consistent gets 0 physical reads 0 redo size 865 bytes sent via SQL*Net to client 870 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
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:
SQL> set autotrace off SQL> alter tablespace users begin backup; Tablespace altered.
I’ll enable autotrace again and re-run updates with different values just to show that we’re actually changing data:
SQL> set autotrace traceonly statistics SQL> update teste set object_name='loredatatable' where object_id=1000; 1 row updated. Statistics ---------------------------------------------------------- 4 recursive calls 2 db block gets 1603 consistent gets 0 physical reads 8740 redo size 865 bytes sent via SQL*Net to client 874 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
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:
SQL> update teste set object_name='tablelore' where object_id=1001; 1 row updated. Statistics ---------------------------------------------------------- 0 recursive calls 1 db block gets 1534 consistent gets 0 physical reads 388 redo size 866 bytes sent via SQL*Net to client 870 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
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:
SQL> update teste set object_name='tablelore' where object_id=35000; 1 row updated. Statistics ---------------------------------------------------------- 5 recursive calls 1 db block gets 1603 consistent gets 0 physical reads 8580 redo size 866 bytes sent via SQL*Net to client 871 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
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:
SQL> update teste set object_name='tablelore' where object_id=35001; 1 row updated. Statistics ---------------------------------------------------------- 5 recursive calls 1 db block gets 1603 consistent gets 0 physical reads 336 redo size 866 bytes sent via SQL*Net to client 871 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed SQL> rollback; Rollback complete. SQL> alter tablespace users end backup; Tablespace altered.
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.
