Focus on Oracle: January 2010 Archives

简单描述一下buffer cache管理的过程

By cui hua on January 25, 2010 5:09 PM | 2 Comments | No TrackBacks

我们先来看buffer header的具体结构：

接着，我们再来看跟buffer cache的管理有关的5个LIST，这些LIST可以简单的认为就是存储了上述buffer header结构的双向指针链表（doubly linked lists），这5个LIST分别是：

LRU LIST：

Buffers containing block images being used

LRU AUXILIARY LIST：

Buffers ready to be used for I/O or CR build，在实例启动的时候，当前实例的buffer cache中的所有buffer都会被链接到LRU AUXILIARY LIST中，当oracle要从datafile中读一个block到buffer cache中来的时候，首先就会去扫描LRU AUXILIARY LIST，如果此时LRU AUXILIARY LIST非空，则直接把从datafile中读到的那个block拷到LRU AUXILIARY LIST上那个buffer header所链接的buffer cache block中，同时把这个buffer header再挪到LRU LIST中；如果此时LRU AUXILIARY LIST为空，则表明当前没有free block，此时就需要去扫描LRU LIST。

WRITE LIST：

Dirty Buffers requiring I/O，当LRU AUXILIARY LIST为空的时候，oracle需要去扫描LRU LIST，当扫到一个block，发现它的Flag是dirty的时候，oracle会把这个block所对应的buffer header从LRU LIST移到WRITE LIST。

CHECKPOINT QUEUE LIST：

Dirty Buffers requiring I/O，当buffer cache中的block第一次变为dirty的时候，它首先一定会在LRU LIST中存在，并且其Flag会被标记为dirty。同时，oracle会把这个block添加到CHECKPOINT QUEUE LIST中。

WRITE AUXILIARY LIST：

Dirty Buffers with I/O in progress，当DBWR需要写dirty block的时候，oracle会把那些dirty block从WRITE LIST移到WRITE AUXILIARY LIST，然后开始写。写完了，会把已经写完的dirty block从CHECKPOINT QUEUE LIST中删掉，同时把这些block再从WRITE AUXILIARY LIST移到LRU AUXILIARY LIST。

简单总结一下，oracle中buffer cache管理最简单的流程就是：

oracle首先会去LRU LIST中找，看看要找的block是否已经缓存在buffer cache中，找到了就直接用；找不到就再去LRU AUXILIARY LIST中找free block，如果找到了（即LRU AUXILIARY LIST非空），就去datafile中把要读的那个block拷到LRU AUXILIARY LIST上那个buffer header所链接的buffer cache block中，同时把这个buffer header再挪到LRU LIST中；如果找不到（即此时LRU AUXILIARY LIST为空），则表明当前没有free block，此时就需要去扫描LRU LIST，因为要找一个buffer header来reuse。此时，在扫描LRU LIST的过程中，当扫到一个buffer header，且发现它的Flag是dirty的时候，oracle会把这个buffer header从LRU LIST移到WRITE LIST；当WRITE LIST达到一定的阀值，DBWR会写这些buffer header所指向的dirty block，当DBWR需要写dirty block的时候，oracle会把那些dirty block所对应的buffer header从WRITE LIST移到WRITE AUXILIARY LIST，然后开始写。写完了，会把已经写完的dirty block所对应的buffer header从CHECKPOINT QUEUE LIST中删掉，同时把这些dirty block所对应的buffer header再从WRITE AUXILIARY LIST移到LRU AUXILIARY LIST，如此循环往复，生生不息！

详细解析ASSM的Segment Header的结构

By cui hua on January 24, 2010 12:47 AM | No Comments | No TrackBacks

之前已经写过：

"详细解析9i和10g的 datafile header"

"详细解析LMT的datafile的物理结构"

"详细解析datafile的 status"

"详细解析oracle中的 transaction"

"详细解析truncate引发的object checkpoint"

这里是详细解析系列的第六篇文章，在这篇文章里，我们逐个byte的解析了9i下ASSM的Segment Header的结构，为什么选9i而不是10g，是因为我用的笔记本上只有9i的BBED，9i的BBED和10g不兼容（因为10g里datafile header的结构发生了变化）。

详细内容在如下的这篇word文档里：

ASSM_Segment_Header_Structure.doc

bing，我答应过你要写一些关于ASSM的东西，这个就算是开头吧，以后我还会继续。

oracle中如何分析索引的结构

By cui hua on January 15, 2010 1:05 PM | 2 Comments | No TrackBacks

在"Index Rebuild, the Need vs the Implications [ID 989093.1]" 中明确指出：

