[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 Production

create 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 ABCDEF

SCOTT@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 -c

SCOTT@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 -c

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> @ query_curr_redo.sql 6YYYYY
$ strings /mnt/ramdisk/book/redo02.log | grep -i 6YYYYY | uniq -c
--//可以发现没有写在线日志.