模拟data block lost write的简单方法

最新推荐文章于 2024-03-03 18:06:15 发布

cuiqiong6888

最新推荐文章于 2024-03-03 18:06:15 发布

阅读量275

点赞数

文章标签：数据库

data block lost write是指数据库下发一个磁盘写操作后收到I/O系统关于写入已经完成的通知，但实际却由于磁盘或者磁盘控制器异常等原因数据块最终没有记入到磁盘，导致对于数据块的修改丢失的情况，data block lost write虽不会影响数据库的高可用性，但若不及时发现会引起数据库的逻辑错误进一步的扩散，如果test1表里有一行记录
ID
--
1

test2表里有一行记录
ID salary
-- ------
2 5000

session 1里执行第一条update语句后发生了写丢失
update test1 set id=2 where id=1;
commit;
<发生了data block lost write>

由于修改的结果没有永久保存到磁盘，磁盘上test1表里的记录还是
ID
--
1

session 1紧接着又执行了第二条update，这次update的是test2表，关联了test1表
update test2 set salary=salary+1000 where id in (select id from test1)

由于lost write的发生，最终update了0行记录

session 1里的最后一条记录还是update，这次update的是test3表，关联了test2表

update test3 set job='manager' where id in (select id from test2 where salary > 5000);

最终还是update了0行记录

可以看出data block lost write会引起一连串的逻辑错误，其后果是非常严重的

oracle提供了db_lost_write_protect参数用来检测是否发生了lost write，一般我们在Dataguard环境里会设置主库以及备库的db_lost_write_protect=typical，这样主库若发生data block lost write备库就能检测出来。当然如果没有DG环境使用下面的方法也能够模拟出data block lost write，我们利用RMAN 备份和恢复的过程来模拟data block lost write

###设置db_lost_write_protect=typical
alter system set db_lost_write_protect=typical scope=both;

SYS@tstdb1-SQL> show parameter db_lost_write_protect

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_lost_write_protect string NONE

###创建测试用表空间和表
drop tablespace ts0815_1 including contents and datafiles;

create tablespace ts0815_1 datafile '/oradata06/testaaaaa/ts0815_1.dbf' size 128m;

create table t0815_1 (c1 varchar2(2),c2 varchar2(2)) tablespace ts0815_1 ;

insert into t0815_1 values('a','aa');
commit;

select * from t0815_1;
C1 C2
-- --
a aa

alter system flush buffer_cache;
alter system flush buffer_cache;

select dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno from t0815_1;
RFNO BLKNO
---------- ----------
8 131

###保存block 8/131的快照
dd if=/oradata06/testaaaaa/ts0815_1.dbf of=/oradata06/testaaaaa/blk131.dmp bs=8192 count=1 skip=131
1+0 records in.
1+0 records out.

ls -l /oradata06/testaaaaa/blk131.dmp
-rw-r--r-- 1 oracle oinstall 8192 Aug 15 13:41 /oradata06/testaaaaa/blk131.dmp

###修改记录
update t0815_1 set c1='b',c2='bb';
commit;

alter system flush buffer_cache;
alter system flush buffer_cache;

select * from t0815_1;
C1 C2
-- --
b bb

rman target /

###备份datafile
RMAN> backup datafile 8;

Starting backup at 20150815 13:42:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=663 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/oradata06/testaaaaa/ts0815_1.dbf
channel ORA_DISK_1: starting piece 1 at 20150815 13:42:57
channel ORA_DISK_1: finished piece 1 at 20150815 13:42:58
piece handle=/oradata06/fra/TSTDB1/backupset/2015_08_15/o1_mf_nnndf_TAG20150815T134257_1lU8o7H7G_.bkp tag=TAG20150815T134257 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150815 13:42:58

alter system switch logfile;

alter system flush buffer_cache;

###将old block image覆盖现有的block，模拟data block lost write
dd if=/oradata06/testaaaaa/blk131.dmp of=/oradata06/testaaaaa/ts0815_1.dbf conv=notrunc bs=8192 count=1 seek=131
1+0 records in.
1+0 records out

###变回了修改前的记录
alter system flush buffer_cache;

select * from t0815_1;
C1 C2
-- --
a aa

alter system switch logfile;

###redo size for lost write detection、redo entries for lost write detection的统计信息有增长，因为read block from disk的操作会写redo
SYS@tstdb1-SQL> select * from v$mystat m,v$statname s where s.statistic#=m.statistic# and s.name like '%lost write%';

       SID STATISTIC#      VALUE STATISTIC# NAME                                                    CLASS    STAT_ID
