Hot Backups,extras redo generated and Fractured Blocks

Today , I  have  come  across  a good  article. I  have worked  and  modify it to explained  in detail . Hope you  all  appreciate  it . Before discussing this topic, let's have an overview of  user-managed  hot backup i.e, what  happen  during  begin  backup  mode  and  end backup  mode.

When  we  begin  backup, it  freezes  the header  of the datafiles (means the SCN number will not increment  any more until the backup is ended ) . It  also  instructs  LGWR  to write  whole  blocks  to  redo the  first  time  a  block  is  touched. Writes  still  occur to  the datafile,  just  the  SCN  is  frozen. This occurs so that at recovery  time, Oracle will  know that  it  must  overwrite  all  blocks  in the backup file with  redo entries  due  to  fracturing. The original  datafiles remain up-to-date, but the backup files will  not be  because they  are  being  changed  during  backup. When  we  end  backup, it unfreezes  the header of the  datafiles  and  allows SCNs  to  be  recorded  properly  during  checkpoint.

Question:  The  oracle documentation  tells  us  that when  we  put  a  tablespace  in  backup  mode , the first  DML  in  the  session  logs  the entire  block  in  the  redo  log buffer  and  not  just the changed vectors.

1.) Can  we  simulate  an example  to  see  this  happening?

2.) What  can  be  the  purpose  of  logging  the  entire  block  the  first  time  and  not  do  the  same subsequently?

Answer:  Below, I’ve created  a simulation. Pay  attention  to  the  “redo size”  statistic in each. First, I have  updated  a  single  row  of  the  employees  table.

SQL> set  autotrace  trace  stat

SQL> update  employees  set  first_name='Stephen'   where   employee_id = 100; 

1 row updated.

Statistics

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

          0  recursive calls

          1  db block gets

          1  consistent gets

          0  physical reads

        292  redo size

        669  bytes sent via SQL*Net to client

        598  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>  rollback;

Rollback  complete.

Notice  the  redo  size  was  only  292  bytes, not  a  very  large  amount.  Now,  let’s  put  the  USERS tablespace  into  hot  backup  mode.

SQL> alter  tablespace  users  begin  backup;

Tablespace altered.

SQL> update  employees  set  first_name = 'Stephen'  where  employee_id = 100;

1 row updated.

Statistics

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

          0  recursive calls

          2  db block gets

          1  consistent gets

          0  physical reads

       8652  redo size

        670  bytes sent via SQL*Net to client

        598  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

Wow!    Quite  a  bit  of  a difference. This  time,  we  can  see  that  atleast  an  entire  block  was written to redo; 8,652  bytes  total. Let’s  run  it  one more  time, with  the  tablespace still  in  hot  backup mode.

SQL> /  

1 row updated.

Statistics

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

          0  recursive calls

          1  db block gets

          1  consistent gets

          0  physical reads

        292  redo size

        671  bytes sent via SQL*Net to client

        598  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

This time , it only used 292 bytes, the same as the original amount. However, to address the second question, we’re  going to attempt  changing a  different  block,  by  changing a record in the departments table  instead  of  employees.

SQL> update  departments  set  department_name = 'Test Dept'  where  department_id = 270;

1 row updated.

Statistics

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

         17  recursive calls

          1  db block gets

          5  consistent gets

          1  physical reads

       8572  redo size

        673  bytes sent via SQL*Net to client

        610  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          1  rows processed

The  result is that  another entire block was written to redo. In  the question, we  stated: “The  oracle documentation  tells  us  that  when  we  put  a  tablespace  in  backup mode ,  the  first  DML  in  the session  logs  the  entire  block  in  the redo log buffer and  not  just  the  changed  vectors” . This  is close, but not right on the mark.


It  is  not  the  first  DML of  the  session, but  the  first  DML to  a  block  that  is  written  to redo . However, when Oracle  writes the  first  DML  for  the  block, it   ensures  that  the  redo  logs/archive trail  contains  at  least one  full  representation of  each  block  that  is changed. Subsequent  changes will therefore  be  safe.

This  process  exists  to  resolve  block  fractures.  A  block  fracture  occurs  when  a  block  is being  read  by  the  backup, and  being  written  to  at  the  same  time  by  DBWR . Because  the  OS  (usually) reads  blocks  at  a  different  rate  than   Oracle, the  OS  copy  will   pull pieces  of  an  Oracle  block  at  a time.  What  if  the  OS  copy  pulls  half  a  block, and  while  that  is  happening,  the  block  is  changed  by  DBWR?  When  the  OS  copy  pulls  the  second  half  of  the  block  it  will  result  in  mismatched  halves,  which  Oracle  would  not  know  how  to  reconcile .

This  is  also  why  the SCN  of  the  datafile  header does  not change  when  a  tablespace  enters  hot backup  mode. The  current  SCNs  are  recorded  in  redo, but   not  in  the datafile. This  is  to  ensure  that Oracle  will  always  recover over  the  datafile  contents  with  redo  entries. When  recovery  occurs, the  fractured  datafile  block will  be  replaced  with  a  complete  block  from redo,  making  it whole  again.  After  Oracle  can  be  certain  it  has a  complete  block,  all   it  needs  are  the  vectors.

Enjoy    :-)