4104 oracle 数据文件名,【案例】Oracle报错ora-04031产生原因和应急解决办法

【案例】Oracle报错ora-04031产生原因和应急解决办法

时间:2016-11-16 21:01   来源:Oracle研究中心   作者:网络   点击:

次

天萃荷净

Oracle研究中心案例分析：运维DBA反映Oracle数据库alert日志报错ora-04031，结合MOS官方文档分析ora-04031产生原因。

本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客

本文链接地址: 缓解ora-04031的一种方法

关于ora-04031错误，已经是老生常谈的问题了。我这里主要描述一种另外一种方式,来说缓解该错误，不过通常不建议这么做，这是一种无奈之举！

SQL> SELECT * FROM v$version WHERE rownum > 2;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL> SHOW parameter shared_pool

NAME TYPE VALUE

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

__shared_pool_size big integer 92M

_dm_max_shared_pool_pct integer 1

_enable_shared_pool_durations boolean TRUE

_io_shared_pool_size big integer 4M

_shared_pool_max_size big integer 0

_shared_pool_minsize_on boolean FALSE

_shared_pool_reserved_min_alloc big integer 4400

_shared_pool_reserved_pct integer 5

shared_pool_reserved_size big integer 4823449

shared_pool_size big integer 0

SQL> SET pagesize 100

SQL> SELECT   '0 (<140)' bucket, ksmchcls, 10 * TRUNC (ksmchsiz / 10) "From",

2           COUNT (*) "Count", MAX (ksmchsiz) "Biggest",

3           TRUNC (AVG (ksmchsiz)) "AvgSize", TRUNC (SUM (ksmchsiz)) "Total"

4      FROM x$ksmsp

5     WHERE ksmchsiz < 140 AND ksmchcls = 'free'

6  GROUP BY ksmchcls, 10 * TRUNC (ksmchsiz / 10)

7  UNION ALL

8  SELECT   '1 (140-267)' bucket, ksmchcls, 20 * TRUNC (ksmchsiz / 20),

9           COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",

10           TRUNC (SUM (ksmchsiz)) "Total"

11      FROM x$ksmsp

12     WHERE ksmchsiz BETWEEN 140 AND 267 AND ksmchcls = 'free'

13  GROUP BY ksmchcls, 20 * TRUNC (ksmchsiz / 20)

14  UNION ALL

15  SELECT   '2 (268-523)' bucket, ksmchcls, 50 * TRUNC (ksmchsiz / 50),

16           COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",

17           TRUNC (SUM (ksmchsiz)) "Total"

18      FROM x$ksmsp

19     WHERE ksmchsiz BETWEEN 268 AND 523 AND ksmchcls = 'free'

20  GROUP BY ksmchcls, 50 * TRUNC (ksmchsiz / 50)

21  UNION ALL

22  SELECT   '3-5 (524-4107)' bucket, ksmchcls, 500 * TRUNC (ksmchsiz / 500),

23           COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",

24           TRUNC (SUM (ksmchsiz)) "Total"

25      FROM x$ksmsp

26     WHERE ksmchsiz BETWEEN 524 AND 4107 AND ksmchcls = 'free'

27  GROUP BY ksmchcls, 500 * TRUNC (ksmchsiz / 500)

28  UNION ALL

29  SELECT   '6+ (4108+)' bucket, ksmchcls, 1000 * TRUNC (ksmchsiz / 1000),

30           COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",

31           TRUNC (SUM (ksmchsiz)) "Total"

32      FROM x$ksmsp

33     WHERE ksmchsiz <= 4108 AND ksmchcls = 'free'

34  GROUP BY ksmchcls, 1000 * TRUNC (ksmchsiz / 1000);

BUCKET KSMCHCLS From Count Biggest AvgSize Total

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

0 (<140) free 70 9 76 75 676

0 (<140) free 40 15 48 43 648

0 (<140) free 20 64 28 24 1572

0 (<140) free 100 73 108 104 7600

0 (<140) free 90 8 92 92 736

0 (<140) free 80 37 88 83 3100

0 (<140) free 110 1 112 112 112

0 (<140) free 50 72 56 52 3760

0 (<140) free 30 10 36 34 344

0 (<140) free 130 3 136 136 408

0 (<140) free 60 18 68 64 1152

0 (<140) free 120 3 128 125 376

1 (140-267) free 180 12 192 187 2252

1 (140-267) free 220 2 232 226 452

1 (140-267) free 200 17 216 205 3492

1 (140-267) free 140 9 156 156 1404

1 (140-267) free 160 4 164 161 644

2 (268-523) free 500 1 520 520 520

3-5 (524-4107) free 1000 5 1060 1020 5104

3-5 (524-4107) free 500 25 996 904 22600

3-5 (524-4107) free 3000 1 3404 3404 3404

3-5 (524-4107) free 1500 1 1736 1736 1736

6+ (4108+) free 20000 1 20584 20584 20584

6+ (4108+) free 574000 1 574780 574780 574780

6+ (4108+) free 2917000 1 2917616 2917616 2917616

6+ (4108+) free 2362000 1 2362844 2362844 2362844

6+ (4108+) free 1085000 1 1085032 1085032 1085032

27 rows selected.

SQL> SET LINES 120

SQL> col sga_heap FOR a30

SQL> SELECT KSMCHIDX "SubPool",

2 'sga heap(' || KSMCHIDX || ',0)' sga_heap,

3 ksmchcom ChunkComment,

4 decode(round(ksmchsiz / 1000),

5 0,

6 '0-1K',

7 1,

8 '1-2K',

9 2,

10 '2-3K',

11 3,

12 '3-4K',

13 4,

14 '4-5K',

15 5,

16 '5-6k',

17 6,

18 '6-7k',

19 7,

20 '7-8k',

21 8,

22 '8-9k',

23 9,

24 '9-10k',

25 '> 10K') "size",

26 COUNT(*),

27 ksmchcls STATUS,

28 SUM(ksmchsiz) Bytes

29 FROM x$ksmsp

30 WHERE KSMCHCOM = 'free memory'

31 GROUP BY ksmchidx,

32 ksmchcls,

33 'sga heap(' || KSMCHIDX || ',0)',

34 ksmchcom,

35 ksmchcls,

36 decode(round(ksmchsiz / 1000),

37 0,

38 '0-1K',

39 1,

40 '1-2K',

41 2,

42 '2-3K',

43 3,

44 '3-4K',

45 4,

46 '4-5K',

47 5,

48 '5-6k',

49 6,

50 '6-7k',

51 7,

52 '7-8k',

53 8,

54 '8-9k',

55 9,

56 '9-10k',

57 '> 10K')

58 ORDER BY "size";

SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*) STATUS BYTES

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

1 sga heap(1,0) free memory 0-1K 357 free 28728

1 sga heap(1,0) free memory 1-2K 31 free 28224

1 sga heap(1,0) free memory 2-3K 1 free 1736

1 sga heap(1,0) free memory 3-4K 1 free 3404

1 sga heap(1,0) free memory > 10K 22 R-free 4683536

1 sga heap(1,0) free memory > 10K 5 free 6944176

6 rows selected.

SQL> break ON ksmchidx ON ksmchdur

SQL> SELECT   ksmchidx, ksmchdur,

2           CASE

3              WHEN ksmchsiz < 1672

4                 THEN TRUNC ((ksmchsiz - 32) / 8)

5              WHEN ksmchsiz < 4120

6                 THEN TRUNC ((ksmchsiz + 7928) / 48)

7              WHEN ksmchsiz < 8216

8                 THEN 250

9              WHEN ksmchsiz < 16408

10                 THEN 251

11              WHEN ksmchsiz < 32792

12                 THEN 252