---------- ---------- ---------- ---------- -------------------------------------------------- ---------- ----------
       598         87          0         87 recovery blocks read for lost write detection               8 2677324291
       598         88          0         88 recovery blocks skipped lost write checks                   8 680578166
       598        179          8        179 redo entries for lost write detection                       2 2194106845
       598        180       1648        180 redo size for lost write detection                          2 3835726666

###数据文件丢失
rm /oradata06/testaaaaa/ts0815_1.dbf

startup force
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2233480 bytes
Variable Size            3623881592 bytes
Database Buffers         2768240640 bytes
Redo Buffers               19324928 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/oradata06/testaaaaa/ts0815_1.dbf'

###recover datafile时出现了ORA-00752
rman target /

RMAN> restore datafile 8;

Starting restore at 20150815 13:48:46
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=463 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /oradata06/testaaaaa/ts0815_1.dbf
channel ORA_DISK_1: reading from backup piece /oradata06/fra/TSTDB1/backupset/2015_08_15/o1_mf_nnndf_TAG20150815T134257_1lU8o7H7G_.bkp
channel ORA_DISK_1: piece handle=/oradata06/fra/TSTDB1/backupset/2015_08_15/o1_mf_nnndf_TAG20150815T134257_1lU8o7H7G_.bkp tag=TAG20150815T134257
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20150815 13:48:49

RMAN> recover datafile 8;

Starting recover at 20150815 13:49:00
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 201 is already on disk as file /oradata06/arch/1_201_884446916.dbf
archived log for thread 1 with sequence 202 is already on disk as file /oradata06/arch/1_202_884446916.dbf
archived log for thread 1 with sequence 203 is already on disk as file /oradata06/arch/1_203_884446916.dbf
archived log for thread 1 with sequence 204 is already on disk as file /oradata06/arch/1_204_884446916.dbf
archived log file name=/oradata06/arch/1_201_884446916.dbf thread=1 sequence=201
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/15/2015 13:49:02
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/oradata06/arch/1_201_884446916.dbf'
ORA-00283: recovery session canceled due to errors
ORA-00752: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 8, block# 131, file offset is 1073152 bytes)
ORA-10564: tablespace TS0815_1
ORA-01110: data file 8: '/oradata06/testaaaaa/ts0815_1.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 7856057

附：recover过程中的alert.log日志
Full restore complete of datafile 8 /oradata06/testaaaaa/ts0815_1.dbf. Elapsed time: 0:00:00
checkpoint is 12793185443139
last deallocation scn is 12723364365344
Sat Aug 15 13:49:00 2015
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 8
Media Recovery Start
Serial Media Recovery started
ORA-279 signalled during: alter database recover if needed
datafile 8
...
alter database recover logfile '/oradata06/arch/1_201_884446916.dbf'
Media Recovery Log /oradata06/arch/1_201_884446916.dbf
Recovery of Online Redo Log: Thread 1 Group 1 Seq 202 Reading mem 0
Mem# 0: /oradata06/testaaaaa/redo01a.log
Mem# 1: /oradata06/testaaaaa/redo01b.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 203 Reading mem 0
Mem# 0: /oradata06/testaaaaa/redo02a.log
Mem# 1: /oradata06/testaaaaa/redo02b.log
Recovery of Online Redo Log: Thread 1 Group 4 Seq 204 Reading mem 0
Mem# 0: /oradata06/testaaaaa/redo04a.log
Mem# 1: /oradata06/testaaaaa/redo04b.log
REDO APPLICATION DURING RECOVERY HAS DETECTED THAT THE DATABASE
LOST A DISK WRITE OF BLOCK 131, FILE 8 <---定位到了发生lost write的数据块
DURING NORMAL DATABASE OPERATION.
NO REDO AT OR AFTER SCN 12793185443253 CAN BE USED FOR RECOVERY.
Recovery of Online Redo Log: Thread 1 Group 4 Seq 204 Reading mem 0
Mem# 0: /oradata06/testaaaaa/redo04a.log
Mem# 1: /oradata06/testaaaaa/redo04b.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 203 Reading mem 0
Mem# 0: /oradata06/testaaaaa/redo02a.log
Mem# 1: /oradata06/testaaaaa/redo02b.log
Media Recovery failed with error 752
ORA-283 signalled during: alter database recover logfile '/oradata06/arch/1_201_884446916.dbf'...