首页 » ORACLE » Oracle background process DBWR trace file not found?

Oracle background process DBWR trace file not found?

近日分析一个数据库checkpoint long time 未完成的一个case时,本想分析dbwr trace file中看看是否有报错,发现dbwr的trace file并不存在,并且重启数据库后也并未生成,发现这并非个案,好多环境中dbwr trace不存在, 下面记录一种启用方式。

SQL> col tracefile for a90
SQL> r
  1* select spid,program,tracefile from v$process where pname like '%DBW%'

SPID                     PROGRAM                                          TRACEFILE
------------------------ ------------------------------------------------ -------------------------------------------------------------------------------------                          -----
2032                     oracle@oel7db1 (DBW0)                            /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc

SQL> select spid,program,tracefile from v$process where pname like '%DBW%';

SPID                     PROGRAM                                          TRACEFILE
------------------------ ------------------------------------------------ -------------------------------------------------------------------------------------                          -----
2032                     oracle@oel7db1 (DBW0)                            /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc

SQL> host
[oracle@oel7db1 ~]$ ls -l /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc
ls: cannot access /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc: No such file or directory
[oracle@oel7db1 ~]$ exit
exit

SQL> @pd dbwr
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ------------------------------------------------------                          ---------------------------------------------
       392   188 dbwr_io_slaves                                           0                              DBWR I/O slaves
      1620   654 _dbwr_tracing                                            0                              Enable dbwriter tracing
      1629   65D _dbwr_async_io                                           TRUE                           Enable dbwriter asynchronous writes
      1683   693 _db_writer_chunk_writes                                  0                              Number of writes DBWR should wait for
      1686   696 _db_writer_histogram_statistics                          FALSE                          maintain dbwr histogram statistics in x$kcbbhs
      1687   697 _dbwr_scan_interval                                      300                            dbwriter scan interval
      1688   698 _db_writer_flush_imu                                     TRUE                           If FALSE, DBWR will not downgrade IMU txns for AGING
      1691   69B _db_writer_coalesce_area_size                            524288                         Size of memory allocated to dbwriter for coalescing wr                          ites
      1692   69C _db_writer_nomemcopy_coalesce                            FALSE                          Enable DBWR no-memcopy coalescing
      1695   69F _dbwr_stall_write_detection_interval                     900                            dbwriter stall write detection interval
      1696   6A0 _dbwr_flashlock_shrink_limit                             0                              limit to shrink le to flash lock per dbwr iteration
      1697   6A1 _enable_dbwr_auto_tracing                                0                              enable dbwriter auto-tracing
      1698   6A2 _dbwr_nowrite_assert_interval                            7200                           dbwriter assert interval after no write seconds
      1699   6A3 _dbwr_nwp_assert_interval                                1800                           dbwriter no write permission assert interval after no                           write seconds
      1700   6A4 _remessage_dbwrs                                         0                              DBWR wait and remessage time - in cs

15 rows selected.


SQL> alter system set "_enable_dbwr_auto_tracing"=1;
System altered.

SQL> ho ls -l /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc
ls: cannot access /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc: No such file or directory

SQL> alter system checkpoint;
System altered.

SQL> ho ls -l /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc
ls: cannot access /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc: No such file or directory

SQL> alter system set "_enable_dbwr_auto_tracing"=0;
System altered.

SQL> alter system reset "_enable_dbwr_auto_tracing";
System altered.

Note:
_enable_dbwr_auto_tracing并没有,可能是level值不对。

SQL> alter system set "_dbwr_tracing"=16;

System altered.

SQL> ho ls -l /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc
ls: cannot access /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc: No such file or directory

SQL> alter system checkpoint;
System altered.

SQL> ho ls -l /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc
-rw-r----- 1 oracle oinstall 6580 Sep  9 14:44 /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc

SQL> host
[oracle@oel7db1 ~]$ ls -l /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc
-rw-r----- 1 oracle oinstall 6580 Sep  9 14:44 /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc

SQL> insert into anbob.test1 select rownum from dual connect by rownum<=1e3;
1000 rows created.

SQL> commit;
Commit complete.

SQL> alter system checkpoint;
System altered.

SQL> ho ls -l /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc
-rw-r----- 1 oracle oinstall 6580 Sep  9 14:44 /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc

Note:
_dbwr_tracing level 16可以生成trace file 但是不是checkpoint相关,多次checkpoint后tracefile未更新。

SQL> alter system set "_dbwr_tracing"=31;
System altered.

SQL> alter system checkpoint;
System altered.

SQL> ho ls -l /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc
-rw-r----- 1 oracle oinstall 131737 Sep  9 15:24 /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc

SQL> alter system checkpoint;
System altered.

SQL> ho ls -l /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc
-rw-r----- 1 oracle oinstall 133226 Sep  9 15:24 /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_dbw0_2032.trc

Note:
“_dbwr_tracing”=31 可以生成tracefile 并更新checkpoint信息。

trace file内容摘要

*** 2022-09-09T15:24:15.697616+08:00 (CDB$ROOT(1))
wdone=0, nwr=0, ndefer=0, nfree=4096, nchnkio=204, nbatch=94,nlw2=0, nwdlm=0
Scanned 0 buffers
Scanned 0 buffers
kcbkcbc: bcnt 6344, nbh 6344, klic 0, ourmax 0, dcnt 38
self tune: threshold=1 stoio=0 tdirty=38
self tune rba:isrbaval=1, ii=667 si=566
kcbbwdl: stune=1  mwrites=1 nsaved:0 nwritten:0 tdirty=38 target=1
3:ts=1662708255 fbx=0 wr=0 defer=0 issue=0 done=1 l2w=0
  l2issued=0 nthrd=0 ntbsp=0 nircv=0 nmrcv=0 npqto=0 nrbrc=0
