Excessive Redo Generation During Begin Backup Mode.

Excessive Redo Generation During Hot/Online backup.

During hot backup there is no excessive redo generation,
only some additional information is generated.

The process is something like following.

1) Before backup, tablespace or database is put
   into begin backup mode. 

SQL> alter database begin backup;

2) Now in the begin backup state Only the headers of the data files are frozen.
   The remaining portion of the data files keeps on getting updated.

3) In the begin backup state, Instead of writing the changes that have been done in the block,
   The entire block is written to the redo
   log files . But this incident only happens the first time, after this only 
   subsequent modifications to the blocks are written to 
   the online redo log files.

4) After backup the tablespace or database is returned to 
   end backup state.

SQL> alter database end backup;

5) At this stage it is important to archive the current 
   log file, In order to facilitate recovery at a later time.
 
   This is because the database has a block size of 8192 bytes 
   (mine is) and OS block size has 512 bytes. So that means one oracle 
   block is made up of 16 OS blocks.

   That means when a backup is taken of a oracle block at the 
   OS level then that block will be inconsistent because all
   the parts of the single Oracle block will be backed up at
   different points of time.
   
   So, for the purpose of recovery it is necessary to archive 
   the current redo log so that a consistent image can be
   created when database or tablespace is restored later on.


conn scott/tiger

SQL> set autotrace traceonly statistics;

SQL> update emp set ename = 'JAMES' where ename = 'BOND';

1 row updated.


Statistics
----------------------------------------------------------
          4  recursive calls
          1  db block gets
         15  consistent gets
          0  physical reads
        304  redo size
        672  bytes sent via SQL*Net to client
        623  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL>  update emp set ename = 'BOND' where ename = 'JAMES';

1 row updated.


Statistics
----------------------------------------------------------
          4  recursive calls
          1  db block gets
         15  consistent gets
          0  physical reads
        304  redo size
        672  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

So, when i update the emp table the redo generated is 304 bytes.

Lets put the tablespace in begin backup mode.

SQL> alter tablespace users begin backup;

Tablespace altered.


SQL> update emp set ename = 'JAMES' where ename = 'BOND';

1 row updated.


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          7  consistent gets
          0  physical reads
       8532  redo size
        673  bytes sent via SQL*Net to client
        623  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> update emp set ename = 'BOND' where ename = 'JAMES';

1 row updated.


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          7  consistent gets
          0  physical reads
        304  redo size
        673  bytes sent via SQL*Net to client
        623  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


As you can see after the tablespace was put into begin backup
mode, The FIRST modification to the BLOCK resulted into the entire
BLOCK being written to the redo log files, but subsequent changes
only resulted in normal redo data generation as it was generating
earlier.