13              WHEN ksmchsiz < 65560

14                 THEN 253

15              WHEN ksmchsiz <= 65560

16                 THEN 253

17           END bucket,

18           SUM (ksmchsiz) free_space, COUNT (*) free_chunks,

19           TRUNC (AVG (ksmchsiz)) average_size, MAX (ksmchsiz) biggest

20      FROM SYS.x$ksmsp

21     WHERE inst_id = USERENV ('Instance') AND ksmchcls = 'free'

22  GROUP BY CASE

23              WHEN ksmchsiz < 1672

24                 THEN TRUNC ((ksmchsiz - 32) / 8)

25              WHEN ksmchsiz < 4120

26                 THEN TRUNC ((ksmchsiz + 7928) / 48)

27              WHEN ksmchsiz < 8216

28                 THEN 250

29              WHEN ksmchsiz < 16408

30                 THEN 251

31              WHEN ksmchsiz < 32792

32                 THEN 252

33              WHEN ksmchsiz < 65560

34               THEN 253

35           WHEN ksmchsiz <= 65560

36               THEN 253

37         END,

38         ksmchidx,

39         ksmchdur

40  ORDER BY ksmchidx, ksmchdur

41  /

KSMCHIDX KSMCHDUR BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST

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

1 1 4 68 1 68 68

61 520 1 520 520

83 696 1 696 696

98 820 1 820 820

121 1000 1 1000 1000

201 1736 1 1736 1736

236 3404 1 3404 3404

253 574780 1 574780 574780

2 -1 1108 47 23 24

0 596 20 29 36

1 504 12 42 44

2 256 5 51 52

3 348 6 58 60

4 256 4 64 64

5 452 6 75 76

6 1500 18 83 84

7 912 10 91 92

253 1079128 1 1079128 1079128

3 3 56 1 56 56

16 160 1 160 160

20 192 1 192 192

90 756 1 756 756

94 784 1 784 784

106 1764 2 882 884

110 916 1 916 916

253 2906896 1 2906896 2906896

4 -1 44 2 22 24

0 168 5 33 36

2 3424 66 51 52

3 240 4 60 60

4 408 6 68 68

5 224 3 74 76

6 1248 15 83 84

7 176 2 88 88

8 100 1 100 100

9 7500 72 104 108

10 112 1 112 112

11 248 2 124 124

12 128 1 128 128

13 408 3 136 136

15 1404 9 156 156

16 484 3 161 164

18 720 4 180 180

19 188 1 188 188

20 1152 6 192 192

21 2012 10 201 204

22 1264 6 210 212

23 436 2 218 220

25 232 1 232 232

106 6160 7 880 880

110 912 1 912 912

113 940 1 940 940

114 948 1 948 948

119 5912 6 985 988

120 1992 2 996 996

122 2016 2 1008 1008

124 1028 1 1028 1028

128 1060 1 1060 1060

250 4200 1 4200 4200

253 2362844 1 2362844 2362844

60 rows selected.

从上面我们可以发现，目前shared pool有一个subpool(_kghdsidx_count为1)，

该subpool又划分为4个subheap(也有人成为min heap)。

在10g和11g中，使用上面的几个sql查询结果已经不准确了(当然仍然可以参考)，

通过下面的heapdump方式来查看更加确切的信息，如下：

SQL> ALTER system SET events 'immediate trace name heapdump level 2';

System altered.

[oracle@roger ~]$./shared_pool_freelist.ksh roger_ora_5551.trc

-- Heapdump freelist v1.00 by Riyaj Shamsudeen @OraInternals

This script prints shared pool freelists details

1. /tmp/shared_pool_freelist.lst - Freelist information for various heaps

[oracle@roger ~]$ls -ltr /tmp/shared_pool_freelist.lst

-rw-r--r-- 1 oracle dba 6376 Aug 31 14:19 /tmp/shared_pool_freelist.lst

[oracle@roger ~]$cat /tmp/shared_pool_freelist.lst

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

sga heap(1,0)

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

Bucket 13 size=68 Count= 1 Sum= 68

Bucket 126 size=520 Count= 1 Sum= 520

Bucket 170 size=696 Count= 1 Sum= 696

Bucket 187 size=812 Count= 1 Sum= 820

Bucket 189 size=940 Count= 1 Sum= 1000

Bucket 203 size=1708 Count= 1 Sum= 1736

Bucket 229 size=3372 Count= 1 Sum= 3404

Bucket 254 size=65548 Count= 1 Sum= 574780

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

sga heap(1,1)

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

Bucket 1 size=20 Count= 5 Sum= 100

Bucket 2 size=24 Count= 42 Sum= 1008

Bucket 3 size=28 Count= 15 Sum= 420

Bucket 4 size=32 Count= 1 Sum= 32

Bucket 5 size=36 Count= 4 Sum= 144

Bucket 6 size=40 Count= 6 Sum= 240

Bucket 7 size=44 Count= 6 Sum= 264

Bucket 8 size=48 Count= 1 Sum= 48

Bucket 9 size=52 Count= 4 Sum= 208

Bucket 10 size=56 Count= 3 Sum= 168

Bucket 11 size=60 Count= 3 Sum= 180

Bucket 12 size=64 Count= 4 Sum= 256

Bucket 14 size=72 Count= 1 Sum= 72

Bucket 15 size=76 Count= 5 Sum= 380

Bucket 16 size=80 Count= 3 Sum= 240

Bucket 17 size=84 Count= 15 Sum= 1260

Bucket 18 size=88 Count= 2 Sum= 176

Bucket 19 size=92 Count= 8 Sum= 736

Bucket 254 size=65548 Count= 1 Sum= 1078492

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

sga heap(1,2)

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

Bucket 10 size=56 Count= 1 Sum= 56

Bucket 36 size=160 Count= 1 Sum= 160

Bucket 44 size=192 Count= 1 Sum= 192

Bucket 180 size=756 Count= 1 Sum= 756

Bucket 183 size=780 Count= 1 Sum= 784

Bucket 188 size=876 Count= 3 Sum= 2680

Bucket 254 size=65548 Count= 1 Sum= 2904752

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

sga heap(1,3)

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

Bucket 1 size=20 Count= 1 Sum= 20

Bucket 2 size=24 Count= 1 Sum= 24

Bucket 4 size=32 Count= 3 Sum= 96

Bucket 5 size=36 Count= 2 Sum= 72

Bucket 8 size=48 Count= 2 Sum= 96

Bucket 9 size=52 Count= 64 Sum= 3328

Bucket 11 size=60 Count= 4 Sum= 240

Bucket 13 size=68 Count= 6 Sum= 408

Bucket 14 size=72 Count= 1 Sum= 72

Bucket 15 size=76 Count= 2 Sum= 152

Bucket 16 size=80 Count= 3 Sum= 240

Bucket 17 size=84 Count= 12 Sum= 1008

Bucket 18 size=88 Count= 2 Sum= 176

Bucket 21 size=100 Count= 1 Sum= 100

Bucket 22 size=104 Count= 69 Sum= 7176

Bucket 23 size=108 Count= 3 Sum= 324

Bucket 24 size=112 Count= 1 Sum= 112

Bucket 27 size=124 Count= 2 Sum= 248

Bucket 28 size=128 Count= 1 Sum= 128

Bucket 30 size=136 Count= 3 Sum= 408

Bucket 35 size=156 Count= 9 Sum= 1404

Bucket 36 size=160 Count= 2 Sum= 320

Bucket 37 size=164 Count= 1 Sum= 164

Bucket 41 size=180 Count= 4 Sum= 720

Bucket 43 size=188 Count= 1 Sum= 188

Bucket 44 size=192 Count= 6 Sum= 1152

http://www.oracleplus.net Bucket 46 size=200 Count= 7 Sum= 1400

Bucket 47 size=204 Count= 3 Sum= 612

Bucket 48 size=208 Count= 2 Sum= 416

Bucket 49 size=212 Count= 4 Sum= 848

Bucket 50 size=216 Count= 1 Sum= 216

Bucket 51 size=220 Count= 1 Sum= 220

Bucket 54 size=232 Count= 1 Sum= 232

Bucket 188 size=876 Count= 8 Sum= 7072

Bucket 189 size=940 Count= 10 Sum= 9792

Bucket 190 size=1004 Count= 4 Sum= 4104

Bucket 242 size=4108 Count= 1 Sum= 4200

Bucket 254 size=65548 Count= 1 Sum= 2362844

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

我们可以清楚的看到一共有4个subheap，每个subheap有254个bucket，这里需要说明一下的是，其实每个subheap的管理方式完全一样，也是通过freelist的方式，上面的ksh脚本就是print一个完整详细的freelist chunk信息。 下面我们将参数_enable_shared_pool_durations修改为false。

SQL> ALTER system SET "_enable_shared_pool_durations"=FALSE scope=spfile;

System altered.

SQL> shutdown immediate;

DATABASE closed.

DATABASE dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed SIZE 1266392 bytes

Variable SIZE 104860968 bytes

DATABASE Buffers 58720256 bytes

Redo Buffers 2924544 bytes

DATABASE mounted.

DATABASE opened.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dump heapdump 2

Statement processed.

SQL> oradebug tracefile_name

/oracle/product/admin/roger/udump/roger_ora_6086.trc

SQL> !

[oracle@roger ~]$ ls -ltr *.ksh

-rwxrwxrwx 1 oracle dba 2529 Aug 31 14:18 shared_pool_freelist.ksh

[oracle@roger ~]$ ./shared_pool_freelist.ksh /oracle/product/admin/roger/udump/roger_ora_6086.trc

-- Heapdump freelist v1.00 by Riyaj Shamsudeen @OraInternals

This script prints shared pool freelists details

1. /tmp/shared_pool_freelist.lst - Freelist information for various heaps

[oracle@roger ~]$ cat /tmp/shared_pool_freelist.lst

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

sga heap(1,0)

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

Bucket 1 size=20 Count= 14 Sum= 280

Bucket 2 size=24 Count= 8 Sum= 192

Bucket 3 size=28 Count= 13 Sum= 364

Bucket 5 size=36 Count= 4 Sum= 144

Bucket 6 size=40 Count= 4 Sum= 160

Bucket 7 size=44 Count= 11 Sum= 484

Bucket 8 size=48 Count= 19 Sum= 912

Bucket 9 size=52 Count= 24 Sum= 1248

Bucket 10 size=56 Count= 13 Sum= 728

Bucket 11 size=60 Count= 10 Sum= 600

Bucket 12 size=64 Count= 6 Sum= 384

Bucket 13 size=68 Count= 18 Sum= 1224

Bucket 14 size=72 Count= 6 Sum= 432

Bucket 15 size=76 Count= 7 Sum= 532

Bucket 16 size=80 Count= 1 Sum= 80

Bucket 17 size=84 Count= 8 Sum= 672

Bucket 18 size=88 Count= 9 Sum= 792

Bucket 19 size=92 Count= 21 Sum= 1932

Bucket 253 size=32780 Count= 1 Sum= 53844

Bucket 254 size=65548 Count= 0 Sum= 0

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

我们可以看到，将该隐含参数修改为false以后，subheap 消失了，每个subpool只有一个subheap了。

虽然说这样可以在一定程度上消除shared pool 碎片，但是存在另外的隐患，那就是增加了latch的

争用。

关于文中提到的脚本，大家可以去这里下载http://orainternals.wordpress.com/2009/08/06/ora-4031-and-shared-pool-duration/

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ora-04031产生原因和应急解决办法