7:ts=1662708255
1:ts=1662708258 fbx=0 wr=0 defer=0 nthrd=0 ntbsp=0 nircv=0 nmrcv=0 npqto=0 nrbrc=0 nwl2=0 nwdlm=0msgp=(nil)

*** 2022-09-09T15:24:18.705121+08:00 (CDB$ROOT(1))
wdone=0, nwr=0, ndefer=0, nfree=4096, nchnkio=204, nbatch=95,nlw2=0, nwdlm=0
Scanned 0 buffers
Scanned 0 buffers
kcbkcbc: bcnt 6344, nbh 6344, klic 0, ourmax 0, dcnt 38
self tune: threshold=1 stoio=0 tdirty=38
self tune rba:isrbaval=1, ii=668 si=567
kcbbwdl: stune=1  mwrites=1 nsaved:0 nwritten:0 tdirty=38 target=1
3:ts=1662708258 fbx=0 wr=0 defer=0 issue=0 done=1 l2w=0
  l2issued=0 nthrd=0 ntbsp=0 nircv=0 nmrcv=0 npqto=0 nrbrc=0
7:ts=1662708258

*** 2022-09-09T15:24:21.713469+08:00 (CDB$ROOT(1))
1:ts=1662708261 fbx=0 wr=0 defer=0 nthrd=0 ntbsp=0 nircv=0 nmrcv=0 npqto=0 nrbrc=0 nwl2=0 nwdlm=0msgp=(nil)
wdone=0, nwr=0, ndefer=0, nfree=4096, nchnkio=204, nbatch=96,nlw2=0, nwdlm=0
Scanned 0 buffers
Scanned 0 buffers
kcbkcbc: bcnt 6344, nbh 6344, klic 0, ourmax 0, dcnt 38
self tune: threshold=1 stoio=0 tdirty=38
self tune rba:isrbaval=1, ii=669 si=568
kcbbwdl: stune=1  mwrites=1 nsaved:0 nwritten:0 tdirty=38 target=1
3:ts=1662708261 fbx=0 wr=0 defer=0 issue=0 done=1 l2w=0
  l2issued=0 nthrd=0 ntbsp=0 nircv=0 nmrcv=0 npqto=0 nrbrc=0
7:ts=1662708261
1:ts=1662708261 fbx=1 wr=0 defer=0 nthrd=0 ntbsp=0 nircv=0 nmrcv=0 npqto=0 nrbrc=0 nwl2=0 nwdlm=0msgp=0x7ffff3bbae88 proc=770e6968
...
KCBB: dump buffer
BH (0x873f03d8) file#: 4 rdba: 0x0100aecd (4/44749) class: 30 ba: 0x872b2000
  set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
  dbwrid: 0 obj: -1 objn: 0 tsn: [1/2] afn: 4 hint: f
  hash: [0x84ffc530,0x746b1cb0] lru: [0x873f0790,0x873f0358]
  obj-flags: object_ckpt_list
  ckptq: [0x857d3f08,0x87fe7a08] fileq: [0x87fe7e50,0x87fe7a18]
  objq: [0x6d8055c8,0x87fe7f50] objaq: [0x873f07c8,0x873f0390]
  st: XCURRENT md: NULL fpin: 'ktuwh03: ktugnb' fscn: 0x24a7561 tch: 2
  flags: buffer_dirty block_written_once
  LRBA: [0x165.65d0.0] LSCN: [0x24a7730] HSCN: [0x24a7745] HSUB: [1]
  Printing buffer operation history (latest change first):
  cnt: 11
  01. sid:10 L464:chg1_mn:bic:FMS     02. sid:10 L778:chg1_mn:bis:FMS
  03. sid:10 L362:chg1:set:MEXCL      04. sid:10 L464:chg1_mn:bic:FMS
  05. sid:10 L614:chg1_mn:bis:FBD     06. sid:10 L922:klbc:sw:cq
  07. sid:10 L778:chg1_mn:bis:FMS     08. sid:10 L362:chg1:set:MEXCL
  09. sid:02 L192:kcbbic2:bic:FBD     10. sid:02 L191:kcbbic2:bic:FBW
  11. sid:02 L602:bic1_int:bis:FWC    12. sid:02 L822:bic1_int:ent:rtn
  13. sid:02 L832:oswmqbg1:clr:WRT    14. sid:02 L930:kubc:sw:mq
  15. sid:02 L913:bxsv:sw:objq        16. sid:02 L608:bxsv:bis:FBW
  buffer tsn: 2 rdba: 0x0100aecd (4/44749)
  scn: 0x24a7745 seq: 0x01 flg: 0x00 tail: 0x77450201
  frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
KCBB: going to write
xsv: slt=7fc1538aafe8, nfr=4094, buf=0x873f03d8, dba=0x100aecd, nwr=1, clse=1
KCBB: set being written
KCBB: Returning from xsv ret 0
CURRENT: index = 0 nchunks = 128 nbytes = 524288 failed_chunks = 129, SSTIOMAX = 1048576
KCBB: Entered xsv tsn 2, rdba 100aece, afn 4, class 30, obj 4294967295
KCBB: not found tsn 0x100000002, rdba 0x100aece
KCBB: Returning from xsv ret 8
KCBB: Entered xsv tsn 2, rdba 100aecc, afn 4, class 30, obj 4294967295
...

Note:
该trace 更新较快,建议仅用于诊断问题期间,避免文件过大导致文件系统空间耗尽。

— over —

打赏

目前这篇文章还没有评论(Rss)

我要评论