oracle trc文件详解

天萃荷净

11GR2 Control file enqueue hold time tracking dump

如果你比较心细,可能在11.2的数据库中发现alert文件中存在存在类此下面的记录

Errors in file /oradb/diag/rdbms/offon/offon2/trace/offon2_ckpt_.trc:

查看trace文件发现

* 2012-08-01 03:36:03.520

1: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time:

2: 890ms (rw) file: kcrf.c line: 10012 count: 6 total: 4266ms time:

3: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time:

4: 530ms (rw) file: kcv.c line: 11783 count: 1 total: 530ms time:

Control file enqueue hold time tracking dump at time:

* 2012-08-03 02:14:38.714

1: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time:

2: 890ms (rw) file: kcrf.c line: 10012 count: 7 total: 4953ms time:

3: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time:

4: 530ms (rw) file: kcv.c line: 11783 count: 1 total: 530ms time:

Control file enqueue hold time tracking dump at time:

这个类此我们在10g中看到的lgwr的警告类此,其实也就是oracle对lgwr进程写入日志慢的时候的一个trace功能记录下来的.

我们这里遇到的是因为oracle对ckpt进程的trace,当control file enqueue holding time tracking size超过10的时候，就会记录到trace文件中,oracle 内部文档对于该问题的一些描述如下:

About the issue, this is the expected behavior on 11.2.

New controlfile enqueue hold time tracking statistics have been added in 11.2 to

aid diagnosis of controlfile transaction related performance related issues:

Control File Enqueue AWR Statistics:

* max cf enq hold time - The maximum amount of time in milliseconds a client has held the control file enqueue.

* total cf enq hold time - The total amount of time in milliseconds all clients have held the control file enqueue.

* total number of cf enq holders - The total number of times clients have held the control file enqueue.

Periodically, the CKPT process dumps statistics for the top N control file enqueue holders.

N defaults to 10, but can be modified with the static hidden parameter:

_controlfile_enqueue_holding_time_tracking_size.The dump looks like the following:

Preface: "Control file enqueue hold time tracking dump at time: [relative time]".

* a. Time the client has held the control file enqueue.

* b. Type of client's control file enqueue transaction - rw or ro.

* c. File name where the client obtained control file enqueue.

* d. Line number where the client obtained control file enqueue.

* e. Number of times the client has held the control file enqueue since it became a member of the top N.

* f. Total time the client has held the control file in all those times from [e].

* g. Relative time the client obtained the control file enqueue from [a].

查询数据库默认值

SQL> col value for a24

SQL> col description for a70

SQL> set linesize 180

SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description

2 from x$ksppi a,x$ksppcv b

3 where a.inst_id = USERENV ('Instance')

4 and b.inst_id = USERENV ('Instance')

5 and a.indx = b.indx

6 and upper(a.ksppinm) LIKE upper('%¶m%')

7 order by name

8 /

Enter value for param: _controlfile_enqueue_holding_time_tracking_size

old 6: and upper(a.ksppinm) LIKE upper('%¶m%')

new 6: and upper(a.ksppinm) LIKE upper('%_controlfile_enqueue_holding_time_tracking_size%')

NAME VALUE DESCRIPTION

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

_controlfile_enqueue_holding_time_tracking_size 10 control file enqueue holding time tracking size

虽然在alert日志中使用Error的形式显示该错误,但是这只是一个oracle作为诊断数据库Control File Enqueue性能的一个依据,大部分情况下,我们可以选择忽略或者关闭该诊断功能.屏蔽该提示方法如下:

The way to shut off is set _controlefile_enqueue_holding_time_tracking_size = 0 then restart the database

-- spfile

alter system set "_controlfile_enqueue_holding_time_tracking_size"=0 scope=spfile;

-- pfile

_controlfile_enqueue_holding_time_tracking_size=0

Restart database

-----------------温馨提示--------------------

操作有风险，动手需谨慎

Oracle研究中心 www.oracleplus.net

本文由大师惜分飞分享，转载请尽量保留本站网址。

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

最权威、专业的Oracle案例资源汇总之11.2的数据库中发现alert文件中存在Errors in file trc