User managed backup and redo size growth

 Dear Readers,

As per oracle document, when you perform user managed backup using 
BEGIN BACKUP at database or tablespace level, your expected to generate more redo (since we will include the image of the full block undergoing change, since the user managed backup tool doesnt understand the dbfile architecture - might create fractured block in backup) compared to using RMAN to perform the same backup.

So I wanted to test that...

Test platform details:

Oracle Version: 19.3.0
DB Type: Standalone x86_64
Os: Linux

In session 1:

  1  create table t1 as
  2  select level lvl,mod(level,3) mdlvl3,mod(level,4) mdlvl4
  3  from dual
  4* connect by level<10000
SQL> /
Table created.

In session 2:

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

NOT ACTIVE

In session 1 back:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=20;

9999 rows updated.

Statistics

----------------------------------------------------------

         95  recursive calls

        156  db block gets

        141  consistent gets

         38  physical reads

     949056  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

So we see it is only 949K or so redo generated.

In session 2 again:

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

NOT ACTIVE

SQL> alter database begin backup;

Database altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

ACTIVE

In session 1 again:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=30;

9999 rows updated.

Statistics

----------------------------------------------------------

         55  recursive calls

        158  db block gets

        139  consistent gets

         37  physical reads

    1134532  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

This time 1.1M redo bytes generated, this ideally should be the full image of the block which are undergoing change.

In session 2 again:

SQL> alter database end backup;

Database altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

NOT ACTIVE

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

In session 1 again:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=40;

9999 rows updated.

Statistics

----------------------------------------------------------

         89  recursive calls

        158  db block gets

        141  consistent gets

         37  physical reads

     949164  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

SQL>

In session 2 again:

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter database begin backup;

Database altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

ACTIVE

In session 1 again:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=30;

9999 rows updated.

Statistics

----------------------------------------------------------

         55  recursive calls

        156  db block gets

        139  consistent gets

         37  physical reads

    1134400  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

SQL>

So we repeated the same update 4 times, 2 times in "BACKUP" mode and other 2 times normally.

In all the scenario when we had "BACKUP" mode active, the amount of redo size is 1.1M vs "NO ACTIVE BACKUP" mode, the size is 949K.

So this confirms oracle's document and expected behaviour.

Thanks