Due to the above it is strongly advised not to rebuild indexes on a regular basis but instead use proper diagnostics. Especially avoid to rebuild index on a regular basis as there is a reason why the index becomes in this state over-and-over again.

Please see the following note that lists a script that can be used to analyze the index structure. It does not use the 'analyze index validate structure' command but is based on the current table and index statistics to estimate the index size.

这个里面提到的脚本就在"Script to investigate a b-tree index structure [ID 989186.1]" 中，但是遗憾的是上述脚本是wrap过的。

不过没关系，我们把它unwrap掉，可以看到里面的包index_util的核心过程inspect_index的代码是：

PROCEDURE INSPECT_INDEX (AIDXOWNER IN VARCHAR2, AIDXNAME IN VARCHAR2) IS

VIDXREC ALL_INDEXES%ROWTYPE;

VTABNUMROWS ALL_TABLES.NUM_ROWS%TYPE;

VNUMNULLS ALL_TAB_COLUMNS.NUM_NULLS%TYPE;

VBLKSIZE DBA_TABLESPACES.BLOCK_SIZE%TYPE;

VIDXOBJID ALL_OBJECTS.OBJECT_ID%TYPE;

VUNIQIND NUMBER(2) := 0;

VAVGIDXROWLEN NUMBER := 0;

VAVGROWSPERBLK NUMBER := 0;

VESTSIZE NUMBER := 0;

VSQLSTR VARCHAR2(512) := '';

VBLKCNT NUMBER := 0;

VROWCNT NUMBER := 0;

VIDXSTRUCTSTR VARCHAR2(4000) := '';

VIDXHISTCNT NUMBER;

TYPE IDXRECORD IS RECORD (

ROWS_PER_BLOCK NUMBER,

BLOCKS NUMBER);

TYPE IDXARRAY IS TABLE OF IDXRECORD;

LF_DATA IDXARRAY;

VIDXLOGREC ISA_INDEX_LOG%ROWTYPE;

CURSOR CUR_INS_OR_UPD IS

SELECT *

FROM ISA_INDEX_LOG

WHERE OWNER = AIDXOWNER

AND INDEX_NAME = AIDXNAME;

BEGIN

DBMS_APPLICATION_INFO.SET_MODULE('index_util','inspect_index');

SELECT *

INTO VIDXREC

FROM ALL_INDEXES

WHERE OWNER = AIDXOWNER

AND INDEX_NAME = AIDXNAME;

SELECT NUM_ROWS

INTO VTABNUMROWS

FROM ALL_TABLES

WHERE OWNER = VIDXREC.TABLE_OWNER

AND TABLE_NAME = VIDXREC.TABLE_NAME;

SELECT BLOCK_SIZE

INTO VBLKSIZE

FROM DBA_TABLESPACES

WHERE TABLESPACE_NAME = VIDXREC.TABLESPACE_NAME;

SELECT OBJECT_ID

INTO VIDXOBJID

FROM ALL_OBJECTS

WHERE OWNER = AIDXOWNER

AND OBJECT_NAME = AIDXNAME;

SELECT SUM(TC.AVG_COL_LEN + DECODE(IC.DESCEND,'ASC',0,1)) , SUM(TC.NUM_NULLS)

INTO VAVGIDXROWLEN, VNUMNULLS

FROM ALL_IND_COLUMNS IC, ALL_TAB_COLUMNS TC

WHERE IC.COLUMN_NAME = TC.COLUMN_NAME

AND IC.TABLE_OWNER = TC.OWNER

AND IC.TABLE_NAME = TC.TABLE_NAME

AND IC.INDEX_OWNER = AIDXOWNER

AND IC.INDEX_NAME = AIDXNAME;

IF VIDXREC.UNIQUENESS = 'UNIQUE'

THEN VUNIQIND := 10;

ELSE VUNIQIND := 11;

END IF;

VESTSIZE := ROUND((1.01 * (VIDXREC.NUM_ROWS * VUNIQIND + VAVGIDXROWLEN * (VTABNUMROWS - VNUMNULLS))) / (VBLKSIZE - 100));

VAVGROWSPERBLK := ROUND ((VBLKSIZE - 100) * ((100-VIDXREC.PCT_FREE)/100) / (VAVGIDXROWLEN + VUNIQIND));

VSQLSTR := 'SELECT rows_per_block, count(*) blocks FROM (SELECT /*+ cursor_sharing_exact ' ||

'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || VIDXREC.TABLE_OWNER || '.' || VIDXREC.TABLE_NAME ||

',' || AIDXNAME || ') noparallel_index(' || VIDXREC.TABLE_NAME ||

',' || AIDXNAME || ') */ sys_op_lbid(' || VIDXOBJID ||

', ''L'', ' || VIDXREC.TABLE_NAME || '.rowid) block_id, ' ||

'COUNT(*) rows_per_block FROM ' || VIDXREC.TABLE_OWNER || '.' || VIDXREC.TABLE_NAME || ' GROUP BY sys_op_lbid(' ||

VIDXOBJID || ', ''L'', ' || VIDXREC.TABLE_NAME || '.rowid)) group by rows_per_block order by rows_per_block';

EXECUTE IMMEDIATE VSQLSTR BULK COLLECT INTO LF_DATA;

FOR I IN 1..LF_DATA.LAST LOOP

VBLKCNT := VBLKCNT + LF_DATA(I).BLOCKS;

VROWCNT := VROWCNT + LF_DATA(I).ROWS_PER_BLOCK;

VIDXSTRUCTSTR := VIDXSTRUCTSTR || LF_DATA(I).ROWS_PER_BLOCK || ' - ' || LF_DATA(I).BLOCKS || CHR(10);

END LOOP;

OPEN CUR_INS_OR_UPD;

FETCH CUR_INS_OR_UPD INTO VIDXLOGREC;

IF CUR_INS_OR_UPD%NOTFOUND

THEN INSERT INTO ISA_INDEX_LOG VALUES (AIDXOWNER, AIDXNAME, SYSDATE, NULL, NULL, VIDXREC.LEAF_BLOCKS, VIDXREC.PCT_FREE, VESTSIZE, (100/VIDXREC.LEAF_BLOCKS)*(VIDXREC.LEAF_BLOCKS-VBLKCNT), VAVGROWSPERBLK, NULL, VIDXSTRUCTSTR);

ELSE INSERT INTO ISA_INDEX_HIST VALUES (AIDXOWNER, AIDXNAME, VIDXLOGREC.LAST_INSPECTED, VIDXLOGREC.LEAF_BLOCKS, VIDXLOGREC.PCT_FREE, VIDXLOGREC.EST_UTILIZATION, VIDXLOGREC.EMPTY_BLOCKS, VIDXLOGREC.AVG_#ROWS_PER_BLK, VIDXLOGREC.ACTION_FLAG, VIDXLOGREC.IDX_LAYOUT);

SELECT COUNT(*) INTO VIDXHISTCNT FROM ISA_INDEX_HIST WHERE OWNER = AIDXOWNER AND INDEX_NAME = AIDXNAME;

WHILE VIDXHISTCNT > VHISTRET LOOP

DELETE FROM ISA_INDEX_HIST

WHERE OWNER = AIDXOWNER

AND INDEX_NAME = AIDXNAME

AND INSPECTED_DATE = (SELECT MIN(INSPECTED_DATE) FROM ISA_INDEX_HIST WHERE OWNER = AIDXOWNER AND INDEX_NAME = AIDXNAME);

VIDXHISTCNT := VIDXHISTCNT - 1;

END LOOP;

UPDATE ISA_INDEX_LOG SET LAST_INSPECTED = SYSDATE, LAST_COALESCED = VIDXLOGREC.LAST_COALESCED, LAST_REBUILD = VIDXLOGREC.LAST_REBUILD, LEAF_BLOCKS = VIDXREC.LEAF_BLOCKS, PCT_FREE = VIDXREC.PCT_FREE, EST_UTILIZATION = VESTSIZE, EMPTY_BLOCKS = (100/VIDXREC.LEAF_BLOCKS)*(VIDXREC.LEAF_BLOCKS-VBLKCNT), AVG_#ROWS_PER_BLK = VAVGROWSPERBLK, IDX_LAYOUT= VIDXSTRUCTSTR

WHERE OWNER = AIDXOWNER

AND INDEX_NAME = AIDXNAME;

END IF;

CLOSE CUR_INS_OR_UPD;

COMMIT;

END INSPECT_INDEX;

可以看到上述代码的核心就是我用蓝色字体显示的那一段代码，其中用到了一个oracle的internal function（即SYS_OP_LBID(<object_id>, <block_type>, <table_name.rowid>），它的作用是Leaf Block ID Scanning，即

/*

Has many functional variations, but the point is to scan through index leaf blocks and calculate a measure of the quality of the index. The way it is counted, and the thing being counted, depends on the type of index (viz: bitmap, simple b-tree, local/global index, IOT, or secondary on IOT, cluster).

*/

上述过程其实就是模拟了analyze index ... validate structure，但是它的好处是不用像analyze index ... validate structure那样在目标表上加exclusive的table lock. 对这一点，"Index Rebuild, the Need vs the Implications [ID 989093.1] "中也有描述：

Most scripts around depend on the index_stats dynamic table. This is populated by the command:

analyze index ... validate structure;

While this is a valid method to inspect the index, it grabs an exclusive table lock while analyzing the index. Especially for large indexes this can be very dramatic as DML operations on the table are not permitted during that time.

google reader死活抓不到我这边的更新

By cui hua on January 13, 2010 5:51 PM | 2 Comments | No TrackBacks

今天写了详细解析系列的第5篇文章---- "详细解析truncate引发的object checkpoint".

写完后就发现google reader死活抓不到上述文章了，不明白为什么？

难道是有字数的限制吗？我测一下。

新写的这篇能被google reader抓到吗？

详细解析truncate引发的object checkpoint

By cui hua on January 13, 2010 11:44 AM | No Comments | No TrackBacks

之前已经写过：

"详细解析9i和10g的datafile header"

"详细解析LMT的datafile的物理结构"

"详细解析datafile的status"

"详细解析oracle中的transaction"

这里是详细解析系列的第五篇文章，在这篇文章里，我们详细解析了oracle在truncate时引发的object checkpoint具体做了什么事情，并阐明了可能会由此导致ORA-00600 [kcbz_check_objd_typ_3]。

在 "log switch checkpoint" 这篇文章里我们已经提到过oracle里一共有7种checkpoint，它们分别是：

1、Full Checkpoint

2、Thread Checkpoint

3、File Checkpoint

4、Object Checkpoint

5、Parallel Query Checkpoint

6、Incremental Checkpoint

7、Log Switch Checkpoint

Oracle通常会在你执行如下操作的时候触发object checkpoint：

Drop table XXX

Drop table XXX purge

Truncate table XXX

Drop index XXX

这么做的目的是为了缩短recovery的时间.

简单的说，oracle在truncate时触发的object checkpoint做的事情用一句话来概括就是：writes block images to the database for all dirty buffers belonging to an object from all instances.

但是事情往往没有那么美好，oracle的上述机制在极端的情况下会导致ORA-00600 [kcbz_check_objd_typ_3]，因为oracle在truncate的时候并没有对这个object所在的dictionary cache做checkpoint！

我们来看一个实例：

SQL> create table test_truncate1 as select * from dba_users;

Table created

SQL> select count(*) from test_truncate1;

COUNT(*)

----------

29

SQL> alter system checkpoint;

System altered

SQL> select object_id,data_object_id from dba_objects where object_name='TEST_TRUNCATE1';

OBJECT_ID DATA_OBJECT_ID

---------- --------------

30217 30217

SQL> select dataobj#,file#,block# from tab$ where obj#=30217;

DATAOBJ# FILE# BLOCK#

---------- ---------- ----------

30217 1 50601

SQL> select dataobj#,to_char(dataobj#,'XXXXXX') RAW_DATAOBJ#,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location from obj$ where obj#=30217;

DATAOBJ# RAW_DATAOBJ# LOCATION

---------- ------------ --------------------------------------------------------------------------------

30217 7609 1_30058

在没执行truncate之前，我们先去看一下obj$中test_truncate1的dataobj#和test_truncate1的段头：

先来看obj$中test_truncate1的dataobj#：

BBED> set file 1

FILE# 1

BBED> set block 30058

BLOCK# 30058

BBED> find /x 544553545f5452554e4341544531

File: /dras21/testdb/system01.dbf (1)

Block: 30058 Offsets: 610 to 1121 Dba:0x0040756a

------------------------------------------------------------------------

54455354 5f545255 4e434154 453102c1 02ff02c1 0307786e 010d0b0a 0f07786e

010d0b0a 0f07786e 010d0b0a 0f02c102 ffff0180 ff02c107 02c1022c 011104c3

......省略显示部分内容

0207786d 0c170b19 3707786d 0c170b1a 1107786d 0c170b19 3702c102 ffff0180

<32 bytes per line>

通过上述offset 610，我们可以定位出这里应该是kdbr[54]：

BBED> p kdbr

sb2 kdbr[0] @110 8012

sb2 kdbr[1] @112 7909

......省略显示部分内容

sb2 kdbr[53] @216 3642

sb2 kdbr[54] @218 502

......省略显示部分内容

sb2 kdbr[72] @254 1517

sb2 kdbr[73] @256 875

BBED> p *kdbr[54]

rowdata[0]

----------

ub1 rowdata[0] @594 0x2c

BBED> x /rnnnc

rowdata[0] @594

----------

flag@594: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@595: 0x02

cols@596: 17

col 0[4] @597: 30217

col 1[4] @602: 30217

col 2[1] @607: 0

col 3[14] @609: TEST_TRUNCATE1

......省略显示部分内容

col 16[2] @666: ?

从上述结果中我们可以看到obj$中test_truncate1的dataobj#现在是30217。

我们再来看test_truncate1的段头：

BBED> set file 1

FILE# 1

BBED> set block 50601

BLOCK# 50601

BBED> p ktech

struct ktech, 72 bytes @20

ub4 spare1_ktech @20 0x00000000

......省略显示部分内容

struct hwmark_ktech, 32 bytes @48

ub4 extno_ktehw @48 0x00000000

ub4 blkno_ktehw @52 0x00000001

ub4 extsize_ktehw @56 0x00000007

ub4 blkaddr_ktehw @60 0x0040c5ab

ub4 mapblk_ktehw @64 0x00000000

......省略显示部分内容

ub4 kxidsqn @84 0x00000000

ub4 flag_ktech @88 0x00000000 (NONE)

BBED> p ktemh

struct ktemh, 16 bytes @92

ub4 count_ktemh @92 0x00000001

ub4 next_ktemh @96 0x00000000

ub4 obj_ktemh @100 0x00007609

ub4 flag_ktemh @104 0x40000000

可以看到现在test_truncate1的段头中的HWM是0x0040c5ab，HWM下的block数量是0x00000001，它所指向的data object id是0x00007609（即30217）。

现在我们来开始执行truncate操作：

SQL> truncate table test_truncate1;

Table truncated

SQL> select count(*) from test_truncate1;

COUNT(*)

----------

0

然后我们执行shutdown abort：

$ sqlplus '/ as sysdba';

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Jan 13 11:08:50 2010

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production.

SQL>shutdown abort

ORACLE instance shut down.

现在我们再去看一下obj$中test_truncate1的dataobj#和test_truncate1的段头：

BBED> set file 1

FILE# 1

BBED> set block 30058

BLOCK# 30058

BBED> p *kdbr[54]

rowdata[0]

----------

ub1 rowdata[0] @594 0x2c

BBED> x /rnnnc

rowdata[0] @594

----------

flag@594: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@595: 0x02

cols@596: 17

col 0[4] @597: 30217

col 1[4] @602: 30217

......省略显示部分内容

col 16[2] @666: ?

从上述结果中我们可以看到，oracle并没有对test_truncate1的dictionary cache做checkpoint，此时test_truncate1的data object id还是30217。

BBED> set file 1

FILE# 1

BBED> set block 50601

BLOCK# 50601

BBED> p ktech

struct ktech, 72 bytes @20

ub4 spare1_ktech @20 0x00000000

......省略显示部分内容

struct hwmark_ktech, 32 bytes @48

ub4 extno_ktehw @48 0x00000000

ub4 blkno_ktehw @52 0x00000000

ub4 extsize_ktehw @56 0x00000007

ub4 blkaddr_ktehw @60 0x0040c5aa

ub4 mapblk_ktehw @64 0x00000000

......省略显示部分内容

ub4 kxidsqn @84 0x00000023

ub4 flag_ktech @88 0x00000001 (KTE_LOCKED)

BBED> p ktemh

struct ktemh, 16 bytes @92

ub4 count_ktemh @92 0x00000001

ub4 next_ktemh @96 0x00000000

ub4 obj_ktemh @100 0x0000760a

ub4 flag_ktemh @104 0x40000000

可以看到现在test_truncate1的段头的HWM由0x0040c5ab变为了0x0040c5aa，HWM下的block数量由0x00000001变为了0x00000000，它所指向的data object id由0x00007609（即30217）变为了0x0000760a（即30218）。

也就是说当oracle在做truncate触发的object checkpoint的时候，会马上去改写目标object的段头。

朋友们看到这里应该已经明白了为什么我说----"在极端情况下可能会由此导致ORA-00600 [kcbz_check_objd_typ_3]？"

比如说，你这个时候对表A先做了truncate，接着又对表A插入了一些记录，接着库就crash了，而且crash的原因是因为current redo log损坏或者被rm掉了，那当你采用非常规手段恢复了上述数据库后，在10gR2里，如果你这个时候再想去select表A的时候，很有可能oracle这里就会报错ORA-00600 [kcbz_check_objd_typ_3]，因为oracle这里检查到表A段头记录的data object id和数据字典里记录的data object id已经不一致了。

如何通过修改数据字典来恢复被truncate的数据

By cui hua on January 11, 2010 4:02 PM | No Comments | No TrackBacks

在这篇文章里，我们演示了如何以修改数据字典并配合BBED的方式来恢复被truncate的数据，看这篇文章的朋友有几点一定要注意（免得被我误导了）：

1、如下的方法只适用于MSSM，ASSM要复杂很多；

2、恢复被truncate的数据用ODU就可以了，万万不要用我这里用到的方法；

3、 这里提到的方法没有任何实际意义，仅仅是出于研究的目的。

好了，我们来看一个实例，我是在9.2.0.6上做的测试：

SQL> conn sys/oracle@testdbaix as sysdba;

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as SYS

SQL> create table test_truncate as select * from dba_users;

Table created

SQL> select username from test_truncate;

USERNAME

------------------------------

SYS

SYSTEM

......省略显示部分内容

QS_CB

QS_CS

29 rows selected

SQL> select object_id,data_object_id from dba_objects where object_name='TEST_TRUNCATE';

OBJECT_ID DATA_OBJECT_ID

---------- --------------

30207 30207

data_object_id通常是通过dump数据块或者以logmnr的方式得到。

SQL> select FILE#,BLOCK# from tab$ where obj#=30207;

FILE# BLOCK#

---------- ----------

1 50593

准备工作都做好了，现在我们来truncate：

SQL> truncate table test_truncate;

Table truncated

SQL> select count(*) from test_truncate;

COUNT(*)

----------

0

SQL> select username from test_truncate;

USERNAME

------------------------------

Truncate完后，我们来开始恢复的过程。

首先我们来修改数据字典：

SQL> update tab$ set dataobj#=30207 where obj#=30207;

1 row updated

SQL> update seg$ set hwmincr=30207 where file#=1 and block#=50593;

1 row updated

SQL> update obj$ set dataobj#=30207 where obj#=30207;

1 row updated

SQL> commit;

Commit complete

shutdown上述数据库，接着我们来用BBED修改上述段头（即file#=1 block#=50593）：

对于MSSM而言，有三个地方需要修改，分别是HWM，HWM下的block数量以及段头所指向的data object id，如下是完整的修改过程：

BBED> set file 1

FILE# 1

BBED> set block 50593

BLOCK# 50593

BBED> p ktech

struct ktech, 72 bytes @20

ub4 spare1_ktech @20 0x00000000

word tsn_ktech @24 0

ub4 lastmap_ktech @28 0x00000000

ub4 mapcount_ktech @32 0x00000000

ub4 extents_ktech @36 0x00000001

ub4 blocks_ktech @40 0x00000007

ub2 mapend_ktech @44 0x1020

struct hwmark_ktech, 32 bytes @48

ub4 extno_ktehw @48 0x00000000

ub4 blkno_ktehw @52 0x00000000

ub4 extsize_ktehw @56 0x00000007

ub4 blkaddr_ktehw @60 0x0040c5a2

ub4 mapblk_ktehw @64 0x00000000

ub4 offset_ktehw @68 0x00000000

ub4 flblks_ktehw @72 0x00000000

ub4 blkcnt_ktehw @76 0x00000000

struct locker_ktech, 8 bytes @80

ub2 kxidusn @80 0x0000

ub2 kxidslt @82 0x0000

ub4 kxidsqn @84 0x00000000

ub4 flag_ktech @88 0x00000000 (NONE)

BBED> set offset 60

OFFSET 60

BBED> modify /x 0040c5a3

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /dras21/testdb/system01.dbf (1)

Block: 50593 Offsets: 60 to 571 Dba:0x0040c5a1

------------------------------------------------------------------------

0040c5a3 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000001 00000000 00007600 40000000 0040c5a2 00000007 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> set offset 52

OFFSET 52

BBED> modify /x 00000001

File: /dras21/testdb/system01.dbf (1)

Block: 50593 Offsets: 52 to 563 Dba:0x0040c5a1

------------------------------------------------------------------------

00000001 00000007 0040c5a3 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000001 00000000 00007600 40000000 0040c5a2 00000007

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> p ktemh

struct ktemh, 16 bytes @92

ub4 count_ktemh @92 0x00000001

ub4 next_ktemh @96 0x00000000

ub4 obj_ktemh @100 0x00007600

ub4 flag_ktemh @104 0x40000000

BBED> set offset 100

OFFSET 100

BBED> modify /x 000075ff

File: /dras21/testdb/system01.dbf (1)

Block: 50593 Offsets: 100 to 611 Dba:0x0040c5a1

------------------------------------------------------------------------

000075ff 40000000 0040c5a2 00000007 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply

Check value for File 1, Block 50593:

current = 0xe03d, required = 0xe03d

最后我们来看一下修改后的效果：

SQL>startup

ORACLE instance started.

Total System Global Area 505382744 bytes

Fixed Size 743256 bytes

Variable Size 285212672 bytes

Database Buffers 218103808 bytes

Redo Buffers 1323008 bytes

Database mounted.

Database opened.

SQL >select count(*) from test_truncate;

COUNT(*)

----------

29

SQL >select username from test_truncate;

USERNAME

------------------------------

SYS

SYSTEM

......省略显示部分内容

QS_CB

QS_CS

29 rows selected

即我们已经恢复了上述被truncate掉的数据。

对373472.1中脚本的一点解释

By cui hua on January 7, 2010 3:41 PM | 6 Comments | No TrackBacks

在"Script to Identify Objects and Amount of Blocks in the Buffer Pools - Default, Keep, Recycle, nK Cache [ID 373472.1]"用到了一个查询脚本，这个脚本的内容为：

select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',

4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',

7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,

bh.object_name,bh.blocks

from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,

o.name object_name,count(*) BLOCKS

from obj$ o, x$bh x where o.dataobj# = x.obj

and x.state !=0 and o.owner# !=0

group by set_ds,o.name) bh

where ds.set_id >= pd.bp_lo_sid

and ds.set_id <= pd.bp_hi_sid

and pd.bp_size != 0

and ds.addr=bh.set_ds;

上述脚本用来定位哪些object在buffer cache中存在，占用的buffer cache的量是多少以及占用的是什么类型的buffer cache。

我们现在来用一下上述脚本：

SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',

2 4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',

3 7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,

4 bh.object_name,bh.blocks

5 from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,

6 o.name object_name,count(*) BLOCKS

7 from obj$ o, x$bh x where o.dataobj# = x.obj

8 and x.state !=0 and o.owner# !=0

9 group by set_ds,o.name) bh

10 where ds.set_id >= pd.bp_lo_sid

11 and ds.set_id <= pd.bp_hi_sid

12 and pd.bp_size != 0

13 and ds.addr=bh.set_ds order by bh.blocks desc;

SUBCACHE OBJECT_NAME BLOCKS

------------ ------------------------------ ----------

DEFAULT MGMT_SEVERITY 4688

DEFAULT UPLLKT 4471

DEFAULT SEVERITY_PRIMARY_KEY 2829

DEFAULT UPLTER 2707

DEFAULT UPLBCR 2267

DEFAULT SALWAT 1825

DEFAULT OWBATN 1763

DEFAULT MGMT_SEVERITY_IDX_03 753

DEFAULT IDX_UPLTER_UTETKT 626

DEFAULT MGMT_METRICS_1HOUR_PK 377

DEFAULT OWBWTK 377

DEFAULT MGMT_METRICS_RAW_PK 370

DEFAULT SALDRT 187

DEFAULT SALDAT 137

DEFAULT SALBTH 125

DEFAULT MGMT_SYSTEM_PERFORMANCE_LOG 104

DEFAULT MGMT_METRICS_1DAY_PK 101

DEFAULT MGMT_METRICS 81

DEFAULT IDX_SALWAT_SWABTH 81

DEFAULT IWBCTK 66

