-
加我微信
加我QQ
标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,597)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (17)
- PostgreSQL恢复 (5)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
月归档：四月 2012
使用dd复制asm中文件
随着数据库新版本的推广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)
发表在 Oracle ASM
评论关闭
DB2 主要表级锁模拟
模拟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查询数据库时给表加的一个表级锁.
发表在 DB2
评论关闭
DB2 runstats和reorg操作
db2收集统计信息(runstats)
[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 "select STATS_TIME from syscat.tables where tabname in('T_01XFF','T_02XFF','T_03XFF')"
STATS_TIME
--------------------------
2012-04-12-04.35.07.539790
2012-04-11-19.55.12.023748
2012-04-11-22.20.07.016905
3 record(s) selected.
--收集表和索引统计信息,包括数据分布
[db2inst1@xifenfei ~]$ db2 "runstats on table db2inst1.t_01xff on all columns
with distribution and detailed indexes all"
DB20000I The RUNSTATS command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select STATS_TIME from syscat.tables where tabname in('T_01XFF')"
STATS_TIME
--------------------------
2012-04-28-23.43.23.904759
1 record(s) selected.
--收集索引统计信息,如果表没有被收集,也会同时对表收集统计信息,对不会收集数据分布信息
[db2inst1@xifenfei ~]$ db2 "runstats on table db2inst1.t_02xff for indexes all"
DB20000I The RUNSTATS command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select STATS_TIME from syscat.tables where tabname in('T_01XFF','T_02XFF')"
STATS_TIME
--------------------------
2012-04-28-23.43.23.904759
2012-04-28-23.44.39.762858
2 record(s) selected.
db2 reorg操作
--删除部分表数据
[db2inst1@xifenfei ~]$ db2 "delete from t_01xff"
DB20000I The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "delete from t_03xff"
DB20000I The SQL command completed successfully.
--reorgchk检查是否需要进行reorg
[db2inst1@xifenfei ~]$ db2 reorgchk on schema db2inst1
Doing RUNSTATS ....
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: DB2INST1.T_01XFF
0 0 0 42 - 0 0 0 0 -**
Table: DB2INST1.T_02XFF
371 0 42 42 - 152110 0 100 100 ---
Table: DB2INST1.T_03XFF
0 0 0 83 - 0 0 0 0 -**
----------------------------------------------------------------------------------------
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20
SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD F4 F5 F6 F7 F8 REORG
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: DB2INST1.T_01XFF
Index: DB2INST1.I_T_01XFF
0 3 3 2 0 0 2 2 822 822 100 0 - 0 100 ----*
--------------------------------------------------------------------------------------------------------------------------------------------------------------
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.
Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.
--离线reorg index
[db2inst1@xifenfei ~]$ db2 reorg table db2inst1.t_01xff index DB2INST1.I_T_01XFF allow read access
DB20000I The REORG command completed successfully.
--在线reorg table
[db2inst1@xifenfei ~]$ db2 reorg table db2inst1.t_01xff inplace allow write access
DB20000I The REORG command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@xifenfei ~]$ db2 reorg table db2inst1.t_03xff inplace allow write access
DB20000I The REORG command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
--证明异步操作完成
[db2inst1@xifenfei ~]$ ps -ef|grep db2reo
db2inst1 1496 1311 0 00:24 pts/1 00:00:00 grep db2reo
--检查reorg操作结果
[db2inst1@xifenfei ~]$ db2 reorgchk on schema db2inst1
Doing RUNSTATS ....
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: DB2INST1.T_01XFF
0 0 0 1 - 0 0 - 0 ---
Table: DB2INST1.T_02XFF
371 0 42 42 - 152110 0 100 100 ---
Table: DB2INST1.T_03XFF
0 0 0 1 - 0 0 - 0 ---
----------------------------------------------------------------------------------------
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20
SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD F4 F5 F6 F7 F8 REORG
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: DB2INST1.T_01XFF
Index: DB2INST1.I_T_01XFF
0 1 0 1 0 0 2 2 822 822 100 - - 0 0 -----
--------------------------------------------------------------------------------------------------------------------------------------------------------------
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.
Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.
发表在 DB2
评论关闭