Study note

rac onenode相关知识小结

2017-03-29 没有评论

Rac onenode是一种非常高可用的架构，他其实就是在10g的single instance HA。但是它又封装了很多东西，使得操作可以很简单，不再需要自己写脚本，也不再需要手工的敲一堆的命令。我们先来看看安装，先安装rac，再将其转换成rac onenode，再转回rac。在已经已经安装好 […]

Study note

12c的一些新等待事件

2017-03-18 没有评论

今天用swingbench加载数据的时候，发现了一些之前没有看过的等待事件，列举一下：（1）LGWR worker group idle 这是因为12c默认是以adaptive方式启用scalable lgwr，即会在自动的在 singlescalable 之间进行切换，可以参考我的这个文章。设 […]

Study note

12.2 online TDE

2017-03-13 没有评论

在12.2之前，如果对表空间进行透明数据加密，这是需要停机时间的，可参考 Oracle Advanced Security 透明数据加密最佳实践，但是在12.2中，我们可以不用停机的进行TDE加密了。是的，no downtime。我们先来创建一个表空间，创建一个表，如信用卡信息表（credit_ […]

..oracle service, Study note, Working case

Documented Database Bugs With High “Solved SR” Count

2017-02-02 没有评论

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.8 and later

1	Oracle Database - Enterprise Edition - Version 9.2.0.8 and later

PURPOSE:

This note lists "documented" database bugs that have been marked as the solution to a "high number of Service Requests".
For the purpose of this listing:
A "documented" bug means one with a bugno.8 KM document created from BugTag data.
A "high number of Service Requests" means that there are more than 50 "Solved SR" entries for the bug - ie: all bugs in this doc have > 50 "Solved SR" entries from the SR closure data.
It is suggested to restrict the list based on the version of interest, and then use the radio buttons to check on particular features / symptoms / facts linked to the bug descriptions.

The list includes some bugs with a "D" in the "NB" column - this is used to denote a bug fix that is DISABLED by default and so a customer may encounter that issue in DB versions where the bug is marked as fixed.

1

2

3

4

5

6

7

This note lists "documented" database bugs that have been marked as the solution to a "high number of Service Requests".

For the purpose of this listing:

A "documented" bug means one with a bugno.8 KM document created from BugTag data.

A "high number of Service Requests" means that there are more than 50 "Solved SR" entries for the bug - ie: all bugs in this doc have > 50 "Solved SR" entries from the SR closure data.

It is suggested to restrict the list based on the version of interest, and then use the radio buttons to check on particular features / symptoms / facts linked to the bug descriptions.

The list includes some bugs with a "D" in the "NB" column - this is used to denote a bug fix that is DISABLED by default and so a customer may encounter that issue in DB versions where the bug is marked as fixed.

KNOWN BUGS: NB Prob Bug Fixed Description P IIII 7272646 Linux- […]

Study note

设置threaded_execution启用12c的多线程模式

2016-12-20 2条评论

Unix/Linux中oracle数据库进程采用多进程模式，如我们可以在系统进程列表中看到pmon，smon，dbwr，lgwr，ckpt等oracle系统进程。随着oracle数据库功能增多，进程数量也随之增加，创建进程的开销以及进程上下文切换的开销也越来越大（进程状态切换 switching 是 […]

Study note

关于oradebug -prelim

2016-12-16 没有评论

在oracle数据库hang的情况下，我们可以用sqlplus -prelim / as sysdba登录数据库，进行一些收集信息的操作，也可以进行shutdown database的操作。这里需要注意几点： 1. process满是可以用sqlplus -prelim / as sysdba登录的 […]

Study note

查找被kill掉的session的操作系统进程号

2016-12-16 3条评论

11g之前：

select spid, program from v$process
where program!= 'PSEUDO'
and addr not in (select paddr from v$session)
and addr not in (select paddr from v$bgprocess )
and addr not in (select paddr from v$shared_server);

1

2

3

4

5

select spid, program from v$process

where program!= 'PSEUDO'

and addr not in (select paddr from v$session)

and addr not in (select paddr from v$bgprocess )

and addr not in (select paddr from v$shared_server);

11g之后：

select * from v$process where addr=(select creator_addr from v$session where sid=140);

1	select * from v$process where addr=(select creator_addr from v$session where sid=140);

参考： How To Find The Process Identifier (pid, spid) After The Correspon […]

Study note

分区索引知识点拾遗

2016-12-14 没有评论

索引是一般索引还是分区索引，可以看dba_indexes的partitioned字段。如果partitioned字段是YES，说明是分区索引，那么，这个索引是global还是local，可以看dba_part_indexes的LOCALITY字段。另外，我们还可以看ALIGNMENT字段，看这个 […]

Study note

再谈sharding database的一些概念

2016-11-30 没有评论

在继『Oracle sharding database的一些概念』和『sharding database的一些概念的补充』之后，我觉得还是有些概念需要谈一下。 1.shard prune(分片裁剪): 这个概念类似分区裁剪（partition prune），是指根据sql语句会到对应的分片上去。但 […]

Study note

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上的操作会发生什么问题。这里我们要注意下，查询分如下几种情况： […]