......省略显示部分内容

DEFAULT MGMT_CURRENT_SEVERITY_IDX_01 1

DEFAULT MGMT_DB_FEATUREUSAGE_PK 1

SQL> alter system flush buffer_cache;

System altered

SQL> select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',

2 4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',

3 7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,

4 bh.object_name,bh.blocks

5 from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,

6 o.name object_name,count(*) BLOCKS

7 from obj$ o, x$bh x where o.dataobj# = x.obj

8 and x.state !=0 and o.owner# !=0

9 group by set_ds,o.name) bh

10 where ds.set_id >= pd.bp_lo_sid

11 and ds.set_id <= pd.bp_hi_sid

12 and pd.bp_size != 0

13 and ds.addr=bh.set_ds order by bh.blocks desc;

SUBCACHE OBJECT_NAME BLOCKS

------------ ------------------------------ ----------

现在我们来研究一下上述脚本的含义是什么？

里面主要涉及到了x$kcbwds 、x$kcbwbpd及x$bh，x$bh的含义我相信大家都清楚，但是另外两个（即x$kcbwds 和x$kcbwbpd）的含义是什么呢？

答案是----我也不知道，而且这里你也不用去metalink上查了，oracle一般不会把这种internal的东西公开。

但是，即便在不清楚上述x$视图含义的情况下，还是可以来分析上述脚本的，我们来看一下分析的过程：

