预备知识：
drop user 能引发checkpoint的前提是 USER下面有TABLE，因为DROP USER 等若隐含了不可flashback drop的DROP TABLE。
如果user下无任何表，则此讨论不成立
多种的checkpoint中， drop table 触发的是OBJECT CHECKPOINT
其行为为 写出所有节点上属于某个 对象 object 的所有脏dirty buffer到磁盘
可能由以下操作触发：
drop table xx;
drop table xx purge;
truncate table xx;
相关的统计信息有
DBWR object drop buffers written
DBWR checkpoints
1、我们通过 统计信息来验证 OBJECT CHECKPOINT
SQL> select * from v$VERSION;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bi
PL/SQL Release 10.2.0.5.0 – Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 – Production
NLSRTL Version 10.2.0.5.0 – Production
SQL> create user dropme identified by oracle;
User created.
SQL> grant dba to dropme;
Grant succeeded.
SQL> create table dropme.tab as select * from dba_objects;
Table created.
SQL> delete dropme.tab;
77262 rows deleted.
SQL> commit;
Commit complete.
SQL> select name,value from v$sysstat where name like ‘DBWR%’;
NAME VALUE
—————————————————————- ———-
DBWR checkpoint buffers written 54367
DBWR thread checkpoint buffers written 0
DBWR tablespace checkpoint buffers written 0
DBWR parallel query checkpoint buffers written 0
DBWR object drop buffers written 0
DBWR transaction table writes 2842
DBWR undo block writes 14263
DBWR revisited being-written buffer 0
DBWR make free requests 0
DBWR lru scans 0
DBWR checkpoints 7
DBWR fusion writes 0
12 rows selected.
SQL> drop user dropme cascade;
User dropped.
SQL> select name,value from v$sysstat where name like ‘DBWR%’;
NAME VALUE
—————————————————————- ———-
DBWR checkpoint buffers written 54367
DBWR thread checkpoint buffers written 0
DBWR tablespace checkpoint buffers written 0
DBWR parallel query checkpoint buffers written 0
DBWR object drop buffers written 1167
DBWR transaction table writes 2842
DBWR undo block writes 14263
DBWR revisited being-written buffer 0
DBWR make free requests 0
DBWR lru scans 0
DBWR checkpoints 8
DBWR fusion writes 0
12 rows selected.
可以看到 DBWR checkpoints 增长了1， DBWR object drop buffers written 增长了1167
从而证明确实发生了 OBJECT CHECKPOINT
2、通过dump buffer 来证明
session A:
SQL> create table dropme.tab as select * from dba_objects;
create table dropme.tab as select * from dba_objects
*
ERROR at line 1:
ORA-01918: user ‘DROPME’ does not exist
SQL> create user dropme identified by oracle;
User created.
SQL> grant dba to dropme;
Grant succeeded.
SQL> create table dropme.tab as select * from dba_objects;
Table created.
SQL> delete dropme.tab;
77262 rows deleted.
SQL> commit;
Commit complete.
此时SESSION B 上来suspend DBWR让DBWR HANG住：
[oracle@vrh8 ~]$ ps -ef|grep dbw
oracle 6739 1 0 Jul11 ? 00:00:29 ora_dbw0_G10R25
oracle 29514 29491 0 08:16 pts/1 00:00:00 grep dbw
[oracle@vrh8 ~]$
[oracle@vrh8 ~]$
[oracle@vrh8 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 – Production on Mon Jul 15 08:16:36 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> oradebug setospid 6739;
Oracle pid: 5, Unix process pid: 6739, image: oracle@vrh8.oracle.com (DBW0)
SQL> oradebug suspend;
Statement processed.
然后SESSION A执行drop user :
SQL> drop user dropme cascade;
由于DBWR无法工作所以 object checkpoint 无法完成所以drop user HANG
此时session C 上来dump buffers level 1;
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump buffers 1;
Statement processed.
SQL>
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_29541.trc
分析trace ， 可以看到大量object checkpoint list上的dirty buffer;
[oracle@vrh8 ~]$ grep -B4 -A3 object_ckpt_list /s01/admin/G10R25/udump/g10r25_ora_29541.trc|less
BH (0x71fce868) file#: 4 rdba: 0x010bacbf (4/765119) class: 1 ba: 0x71a6a000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 91
dbwrid: 0 obj: 94007 objn: 94007 tsn: 4 afn: 4
hash: [70fc5298,a5d26830] lru: [71fd0bf8,71fce7d8]
obj-flags: object_ckpt_list
ckptq: [71fce678,71fd0ba8] fileq: [71fce138,71fd4958] objq: [71fd0e88,71fce628]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty gotten_in_current_mode redo_since_read
—
BH (0x6ffd97b8) file#: 4 rdba: 0x010bada8 (4/765352) class: 1 ba: 0x6fbb4000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 91
dbwrid: 0 obj: 94007 objn: 94007 tsn: 4 afn: 4
hash: [6ffdc018,a5d26d80] lru: [6ffd9948,6ffd9728]
obj-flags: object_ckpt_list
ckptq: [6ffd96d8,6ffd9a08] fileq: [6ffd9088,6ffd9e58] objq: [6ffd9bd8,6ffd9578]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty gotten_in_current_mode redo_since_read
—
BH (0x6ff9c528) file#: 4 rdba: 0x010bae91 (4/765585) class: 1 ba: 0x6f482000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 91
dbwrid: 0 obj: 94007 objn: 94007 tsn: 4 afn: 4
hash: [6ff9e618,a5d272d0] lru: [6ff9c6b8,6ff9c498]
obj-flags: object_ckpt_list
ckptq: [6ff9c448,6ff9c778] fileq: [6ff9bf08,6ff9ccd8] objq: [6ff9ca58,6ff9c2e8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty gotten_in_current_mode redo_since_read
—
BH (0x6efe62a8) file#: 4 rdba: 0x010baf7a (4/765818) class: 1 ba: 0x6ed32000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 91
dbwrid: 0 obj: 94007 objn: 94007 tsn: 4 afn: 4
hash: [70f97bd8,a5d27820] lru: [6efe6438,6efe6218]
obj-flags: object_ckpt_list
ckptq: [6efe61c8,6efe64f8] fileq: [6efe4eb8,6efe6948] objq: [6efe66c8,6efe6068]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty gotten_in_current_mode redo_since_read
—
[oracle@vrh8 ~]$ grep object_ckpt_list /s01/admin/G10R25/udump/g10r25_ora_29541.trc|wc -l
3700
有3700个dirty buffer在object checkpoint list上
之后释放DBWR
SQL> oradebug resume;
Statement processed.
则drop user 顺利完成， 之后再dump buffer:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump buffers 1;
Statement processed.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_29636.trc
[oracle@vrh8 ~]$ grep object_ckpt_list /s01/admin/G10R25/udump/g10r25_ora_29636.trc|wc -l
14
仅有14个dirty buffer在object checkpoint list上了
3、通过内部视图来判断
X$ACTIVECKPT代表 活跃的 检查点队列， X$CKTPBUF代表buffer checkpoint queue
X$ACTIVECKPT的 ckpt_type 0代表PQ induced Tablespace/Object checkpoint
7代表Incremental ckpt
10代表object reuse/truncate checkpoint
11 代表object checkpoint;
使用方法2中的步骤：
SQL> oradebug setospid 6739;
Oracle pid: 5, Unix process pid: 6739, image: oracle@vrh8.oracle.com (DBW0)
SQL> oradebug suspend;
Statement processed.
HANG DBWR
检查 x$activeckpt;
SQL> select ckpt_type from x$activeckpt;
CKPT_TYPE
———-
7
0
0
0
0
0
0
0
0
之后呢
SQL> drop user dropme cascade;
并查看
SQL> select ckpt_type from x$activeckpt;
CKPT_TYPE
———-
10 ==》10代表object reuse/truncate checkpoint
10
3
7
0
0
0
0
0
0
0
0
12 rows selected.
SQL> select count(*),BUF_RBA_SEQ from X$CKPTBUF
2 where BUF_RBA_SEQ!=0
3 group by BUF_RBA_SEQ;
COUNT(*) BUF_RBA_SEQ
———- ———–
3 1356
5 1355
释放dbwr
SQL> oradebug resume;
Statement processed.
SQL> select ckpt_type from x$activeckpt;
CKPT_TYPE
———-
7
0
0
0
0
0
0
0
0
Comment