Hot Backup Mode Causes Excessive Redo Generation..Really??


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.

reference: http://practical-tech.blogspot.in/

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