Monthly Archives: 四月 2012

DB2 主要行级锁模拟

Posted on by

S行锁和X行锁模拟

--会话1
[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei(id int,name varchar(100))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c "insert into t_xifenfei values(1,'www.orasos.com')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:07:02 -- Date 2012-04-29-01.46.48.462742

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3AE40 8          53514C4332473137315992A241 Internal P ..S  G   8          1   0          0x00 0x40000000 
0x99B3A690 8          02000700040080000000000052 Row        ..X  G   8          1   0          0x08 0x40000000 
0x99B3A6F0 8          02000700000000000000000054 Table      .IX  G   8          1   0          0x00 0x40000000 

--会话2
[db2inst1@xifenfei ~]$ db2 "select * from t_xifenfei"
hang住

--会话3查询等待
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:08:40 -- Date 2012-04-29-01.48.26.676607

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3AE40 8          53514C4332473137315992A241 Internal P ..S  G   8          1   0          0x00 0x40000000 
0x99B3A540 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B3A690 8          02000700040080000000000052 Row        ..X  G   8          1   0          0x08 0x40000000 
0x99B40C60 7          02000700040080000000000052 Row        .NS  W   8          1   0          0x00 0x00000001 
0x99B3A420 7          02000000010000000100407056 Internal V ..S  G   7          1   0          0x00 0x40000000 
0x99B3A6F0 8          02000700000000000000000054 Table      .IX  G   8          1   0          0x00 0x40000000 
0x99B3A510 7          02000700000000000000000054 Table      .IS  G   7          1   0          0x00 0x00000001 
--这里可以发现Sts=W(STATUS=WAIT),会话的NS锁处于等待状态

--会话1
[db2inst1@xifenfei ~]$ db2 commit
DB20000I  The SQL command completed successfully.

--会话2
[db2inst1@xifenfei ~]$ db2 "select * from t_xifenfei"

ID          NAME                                                                                                
----------- ----------------------------------------------------------------------------------------------------
          1 www.orasos.com                                                                                    

  1 record(s) selected.
--结果出现

--会话3
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:09:06 -- Date 2012-04-29-01.48.52.069878

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

U锁模拟

--会话1
[db2inst1@xifenfei ~]$ db2 +c "declare c1 cursor for select * from t_xifenfei for update"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c open c1
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c fetch c1

ID          NAME                                                                                                
----------- ----------------------------------------------------------------------------------------------------
          1 WWW.XIFENFEI.COM                                                                                    

  1 record(s) selected.

--会话2
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:27:27 -- Date 2012-04-29-02.07.13.594441

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3A420 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B3A510 7          02000700040080000000000052 Row        ..U  G   7          1   0          0x00 0x00000001 
0x99B40C60 7          01000000010000000100807256 Internal V ..S  G   7          1   0          0x00 0x40000000 
0x99B3A4E0 7          02000700000000000000000054 Table      .IX  G   7          1   0          0x00 0x00000001 

--会话1
[db2inst1@xifenfei ~]$ db2 +c "update t_xifenfei set name='www.orasos.com'"
DB20000I  The SQL command completed successfully.

--会话2
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:27:44 -- Date 2012-04-29-02.07.30.684616

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3A420 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B3A510 7          02000700040080000000000052 Row        ..X  G   7          2   0          0x00 0x40000001 
0x99B40C60 7          01000000010000000100807256 Internal V ..S  G   7          1   0          0x00 0x40000000 
0x99B3A4E0 7          02000700000000000000000054 Table      .IX  G   7          2   0          0x00 0x40000001 
--行级锁由U升级到X

--会话1
[db2inst1@xifenfei ~]$ db2 commit
DB20000I  The SQL command completed successfully.

--会话2
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 02:31:43 -- Date 2012-04-29-02.11.29.167659

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
Posted in DB2 |

ASM未正常启动,使用dd找回数据文件

Posted on by

本篇主要测试在ASM未启动状态下,如何找出找出文件文件对应的AU,并且通过dd还原出数据文件
参考文档:ASM的文件管理深入解析（内含开源的ASM文件挖掘研究版程序）
模拟测试数据

SQL> alter database datafile '+XIFENFEI/xff/datafile/xifenfei.268.781905429' resize 50M ;

Database altered.

SQL> alter database datafile '+XIFENFEI/xff/datafile/xifenfei.268.781905429' autoextend off;

Database altered.

SQL> select GROUP_NUMBER from V$ASM_DISKGROUP where NAME like '%XIFENFEI%';
 
GROUP_NUMBER
------------
           2

SQL> select DISK_NUMBER,path from v$asm_disk where GROUP_NUMBER=2;

DISK_NUMBER PATH
----------- ---------------------------------------------
          1 /dev/oracleasm/disks/VOL4
          0 /dev/oracleasm/disks/VOL3

SQL> create table t_xifenfei tablespace xifenfei
  2  as
  3  select  * from dba_objects;

Table created.

SQL> select count(*)  from t_xifenfei;

  COUNT(*)
----------
     74537

kfed找出相关AU映射

------------------------------------------------------------------------------
1号文件的第一个AU（0号盘2号AU）中，只能保存1至255号文件的。
从256号文件开始，AU的分布信息保存在1号文件第二个AU中，也就是（1号盘，64号AU）。
其中第一个块（0号块），对应256号文件。1号块对应257号文件，等等，依此类推。
------------------------------------------------------------------------------
[grid@rac1 disks]$  kfed read /dev/oracleasm/disks/VOL3 aun=2 blkn=1|more

kfffde[0].xptr.au:                    2 ; 0x4a0: 0x00000002
kfffde[0].xptr.disk:                  0 ; 0x4a4: 0x0000
kfffde[0].xptr.flags:                 0 ; 0x4a6: L=0 E=0 D=0 S=0
kfffde[0].xptr.chk:                  40 ; 0x4a7: 0x28
kfffde[1].xptr.au:                   64 ; 0x4a8: 0x00000040
kfffde[1].xptr.disk:                  1 ; 0x4ac: 0x0001
kfffde[1].xptr.flags:                 0 ; 0x4ae: L=0 E=0 D=0 S=0
kfffde[1].xptr.chk:                 107 ; 0x4af: 0x6b
kfffde[2].xptr.au:           4294967295 ; 0x4b0: 0xffffffff
kfffde[2].xptr.disk:              65535 ; 0x4b4: 0xffff
kfffde[2].xptr.flags:                 0 ; 0x4b6: L=0 E=0 D=0 S=0
kfffde[2].xptr.chk:                  42 ; 0x4b7: 0x2a

[grid@rac1 disks]$  kfed read /dev/oracleasm/disks/VOL4 aun=64 blkn=12|more
kfffde[0].xptr.au:                  681 ; 0x4a0: 0x000002a9
kfffde[0].xptr.disk:                  0 ; 0x4a4: 0x0000
kfffde[0].xptr.flags:                 0 ; 0x4a6: L=0 E=0 D=0 S=0
kfffde[0].xptr.chk:                 129 ; 0x4a7: 0x81
kfffde[1].xptr.au:                 1092 ; 0x4a8: 0x00000444
kfffde[1].xptr.disk:                  1 ; 0x4ac: 0x0001
kfffde[1].xptr.flags:                 0 ; 0x4ae: L=0 E=0 D=0 S=0
kfffde[1].xptr.chk:                 107 ; 0x4af: 0x6b
kfffde[2].xptr.au:                 1093 ; 0x4b0: 0x00000445
kfffde[2].xptr.disk:                  1 ; 0x4b4: 0x0001
kfffde[2].xptr.flags:                 0 ; 0x4b6: L=0 E=0 D=0 S=0
kfffde[2].xptr.chk:                 106 ; 0x4b7: 0x6a
kfffde[3].xptr.au:                  682 ; 0x4b8: 0x000002aa
kfffde[3].xptr.disk:                  0 ; 0x4bc: 0x0000
kfffde[3].xptr.flags:                 0 ; 0x4be: L=0 E=0 D=0 S=0
kfffde[3].xptr.chk:                 130 ; 0x4bf: 0x82
kfffde[4].xptr.au:                 1094 ; 0x4c0: 0x00000446
kfffde[4].xptr.disk:                  1 ; 0x4c4: 0x0001
kfffde[4].xptr.flags:                 0 ; 0x4c6: L=0 E=0 D=0 S=0
kfffde[4].xptr.chk:                 105 ; 0x4c7: 0x69
kfffde[5].xptr.au:                 1095 ; 0x4c8: 0x00000447
kfffde[5].xptr.disk:                  1 ; 0x4cc: 0x0001
kfffde[5].xptr.flags:                 0 ; 0x4ce: L=0 E=0 D=0 S=0
kfffde[5].xptr.chk:                 104 ; 0x4cf: 0x68
kfffde[6].xptr.au:                  683 ; 0x4d0: 0x000002ab
kfffde[6].xptr.disk:                  0 ; 0x4d4: 0x0000
kfffde[6].xptr.flags:                 0 ; 0x4d6: L=0 E=0 D=0 S=0
kfffde[6].xptr.chk:                 131 ; 0x4d7: 0x83
kfffde[7].xptr.au:                 1096 ; 0x4d8: 0x00000448
kfffde[7].xptr.disk:                  1 ; 0x4dc: 0x0001
kfffde[7].xptr.flags:                 0 ; 0x4de: L=0 E=0 D=0 S=0
kfffde[7].xptr.chk:                 103 ; 0x4df: 0x67
kfffde[8].xptr.au:                  684 ; 0x4e0: 0x000002ac
kfffde[8].xptr.disk:                  0 ; 0x4e4: 0x0000
kfffde[8].xptr.flags:                 0 ; 0x4e6: L=0 E=0 D=0 S=0
kfffde[8].xptr.chk:                 132 ; 0x4e7: 0x84
kfffde[9].xptr.au:                 1097 ; 0x4e8: 0x00000449
kfffde[9].xptr.disk:                  1 ; 0x4ec: 0x0001
kfffde[9].xptr.flags:                 0 ; 0x4ee: L=0 E=0 D=0 S=0
kfffde[9].xptr.chk:                 102 ; 0x4ef: 0x66
kfffde[10].xptr.au:                1098 ; 0x4f0: 0x0000044a
kfffde[10].xptr.disk:                 1 ; 0x4f4: 0x0001
kfffde[10].xptr.flags:                0 ; 0x4f6: L=0 E=0 D=0 S=0
kfffde[10].xptr.chk:                101 ; 0x4f7: 0x65
kfffde[11].xptr.au:                 685 ; 0x4f8: 0x000002ad
kfffde[11].xptr.disk:                 0 ; 0x4fc: 0x0000
kfffde[11].xptr.flags:                0 ; 0x4fe: L=0 E=0 D=0 S=0
kfffde[11].xptr.chk:                133 ; 0x4ff: 0x85
kfffde[12].xptr.au:                1099 ; 0x500: 0x0000044b
kfffde[12].xptr.disk:                 1 ; 0x504: 0x0001
kfffde[12].xptr.flags:                0 ; 0x506: L=0 E=0 D=0 S=0
kfffde[12].xptr.chk:                100 ; 0x507: 0x64
kfffde[13].xptr.au:                 686 ; 0x508: 0x000002ae
kfffde[13].xptr.disk:                 0 ; 0x50c: 0x0000
kfffde[13].xptr.flags:                0 ; 0x50e: L=0 E=0 D=0 S=0
kfffde[13].xptr.chk:                134 ; 0x50f: 0x86
kfffde[14].xptr.au:                1100 ; 0x510: 0x0000044c
kfffde[14].xptr.disk:                 1 ; 0x514: 0x0001
kfffde[14].xptr.flags:                0 ; 0x516: L=0 E=0 D=0 S=0
kfffde[14].xptr.chk:                 99 ; 0x517: 0x63
kfffde[15].xptr.au:                1101 ; 0x518: 0x0000044d
kfffde[15].xptr.disk:                 1 ; 0x51c: 0x0001
kfffde[15].xptr.flags:                0 ; 0x51e: L=0 E=0 D=0 S=0
kfffde[15].xptr.chk:                 98 ; 0x51f: 0x62
kfffde[16].xptr.au:                 687 ; 0x520: 0x000002af
kfffde[16].xptr.disk:                 0 ; 0x524: 0x0000
kfffde[16].xptr.flags:                0 ; 0x526: L=0 E=0 D=0 S=0
kfffde[16].xptr.chk:                135 ; 0x527: 0x87
kfffde[17].xptr.au:                1102 ; 0x528: 0x0000044e
kfffde[17].xptr.disk:                 1 ; 0x52c: 0x0001
kfffde[17].xptr.flags:                0 ; 0x52e: L=0 E=0 D=0 S=0
kfffde[17].xptr.chk:                 97 ; 0x52f: 0x61
kfffde[18].xptr.au:                1103 ; 0x530: 0x0000044f
kfffde[18].xptr.disk:                 1 ; 0x534: 0x0001
kfffde[18].xptr.flags:                0 ; 0x536: L=0 E=0 D=0 S=0
kfffde[18].xptr.chk:                 96 ; 0x537: 0x60
kfffde[19].xptr.au:                 688 ; 0x538: 0x000002b0
kfffde[19].xptr.disk:                 0 ; 0x53c: 0x0000
kfffde[19].xptr.flags:                0 ; 0x53e: L=0 E=0 D=0 S=0
kfffde[19].xptr.chk:                152 ; 0x53f: 0x98
kfffde[20].xptr.au:                1104 ; 0x540: 0x00000450
kfffde[20].xptr.disk:                 1 ; 0x544: 0x0001
kfffde[20].xptr.flags:                0 ; 0x546: L=0 E=0 D=0 S=0
kfffde[20].xptr.chk:                127 ; 0x547: 0x7f
kfffde[21].xptr.au:                 689 ; 0x548: 0x000002b1
kfffde[21].xptr.disk:                 0 ; 0x54c: 0x0000
kfffde[21].xptr.flags:                0 ; 0x54e: L=0 E=0 D=0 S=0
kfffde[21].xptr.chk:                153 ; 0x54f: 0x99
kfffde[22].xptr.au:                1105 ; 0x550: 0x00000451
kfffde[22].xptr.disk:                 1 ; 0x554: 0x0001
kfffde[22].xptr.flags:                0 ; 0x556: L=0 E=0 D=0 S=0
kfffde[22].xptr.chk:                126 ; 0x557: 0x7e
kfffde[23].xptr.au:                1106 ; 0x558: 0x00000452
kfffde[23].xptr.disk:                 1 ; 0x55c: 0x0001
kfffde[23].xptr.flags:                0 ; 0x55e: L=0 E=0 D=0 S=0
kfffde[23].xptr.chk:                125 ; 0x55f: 0x7d
kfffde[24].xptr.au:                 690 ; 0x560: 0x000002b2
kfffde[24].xptr.disk:                 0 ; 0x564: 0x0000
kfffde[24].xptr.flags:                0 ; 0x566: L=0 E=0 D=0 S=0
kfffde[24].xptr.chk:                154 ; 0x567: 0x9a
kfffde[25].xptr.au:                1107 ; 0x568: 0x00000453
kfffde[25].xptr.disk:                 1 ; 0x56c: 0x0001
kfffde[25].xptr.flags:                0 ; 0x56e: L=0 E=0 D=0 S=0
kfffde[25].xptr.chk:                124 ; 0x56f: 0x7c
kfffde[26].xptr.au:                 691 ; 0x570: 0x000002b3
kfffde[26].xptr.disk:                 0 ; 0x574: 0x0000
kfffde[26].xptr.flags:                0 ; 0x576: L=0 E=0 D=0 S=0
kfffde[26].xptr.chk:                155 ; 0x577: 0x9b
kfffde[27].xptr.au:                1108 ; 0x578: 0x00000454
kfffde[27].xptr.disk:                 1 ; 0x57c: 0x0001
kfffde[27].xptr.flags:                0 ; 0x57e: L=0 E=0 D=0 S=0
kfffde[27].xptr.chk:                123 ; 0x57f: 0x7b
kfffde[28].xptr.au:                1109 ; 0x580: 0x00000455
kfffde[28].xptr.disk:                 1 ; 0x584: 0x0001
kfffde[28].xptr.flags:                0 ; 0x586: L=0 E=0 D=0 S=0
kfffde[28].xptr.chk:                122 ; 0x587: 0x7a
kfffde[29].xptr.au:                 692 ; 0x588: 0x000002b4
kfffde[29].xptr.disk:                 0 ; 0x58c: 0x0000
kfffde[29].xptr.flags:                0 ; 0x58e: L=0 E=0 D=0 S=0
kfffde[29].xptr.chk:                156 ; 0x58f: 0x9c
kfffde[30].xptr.au:                1110 ; 0x590: 0x00000456
kfffde[30].xptr.disk:                 1 ; 0x594: 0x0001
kfffde[30].xptr.flags:                0 ; 0x596: L=0 E=0 D=0 S=0
kfffde[30].xptr.chk:                121 ; 0x597: 0x79
kfffde[31].xptr.au:                1111 ; 0x598: 0x00000457
kfffde[31].xptr.disk:                 1 ; 0x59c: 0x0001
kfffde[31].xptr.flags:                0 ; 0x59e: L=0 E=0 D=0 S=0
kfffde[31].xptr.chk:                120 ; 0x59f: 0x78
kfffde[32].xptr.au:                 693 ; 0x5a0: 0x000002b5
kfffde[32].xptr.disk:                 0 ; 0x5a4: 0x0000
kfffde[32].xptr.flags:                0 ; 0x5a6: L=0 E=0 D=0 S=0
kfffde[32].xptr.chk:                157 ; 0x5a7: 0x9d
kfffde[33].xptr.au:                1112 ; 0x5a8: 0x00000458
kfffde[33].xptr.disk:                 1 ; 0x5ac: 0x0001
kfffde[33].xptr.flags:                0 ; 0x5ae: L=0 E=0 D=0 S=0
kfffde[33].xptr.chk:                119 ; 0x5af: 0x77
kfffde[34].xptr.au:                 694 ; 0x5b0: 0x000002b6
kfffde[34].xptr.disk:                 0 ; 0x5b4: 0x0000
kfffde[34].xptr.flags:                0 ; 0x5b6: L=0 E=0 D=0 S=0
kfffde[34].xptr.chk:                158 ; 0x5b7: 0x9e
kfffde[35].xptr.au:                1113 ; 0x5b8: 0x00000459
kfffde[35].xptr.disk:                 1 ; 0x5bc: 0x0001
kfffde[35].xptr.flags:                0 ; 0x5be: L=0 E=0 D=0 S=0
kfffde[35].xptr.chk:                118 ; 0x5bf: 0x76
kfffde[36].xptr.au:                1114 ; 0x5c0: 0x0000045a
kfffde[36].xptr.disk:                 1 ; 0x5c4: 0x0001
kfffde[36].xptr.flags:                0 ; 0x5c6: L=0 E=0 D=0 S=0
kfffde[36].xptr.chk:                117 ; 0x5c7: 0x75
kfffde[37].xptr.au:                 695 ; 0x5c8: 0x000002b7
kfffde[37].xptr.disk:                 0 ; 0x5cc: 0x0000
kfffde[37].xptr.flags:                0 ; 0x5ce: L=0 E=0 D=0 S=0
kfffde[37].xptr.chk:                159 ; 0x5cf: 0x9f
kfffde[38].xptr.au:                1115 ; 0x5d0: 0x0000045b
kfffde[38].xptr.disk:                 1 ; 0x5d4: 0x0001
kfffde[38].xptr.flags:                0 ; 0x5d6: L=0 E=0 D=0 S=0
kfffde[38].xptr.chk:                116 ; 0x5d7: 0x74
kfffde[39].xptr.au:                1116 ; 0x5d8: 0x0000045c
kfffde[39].xptr.disk:                 1 ; 0x5dc: 0x0001
kfffde[39].xptr.flags:                0 ; 0x5de: L=0 E=0 D=0 S=0
kfffde[39].xptr.chk:                115 ; 0x5df: 0x73
kfffde[40].xptr.au:                 696 ; 0x5e0: 0x000002b8
kfffde[40].xptr.disk:                 0 ; 0x5e4: 0x0000
kfffde[40].xptr.flags:                0 ; 0x5e6: L=0 E=0 D=0 S=0
kfffde[40].xptr.chk:                144 ; 0x5e7: 0x90
kfffde[41].xptr.au:                1117 ; 0x5e8: 0x0000045d
kfffde[41].xptr.disk:                 1 ; 0x5ec: 0x0001
kfffde[41].xptr.flags:                0 ; 0x5ee: L=0 E=0 D=0 S=0
kfffde[41].xptr.chk:                114 ; 0x5ef: 0x72
kfffde[42].xptr.au:                 697 ; 0x5f0: 0x000002b9
kfffde[42].xptr.disk:                 0 ; 0x5f4: 0x0000
kfffde[42].xptr.flags:                0 ; 0x5f6: L=0 E=0 D=0 S=0
kfffde[42].xptr.chk:                145 ; 0x5f7: 0x91
kfffde[43].xptr.au:                1118 ; 0x5f8: 0x0000045e
kfffde[43].xptr.disk:                 1 ; 0x5fc: 0x0001
kfffde[43].xptr.flags:                0 ; 0x5fe: L=0 E=0 D=0 S=0
kfffde[43].xptr.chk:                113 ; 0x5ff: 0x71
kfffde[44].xptr.au:                1119 ; 0x600: 0x0000045f
kfffde[44].xptr.disk:                 1 ; 0x604: 0x0001
kfffde[44].xptr.flags:                0 ; 0x606: L=0 E=0 D=0 S=0
kfffde[44].xptr.chk:                112 ; 0x607: 0x70
kfffde[45].xptr.au:                 698 ; 0x608: 0x000002ba
kfffde[45].xptr.disk:                 0 ; 0x60c: 0x0000
kfffde[45].xptr.flags:                0 ; 0x60e: L=0 E=0 D=0 S=0
kfffde[45].xptr.chk:                146 ; 0x60f: 0x92
kfffde[46].xptr.au:                1120 ; 0x610: 0x00000460
kfffde[46].xptr.disk:                 1 ; 0x614: 0x0001
kfffde[46].xptr.flags:                0 ; 0x616: L=0 E=0 D=0 S=0
kfffde[46].xptr.chk:                 79 ; 0x617: 0x4f
kfffde[47].xptr.au:                 699 ; 0x618: 0x000002bb
kfffde[47].xptr.disk:                 0 ; 0x61c: 0x0000
kfffde[47].xptr.flags:                0 ; 0x61e: L=0 E=0 D=0 S=0
kfffde[47].xptr.chk:                147 ; 0x61f: 0x93
kfffde[48].xptr.au:                1121 ; 0x620: 0x00000461
kfffde[48].xptr.disk:                 1 ; 0x624: 0x0001
kfffde[48].xptr.flags:                0 ; 0x626: L=0 E=0 D=0 S=0
kfffde[48].xptr.chk:                 78 ; 0x627: 0x4e
kfffde[49].xptr.au:                1122 ; 0x628: 0x00000462
kfffde[49].xptr.disk:                 1 ; 0x62c: 0x0001
kfffde[49].xptr.flags:                0 ; 0x62e: L=0 E=0 D=0 S=0
kfffde[49].xptr.chk:                 77 ; 0x62f: 0x4d
kfffde[50].xptr.au:                 700 ; 0x630: 0x000002bc
kfffde[50].xptr.disk:                 0 ; 0x634: 0x0000
kfffde[50].xptr.flags:                0 ; 0x636: L=0 E=0 D=0 S=0
kfffde[50].xptr.chk:                148 ; 0x637: 0x94
--到此截止
kfffde[51].xptr.au:          4294967295 ; 0x638: 0xffffffff
kfffde[51].xptr.disk:             65535 ; 0x63c: 0xffff
kfffde[51].xptr.flags:                0 ; 0x63e: L=0 E=0 D=0 S=0
kfffde[51].xptr.chk:                 42 ; 0x63f: 0x2a

dd操作

[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL3 bs=1024k count=1 skip=681 seek=0 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0924808 seconds, 11.3 MB/s
[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL4 bs=1024k count=1 skip=1092 seek=1 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.051765 seconds, 20.3 MB/s
[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL4 bs=1024k count=1 skip=1093 seek=2 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0695681 seconds, 15.1 MB/s
[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL3 bs=1024k count=1 skip=682 seek=3 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0516708 seconds, 20.3 MB/s
…………类此处理…………
[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL4 bs=1024k count=1 skip=1122 seek=49 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0699421 seconds, 15.0 MB/s
[grid@rac1 disks]$ dd if=/dev/oracleasm/disks/VOL3 bs=1024k count=1 skip=700 seek=50 of=/tmp/xifenfei01.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0650649 seconds, 16.1 MB/s

修改所有者

[root@rac1 ~]#  chown oracle.oinstall /tmp/xifenfei01.dbf
[root@rac1 ~]# ll /tmp/xifenfei01.dbf 
-rw-r--r-- 1 oracle oinstall 53477376 Apr 30 12:57 /tmp/xifenfei01.dbf

dbv验证数据文件

[oracle@rac1 ~]$ dbv file='+XIFENFEI/xff/datafile/xifenfei.268.781905429' userid=sys/xifenfei

DBVERIFY: Release 11.2.0.3.0 - Production on Mon Apr 30 12:53:32 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +XIFENFEI/xff/datafile/xifenfei.268.781905429


DBVERIFY - Verification complete

Total Pages Examined         : 6400
Total Pages Processed (Data) : 1066
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 156
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 5178
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)
[oracle@rac1 ~]$ dbv file='/tmp/xifenfei01.dbf'

DBVERIFY: Release 11.2.0.3.0 - Production on Mon Apr 30 12:53:50 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /tmp/xifenfei01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 6400
Total Pages Processed (Data) : 1066
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 156
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 5178
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1039267 (0.1039267)

数据库验证数据文件

SQL> alter database datafile '+XIFENFEI/xff/datafile/xifenfei.268.781905429' offline;

Database altered.

SQL> alter database rename file '+XIFENFEI/xff/datafile/xifenfei.268.781905429'
  2  to '/tmp/xifenfei01.dbf';

Database altered.

SQL>  recover datafile '/tmp/xifenfei01.dbf';
Media recovery complete.
SQL> alter database datafile '/tmp/xifenfei01.dbf' online;

Database altered.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     74537

bbed修改ASM中数据

Posted on by

本篇文章演示了如何从ASM中通过dd拷贝出某种表的记录,然后通过bbed修改相关记录,再拷贝到库中.说的简单点就是通过dd拷贝出最少的需要对象数据块,然后通过bbed绕过数据库级别对相关记录进行修改
模拟测试数据

--ORACLE数据库中执行
SQL> create tablespace xifenfei datafile '+xifenfei' size 30M autoextend on maxsize 10G;

Tablespace created.

SQL> create table t_xifenfei (id number,name varchar2(10)) tablespace xifenfei;

Table created.

SQL> insert into t_xifenfei values(1,'xifenfei');

1 row created.

SQL> insert into t_xifenfei values(2,'XFF');

1 row created.

SQL> SELECT * FROM T_XIFENFEI;

        ID NAME
---------- ----------
         1 xifenfei
         2 XFF

SQL> commit;

Commit complete.

SQL> select EXTENT_ID, BLOCK_ID, BLOCKS, FILE_ID from dba_extents 
  2  where SEGMENT_NAME='T_XIFENFEI' and OWNER='SYS';

 EXTENT_ID   BLOCK_ID     BLOCKS    FILE_ID
---------- ---------- ---------- ----------
         0        128          8          6

SQL> select name from v$datafile where file#=6;

NAME
----------------------------------------------------
+XIFENFEI/xff/datafile/xifenfei.268.781905429

SQL> select GROUP_NUMBER from V$ASM_DISKGROUP where NAME like '%XIFENFEI%';

GROUP_NUMBER
------------
           2

在ASM用户中查询相关数据

--ASM中执行
SQL> SELECT disk_kffxp, au_kffxp, xnum_kffxp
  2  FROM x$kffxp                      
  3   WHERE GROUP_KFFXP=2 
  4  AND NUMBER_KFFXP=268; 

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         0        681          0
         1       1092          1
         1       1093          2
         0        682          3
         1       1094          4
         1       1095          5
         0        683          6
         1       1096          7
         0        684          8
         1       1097          9
         1       1098         10

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         0        685         11
         1       1099         12
         0        686         13
         1       1100         14
         1       1101         15
         0        687         16
         1       1102         17
         1       1103         18
         0        688         19
         1       1104         20
         0        689         21

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         1       1105         22
         1       1106         23
         0        690         24
         1       1107         25
         0        691         26
         1       1108         27
         1       1109         28
         0        692         29
         1       1110         30

31 rows selected.
--数据文件6的AU分配情况

SQL> select 128*8/1024 from dual;

128*8/1024
----------
         1


SQL> select 8*8/1024 from dual;

  8*8/1024
----------
     .0625
--可以得出该表T_XIFENFEI的数据分布在第二块AU中(DISK_KFFXP=1/AU_KFFXP=1092/XNUM_KFFXP=1)

SQL> select name, path from v$asm_disk where group_number=2     
  2  and disk_number=1;

NAME                           PATH
------------------------------ --------------------------
XIFENFEI_0001                  /dev/oracleasm/disks/VOL4

找出对应磁盘或者分区

[grid@rac1 ~]$ /etc/init.d/oracleasm querydisk -d VOL4
Disk "VOL4" is a valid ASM disk on device [8,18]
[grid@rac1 ~]$ cat /proc/partitions |grep "8       18"
   8       18    3879697 sdb2

因为这里的block_id=128,刚好是下一个AU的起点,所以dd操作的起点是第二个AU(DISK_KFFXP=1/AU_KFFXP=1092),而终点是8*8=64K(第二个AU中offset 64KB)

执行dd导出表数据

of=/dev/sdb2
sb=1K
skip=1092*1024=1118208
count=64

[root@rac1 ~]# dd if=/dev/sdb2 bs=1k count=64 skip=1118208|strings
64+0 records in
64+0 records out
65536 bytes (66 kB) copied, 0.000656471 seconds, 99.8 MB/s
XFF,
xifenfei
[root@rac1 ~]# dd if=/dev/sdb2 bs=1k count=64 skip=1118208 of=/tmp/t_xifenfe.tab
64+0 records in
64+0 records out
65536 bytes (66 kB) copied, 0.00226337 seconds, 29.0 MB/s
[root@rac1 ~]# chown oracle.oinstall /tmp/t_xifenfe.tab 
[root@rac1 ~]# ll /tmp/t_xifenfe.tab 
-rw-r--r-- 1 oracle oinstall 65536 Apr 29 21:54 /tmp/t_xifenfe.tab

bbed 修改数据内容

[oracle@rac1 ~]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 29 22:43:56 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/tmp/t_xifenfe.tab'
        FILENAME        /tmp/t_xifenfe.tab

BBED> set block 4
        BLOCK#          4

BBED> set mode edit
        MODE            Edit

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> map
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                                     Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[2]                                @118     

 ub1 freespace[8041]                        @122     

 ub1 rowdata[25]                            @8163    

 ub4 tailchk                                @8188    


BBED> p kdbr
sb2 kdbr[0]                                 @118      8073
sb2 kdbr[1]                                 @120      8063

BBED> find /c XFF
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8170 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 5846462c 010202c1 02087869 66656e66 65690106 ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8170 to 8191  Dba:0x00000000
-------------------------------------------------------
 5846462c 010202c1 02087869 66656e66 l XFF,......xifenf
 65690106 ba33                       l ei...3

 <16 bytes per line>

BBED> m /c xff
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8170 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 7866662c 010202c1 02087869 66656e66 65690106 ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8170 to 8191  Dba:0x00000000
-------------------------------------------------------
 7866662c 010202c1 02087869 66656e66 l xff,......xifenf
 65690106 ba33                       l ei...3

 <16 bytes per line>

BBED> find /c xifenfei
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 78696665 6e666569 0106ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8180 to 8191  Dba:0x00000000
-------------------------------------------------------
 78696665 6e666569 0106ba33          l xifenfei...3

 <16 bytes per line>

BBED> m /c XIFENFEI
 File: /tmp/t_xifenfe.tab (0)
 Block: 4                Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 58494645 4e464549 0106ba33 

 <32 bytes per line>

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8180 to 8191  Dba:0x00000000
-------------------------------------------------------
 58494645 4e464549 0106ba33          l XIFENFEI...3

 <16 bytes per line>

BBED> sum
Check value for File 0, Block 4:
current = 0xd332, required = 0xf332

BBED> sum apply
Check value for File 0, Block 4:
current = 0xf332, required = 0xf332

BBED> set offset 8073
        OFFSET          8073

BBED> dump /v
 File: /tmp/t_xifenfe.tab (0)
 Block: 4       Offsets: 8073 to 8191  Dba:0x00000000
-------------------------------------------------------
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00002c01 0202c103 l ..........,.....
 03786666 2c010202 c1020858 4946454e l .xff,......XIFEN
 46454901 06ba33                     l FEI...3

 <16 bytes per line>

BBED> exit

dd导入修改后数据验证

--会话1关闭数据库
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 29 22:48:51 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


--会话2导入bbed修改后数据
[root@rac1 ~]# dd of=/dev/sdb2 bs=1k count=64 seek=1118208 if=/tmp/t_xifenfe.tab
64+0 records in
64+0 records out
65536 bytes (66 kB) copied, 0.0014908 seconds, 44.0 MB/s

--会话1启动数据库库查询
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 29 22:51:00 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1346140 bytes
Variable Size             411043236 bytes
Database Buffers          117440512 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> select * from t_xifenfei;

        ID NAME
---------- ----------
         1 XIFENFEI
         2 xff

使用dd复制asm中文件

Posted on by

随着数据库新版本的推广ASM肯定会越来越被重视,最近准备系统的学习下ASM,以备突发情况需要,这是asm深入学习第一篇,参考:dd复制ASM中的datafile
查询ASM某个数据文件AU信息

SQL> SELECT GROUP_NUMBER, FILE_NUMBER, NAME 
  2      FROM v$asm_alias
  3  WHERE NAME LIKE '%USER%'
  4   GROUP BY GROUP_NUMBER, FILE_NUMBER, NAME;

GROUP_NUMBER FILE_NUMBER NAME
------------ ----------- -----------------------------
           2         259 USERS.259.776961317


SQL>  SELECT disk_kffxp, au_kffxp, xnum_kffxp
  2    FROM x$kffxp                      
  3     WHERE GROUP_KFFXP=2 
  4    AND NUMBER_KFFXP=259; 

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         1        817          0
         0        507          1
         1        818          2
         0        508          3
         1        819          4
         1        820          5

SQL> select DISK_NUMBER,GROUP_NUMBER,PATH from v$asm_disk 
   2 where GROUP_NUMBER=2 and DISK_NUMBER in(1,0);

DISK_NUMBER GROUP_NUMBER PATH
----------- ------------ -----------------------------------
          1            2 /dev/oracleasm/disks/VOL4
          0            2 /dev/oracleasm/disks/VOL3

查询ASM DISK对应的磁盘或者分区

[grid@rac1 ~]$ /etc/init.d/oracleasm querydisk -d VOL3
Disk "VOL3" is a valid ASM disk on device [8,17]
[grid@rac1 ~]$ /etc/init.d/oracleasm querydisk -d VOL4
Disk "VOL4" is a valid ASM disk on device [8,18]
[grid@rac1 ~]$ cat /proc/partitions |grep "8       17"
   8       17    2409718 sdb1
[grid@rac1 ~]$ cat /proc/partitions |grep "8       18"
   8       18    3879697 sdb2

dd操作磁盘或者分区

[root@rac1 ~]# dd if=/dev/sdb2 bs=1024k count=1 skip=817 of=/tmp/user_1.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.188362 seconds, 5.6 MB/s
[root@rac1 ~]# dd if=/dev/sdb1 bs=1024k count=1 skip=507 of=/tmp/user_2.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.259001 seconds, 4.0 MB/s
[root@rac1 ~]# dd if=/dev/sdb2 bs=1024k count=1 skip=818 of=/tmp/user_3.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.182559 seconds, 5.7 MB/s
[root@rac1 ~]# dd if=/dev/sdb1 bs=1024k count=1 skip=508 of=/tmp/user_4.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.10011 seconds, 10.5 MB/s
[root@rac1 ~]# dd if=/dev/sdb2 bs=1024k count=2 skip=819 of=/tmp/user_5.dbf
2+0 records in
2+0 records out
2097152 bytes (2.1 MB) copied, 0.22389 seconds, 9.4 MB/s
[root@rac1 ~]# ll /tmp/user_*
-rw-r--r-- 1 root root 1048576 Apr 29 18:50 /tmp/user_1.dbf
-rw-r--r-- 1 root root 1048576 Apr 29 18:51 /tmp/user_2.dbf
-rw-r--r-- 1 root root 1048576 Apr 29 18:51 /tmp/user_3.dbf
-rw-r--r-- 1 root root 1048576 Apr 29 18:51 /tmp/user_4.dbf
-rw-r--r-- 1 root root 2097152 Apr 29 18:51 /tmp/user_5.dbf
[root@rac1 ~]# dd if=/tmp/user_1.dbf bs=1024k count=1 of=/tmp/user_dd.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0104619 seconds, 100 MB/s
[root@rac1 ~]# dd if=/tmp/user_2.dbf bs=1024k count=1 seek=1 of=/tmp/user_dd.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0129077 seconds, 81.2 MB/s
[root@rac1 ~]# dd if=/tmp/user_3.dbf bs=1024k count=1 seek=2 of=/tmp/user_dd.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.00737324 seconds, 142 MB/s
[root@rac1 ~]# dd if=/tmp/user_4.dbf bs=1024k count=1 seek=3 of=/tmp/user_dd.dbf
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0143482 seconds, 73.1 MB/s
[root@rac1 ~]# dd if=/tmp/user_5.dbf bs=1024k count=2 seek=4 of=/tmp/user_dd.dbf
2+0 records in
2+0 records out
2097152 bytes (2.1 MB) copied, 0.0809296 seconds, 25.9 MB/s
[root@rac1 ~]# ll /tmp/user_*
-rw-r--r-- 1 root root 1048576 Apr 29 18:50 /tmp/user_1.dbf
-rw-r--r-- 1 root root 1048576 Apr 29 18:51 /tmp/user_2.dbf
-rw-r--r-- 1 root root 1048576 Apr 29 18:51 /tmp/user_3.dbf
-rw-r--r-- 1 root root 1048576 Apr 29 18:51 /tmp/user_4.dbf
-rw-r--r-- 1 root root 2097152 Apr 29 18:51 /tmp/user_5.dbf
-rw-r--r-- 1 root root 5242880 Apr 29 18:54 /tmp/user_dd.dbf
[root@rac1 ~]# chown oracle.oinstall /tmp/user_dd.dbf 
[root@rac1 ~]# ll /tmp/user_*
-rw-r--r-- 1 root   root     1048576 Apr 29 18:50 /tmp/user_1.dbf
-rw-r--r-- 1 root   root     1048576 Apr 29 18:51 /tmp/user_2.dbf
-rw-r--r-- 1 root   root     1048576 Apr 29 18:51 /tmp/user_3.dbf
-rw-r--r-- 1 root   root     1048576 Apr 29 18:51 /tmp/user_4.dbf
-rw-r--r-- 1 root   root     2097152 Apr 29 18:51 /tmp/user_5.dbf
-rw-r--r-- 1 oracle oinstall 6291456 Apr 29 18:55 /tmp/user_dd.dbf

验证dd拷贝数据文件

[oracle@rac1 ~]$ dbv file='/tmp/user_dd.dbf' 

DBVERIFY: Release 11.2.0.3.0 - Production on Sun Apr 29 18:56:31 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /tmp/user_dd.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 15
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 590
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 33
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 782778 (0.782778)
[oracle@rac1 ~]$ dbv userid=sys/xifenfei file='+XIFENFEI/xff/datafile/users.259.776961317' 
> blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Sun Apr 29 18:58:13 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +XIFENFEI/xff/datafile/users.259.776961317


DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 15
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 590
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 33
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

DB2 主要表级锁模拟

Posted on by

模拟X锁

[db2inst1@xifenfei ~]$ db2 connect to xff

   Database Connection Information

 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF

[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326
T_03XFF                         DB2INST1        T     2012-04-11-21.33.12.479480

  3 record(s) selected.

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in exclusive mode"
DB20000I  The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:28:32 -- Date 2012-04-29-01.08.18.056347

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B43210 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B42F30 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          1   0          0x00 0x40000000 
0x99B43240 7          53514C4445464C5428DD630641 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B430F0 7          02000400000000000000000054 Table      ..X  G   7          255 0          0x00 0x40000000 
--还有db2内部P锁和CatCache锁

[db2inst1@xifenfei ~]$ db2 commit
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:28:44 -- Date 2012-04-29-01.08.30.152903

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟S锁

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in share mode"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:29:23 -- Date 2012-04-29-01.09.09.610865

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B43240 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B43030 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          1   0          0x00 0x40000000 
0x99B431B0 7          02000400000000000000000054 Table      ..S  G   7          255 0          0x00 0x40000000 
[db2inst1@xifenfei ~]$ db2 commit
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:29:41 -- Date 2012-04-29-01.09.27.402678

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟Z锁

[db2inst1@xifenfei ~]$ db2 +c drop table t_02xff
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:31:14 -- Date 2012-04-29-01.11.00.399066

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B41270 7          000006000E006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42870 7          00000600010071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42CF0 7          0100000000000000B0FA119F43 CatCache   ..X  G   7          255 0          0x00 0x40000000 
0x99B43240 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B418D0 7          00000600000070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B424B0 7          00000600060071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B415D0 7          00000600050070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42090 7          000006000B0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41AB0 7          0000060004006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B412D0 7          000006000A0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42930 7          00000600100071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42ED0 7          00000C0107004C030000000052 Row        ..S  G   7          2   0          0x10 0x40000000 
0x99B41A50 7          0000060003006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B425D0 7          0000060009006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B420C0 7          000006000F0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42750 7          0000060008006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42630 7          000006000E006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B425A0 7          00000600010072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41F30 7          000006000D006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41B70 7          00000600000071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40CF0 7          0200000000000000000000004F ObjTab     .IN  G   7          255 0          0x00 0x40000000 
0x99B41C60 7          00000600050071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41630 7          00000600040070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42390 7          000006000A0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42030 7          0000060003006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B416F0 7          00000600090070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41690 7          000006000F0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41990 7          0000060008006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B429F0 7          000006000E0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B426F0 7          0000060007006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41E10 7          000006000D006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B428D0 7          00000600000072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42E40 7          000006000C006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42B10 7          00001101100057120000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41CC0 7          00000600040071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B417B0 7          000006000F00CE1A0000000052 Row        ..X  G   7          1   0          0x00 0x40000000 
0x99B42960 7          00000600030070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B422D0 7          00000600090071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41570 7          000006000F0072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41B10 7          0000060002006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41510 7          00000600080070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B413F0 7          000006000E0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40E70 7          00000E006E003B010000000052 Row        ..S  G   7          1   0          0x10 0x40000000 
0x99B41150 7          0000060001006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41BD0 7          0000060007006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42540 7          000006000D0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41210 7          0000060006006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41E70 7          000006000C006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B43090 7          00000500076E0D00B0FA119F43 CatCache   ..X  G   7          255 0          0x00 0x40000000 
0x99B421B0 7          000006000B006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42C30 7          000013000A00C81A0000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B427B0 7          00000600030071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42D50 7          00000A00080063000000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41870 7          00000600020070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B423F0 7          00000600080071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42690 7          0000060001006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40F30 7          00000600070070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41DB0 7          000006000D0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B410F0 7          0000060000006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41F90 7          0000060006006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42A50 7          000006000C0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40F90 7          0000060005006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41ED0 7          000006000B006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41750 7          00000600110070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B414B0 7          000006000A006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B411B0 7          0000060010006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42450 7          000006000F006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40DB0 7          02000000000000000000000070 Pool       .IX  G   7          255 0          0x00 0x40000000 
0x99B42810 7          00000600020071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42F90 7          0000050007006E0D0000000052 Row        ..X  G   7          7   0          0x20 0x40000000 
0x99B41CF0 7          00000600010070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42330 7          00000600070071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41030 7          0000060000006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41D50 7          00000600060070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41810 7          000006000C0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41090 7          0000060005006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42270 7          000006000B0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B419F0 7          0000060004006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41330 7          000006000A006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42210 7          00000600100070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41450 7          0000060009006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42150 7          000006000F006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42C90 7          00001300000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B43210 7          00000C01000000000000000054 Table      .IS  G   7          2   0          0x10 0x40000000 
0x99B42BD0 7          00000E01000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B430F0 7          00000500000000000000000054 Table      .IX  G   7          7   0          0x00 0x40000000 
0x99B42AB0 7          00000600000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B42B70 7          00001101000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B42E70 7          00000800000000000000000054 Table      .IX  G   7          2   0          0x00 0x40000000 
0x99B42DE0 7          00000A00000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B40D50 7          02000500000000000000000054 Table      ..Z  G   7          255 0          0x00 0x40000000 
0x99B40E10 7          00000C00000000000000000054 Table      .IS  G   7          1   0          0x00 0x40000000 
0x99B40ED0 7          00000E00000000000000000054 Table      .IS  G   7          1   0          0x10 0x40000000 
0x99B413C0 7          00000801000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
--除了Z锁之外,因为DDL操作会修改系统表,因此还出现很多在系统表上表锁和行锁

[db2inst1@xifenfei ~]$ db2 rollback
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:31:41 -- Date 2012-04-29-01.11.27.194147

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟IX锁

[db2inst1@xifenfei ~]$ db2 +c "DELETE FROM T_02XFF where tabname LIKE 'T_%XFF'"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:43:47 -- Date 2012-04-29-01.23.33.163605

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3A510 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B40C30 7          0200050008006F010000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B3A4B0 7          02000500000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 

[db2inst1@xifenfei ~]$ db2 rollback
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:45:06 -- Date 2012-04-29-01.24.52.429166

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟SIX锁

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in share mode"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c "DELETE FROM T_01XFF"
SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
query is an empty table.  SQLSTATE=02000
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:39:47 -- Date 2012-04-29-01.19.33.620920

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B40E10 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B40C30 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          5   0          0x00 0x40000000 
0x99B40D50 7          02000400FFFF01000000000052 Row        ..S  G   7          1   0          0x10 0x00000001 
0x99B431B0 7          02000400000000000000000054 Table      SIX  G   7          255 0          0x10 0x40000001

因为IS锁不太好模拟,在本实验中没有体现出来,其本质就是数据库在select查询数据库时给表加的一个表级锁.

Posted in DB2 |