Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 149,221
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Hot Backup & Fractured Blocks: Test

Posted by FatDBA on December 26, 2012

Fractured block in Oracle
A block in which the header and footer are not consistent at a given SCN. In a user-managed backup, an operating system utility can back up a datafile at the same time that DBWR is updating the file. It is possible for the operating system utility to read a block in a half-updated state, so that the block that is copied to the backup media is updated in its first half, while the second half contains older data. In this case, the block is fractured.
For non-RMAN backups, the ALTER TABLESPACE … BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP command is the solution for the fractured block problem. When a tablespace is in backup mode, and a change is made to a data block, the database logs a copy of the entire block image before the change so that the database can reconstruct this block if media recovery finds that this block was fractured.
The block that the operating system reads can be split, that is, the top of the block is written at one point in time while the bottom of the block is written at another point in time. If you restore a file containing a fractured block and Oracle reads the block, then the block is considered a corrupt.

Let’s perform a test:

–> Before ‘Begin Backup Mode’:

SQL> set autotrace trace stat
SQL> update etr set team=’Oracle’ where id=’7′;

1 row updated.
Statistics
———————————————————-
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        300  redo size
        669  bytes sent via SQL*Net to client
        580  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

: It shows redo size=300 (Normal)

–> Let me put the tablespace in ‘Begin Backup’ Mode and try to executea DML again:
SQL> alter tablespace users begin backup;
Tablespace altered.

SQL> update etr set team=’Oracle’ where id=’1′;
1 row updated.
Statistics
———————————————————-
          1  recursive calls
          6  db block gets
          1  consistent gets
          0  physical reads
      17480  redo size
        669  bytes sent via SQL*Net to client
        580  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
: Check the size of Redo here (17480),  it’s actually the size of the datablock (+Normal redo) where the table column exits and a copy of the block is moved to the redo log buffer .

 
–> Let me try to execute the same DML statement again on same table
SQL> /

1 row updated.
Statistics
———————————————————-
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        300  redo size
        669  bytes sent via SQL*Net to client
        580  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
: Now Redo size is again back to it’s original value (300).

Hence proved that rather pushing changed vectors/values in redo log buffer, oracle actually copies the entire block during initial operations (Reason of large REDO generation) and will not repeat the same for all subsequent operations on the same block.

ALTER TABLESPACE <> BEGIN BACKUP
is the solution to the Fractured Block problem which could have create inconsistencies in case of user managed backup’s which require OS commands to use.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

 
%d bloggers like this: