本文共 5796 字，大约阅读时间需要 19 分钟。
[20170324]alter system checkpoing会写脏块吗?.txt
--//前几天在测试IMU时什么条件触发什么条件写在线日志. --//在测试执行alter system checkpoint时,遇到一些问题,有时候触发有时候不触发,感觉不好理解. --//上午有空,重复测试看看到底是什么回事.1.环境:
SYS@book> @ &r/ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Productioncreate table t1 ( id number,name varchar2(10));
insert into t1 select rownum,'ABCDEF' from dual connect by level<=5;
commit ; alter system checkpoint ;SCOTT@book> select rowid,t1.* from t1 where rownum=1;
ROWID ID NAME ------------------ ------------ -------------------- AAAWEvAAEAAAAIjAAA 1 ABCDEFSCOTT@book> @ &r/rowid AAAWEvAAEAAAAIjAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ------------ ------------ ------------ ------------ -------------------- -------------------- ---------------------------------------- 90415 4 547 0 0x1000223 4,547 alter system dump datafile 4 block 547 ;--//为了测试方便写一个查询当前在线日志脚本:
$ cat query_curr_redo.sql column member new_value v_member column member noprint SELECT member FROM v$log a, v$logfile b WHERE a.group#(+) = b.group# and a.STATUS='CURRENT' and rownum=1; prompt $ strings &&v_member | grep -i &1 | uniq -c host strings &&v_member | grep -i &1 | uniq -c--//另外必须注意一个问题,就是日志是循环写入的,必要是使用大事务清除里面的日志信息.
update tx set object_name=object_name; update tx set object_name=object_name; update tx set object_name=object_name; commit ; --//多执行几次就覆盖了.2.测试:
SCOTT@book> update t1 set name='1YYYYt1' ; 10 rows updated.SCOTT@book> @ query_curr_redo.sql 1YYYYt1
$ strings /mnt/ramdisk/book/redo03.log | grep -i 1YYYYt1 | uniq -c --//可以发现没有写在线日志.SCOTT@book> alter system checkpoint ;
System altered.SCOTT@book> @ query_curr_redo.sql 1YYYYt1
$ strings /mnt/ramdisk/book/redo03.log | grep -i 1YYYYt1 | uniq -c --//可以发现没有写在线日志.SCOTT@book> @ &r/bbvi 4 547 BVI_COMMAND ---------------------------------------------------------------------------------------------------- bvi -b 4481024 -s 8192 /mnt/ramdisk/book/users01.dbf xxd -c16 -g 2 -s 4481024 -l 8192 /mnt/ramdisk/book/users01.dbf
$ xxd -c16 -g 2 -s 4481024 -l 8192 /mnt/ramdisk/book/users01.dbf
... 0447fa0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0447fb0: 0000 0000 0000 0000 0000 002c 0102 02c1 ...........,...? 0447fc0: 0206 4142 4344 4546 2c01 0202 c103 0641 ..ABCDEF,...?.A 0447fd0: 4243 4445 462c 0102 02c1 0406 4142 4344 BCDEF,...?.ABCD 0447fe0: 4546 2c01 0202 c105 0641 4243 4445 462c EF,...?.ABCDEF, 0447ff0: 0102 02c1 0606 4142 4344 4546 0106 1fa7 ...?.ABCDEF....--//直接查询块也可以发现数据块并没有改写,说明在执行alter system checkpoing不一定会写脏块.
SCOTT@book> rollback ;
Rollback complete.SCOTT@book> alter system checkpoint ;
System altered.3.重复测试:
SCOTT@book> update t1 set name='2YYYYt1' ; 5 rows updated.SCOTT@book> @ query_curr_redo.sql 2YYYYt1
$ strings /mnt/ramdisk/book/redo02.log | grep -i 2YYYYt1 | uniq -c --//可以发现没有写在线日志.SCOTT@book> alter system checkpoint ;
System altered.SCOTT@book> @ query_curr_redo.sql 2YYYYt1
$ strings /mnt/ramdisk/book/redo02.log | grep -i 2YYYYt1 | uniq -c 4 2YYYYt1 1 d82YYYYt1s--//这次ok.
$ xxd -c16 -g 2 -s 4481024 -l 8192 /mnt/ramdisk/book/users01.dbf ... 0447ee0: 0000 0000 0000 0000 0000 0000 0000 2c02 ..............,. 0447ef0: 0202 c106 0732 5959 5959 7431 2c02 0202 ..?.2YYYYt1,... 0447f00: c105 0732 5959 5959 7431 2c02 0202 c104 ?.2YYYYt1,...? 0447f10: 0732 5959 5959 7431 2c02 0202 c103 0732 .2YYYYt1,...?.2 0447f20: 5959 5959 7431 2c02 0202 c102 0732 5959 YYYYt1,...?.2YY 0447f30: 5959 7431 2c00 0202 c102 0641 4243 4445 YYt1,...?.ABCDE 0447f40: 462c 0002 02c1 0306 4142 4344 4546 2c00 F,...?.ABCDEF,. 0447f50: 0202 c104 0641 4243 4445 462c 0002 02c1 ..?.ABCDEF,...? 0447f60: 0506 4142 4344 4546 2c00 0202 c106 0641 ..ABCDEF,...?.A 0447f70: 4243 4445 462c 0202 02c1 0607 3159 5959 BCDEF,...?.1YYY 0447f80: 5974 312c 0202 02c1 0507 3159 5959 5974 Yt1,...?.1YYYYt 0447f90: 312c 0202 02c1 0407 3159 5959 5974 312c 1,...?.1YYYYt1, 0447fa0: 0202 02c1 0307 3159 5959 5974 312c 0202 ...?.1YYYYt1,.. 0447fb0: 02c1 0207 3159 5959 5974 312c 0002 02c1 .?.1YYYYt1,...? 0447fc0: 0206 4142 4344 4546 2c00 0202 c103 0641 ..ABCDEF,...?.A 0447fd0: 4243 4445 462c 0002 02c1 0406 4142 4344 BCDEF,...?.ABCD 0447fe0: 4546 2c00 0202 c105 0641 4243 4445 462c EF,...?.ABCDEF, 0447ff0: 0002 02c1 0606 4142 4344 4546 0506 d9ac ...?.ABCDEF..?3.总结:
--//以上测试我重复多次,有时候出现执行alter system checkpoing,就能出现如上现象.不知道我的测试那里出了问题.SCOTT@book> drop table t1 purge; Table dropped.
SCOTT@book> create table t1 ( id number,name varchar2(10));
Table created.SCOTT@book> insert into t1 select rownum,'ABCDEF' from dual connect by level<=5;
5 rows created.SCOTT@book> commit ;
Commit complete.SCOTT@book> alter system checkpoint ;
System altered.SCOTT@book> update t1 set name='5YYYYY';
5 rows updated.SCOTT@book> @ query_curr_redo.sql 5YYYYY
$ strings /mnt/ramdisk/book/redo02.log | grep -i 5YYYYY | uniq -cSCOTT@book> alter system checkpoint ;
System altered.SCOTT@book> @ query_curr_redo.sql 5YYYYY
$ strings /mnt/ramdisk/book/redo02.log | grep -i 5YYYYY | uniq -c 5 5YYYYY --//这次出现写在线日志.SCOTT@book> drop table t1 purge; Table dropped.
SCOTT@book> create table t1 ( id number,name varchar2(10));
Table created.SCOTT@book> insert into t1 select rownum,'ABCDEF' from dual connect by level<=5;
5 rows created.SCOTT@book> commit ;
Commit complete.SCOTT@book> alter system checkpoint ;
System altered.SCOTT@book> update t1 set name='6YYYYY';
5 rows updated.SCOTT@book> @ query_curr_redo.sql 6YYYYY
$ strings /mnt/ramdisk/book/redo02.log | grep -i 6YYYYY | uniq -cSCOTT@book> alter system checkpoint ;
System altered.SCOTT@book> @ query_curr_redo.sql 6YYYYY
$ strings /mnt/ramdisk/book/redo02.log | grep -i 6YYYYY | uniq -c --//可以发现没有写在线日志.转载地址：http://wdwbm.baihongyu.com/