Truths and Lies about DB_LOST_WRITE_PROTECT

The name of this parameter always makes me think that if it is set, the system will be protected against lost writes, so it will prevent occurring.

A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage, but knowing that I/O subsystem is not part of the Oracle Database code, you may  wonder, “how Oracle is able to protect against a lost write when it is not responsible for writing“. Well, in my opinion, the name of this parameter is misguided, because it is not preventing lost writes.

So why use it? When you have a lost write situation, the sooner you detect it, the better, and this parameter is very useful to detect lost write as soon as possible and will help you to, under some circumstances, be able to repair the lost write and at the end, avoid loosing any data, but it will not protect your system against it.

In order to understand how this parameter works, we need to understand first how redo works.

When a database block change is generated by any DML operation, Oracle makes this change in two ways:

  • Logical: Oracle makes a description of the change that is going to be performed including in this description which block version is changing (original scn) when the change happens (scn of the change) and what changes are performed (the changes themself). This information is stored in the redo log files and allow Oracle to re-execute the change when a recover process happens.
  • Physical: The changes are performed in the block itself.

Using the logical description of the change stored in the redo log files, during recover process and before applying the change in the block, Oracle verifies if the block that is going to be modified is the right one, or, to be more precise, if it is from the right version.

Now, what happens if it is not. Lets see an example in a Dataguard configuration: I’m going to simulate a lost write and we can see what happens when a new change for the block that suffered the lost write is applied in standby database.

First, we identify a row and we take a copy of the block before applying any change

SYS@db121rac1> select rowid, employee_id, salary from hr.employees where employee_id=198;

ROWID              EMPLOYEE_ID     SALARY
------------------ ----------- ----------
AAAWZpAAKAAAADPAAA         198      12345

SYS@db121rac1> @rowid
Enter value for rid: 'AAAWZpAAKAAAADPAAA'
old  10:   dbms_rowid.rowid_info(&rid,ridtyp,objnum,relfno,blno,rowno,'SMALLFILE');
new  10:   dbms_rowid.rowid_info('AAAWZpAAKAAAADPAAA',ridtyp,objnum,relfno,blno,rowno,'SMALLFILE');
Row Typ-1
Obj No-91753
RFNO-10
Block No-207
Row No-0 

PL/SQL procedure successfully completed.

SYS@db121rac1> select file_name from dba_data_files where relative_fno=10;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/db121/example01.dbf