在我执行了下述两条sql后：

select * from v$fixed_view_definition where view_definition like '%x$kcbwds%';

select * from v$fixed_view_definition where view_definition like '%x$kcbwbpd%';

结果显示x$kcbwds 和x$kcbwbpd只和GV$BUFFER_POOL_STATISTICS有关，且上述查询结果显示GV$BUFFER_POOL_STATISTICS的创建语句为：

select kcbwbpd.inst_id, kcbwbpd.bp_id, kcbwbpd.bp_name, kcbwbpd.bp_blksz, sum(kcbwds.cnum_set),

sum(kcbwds.cnum_repl), sum(kcbwds.cnum_write), sum(kcbwds.cnum_set), sum(kcbwds.buf_got),

sum(kcbwds.sum_wrt), sum(kcbwds.sum_scn), sum(kcbwds.fbwait), sum(kcbwds.wcwait), sum(kcbwds.bbwait),

sum(kcbwds.fbinsp), sum(kcbwds.dbinsp), sum(kcbwds.dbbchg), sum(kcbwds.dbbget), sum(kcbwds.conget),

sum(kcbwds.pread), sum(kcbwds.pwrite)

from x$kcbwds kcbwds, x$kcbwbpd kcbwbpd

where kcbwds.set_id >= kcbwbpd.bp_lo_sid

