OracleBlog – 第 10 页 – 笑看数据库江湖的那些事儿……

Mode=4的TX锁小结

2016-11-30 没有评论

1. bitmap索引

--session 1
SQL> create table t1_tx(id number,name varchar2(20)) ;

Table created.

SQL> insert into t1_tx select rownum,chr(97+mod(rownum,2)) from dual connect by level<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> select * from t1_tx;

        ID NAME
---------- ----------------------------------------
         1 b
         2 a
         3 b
         4 a
         5 b
         6 a
         7 b
         8 a
         9 b
        10 a

10 rows selected.

SQL> create bitmap index idx_bitmap_name on t1_tx(name);

Index created.

SQL> select sid from v$mystat where rownum=1;

       SID
----------
        63

SQL> update t1_tx set name='tx' where id=3;

1 row updated.

SQL> -- not commit

-- session 2:
SQL> select sid from v$mystat where rownum=1;

       SID
----------
         5

SQL> update t1_tx set name='bitmap' where id=5;
--hang
 
---session 3
SQL> select sid,
  2         chr(bitand(p1, -16777216) / 16777215) ||
  3         chr(bitand(p1, 16711680) / 65535) "Name",
  4         (bitand(p1, 65535)) "Mode",
  5         event,
  6         sql_id,
  7         FINAL_BLOCKING_SESSION
  8    from v$session
  9   where event like 'enq%';

       SID Name                   Mode EVENT                          SQL_ID                     FINAL_BLOCKING_SESSION
---------- ---------------- ---------- ------------------------------ -------------------------- ----------------------
         5 TX                        4 enq: TX - row lock contention  1qtvgrv88q2dj                              63

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

--session 1

SQL> create table t1_tx(id number,name varchar2(20)) ;

Table created.

SQL> insert into t1_tx select rownum,chr(97+mod(rownum,2)) from dual connect by level<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> select * from t1_tx;

ID NAME

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

1 b

2 a

3 b

4 a

5 b

6 a

7 b

8 a

9 b

10 a

10 rows selected.

SQL> create bitmap index idx_bitmap_name on t1_tx(name);

Index created.

SQL> select sid from v$mystat where rownum=1;

SID

----------

63

SQL> update t1_tx set name='tx' where id=3;

1 row updated.

SQL> -- not commit

-- session 2:

SQL> select sid from v$mystat where rownum=1;

SID

----------

5

SQL> update t1_tx set name='bitmap' where id=5;

--hang

---session 3

SQL> select sid,

2 chr(bitand(p1, -16777216) / 16777215) ||

3 chr(bitand(p1, 16711680) / 65535) "Name",

4 (bitand(p1, 65535)) "Mode",

5 event,

6 sql_id,

7 FINAL_BLOCKING_SESSION

8 from v$session

9 where event like 'enq%';

SID Name Mode EVENT SQL_ID FINAL_BLOCKING_SESSION

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

5 TX 4 enq: TX - row lock contention 1qtvgrv88q2dj 63

SQL>

2. 主外键关系，主键表插入数据不提交,外键表插入数据被阻塞

--session 1
SQL> create table parent(id number primary key);

Table created.

SQL> create table child(id number references parent,name varchar2(20));

Table created.

SQL> insert into parent values(1);

1 row created.

SQL>--not commit;

--session 2:
SQL> insert into child values(1,'a');
--hang

--session 3：
SQL> l
  1  select sid,
  2         chr(bitand(p1, -16777216) / 16777215) ||
  3         chr(bitand(p1, 16711680) / 65535) "Name",
  4         (bitand(p1, 65535)) "Mode",
  5         event,
  6         sql_id,
  7         FINAL_BLOCKING_SESSION
  8    from v$session
  9*  where event like 'enq%'
SQL> /

       SID Name                   Mode EVENT                          SQL_ID                     FINAL_BLOCKING_SESSION
---------- ---------------- ---------- ------------------------------ -------------------------- ----------------------
         5 TX                        4 enq: TX - row lock contention  d8gc19unfrcsw                              63

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

--session 1

SQL> create table parent(id number primary key);

Table created.

SQL> create table child(id number references parent,name varchar2(20));

Table created.

SQL> insert into parent values(1);

1 row created.

SQL>--not commit;

--session 2:

SQL> insert into child values(1,'a');

--hang

--session 3：

SQL> l

1 select sid,

2 chr(bitand(p1, -16777216) / 16777215) ||

3 chr(bitand(p1, 16711680) / 65535) "Name",

4 (bitand(p1, 65535)) "Mode",

5 event,

6 sql_id,

7 FINAL_BLOCKING_SESSION

8 from v$session

9* where event like 'enq%'

SQL> /

SID Name Mode EVENT SQL_ID FINAL_BLOCKING_SESSION

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

5 TX 4 enq: TX - row lock contention d8gc19unfrcsw 63

SQL>

3.插入主键同一值

--session 1:
SQL> create table mytab(id number primary key);

Table created.

SQL> insert into mytab values(1);

1 row created.

SQL>

--session 2:
SQL> insert into mytab values(1);
--hang

