首页 » ORACLE 9i-23ai » 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 —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。