如何禁掉ASM实例的AMM
Posted: March 7, 2014 | Author: Cui Hua | Filed under: Oracle | Leave a comment »先交代一下背景：RedHat 6.3 x86_64上的11.2.0.3的两节点RAC，已经打了GI PSU 11.2.0.3.9和Patch:17063116 for 11.2.0.3.7以及Patch:11072246 for 11.2.0.3.9。
这里我想配一下Hugepage，但因为Hugepage和AMM不兼容以及11.2.0.3 RAC的ASM实例默认会启用AMM，所以我得把ASM实例的AMM给禁掉。
本来以为是很简单的操作，但出乎我意料的是，一度出现了我怎么都禁不掉ASM实例的AMM的情形。
我们来看一下整个操作过程：
在任意一个RAC节点执行如下操作（注意——如下做法是错误的做法，我后面会跟正确的做法）：
[oracle@rddb2 ~]$ su – grid
Password:
[grid@rddb2 ~]$ sqlplus ‘/ as sysasm’;
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 6 10:29:23 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> show parameter target;
NAME TYPE VALUE
———————————— ———————- ——————————
memory_max_target big integer 272M
memory_target big integer 272M
pga_aggregate_target big integer 0
sga_target big integer 0
SQL> alter system reset memory_target scope=spfile sid=’*';
alter system reset memory_target scope=spfile sid=’*’
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE
SQL> alter system set memory_target=0 scope=spfile sid=’*';
System altered.
SQL> alter system set memory_max_target=0 scope=spfile sid=’*';
System altered.
SQL> alter system set sga_target=512M scope=spfile sid=’*';
System altered.
SQL> alter system set sga_max_size=1G scope=spfile sid=’*';
System altered.
SQL> alter system set shared_pool_size=150M scope=spfile sid=’*';
System altered.
停掉数据库：
[grid@rddb2 ~]$ su – oracle
Password:
[oracle@rddb2 ~]$ srvctl stop database -d db112
在各个RAC节点依次执行如下操作以重启ASM实例：
停RAC节点1的GI：
[grid@rddb2 ~]$ su – root
Password:
[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rddb2′
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rddb2′
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘rddb2′
CRS-2673: Attempting to stop ‘ora.oc4j’ on ‘rddb2′
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘rddb2′
……省略显示部分内容
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘rddb2′
CRS-2677: Stop of ‘ora.gpnpd’ on ‘rddb2′ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rddb2′ has completed
CRS-4133: Oracle High Availability Services has been stopped.
停RAC节点2的GI：
[grid@rddb3 ~]$ su – root
Password:
[root@rddb3 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rddb3′
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rddb3′
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘rddb3′
CRS-2673: Attempting to stop ‘ora.rddb2.vip’ on ‘rddb3′
……省略显示部分内容
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘rddb3′
CRS-2677: Stop of ‘ora.gpnpd’ on ‘rddb3′ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rddb3′ has completed
CRS-4133: Oracle High Availability Services has been stopped.
启节点1的GI：
[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
在节点1等待1分钟执行如下操作检查GI是否已经全部起来了：
[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4529: Cluster Synchronization Services is online
CRS-4534: Cannot communicate with Event Manager
发现这时候CRSD已经起不来了。
这里的原因是因为ASM实例起不来，ASM实例启动报错：
SQL> startup
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 1073741824 cannot be set to more than MEMORY_MAX_TARGET 0.
用SYSASM角色登录后以pfile启动再覆盖spfile可以解决上述问题：
[grid@rddb2 dbs]$ sqlplus ‘/ as sysasm’;
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 6 14:47:09 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=/opt/u01/app/11.2.0.3/grid/dbs/init+ASM1.ora
ASM instance started
Total System Global Area 283930624 bytes
Fixed Size 2227664 bytes
Variable Size 256537136 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-17502: ksfdcre:4 Failed to create file
+SYSTEMDG/rddb-cluster/asmparameterfile/registry.253.840470757
ORA-15177: cannot operate on system aliases
看起来是oracle自身的问题，因为当我加上DG名称后即可成功覆盖spfile：
SQL> create spfile=’+SYSTEMDG’ from pfile;
File created.
然后我在节点1重启GI:
[grid@rddb2 dbs]$ su – root
Password:
[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rddb2′
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rddb2′
……省略显示部分内容
CRS-2677: Stop of ‘ora.gpnpd’ on ‘rddb2′ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rddb2′ has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
可以看到现在节点1的GI已经启起来了：
[root@rddb2 ~]# su – grid
[grid@rddb2 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
如下才是正确禁掉ASM实例AMM的做法：
特别注意的是——如果要禁掉ASM实例的AMM，就一定不要同时reset memory_target和memory_max_target，而是应该将memory_target设为0并只reset memory_max_target，但很恶心的是基本上所有的MOS文档都在说要同时reset memory_target和memory_max_target，所以如果你没有注意到这一点，你会发现你怎么也禁不掉ASM实例的AMM。
在任意一个RAC节点执行如下操作:
alter system set sga_target=512M scope=spfile sid=’*';
alter system set pga_aggregate_target=256M scope=spfile sid=’*';
alter system set shared_pool_size=150M scope=spfile sid=’*';
alter system set memory_target=0 scope=spfile sid=’*';
alter system set memory_max_target=0 scope=spfile sid=’*';
alter system reset memory_max_target scope=spfile sid=’*';
[grid@rddb2 dbs]$ su – grid
Password:
[grid@rddb2 ~]$ sqlplus ‘/ as sysasm’;
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 6 15:29:07 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> alter system set sga_target=512M scope=spfile sid=’*';
System altered.
SQL> alter system set pga_aggregate_target=256M scope=spfile sid=’*';
System altered.
SQL> alter system set shared_pool_size=150M scope=spfile sid=’*';
System altered.
SQL> alter system set memory_target=0 scope=spfile sid=’*';
System altered.
SQL> alter system set memory_max_target=0 scope=spfile sid=’*';
System altered.
SQL> alter system reset memory_max_target scope=spfile sid=’*';
System altered.
然后在节点1重启GI:
[grid@rddb2 ~]$ su – root
Password:
[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rddb2′
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rddb2′
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘rddb2′
CRS-2673: Attempting to stop ‘ora.oc4j’ on ‘rddb2′
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘rddb2′
……省略显示部分内容
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘rddb2′
CRS-2677: Stop of ‘ora.gpnpd’ on ‘rddb2′ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rddb2′ has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
在节点1等待1分钟后执行如下操作,可以看到现在节点1的GI已经启起来了：
[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
从如下查询结果可以看到，现在节点1上的ASM实例的AMM终于被我们禁掉了：
[root@rddb2 ~]# ls -l /dev/shm
total 88
-r——– 1 gdm gdm 67108904 Mar 6 11:15 pulse-shm-3757187464
-r——– 1 gdm gdm 67108904 Mar 6 11:15 pulse-shm-4050473372
现在我们再来启节点2的GI:
[root@rddb3 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
从如下结果可以看到，两个节点的GI确实已经都起来了：
[root@rddb3 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATADG1.dg
ONLINE ONLINE rddb2
ONLINE ONLINE rddb3
ora.FRADG.dg
ONLINE ONLINE rddb2
ONLINE ONLINE rddb3
ora.LISTENER.lsnr
ONLINE ONLINE rddb2
ONLINE ONLINE rddb3
ora.SYSTEMDG.dg
ONLINE ONLINE rddb2
ONLINE ONLINE rddb3
ora.asm
ONLINE ONLINE rddb2 Started
ONLINE ONLINE rddb3 Started
ora.gsd
OFFLINE OFFLINE rddb2
OFFLINE OFFLINE rddb3
ora.net1.network
ONLINE ONLINE rddb2
ONLINE ONLINE rddb3
ora.ons
ONLINE ONLINE rddb2
ONLINE ONLINE rddb3
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rddb2
ora.cvu
1 ONLINE ONLINE rddb2
ora.db112.db
1 OFFLINE OFFLINE Instance Shutdown
2 OFFLINE OFFLINE Instance Shutdown
ora.oc4j
1 ONLINE ONLINE rddb2
ora.rddb2.vip
1 ONLINE ONLINE rddb2
ora.rddb3.vip
1 ONLINE ONLINE rddb3
ora.scan1.vip
1 ONLINE ONLINE rddb2
我们再来同时reset一下memory_target和memory_max_target,设置完后重启GI：
SQL> alter system set memory_target=0 scope=spfile sid=’*';
System altered.
SQL> alter system reset memory_target scope=spfile sid=’*';
System altered.
SQL> alter system set memory_max_target=0 scope=spfile sid=’*';
System altered.
SQL> alter system reset memory_max_target scope=spfile sid=’*';
System altered.
重启完GI后发现ASM的AMM果然被Oracle启用了，MOS上的不靠谱文章太坑爹了啊：
[root@rddb2 ~]# ls -l /dev/shm
total 516808
-rw-r—– 1 grid oinstall 4194304 Mar 7 09:56 ora_+ASM1_2326534_0
-rw-r—– 1 grid oinstall 4194304 Mar 7 09:55 ora_+ASM1_2326534_1
-rw-r—– 1 grid oinstall 0 Mar 7 09:55 ora_+ASM1_2359303_0
……省略显示部分内容
-rw-r—– 1 grid oinstall 4194304 Mar 7 09:55 ora_+ASM1_2359303_95
-rw-r—– 1 grid oinstall 4194304 Mar 7 09:55 ora_+ASM1_2359303_96
-rw-r—– 1 grid oinstall 4194304 Mar 7 09:55 ora_+ASM1_2359303_97
-rw-r—– 1 grid oinstall 4194304 Mar 7 09:55 ora_+ASM1_2359303_98
-rw-r—– 1 grid oinstall 4194304 Mar 7 09:56 ora_+ASM1_2359303_99
-rw-r—– 1 grid oinstall 4194304 Mar 7 09:55 ora_+ASM1_2392072_0
-r——– 1 gdm gdm 67108904 Mar 6 11:15 pulse-shm-3757187464
-r——– 1 gdm gdm 67108904 Mar 6 11:15 pulse-shm-4050473372
此时memory_target和memory_max_target已经自动被Oracle设置成了808M：
[root@rddb2 ~]# su – grid
[grid@rddb2 ~]$ sqlplus ‘/ as sysasm’;
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 7 09:58:00 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> show parameter memory;
NAME TYPE VALUE
———————————— ———————- ——————————
memory_max_target big integer 808M
memory_target big integer 808M
Oracle索引分支块的结构
Posted: November 22, 2013 | Author: Cui Hua | Filed under: Oracle | 12 Comments »在这篇文章里，我们详细解析了Oracle索引分支块的结构。
我们还是从一个实例来开始说起：
SQL> create table t2 as select object_id,object_name from dba_objects;
Table created
SQL> select count(*) from t2;
COUNT(*)
——————
104530
SQL> create index idx_t2 on t2(object_name);
Index created
SQL> select object_id from dba_objects where object_name=’IDX_T2′;
OBJECT_ID
——————–
239443
SQL> oradebug setmypid
Statement processed.
SQL> alter session set events ‘immediate trace name treedump level 239443′;
Session altered.
SQL> oradebug tracefile_name
/nbstu01/app/oracle/diag/rdbms/nbstest/NBSTEST/trace/NBSTEST_ora_9699378.trc
/nbstu01/app/oracle/diag/rdbms/nbstest/NBSTEST/trace/NBSTEST_ora_9699378.trc的内容为如下所示：
oracle:/home/oracle>cat /nbstu01/app/oracle/diag/rdbms/nbstest/NBSTEST/trace/NBSTEST_ora_9699378.trc
Trace file /nbstu01/app/oracle/diag/rdbms/nbstest/NBSTEST/trace/NBSTEST_ora_9699378.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
……省略显示部分内容
*** 2012-12-28 13:00:47.047
Oradebug command ‘setmypid’ console output: <none>
*** 2012-12-28 13:01:11.575
—– begin tree dump
branch: 0×3800284 58720900 (0: nrow: 2, level: 2)
branch: 0x38003c6 58721222 (-1: nrow: 316, level: 1)
leaf: 0×3800285 58720901 (-1: nrow: 184 rrow: 184)
……省略显示部分内容
leaf: 0x38003c4 58721220 (314: nrow: 280 rrow: 280)
branch: 0×3800461 58721377 (0: nrow: 153, level: 1)
leaf: 0x38003c5 58721221 (-1: nrow: 218 rrow: 218)
……省略显示部分内容
leaf: 0×3800460 58721376 (151: nrow: 46 rrow: 46)
—– end tree dump
*** 2012-12-28 13:01:21.828
Processing Oradebug command ‘tracefile_name’
*** 2012-12-28 13:01:21.828
Oradebug command ‘tracefile_name’ console output:
/nbstu01/app/oracle/diag/rdbms/nbstest/NBSTEST/trace/NBSTEST_ora_9699378.tr
从上述显示内容中我们可以看出，现在索引IDX_T2有如下这三个分支块：
branch: 0×3800284 58720900 (0: nrow: 2, level: 2)
branch: 0x38003c6 58721222 (-1: nrow: 316, level: 1)
branch: 0×3800461 58721377 (0: nrow: 153, level: 1)
SQL> set serveroutput on size 1000000
SQL> exec sys.cdba(’03800284′,’H');
.
The file is 14
The block is 644
PL/SQL procedure successfully completed
SQL> exec sys.cdba(’038003c6′,’H');
.
The file is 14
The block is 966
PL/SQL procedure successfully completed
SQL> select blevel from dba_indexes where index_name=’IDX_T2′;
BLEVEL
——————–
2
SQL> select header_file,header_block from dba_segments where owner=’NBSTEST’ and segment_name=’IDX_T2′;
HEADER_FILE HEADER_BLOCK
——————– ————————-
14 643
我们现在直接来dump上述分分支块0x38003c6，dump后的trace文件内容为如下所示：
Block header dump: 0x038003c6
Object id on Block? Y
seg/obj: 0x3a753 csc: 0×00.1986a526 itc: 1 flg: E typ: 2 – INDEX
brn: 0 bdba: 0×3800381 ver: 0×01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0xffff.000.00000000 0×00000000.0000.00 C— 0 scn 0×0000.1986a526
Branch block dump
=================
header address 4575700044=0x110bba44c
kdxcolev 1
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 315
kdxcofbo 658=0×292
kdxcofeo 664=0×298
kdxcoavs 6
kdxbrlmc 58720901=0×3800285
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8045] dba: 58720902=0×3800286
col 0; len 5; (5): 2f 31 31 31 62
col 1; TERM
row#1[8034] dba: 58720903=0×3800287
col 0; len 5; (5): 2f 31 32 36 61
col 1; TERM
……省略显示部分内容
row#9[7854] dba: 58720911=0x380028f
col 0; len 30; (30):
2f 31 63 31 31 30 32 37 33 5f 41 57 45 78 63 65 70 74 69 6f 6e 4d 65 73 73
61 67 65 52 65
col 1; len 4; (4): 03 80 01 b9
……省略显示部分内容
row#20[7528] dba: 58720922=0x380029a
col 0; len 30; (30):
2f 32 61 35 33 35 36 61 32 5f 49 6e 6c 69 6e 65 52 65 74 75 72 6e 53 74 61
74 65 6d 65 6e
col 1; len 6; (6): 03 80 00 d2 00 5b
……省略显示部分内容
row#230[2371] dba: 58721134=0x380036e
col 0; len 9; (9): 44 41 54 41 5f 54 52 4e 53
col 1; len 4; (4): 03 80 02 47
……省略显示部分内容
row#247[1970] dba: 58721151=0x380037f
col 0; len 10; (10): 44 4d 5f 43 4c 55 53 54 45 52
col 1; len 6; (6): 03 80 00 97 00 b8
……省略显示部分内容
先来看上述显示内容中的第9行记录：
row#9[7854] dba: 58720911=0x380028f
col 0; len 30; (30):
2f 31 63 31 31 30 32 37 33 5f 41 57 45 78 63 65 70 74 69 6f 6e 4d 65 73 73
61 67 65 52 65
col 1; len 4; (4): 03 80 01 b9
SQL> exec sys.undump(’2f 31 63 31 31 30 32 37 33 5f 41 57 45 78 63 65 70 74 69 6f 6e 4d 65 73 73 61 67 65 52 65′);
/1c110273_AWExceptionMessageRe
PL/SQL procedure successfully completed
SQL> select count(*) from t2 where object_name=’/1c110273_AWExceptionMessageRe’;
COUNT(*)
—————–
2
SQL> select object_id,object_name,dbms_rowid.rowid_relative_fno(rowid)||’_'||dbms_rowid.rowid_block_number(rowid)||’_'||dbms_rowid.rowid_row_number(rowid) location from t2 where object_name=’/1c110273_AWExceptionMessageRe’;
OBJECT_ID OBJECT_NAME LOCATION
—————– ————————————————– ————————————————————-
64449 /1c110273_AWExceptionMessageRe 14_440_169
64560 /1c110273_AWExceptionMessageRe 14_441_88
SQL> exec sys.cdba(’038001b9′,’H');
.
The file is 14
The block is 441
PL/SQL procedure successfully completed
上述第9行记录所在叶子块的起止地址为0x380028f：
SQL> exec sys.cdba(’0380028f’,'H’);
.
The file is 14
The block is 655
PL/SQL procedure successfully completed
上述叶子块的dump内容为如下所示：
Block header dump: 0x0380028f
Object id on Block? Y
seg/obj: 0x3a753 csc: 0×00.1986a526 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0×3800280 ver: 0×01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0xffff.000.00000000 0×00000000.0000.00 C— 0 scn 0×0000.1986a526
Leaf block dump
===============
header address 4575700068=0x110bba464
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 185
kdxcofbo 406=0×196
kdxcofeo 1244=0x4dc
kdxcoavs 838
kdxlespl 0
kdxlende 0
kdxlenxt 58720912=0×3800290
kdxleprv 58720910=0x380028e
kdxledsz 0
kdxlebksz 8032
row#0[7992] flag: ——, lock: 0, len=40
col 0; len 30; (30):
2f 31 63 31 31 30 32 37 33 5f 41 57 45 78 63 65 70 74 69 6f 6e 4d 65 73 73
61 67 65 52 65
col 1; len 6; (6): 03 80 01 b9 00 58
然后我们再来dump上述叶子块的前一个块（即kdxleprv 58720910=0x380028e）；
row#183[1250] flag: ——, lock: 0, len=40
col 0; len 30; (30):
2f 31 63 31 31 30 32 37 33 5f 41 57 45 78 63 65 70 74 69 6f 6e 4d 65 73 73
61 67 65 52 65
col 1; len 6; (6): 03 80 01 b8 00 a9
—– end of leaf block dump —–
End dump data blocks tsn: 22 file#: 14 minblk 654 maxblk 654
也就是说上述第9行记录对应了两个索引行，这两个索引行恰好分布在两个叶子块中，一个在叶子块0x0380028f中，一个在叶子块0x380028e中
再来看上述分支块的dump内容中的第20行记录：
row#20[7528] dba: 58720922=0x380029a
col 0; len 30; (30):
2f 32 61 35 33 35 36 61 32 5f 49 6e 6c 69 6e 65 52 65 74 75 72 6e 53 74 61
74 65 6d 65 6e
col 1; len 6; (6): 03 80 00 d2 00 5b
SQL> exec sys.undump(’2f 32 61 35 33 35 36 61 32 5f 49 6e 6c 69 6e 65 52 65 74 75 72 6e 53 74 61 74 65 6d 65 6e’);
/2a5356a2_InlineReturnStatemen
PL/SQL procedure successfully completed
SQL> select count(*) from t2 where object_name=’/2a5356a2_InlineReturnStatemen’;
COUNT(*)
———-
2
SQL> select object_id,object_name,dbms_rowid.rowid_relative_fno(rowid)||’_'||dbms_rowid.rowid_block_number(rowid)||’_'||dbms_rowid.rowid_row_number(rowid) location from t2 where object_name=’/2a5356a2_InlineReturnStatemen’;
OBJECT_ID OBJECT_NAME LOCATION
—————– ————————————————– ————————————————-
18887 /2a5356a2_InlineReturnStatemen 14_210_90
18888 /2a5356a2_InlineReturnStatemen 14_210_91
SQL> exec sys.cdba(’038000d2′,’H');
.
The file is 14
The block is 210
PL/SQL procedure successfully completed
上述第20行记录所在叶子块的起始地址为0x380029a：
SQL> exec sys.cdba(’0380029a’,'H’);
.
The file is 14
The block is 666
PL/SQL procedure successfully completed
上述叶子块的dump内容为如下所示：
Block header dump: 0x0380029a
Object id on Block? Y
seg/obj: 0x3a753 csc: 0×00.1986a526 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0×3800280 ver: 0×01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0xffff.000.00000000 0×00000000.0000.00 C— 0 scn 0×0000.1986a526
Leaf block dump
===============
header address 4575700068=0x110bba464
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 180
kdxcofbo 396=0x18c
kdxcofeo 1242=0x4da
kdxcoavs 846
kdxlespl 0
kdxlende 0
kdxlenxt 58720923=0x380029b
kdxleprv 58720921=0×3800299
kdxledsz 0
kdxlebksz 8032
row#0[7992] flag: ——, lock: 0, len=40
col 0; len 30; (30):
2f 32 61 35 33 35 36 61 32 5f 49 6e 6c 69 6e 65 52 65 74 75 72 6e 53 74 61
74 65 6d 65 6e
col 1; len 6; (6): 03 80 00 d2 00 5b
然后我们再来dump上述叶子块的前一个块（即58720921=0×3800299）；
row#187[1229] flag: ——, lock: 0, len=40
col 0; len 30; (30):
2f 32 61 35 33 35 36 61 32 5f 49 6e 6c 69 6e 65 52 65 74 75 72 6e 53 74 61
74 65 6d 65 6e
col 1; len 6; (6): 03 80 00 d2 00 5a
—– end of leaf block dump —–
End dump data blocks tsn: 22 file#: 14 minblk 665 maxblk 665
也就是说上述第20行记录对应了两个索引行，这两个索引行也恰好分布在两个叶子块中，一个在叶子块0x0380029a中，一个在叶子块0×03800299中
再来看上述分支块dump内容中的第230行记录：
因分析过程和结论和上述类似，这里略去。
从上述测试过程中我们可以得出如下结论：
1、每个索引分支块都只有一个lmc，这个lmc指向的分支块/叶子块中的所有索引键值列中的最大值一定小于该lmc所在分支块的所有索引键值列中的最小值；
2、索引分支块的行记录所对应的存储格式为“行头 + 分支块/叶子块的RDBA + col 0 + col 1”
(2a) 上述存储格式中的col 0为索引键值列，注意，col 0可能为索引键值列的缩写；
(2b) 当col 0为完整的索引键值列时，col 1等于该行行头“分支块/叶子块的RDBA”所指向的叶子块中的第一行索引行所对应的数据行的ROWID（或ROWID的前缀）：如果上述叶子块的第一行索引行所对应的数据行的ROWID和该叶子块kdxleprv所指向的叶子块的最后一行索引行所对应的数据行的ROWID不同，则Oracle只需记录上述叶子块的第一行索引行所对应的数据行的ROWID（或ROWID的前缀，只要能和kdxleprv所指向的叶子块的最后一行索引行所对应的数据行的ROWID区分开就行，可以不是完整的ROWID）就可以了；
(2c) 当col 0为索引键值列的缩写时，col 1等于一个固定值“TERM”;
(2d) 上述存储格式中的“分支块/叶子块的RDBA”指向的分支块/叶子块中的所有索引键值列中的最小值一定大于等于该行记录中col 0的值，但这并不意味着所有索引键值列等于该行记录中col 0的索引行都分布在该行行头的“分支块/叶子块的RDBA”中。
我们来看col1记录的值为其ROWID头3个byte的实例：
row#53[6424] dba: 184552507=0xb000c3b
col 0; len 19; (19): 2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65
col 1; len 3; (3): 01 43 d1
SQL> exec sys.undump(’2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65′);
/32437fb_CacheTable
SQL> select object_id,object_name,dbms_rowid.rowid_relative_fno(rowid)||’_'||dbms_rowid.rowid_block_number(rowid)||’_'||dbms_rowid.rowid_row_number(rowid) location from t2 where object_name=’/32437fb_CacheTable’;
OBJECT_ID OBJECT_NAME LOCATION
———- ——————————————————– —————————————————–
31012 /32437fb_CacheTable 5_249873_144
31013 /32437fb_CacheTable 5_249873_145
31012 /32437fb_CacheTable 5_250365_42
31013 /32437fb_CacheTable 5_250365_43
上述分支块的行记录所对应叶子块的dump内容为如下所示：
Start dump data blocks tsn: 13 file#:44 minblk 3131 maxblk 3131
Block dump from cache:
Dump of buffer cache at level 4 for tsn=13 rdba=184552507
BH (0x70000006efd8988) file#: 44 rdba: 0x0b000c3b (44/3131) class: 1 ba: 0x70000006ebdc000
……省略显示部分内容
Block header dump: 0x0b000c3b
Object id on Block? Y
seg/obj: 0x41a19 csc: 0×00.38dd6a43 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0xb000c00 ver: 0×01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0xffff.000.00000000 0×00000000.0000.00 C— 0 scn 0×0000.38dd6a43
Leaf block dump
===============
header address 4575700068=0x110bba464
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 186
kdxcofbo 408=0×198
kdxcofeo 1254=0x4e6
kdxcoavs 846
kdxlespl 0
kdxlende 0
kdxlenxt 184552508=0xb000c3c
kdxleprv 184552506=0xb000c3a
kdxledsz 0
kdxlebksz 8032
row#0[8003] flag: ——, lock: 0, len=29
col 0; len 19; (19): 2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65
col 1; len 6; (6): 01 43 d1 fd 00 2a
row#1[7974] flag: ——, lock: 0, len=29
col 0; len 19; (19): 2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65
col 1; len 6; (6): 01 43 d1 fd 00 2b
再来dump上述叶子块的前一个块：
Start dump data blocks tsn: 13 file#:44 minblk 3130 maxblk 3130
Block dump from cache:
Dump of buffer cache at level 4 for tsn=13 rdba=184552506
BH (0x700000066f9ac58) file#: 44 rdba: 0x0b000c3a (44/3130) class: 1 ba: 0x70000006655a000
……省略显示部分内容
row#179[1261] flag: ——, lock: 0, len=29
col 0; len 19; (19): 2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65
col 1; len 6; (6): 01 43 d0 11 00 90
row#180[1232] flag: ——, lock: 0, len=29
col 0; len 19; (19): 2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65
col 1; len 6; (6): 01 43 d0 11 00 91
—– end of leaf block dump —–
End dump data blocks tsn: 13 file#: 44 minblk 3130 maxblk 3130
这里上述叶子块的第一行记录所对应的ROWID为01 43 d1 fd 00 2b，其前一个叶子块的最后一行记录所对应的ROWID为01 43 d0 11 00 91，这两个ROWID的头3个byte分别为01 43 d1和01 43 d0，已然不同，所以上述分支块的行记录的col1只用记录01 43 d1就可以了。
最后来看一个col1记录的值为其ROWID头1个byte的实例：
row#18[7464] dba: 184552472=0xb000c18
col 0; len 20; (20): 2f 31 62 39 34 37 31 65 38 5f 53 69 67 6e 61 74 75 72 65 31
col 1; len 1; (1): 0b
上述分支块的行记录所对应叶子块的dump内容为如下所示：
Start dump data blocks tsn: 13 file#:44 minblk 3096 maxblk 3096
Block dump from cache:
Dump of buffer cache at level 4 for tsn=13 rdba=184552472
BH (0x700000069fbc778) file#: 44 rdba: 0x0b000c18 (44/3096) class: 1 ba: 0x7000000698e6000
…省略显示部分内容
Block header dump: 0x0b000c18
Object id on Block? Y
seg/obj: 0x41a19 csc: 0×00.38dd6a43 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0xb000c00 ver: 0×01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0xffff.000.00000000 0×00000000.0000.00 C— 0 scn 0×0000.38dd6a43
Leaf block dump
===============
header address 4575700068=0x110bba464
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 182
kdxcofbo 400=0×190
kdxcofeo 1224=0x4c8
kdxcoavs 824
kdxlespl 0
kdxlende 0
kdxlenxt 184552473=0xb000c19
kdxleprv 184552471=0xb000c17
kdxledsz 0
kdxlebksz 8032
row#0[8002] flag: ——, lock: 0, len=30
col 0; len 20; (20): 2f 31 62 39 34 37 31 65 38 5f 53 69 67 6e 61 74 75 72 65 31
col 1; len 6; (6): 0b 00 0b 6a 00 57
row#1[7972] flag: ——, lock: 0, len=30
col 0; len 20; (20): 2f 31 62 39 34 37 31 65 38 5f 53 69 67 6e 61 74 75 72 65 31
col 1; len 6; (6): 0b 00 0b 6a 00 58
再来dump上述叶子块的前一个块：
Start dump data blocks tsn: 13 file#:44 minblk 3095 maxblk 3095
Block dump from cache:
Dump of buffer cache at level 4 for tsn=13 rdba=184552471
BH (0x700000059f696a8) file#: 44 rdba: 0x0b000c17 (44/3095) class: 1 ba: 0×700000059028000
……省略显示部分内容
row#185[1266] flag: ——, lock: 0, len=30
col 0; len 20; (20): 2f 31 62 39 34 37 31 65 38 5f 53 69 67 6e 61 74 75 72 65 31
col 1; len 6; (6): 01 43 d1 a7 00 05
row#186[1236] flag: ——, lock: 0, len=30
col 0; len 20; (20): 2f 31 62 39 34 37 31 65 38 5f 53 69 67 6e 61 74 75 72 65 31
col 1; len 6; (6): 01 43 d1 a7 00 06
—– end of leaf block dump —–
End dump data blocks tsn: 13 file#: 44 minblk 3095 maxblk 3095
这里上述叶子块的第一行记录所对应的ROWID为0b 00 0b 6a 00 57，其前一个叶子块的最后一行记录所对应的ROWID为01 43 d1 a7 00 06，这两个ROWID的头1个byte分别为0b和01，已然不同，所以上述分支块的行记录的col1只用记录0b就可以了。
Recent Comments