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.
David Loinaz 
Reblogged this on .
LikeLike
Very usefule write up
LikeLike
Thanks for this good detailed simulation
LikeLike