What happened when tablespace/database placed into backup mode?

User managed backup or physical backup without RMAN is still one of the popular method for backup and still part of mostly disk to disk backup methods (BCV or SRDF copy). Today I am going to discuss what happens when we place tablespace/database into backup mode.

Fractured Block

Unit of I/O for any system is block size and for oracle database it’s 2K/4K/8K/16K/32K while for any operating system it could be lower than that. One risk in making online backups is the possibility of inconsistent data within a block. Assume that OS I/O block size is 512 bytes while we used DB block size as 8K.

This means to copy utility of OS would take 16 I/Os to take backup of one DB block, while during the backup, DBWR could write 8K in one I/O.

So assume a scenario where copy utility is busy taking backup of 8th portion of particular block while DBWR came to overwrite same DB block (i.e. all 16 OS block pieces in one DB block). Because copy utility was still busy on taking backup of remaining blocks so it took new image from OS piece 9th onwards. From database perspective that backup copy of db block (collection of 16 OS blocks) has been fractured as its half or the OS blocks are old and remaining half are new. The result is called a fractured block, meaning that the data contained in this block is not consistent at a given SCN.

Oracle’s internal protection mechanism against fractured block

To overcome this danger Oracle introduced a protection feature which will be activate once you trigger ‘alter tablespace/database begin backup’ command. Following internal operations happened with immediate effect after triggering begin backup for tablespace

  1. Partial check point (for all blocks part of the subjected tablespace)
  2. Headers of datafiles would be freeze while datafile blocks would be available for normal access
  3. The first time a block is changed in a datafile that is in hot backup mode, the entire block is written to the redo log files, not just the change vector. Normally only the changed bytes (a change vector) are written. In hot backup mode, the entire block is logged the first time (successive updates against that block would be logged as usual change vector only). This is because you can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously.
  4. The datafile headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file.
  5. Once you end backup the datafile headers would be unfreeze and synced with current SCN from control file.

Now, Consider restoring this copy we have a block that is half at time a and half at time b, We cannot recover that block UNLESS we just happened to keep the full block image from the first change after starting the backup elsewhere which is what we did. So, we are able to recover this block using that image.

Note: With RMAN, this isn’t an issue. RMAN is not affected by OS vs DB block sizes it knows how to read an Oracle datafile safely, with RMAN backups, we don’t put a tablespace into backup mode, it is not necessary.

Let’s see if we can test this!!

For testing I will use SYSTEM tablespace which has 8K blocksize.

SQL> select block_size from dba_tablespaces where tablespace_name=’SYSTEM’;

BLOCK_SIZE

———-

8192

I have created a table with PCTFREE 99 to make sure I can use more blocks for few rows. I have populated table with 100 records (FIRST_NAME => AMIT and LAST_NAME => ‘S’ with suffix from 1 to 100)

SQL> CREATE TABLE “SYS”.”ABC” ( “FIRST_NAME” VARCHAR2(100), “LAST_NAME” VARCHAR2(100)) PCTFREE 99 PCTUSED 1 TABLESPACE “SYSTEM”;

Table created.

SQL> insert into abc (select ‘AMIT’||rownum,’S’||rownum from dba_objects where rownum <101);

100 rows created.

SQL> commit;

Commit complete.

As predicted I have got total 5 extents with 8 blocks each and all together these are containing 100 rows.

SQL> select TABLESPACE_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS from dba_extents where SEGMENT_NAME=’ABC’;

TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS

—————————— ———- ———- ———- ———- ———-

SYSTEM 0 1 86272 65536 8

SYSTEM 1 1 86280 65536 8

SYSTEM 2 1 86288 65536 8

SYSTEM 3 1 86296 65536 8

SYSTEM 4 1 86304 65536 8

To get exact idea of average row length, I have analyzed the table with old analyze method.

SQL> analyze table abc compute statistics;

Table analyzed.