--session 3:
SQL> l
  1  select sid,
  2         chr(bitand(p1, -16777216) / 16777215) ||
  3         chr(bitand(p1, 16711680) / 65535) "Name",
  4         (bitand(p1, 65535)) "Mode",
  5         event,
  6         sql_id,
  7         FINAL_BLOCKING_SESSION
  8    from v$session
  9*  where event like 'enq%'
SQL> /

       SID Name                   Mode EVENT                          SQL_ID                     FINAL_BLOCKING_SESSION
---------- ---------------- ---------- ------------------------------ -------------------------- ----------------------
         5 TX                        4 enq: TX - row lock contention  2srnv7c1ummk0                              63

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

--session 1:

SQL> create table mytab(id number primary key);

Table created.

SQL> insert into mytab values(1);

1 row created.

SQL>

--session 2:

SQL> insert into mytab values(1);

--hang

--session 3:

SQL> l

1 select sid,

2 chr(bitand(p1, -16777216) / 16777215) ||

3 chr(bitand(p1, 16711680) / 65535) "Name",

4 (bitand(p1, 65535)) "Mode",

5 event,

6 sql_id,

7 FINAL_BLOCKING_SESSION

8 from v$session

9* where event like 'enq%'

SQL> /

SID Name Mode EVENT SQL_ID FINAL_BLOCKING_SESSION

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

5 TX 4 enq: TX - row lock contention 2srnv7c1ummk0 63

SQL>

Study note

关于几个工具的替代版本

2016-11-22 没有评论

最近看到由于使用盗版的PL/SQL Developer，导致PL/SQL Developer的登录脚本afterconnect.sql被注入的情况：『针对最近黑客攻击数据库的解决方案和预防建议』『知己知彼-关于Oracle安全比特币勒索问题揭秘和防范』『plsql dev引起的数据库被黑勒索比 […]

Study note

shard node的outage测试

2016-11-22 没有评论

shard node的路由方式有直接路由和代理路由，之前我们已经说过，由于我没有connection pool，我们只能来测试一下，在代理路由的情况下，连接shardcat的情况下，当shard node出现意外，连接在shardcat上的操作会发生什么问题。这里我们要注意下，查询分如下几种情况： […]

..experience, Working case

一次dataguard坏块的修复

2016-11-18 3条评论

客户有个11g的active dataguard库，mrp进程停了，看alertlog，可以看到有关ora-7445[kdxlin]的报错：

cat alert*.log
....
Exception [type:SIGSEOV,Address not mapped to object] [ADDR:0xC] |PC：0x96504C7,kdxlin()+4153][flags: 0x0,count:1]
Errors in le /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/trace/partsm_pr18_21343.trc (incident=70353):
ORA-07445: exception encountered: core dump (kdxlin()+4153) [SIGSEGV] [ADDR:0xC] |PC:0x96504C7][Address not mapped to object][]
Incident details in: /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc
Use ADRCI or Support Workbench to package the incident.

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xC][PC:0x96504C7,kdxlin()+4153][flags:0XO,count:1]
Incidenl 70353 created, dump file/aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc
ORA-07445: exception encountered: core dump (kdxlin()+4153) [SIGSEGV] [ADDR:0xC] |PC:0x96504C7][Address not mapped to object][]
...

1

2

3

4

5

6

7

8

9

10

11

12

cat alert*.log

....

Exception [type:SIGSEOV,Address not mapped to object] [ADDR:0xC] |PC：0x96504C7,kdxlin()+4153][flags: 0x0,count:1]

Errors in le /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/trace/partsm_pr18_21343.trc (incident=70353):

ORA-07445: exception encountered: core dump (kdxlin()+4153) [SIGSEGV] [ADDR:0xC] |PC:0x96504C7][Address not mapped to object][]

Incident details in: /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc

Use ADRCI or Support Workbench to package the incident.

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xC][PC:0x96504C7,kdxlin()+4153][flags:0XO,count:1]

Incidenl 70353 created, dump file/aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc

ORA-07445: exception encountered: core dump (kdxlin()+4153) [SIGSEGV] [ADDR:0xC] |PC:0x96504C7][Address not mapped to object][]

...

cat /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc
...
Error 607 in redo applicalion callback
Dump of change vector
Typ:2 CLS:1 APN:5 DBA0x2598d645 OBJ:3S3792 SCN:0x0960.99d2655e SEQ:1 OP:10.2 ENC:0 RBL:0
index redo(kdxlin):insert leaf row
KTB Redo
op:0x01 ver:0x01
compat bit:4(post-11) padding:1
op:F xid:0x0001.01a.010d8f34 uba:0x00dc8e3.6bf5.20
REDO: SINGLE/NONKEY/--
itl:3, sno:255, row size 23
insert key:(14):05 c4 02 4e 31 4f 07 78 74 0b 09 11 27 29
nonkey (length: 5):
fb: --H-FL-- lb:0x0 cc:1
(2).01 80
Block after image is corrupt:
buffer tsn: 5 rdba:0x2598d645(1024/630773317)
scn:0x960.99d10d33 seq:0x01 flg:0x04 tail:0x0d330601
frmt:0x02 chkval:0xa1ae type:0x06=trans data
Hex dump of currupt header 3=CHKVAL
...

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

cat /aabb/app/oracle/rdbms/diag/rdbms/rmydbsid/mydbsid/incident/incdir_70353/mydbsid_pr18_21343_i70353.trc

...

Error 607 in redo applicalion callback

Dump of change vector

Typ:2 CLS:1 APN:5 DBA0x2598d645 OBJ:3S3792 SCN:0x0960.99d2655e SEQ:1 OP:10.2 ENC:0 RBL:0

index redo(kdxlin):insert leaf row

KTB Redo

op:0x01 ver:0x01

compat bit:4(post-11) padding:1

op:F xid:0x0001.01a.010d8f34 uba:0x00dc8e3.6bf5.20

REDO: SINGLE/NONKEY/--

itl:3, sno:255, row size 23

insert key:(14):05 c4 02 4e 31 4f 07 78 74 0b 09 11 27 29

nonkey (length: 5):

fb: --H-FL-- lb:0x0 cc:1

(2).01 80

Block after image is corrupt:

buffer tsn: 5 rdba:0x2598d645(1024/630773317)

scn:0x960.99d10d33 seq:0x01 flg:0x04 tail:0x0d330601

frmt:0x02 chkval:0xa1ae type:0x06=trans data

Hex dump of currupt header 3=CHKVAL

...

从trace中，我们可以看 […]

..experience, Working case

LGWR不工作在实时优先级

2016-11-15 没有评论

在solaris环境，在11.2.0.4之后（或者Patch 16387058 on top of 11.2.0.3.5），Oracle自动把LGWR进程放到_high_priority_processes中，（不管是单实例还是RAC）。在这之前，需要手工运行priocntl -s FX -m 60 […]

Study note

sharding database的一些概念的补充

2016-11-14 没有评论

在继『Oracle sharding database的一些概念』之后，我觉得还有一些关于sharding的概念，是值得理解的。 (1) shardgroup：

In system-managed and composite sharding, the logical unit of replication is a group of shards called a shardgroup.

1	In system-managed and composite sharding, the logical unit of replication is a group of shards called a shardgroup.

也就是说，在逻辑上，将一组相同复制属性的shard称作shard gro […]

Study note

Step by Step建立带ADG的sharding db

2016-11-14 一条评论

上次的文章，我们说到sharding其实是非常需要HA架构的，我们今天来安装一个带ADG的sharding node的sharding database。（我只是把shard node做成了ADG，且没有加RAC架构，shardcat也没有做ADG，因为……我的虚拟机实在吃不消了！） 1.大致环境 […]

Study note

sharding db的HA架构

2016-11-14 没有评论

sharding database最大的特点是可以横向扩展。但是横向扩展不是RAC的横向扩展，纯sharding db是没有HA架构的。即一个shardcat db，多个shard node db。无论是谁down了，都会造成不可用。我们从上往下捋一下，看看哪里有单点故障，这个单点可以通过什么方式 […]

Working case

re-shard和chunk migration

2016-11-10 没有评论

添加或者删除shard，是shard的数量发生变化，叫做re-shard。re-shard会导致chunk的挪动（chunk migration），re-shard的chunk migration是将原来的chunk的序号大的部分，移动到新node上。示意图如下：我们来发起一次添加shard节点， […]

..oracle service

12.2 Now Available on Oracle Cloud

2016-11-09 没有评论

根据twitter上 ‏@OracleDatabase 的消息12.2在oracle云上已经可用了。具体的说，是在DBCS(Oracle Database Cloud Service) ，ECS(Exadata Cloud Service)，以及EECS(Exadata Express Cloud […]

Study note

关于config shard的状态不正常的处理

2016-11-07 没有评论

我写关于12.2 sharding database的文章已经好久了，今天再次把环境启动了起来，启动了主机之后，依次启动了listener和shardcat数据库和shard node数据库实例。检查shard状态的时候，发现报错：（a）问题一：status显示warnings： [crayon- […]

It's my life

给vbox磁盘空间扩容

2016-11-03 没有评论

我的vbox的根目录磁盘空间快不够用了，

[root@testdb2 ~]# df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg_testdb2-lv_root
                      27431124  21329720   4707948  82% /
tmpfs                  3481600        88   3481512   1% /dev/shm
/dev/sda1               495844     56258    413986  12% /boot
[root@testdb2 ~]#

1

2

3

4

5

6

7

[root@testdb2 ~]# df -k

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/mapper/vg_testdb2-lv_root

27431124 21329720 4707948 82% /

tmpfs 3481600 88 3481512 1% /dev/shm

/dev/sda1 495844 56258 413986 12% /boot

[root@testdb2 ~]#

我原来的oracle软件和数据，都放在根目录的/u01下，现在根目录已经82%了。我决定给vbox加一块盘，扩大根目录的空间。首先，在vbox的图形化界面添加一块硬盘。当然，如果不用图形 […]