SYS@db121rac1>
SYS@db121rac1> exit
[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=bloque_antes count=1 skip=207
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000167299 s, 49.0 MB/s
[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=block_before_change.blk count=1 skip=207
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.0001396 s, 58.7 MB/s

Now, we update the row and propagate the change to our standby database

[oracle@oel7-rac1 lost_write]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 11 09:50:39 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


Session altered.

SYS@db121rac1> update hr.employees set salary=54321 where employee_id=198;

1 row updated.

SYS@db121rac1> commit;

Commit complete.

SYS@db121rac1> alter system switch logfile;

System altered.
SYS@db121rac1> select rowid, employee_id, salary from hr.employees where employee_id=198;

ROWID              EMPLOYEE_ID     SALARY
------------------ ----------- ----------
AAAWZpAAKAAAADPAAA         198      54321

After that, using the copy of the block before the change, we overwrite the datafile simulating a lost write.

SYS@db121rac1> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@db121rac1> exit
[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=first_piece.dbf count=207
207+0 records in
207+0 records out
1695744 bytes (1.7 MB) copied, 0.0106201 s, 160 MB/s
[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=second_piece.dbf skip=208
158993+0 records in
158993+0 records out
1302470656 bytes (1.3 GB) copied, 6.25686 s, 208 MB/s
[oracle@oel7-rac1 lost_write]$ cat first_piece.dbf block_before_change.blk second_piece.dbf >example.dbf
[oracle@oel7-rac1 lost_write]$ ls -l example.dbf
-rw-rw-r-- 1 oracle oracle 1304174592 Apr 11 10:06 example.dbf
[oracle@oel7-rac1 lost_write]$ ls -l /u01/app/oracle/oradata/db121/example01.dbf
-rw-r----- 1 oracle oracle 1304174592 Apr 11 09:54 /u01/app/oracle/oradata/db121/example01.dbf
SYS@db121rac1> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size            1157631520 bytes
Database Buffers          469762048 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SYS@db121rac1> select rowid, employee_id, salary from hr.employees where employee_id=198;

ROWID              EMPLOYEE_ID     SALARY
------------------ ----------- ----------
AAAWZpAAKAAAADPAAA         198      12345    <-----Change is now lost!

 

So now, when we modify the block, Standby Database will realize that the source version of the block is not matching with the version it is trying to modify.

SYS@db121rac1> update hr.employees set salary=salary+1 where employee_id=198;

1 row updated.

SYS@db121rac1> commit;

Commit complete.      <----Now ORA-600 [3020] is reported in standby
Mon Apr 11 10:13:47 2016
ERROR: ORA-00600: internal error code, arguments: [3020] recovery detected a data block with invalid SCN raised at location:kcbr_media_ap_1
Mon Apr 11 10:13:47 2016
Checker run found 1 new persistent data failures
Errors in file /u01/app/oracle/diag/rdbms/db121rac2/db121rac2/trace/db121rac2_pr04_2897.trc  (incident=24505):
ORA-00600: internal error code, arguments: [3020], [5], [207], [41943247], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 207, file offset is 1695744 bytes)
ORA-10564: tablespace EXAMPLE
ORA-01110: data file 5: '/u01/app/oracle/oradata/db121/example01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 91753
Incident details in: /u01/app/oracle/diag/rdbms/db121rac2/db121rac2/incident/incdir_24505/db121rac2_pr04_2897_i24505.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Slave exiting with ORA-600 exception

 

This means that because of the way the software is designed, lost writes detection is implicit in the code, but it is done only when a new change is performed on the block that suffered the issue.

So, what is the difference when DB_LOST_WRITE_PROTECT is set?

Well, the difference is that when it is set, every SELECT statement will generate and small redo change saying “I’ve read this block and it is this version” and will propagate this information to Standby Databases allowing it to detect the lost write when the block is read. If this behavior happens, you can restore and recover the block in Primary database and this will fix the lost write.

Lets see it in action. We repeat exactly the same steps as before but now after setting the parameter to typical.

ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size            1157631520 bytes
Database Buffers          469762048 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SYS@db121rac1> alter system set db_lost_write_protect=typical;

System altered.

SYS@db121rac1> select rowid, salary from  hr.employees where employee_id=198;

ROWID                  SALARY
------------------ ----------
AAAWZpAAKAAAADPAAA      12346

SYS@db121rac1> update hr.employees set salary=54321 where employee_id=198;             

1 row updated.
SYS@db121rac1> commit;

Commit complete.

SYS@db121rac1> select rowid, salary from  hr.employees where employee_id=198;
ROWID                  SALARY
------------------ ----------
AAAWZpAAKAAAADPAAA      54321

Now we introduce the lost write as we did before

[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=first_piece.dbf count=207
207+0 records in
207+0 records out
1695744 bytes (1.7 MB) copied, 0.0105966 s, 160 MB/s
[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=second_piece.dbf skip=208
158993+0 records in
158993+0 records out
1302470656 bytes (1.3 GB) copied, 2.4282 s, 536 MB/s
[oracle@oel7-rac1 lost_write]$ cat first_piece.dbf block_before_change.blk second_piece.dbf >example.dbf
[oracle@oel7-rac1 lost_write]$ cp example.dbf /u01/app/oracle/oradata/db121/example01.dbf
[oracle@oel7-rac1 lost_write]$ sqlplus / as sysdba
SYS@db121rac1> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size            1157631520 bytes
Database Buffers          469762048 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

Now lets read the block

SYS@db121rac1> select rowid, salary from  hr.employees where employee_id=198;

ROWID                  SALARY
------------------ ----------
AAAWZpAAKAAAADPAAA      12345   <----Now standby database reports ORA-752

And this is what we see in the alert.log for the Standby Database

Standby redo application has detected that the primary database lost a disk write.
No redo at or after SCN 2004484 can be used for recovery.
BLOCK THAT LOST WRITE 207, FILE 5, TABLESPACE# 6
The block read during the normal successful database operation had SCN 1995598 (0x0000.001e734e) seq 1 (0x01)
ERROR: ORA-00752 detected lost write on primary
Slave exiting with ORA-752 exception
Mon Apr 11 10:31:51 2016
Errors in file /u01/app/oracle/diag/rdbms/db121rac2/db121rac2/trace/db121rac2_pr04_4812.trc:
ORA-00752: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 5, block# 207, file offset is 1695744 bytes)
ORA-10564: tablespace EXAMPLE
ORA-01110: data file 5: '/u01/app/oracle/oradata/db121/example01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 91753

In summary, this parameter is a must if you have a Dataguard configuration, but it is important to understand its limitations.

DB_BLOCK_CHECKSUM and Risk Perception

Tags

, ,

DBAs are always concerned about the performance impact of features like db_block_checksum or db_block_checking.  told me an story that several years ago, he met a lot of people who had turned off redo logging with an underscore parameter. The performance they’d get from doing this would set the expectation level in their mind, which would cause them to resist (strenuously!) any notion of switching this [now horribly expensive] logging back on. Of course, it makes you wish that it weren’t even a parameter.

Cary believes that the right analysis is to think clearly about risk. Risk is a “soft” word in most people’s minds, but in finance they teach that risk is quantifiable as a probability distribution. For example, you know every day that it’s possible for a disk drive in your system to go bad. The risk of that happening can actually be calculated. For disks, it’s not too difficult, because vendors do those calculations (MTTF) for us. The probability that you’ll wish you had set db_block_checksum enabled today is probably more difficult to compute.

From a psychology perspective, DBAs would be happier if their systems were set to full or typical values to begin with. Then in response to the question, “Would you like us to remove your safety net in exchange for going between 1% and 10% faster? Here’s the horror you might face if we do it…” I’d wager that most people would say no, thank you. They will react emotionally to the idea of their safety net being taken away.

But with the baseline of its being turned off to begin with, the question is “Would you like us to install a safety net in exchange for slowing your system down between 1% and 10%? Here’s the horror you might face if we don’t…” I’d wager that most people would answer no, thank you (the opposite verdict!), because they will react emotionally to the idea of their performance being taken away. If they had been burned by block corruptions before (which is me case because I worked for corruption team at Oracle Support), then I’m going to favor the safety net argument.

But now the question is: “if I’ve never faced a corruption and I have my disks mirrored and I have a good database backup strategy, why do I need to set these kind of parameters that will impact my performance?” The answer is “Because in other case, we can not be 100% sure  that Oracle is reliable to detect the corruption and could provide you wrong information and (the worst) you wont realise!

By default, Oracle has two internal mechanisms to validate the health of a block from physical structure point of view:

  1. At block tail, Oracle stores some information that must match some information stored at the header of the block. In case these two pieces do not match, the block is reported as FRACTURED when it is validated. ORA-1578 error is reported if this problem is found. This validation cannot be disabled. You can see a good explanation for fractured blocks in MOS.
  2. The other mechanism enabled by default is DB_BLOCK_CHECKSUM. In this case, DBWr and the direct loader calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are recalculated when blocks are read and compared with the information at the header. In case they do not match, block is reported as CHECKSUM corrupted and ORA-1578 error is reported at client side.

This DB_BLOCK_CHECKSUM functionality can be disabled in order to improve performance between 1% and 5% for everything but SYSTEM tablespace but, are you able to assume the risk?

Lets try to see an example and simulate a hardware failure in the middle of a block when DB_BLOCK_CHECKSUM is disabled

  • We identify the rows we have stored in a row, in this case file 5 block 206. This is an 8k block tablespace.
SQL> select employee_id, salary from hr.employees where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA';

EMPLOYEE_ID SALARY
----------- ----------
 100 24000
 101 17000
 102 17000
 103 9000
 104 6000
 105 4800
 106 4800
 107 4200
 108 12008
 109 9000
 110 8200
...
...
98 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
  • Now, we take a copy of a piece of the block  storing these rows (512 bytes)
[oracle@oel6-121 DBTEST]$dd if=example01.dbf of=part02.dbf bs=512 skip=3299 count=1
SQL> startup
ORACLE instance started.
Total System Global Area 2499805184 bytes
Fixed Size 2927480 bytes
Variable Size 671089800 bytes
Database Buffers 1811939328 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.

SQL> update hr.employees set salary=salary+1 where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA';
98 rows updated.

SQL> commit;
Commit complete.

SQL> select employee_id, salary from hr.employees where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA';

EMPLOYEE_ID SALARY
----------- ----------
 100 24001
 101 17001
 102 17001
 103 9001
 104 6001
 105 4801
 106 4801
 107 4201
 108 12009
 109 9001
 110 8201
...
...
 177 8401
 178 7001
 179 6201
 180 3201
 181 3101
 182 2501
 183 2801
 184 4201
 185 4101
 186 3401
 187 3001
 188 3801
 189 3601
 190 2901
 191 2501
 192 4001
 193 3901
 194 3201
 195 2801
 196 3101
 197 3001

98 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
  • So far so good, but now lets simulate the problem putting back the block piece taken before the UPDATE into the middle of the updated block.
[oracle@oel6-121 DBTEST]$ dd if=example01.dbf of=part01.dbf bs=512 count=3299
3299+0 records in
3299+0 records out
1689088 bytes (1.7 MB) copied, 0.01112 s, 152 MB/s
[oracle@oel6-121 DBTEST]$ dd if=example01.dbf of=part03.dbf bs=512 skip=3300
2543916+0 records in
2543916+0 records out
1302484992 bytes (1.3 GB) copied, 7.75558 s, 168 MB/s
[oracle@oel6-121 DBTEST]$ cat part01.dbf part02.dbf part03.dbf >file.dbf
[oracle@oel6-121 DBTEST]$ rm example01.dbf 
[oracle@oel6-121 DBTEST]$ mv file.dbf example01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 2499805184 bytes
Fixed Size 2927480 bytes
Variable Size 671089800 bytes
Database Buffers 1811939328 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> select employee_id, salary from hr.employees where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA';

EMPLOYEE_ID SALARY
----------- ----------
 100 24001
 101 17001
 102 17001
 103 9001
 104 6001
 105 4801
 106 4801
 107 4201
 108 12009
 109 9001
 110 8201
...
...
 177 8401
 178 7001
 179 6201
 180 3201
 181 3101
 182 2501
 183 2801
 191
 192 4001
 193 3901
 194 3201
 195 2801
 196 3101
 197 3001

91 rows selected.
  • We have lost 7 rows and we have 1 row with no salary…..and there is no error anywhere!!!

In case, if DB_BLOCK_CHECKSUM had been enabled, we had seen ORA-1578 error and we could restore the block from a backup.

In summary, as my colleague Gabriel Alonso (a.k.a Gaby) says, disabling DB_BLOCK_CHECKSUM is like trying to land a plane without any instrumentation at night in the middle of a hurricane – at least risky.

Follow

Get every new post delivered to your Inbox.