SQL> select NUM_ROWS,AVG_ROW_LEN from dba_tables where table_name=’ABC’;

NUM_ROWS AVG_ROW_LEN

———- ———–

100 14

Before starting actual testing I am going to note down rowids of individual rows (just small sample of 11 records would be enough)

SQL> col first_name for a10

SQL> select first_name,rowid from abc where rownum <12;

FIRST_NAME ROWID

———- ——————

AMIT1 AAASN9AABAAAVEBAAA

AMIT2 AAASN9AABAAAVEBAAB

AMIT3 AAASN9AABAAAVEBAAC

AMIT4 AAASN9AABAAAVECAAA

AMIT5 AAASN9AABAAAVECAAB

AMIT6 AAASN9AABAAAVECAAC

AMIT7 AAASN9AABAAAVEDAAA

AMIT8 AAASN9AABAAAVEDAAB

AMIT9 AAASN9AABAAAVEDAAC

AMIT10 AAASN9AABAAAVEEAAA

AMIT11 AAASN9AABAAAVEEAAB

11 rows selected.

Dbms_rowid is nice package to extract blocks id out of rowid.

SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(‘AAASN9AABAAAVEBAAA’) from dual

DBMS_ROWID.ROWID_BLOCK_NUMBER(‘AAASN9AABAAAVEBAAA’)

—————————————————

86273

SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(‘AAASN9AABAAAVECAAB’) from dual

DBMS_ROWID.ROWID_BLOCK_NUMBER(‘AAASN9AABAAAVECAAB’)

—————————————————

86274

SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(‘AAASN9AABAAAVEDAAC’) from dual

DBMS_ROWID.ROWID_BLOCK_NUMBER(‘AAASN9AABAAAVEDAAC’)

—————————————————

86275

SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(‘AAASN9AABAAAVEEAAB’) from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER(‘AAASN9AABAAAVEEAAB’)

—————————————————

86276

So time to verify if partial checkpoint would take place at the time of begin backup or not

SQL> select FILE#,block#,STATUS from v$bh where block# between ‘86272’ and ‘86311’ and dirty=’Y’;

no rows selected

SQL> update abc set first_name=’AMIT1′ where first_name=’AMIT1′;

1 row updated.

SQL> select FILE#,block#,STATUS from v$bh where block# between ‘86272’ and ‘86311’ and dirty=’Y’;

no rows selected

Because my test environment is almost idle so I need to use commit to push oracle to provide me real time information J

SQL> commit;

Commit complete.

SQL> select FILE#,block#,STATUS from v$bh where block# between ‘86272’ and ‘86311’ and dirty=’Y’;

FILE# BLOCK# STATUS

———- ———- ———-

1 86273 xcur

Here I have notices that after last update statement followed by commit v$bh is showing block id 86273 as dirty block in buffer cache.

SQL> update abc set first_name=’AMIT5′ where first_name=’AMIT5′;

1 row updated.

SQL> commit;

Commit complete.

SQL> select FILE#,block#,STATUS from v$bh where block# between ‘86272’ and ‘86311’ and dirty=’Y’;

FILE# BLOCK# STATUS

———- ———- ———-

1 86274 xcur

1 86273 xcur

SQL> update abc set first_name=’AMIT9′ where first_name=’AMIT9′;

1 row updated.

SQL> commit;

Commit complete.

SQL> select FILE#,block#,STATUS from v$bh where block# between ‘86272’ and ‘86311’ and dirty=’Y’;

FILE# BLOCK# STATUS

———- ———- ———-

1 86274 xcur

1 86275 xcur

1 86273 xcur

Now I am going to place system tablespace into backup mode to see if implicit partial checkpoint for happens or not.

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> select FILE#,block#,STATUS from v$bh where block# between ‘86272’ and ‘86311’ and dirty=’Y’;

no rows selected

SQL> alter tablespace system end backup;

Tablespace altered.

Now we need to check the redo generation during without backup mode and backup mode

SQL> set autotrace on stat

