SQL> create table lost_write(t1 int) tablespace users;
Table created.
SQL>
SQL> insert into lost_write values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from lost_write;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
222 6
alter system set db_lost_write_protect=typical;
SQL> select name from v$datafile where file#=6;
NAME
--------------------------------------------------------------------------------
/s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf
update lost_write set t1=9999;
alter system flush buffer_cache;
dd if=/s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf skip=222 bs=8192 count=1 of=222_block
dd if=222_block of=/s01/oradata/PDPROD/datafile/o1_mf_users_b2wgb20l_.dbf seek=222 bs=8192 count=1 conv=notrunc
Tue Oct 07 03:44:52 2014Standby redo application has detected that the primary database lost a disk write.No redo at or after SCN 3392689 can be used for recovery.MRP0: Primary database lost write detected by standby database pdstbyBLOCK THAT LOST WRITE 140, FILE 2, TABLESPACE# 7The block read during the normal successful database operation had SCN 3392388 (0x0000.0033c384) seq 1 (0x01)ERROR: ORA-00752 detected lost write on primaryMRP0: Background Media Recovery terminated with error 752Tue Oct 07 03:44:52 2014Errors in file /s01/diag/rdbms/pdstby/PDSTBY/trace/PDSTBY_mrp0_3786.trc:ORA-00752: recovery detected a lost write of a data blockORA-10567: Redo is inconsistent with data block (file# 2, block# 140, file offset is 1146880 bytes)ORA-10564: tablespace USERS1ORA-01110: data file 2: ‘/s01/oradata/PDSTBY/datafile/o1_mf_users1_b375vwsc_.dbf’ORA-10561: block type ‘TRANSACTION MANAGED DATA BLOCK’, data object# 93507Managed Standby Recovery not using Real Time ApplyRecovery interrupted!Recovered data files to a consistent state at change 3392689Tue Oct 07 03:44:52 2014Errors in file /s01/diag/rdbms/pdstby/PDSTBY/trace/PDSTBY_mrp0_3786.trc:ORA-00752: recovery detected a lost write of a data blockORA-10567: Redo is inconsistent with data block (file# 2, block# 140, file offset is 1146880 bytes)ORA-10564: tablespace USERS1ORA-01110: data file 2: ‘/s01/oradata/PDSTBY/datafile/o1_mf_users1_b375vwsc_.dbf’ORA-10561: block type ‘TRANSACTION MANAGED DATA BLOCK’, data object# 93507Tue Oct 07 03:44:52 2014MRP0: Background Media Recovery process shutdown (PDSTBY)