and kcbwds.set_id <= kcbwbpd.bp_hi_sid

and kcbwbpd.bp_size != 0

group by kcbwbpd.inst_id, kcbwbpd.bp_id, kcbwbpd.bp_name, kcbwbpd.bp_blksz

看到上述结果中蓝色字体表示的部分了吗？注意看，这里和上述查询脚本中用蓝色字体标注出来的限制条件是一样的。

即上述查询脚本实际上就是利用了V$BUFFER_POOL_STATISTICS。

而且因为上述脚本用到了x$bh中的set_ds与x$kcbwds中的addr关联，所以不能直接用V$BUFFER_POOL_STATISTICS和V$BH（V$BUFFER_POOL_STATISTICS里没有字段addr，v$bh中也没有字段set_ds），也就是说这里只能用x$视图关联。

x$bh中的set_ds的含义为"Buffer cache set this buffer is under"。

好了，分析到这里，上述脚本的含义我们已经清楚了，翻译过来就是：

首先关联x$bh和obj$，找到buffer cache中那些不属于sys用户且状态不是free的block，并且按照这些block所在的buffer cache set与所属的object name做group by.

然后用上述group by的结果与V$BUFFER_POOL_STATISTICS的基表x$kcbwds 和x$kcbwbpd做关联，关联条件除了V$BUFFER_POOL_STATISTIC本身所需的条件外（即上述脚本中蓝色字体标注的部分），还加上了buffer cache set应该相同。

对于oracle提供的脚本，我们应该尽量在自己能力所及的范围内不仅知其然，也知其所以然。

Metalink上也许会有我的一篇文章

By cui hua on January 3, 2010 5:54 PM | 1 Comment | No TrackBacks

前两天写过一篇文章----"如何在oracle里使用java存储过程连接db2"，这篇文章的英文版也许以后会在metalink上出现----Note 984464.1 - How To Connect To DB2 Using Java Stored Procedure。

关于如何在oracle里使用java存储过程连接db2，我是开了SR的，oracle的一位叫susan的工程师跟进了上述SR，虽然她并没有给我实质性的帮助，但是她的回复中提到了JCC，就是她提到的这一点，使得我最后顺利的解决了上述问题。

SR还是应该善加利用的！虽然oracle的工程师不一定能帮你解决问题，但是他们有庞大的资源库，这个是第三方的支持难以望其项背的！而且他们的回复往往可以给你带来好的启示。

后来我告诉susan，非常感谢她的帮助，这个问题我自己已经解决了，她接着在SR中提到：

Hi Hua,

Before closing the SR could you provide the steps of your solution , that will help us to improve our knowledge base
since I'll use that information to create a new knowledge Note

Thanks
Susan
Global Customer Support

在我上传了我那篇文章的英文版后，我在上述SR中看到了如下内容：

ODM Answer 02-Jan-2010 5:59:24 GMT+08:00 PM Oracle Support

Unscheduled

Comments

--------

Complete Steps are defined in new created Note 984464.1 - How To Connect To DB2 Using Java Stored Procedure

ODM Question 02-Jan-2010 5:58:33 GMT+08:00 PM Oracle Support

Unscheduled

Comments

另外，新版的metalink可能是由于广泛使用flash的缘故，会导致你的有些SR无法打开，如果你碰到了这种情况，你可以登陆supporthtml.oracle.com，在这里面，你的SR是能够正常打开的。

January 2010 Archives

简单描述一下buffer cache管理的过程

详细解析ASSM的Segment Header的结构

oracle中如何分析索引的结构

google reader死活抓不到我这边的更新

详细解析truncate引发的object checkpoint

如何通过修改数据字典来恢复被truncate的数据

对373472.1中脚本的一点解释

Metalink上也许会有我的一篇文章

Categories

Monthly Archives

Search

Recent Comments

Recent Entries

About this Archive

January 2010 Archives

Categories

Monthly Archives

Search

Recent Comments

Recent Entries

Tag Cloud

About this Archive