SQL> update abc set first_name=’AMIT1′ where first_name=’AMIT1′;

1 row updated.

Statistics

———————————————————-

0 recursive calls

1 db block gets

39 consistent gets

0 physical reads


364 redo size    
è Redo generated due to update statement.

840 bytes sent via SQL*Net to client

814 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

Now onwards I will remove last 6 lines from Statistics section of each update command to make text readable J

SQL> update abc set first_name=’AMIT5′ where first_name=’AMIT5′;

1 row updated.

Statistics

———————————————————-

1 recursive calls

1 db block gets

39 consistent gets

0 physical reads

404 redo size

SQL> update abc set first_name=’AMIT6′ where first_name=’AMIT6′;

1 row updated.

Statistics

———————————————————-

1 recursive calls

1 db block gets

39 consistent gets

0 physical reads

364 redo size

So I have noticed that update statement for three different rows are generating simple change vector of 364 bytes – 404 bytes. Now I am placing SYSTEM tablespace into backup mode and check the redo generation.

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> SELECT t.name AS “TB_NAME”, d.file# as “DF#”, d.name AS “DF_NAME”, b.status

FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b

WHERE d.TS#=t.TS#

AND b.FILE#=d.FILE#

AND b.STATUS=’ACTIVE’;

TB_NAME DF# DF_NAME STATUS

———- ———- ————————————————– ——————

SYSTEM 1 /u01/app/oracle/oradata/mask11g/system01.dbf ACTIVE

SQL> update abc set first_name=’AMIT1′ where first_name=’AMIT1′;

1 row updated.

Statistics

———————————————————-

1 recursive calls

2 db block gets

39 consistent gets

0 physical reads


8728 redo size
è
sudden change from last time, this time we can see redo worth of 8728 Bytes generated ( 8120 Bytes block + some little extra redo) I don’t have exact breakup of that extra redo.

SQL> update abc set first_name=’AMIT1′ where first_name=’AMIT1′;

1 row updated.

Statistics

———————————————————-

0 recursive calls

1 db block gets

39 consistent gets

0 physical reads

364 redo size

When I have updated the same record again then normal 364 bytes rdo generation, which is showing that block will go to redo when ever got dirty veru first time since backup mode enabled for tablespace.

SQL> update abc set first_name=’AMIT2′ where first_name=’AMIT2′;

1 row updated.

Statistics

———————————————————-

1 recursive calls

1 db block gets

39 consistent gets

0 physical reads

320 redo size

Trying to update another row from block id 86273 and as expected we got normal redo (change vector only)

SQL> update abc set first_name=’AMIT5′ where first_name=’AMIT5′;

1 row updated.

Statistics

———————————————————-

0 recursive calls

1 db block gets

39 consistent gets

0 physical reads

8592 redo size

While updating record in another block I have noticed that redo generation is again 8K + little extra, which is because this block is updated very first time since system tablespace placed into backup mode.

SQL> update abc set first_name=’AMIT6′ where first_name=’AMIT6′;

1 row updated.

Statistics

———————————————————-

1 recursive calls

1 db block gets

39 consistent gets

0 physical reads

364 redo size

Trying to update another row from block id 86274 and as expected we got normal redo (change vector only)

SQL> alter tablespace system end backup;

Tablespace altered.

Ended the backup mode

SQL> update abc set first_name=’AMIT5′ where first_name=’AMIT5′;

1 row updated.

Statistics

———————————————————-

0 recursive calls

1 db block gets

39 consistent gets

0 physical reads

404 redo size

As expected redo generation is normal now.

For more detail you can refer

Advertisements

One Response to What happened when tablespace/database placed into backup mode?

  1. Pingback: Role of SCN in Instance Recovery & Media Recovery | Amit Saraswat

Share Your Comment

Please log in using one of these methods to post your comment:

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

Pierre blog

Pierre Forstmann Oracle Database blog

flashdba

Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

%d bloggers like this: