DBW*的trace文件过大的bug

问题描述：

近期某现场发现trace目录下的dbw*文件达到了大几G的大小导致/oracle目录占用率突增，删除这些trace文件，几天后又重新生成较大的dbw*的trace

11G Dec 4 10:38 rb_dbw0_2086848.trc

3.6G Dec 4 10:38 rb_dbw1_2086852.trc

4.4G Dec 4 10:38 rb_dbw2_2086856.trc

4.7G Dec 4 10:38 rb_dbw3_2086860.trc

2.4G Dec 4 10:37 rb_dbw4_2086864.trc

3.7G Dec 4 10:37 rb_dbw5_2086868.trc

检查message日志和数据库alert日志也无明显异常，第一次看awr报告有个全表查询的sql执行了39177s，表大概800G，怀疑是此人为跑的sql导致；但第二次异常并没有长sql跑，检查trace的内容，如下：

head -n 1000 rb_dbw0_2086848.trc

Trace file /oracle/diag/rdbms/rb/rb/trace/rb_dbw0_2086848.trc

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

Build label: RDBMS_19.11.0.0.0DBRU_LINUX.X64_210412

ORACLE_HOME: /oracle/product/19c

System name: Linux

Node name: dr-rbdb1

Release: 4.18.0-305.el8.x86_64

Version: #1 SMP Thu Apr 29 08:54:30 EDT 2021

Machine: x86_64

Instance name: rb

Redo thread mounted by this instance: 0

Oracle process number: 19

Unix process pid: 2086848, image: oracle@dr-rbdb1 (DBW0)

*** 2022-12-02T02:39:40.177740+01:00

*** CLIENT ID:() 2022-12-02T02:39:40.177758+01:00

*** SERVICE NAME:() 2022-12-02T02:39:40.177761+01:00

*** MODULE NAME:() 2022-12-02T02:39:40.177764+01:00

*** ACTION NAME:() 2022-12-02T02:39:40.177766+01:00

*** CLIENT DRIVER:() 2022-12-02T02:39:40.177769+01:00

WARNING: Failed to disable OOMK

iop-3: nfr=4, buf=0x1a7fae32e8, dba=0x1dc00462, nwr=3457, tim=1669958996, dfr=118,dlt=5, slt0x7ffa11b77ad0

*** 2022-12-02T06:30:01.556395+01:00

*** SESSION ID:(1806.45926) 2022-12-02T06:30:01.556469+01:00

*** SERVICE NAME:(SYS$BACKGROUND) 2022-12-02T06:30:01.556474+01:00

now=1669959001, lct=1669959001

iop-3: nfr=4, buf=0x178ff5d030, dba=0x14445514, nwr=3457, tim=1669958996, dfr=118,dlt=5, slt0x7ffa115f0a20

now=1669959001, lct=1669959001

iop-3: nfr=4, buf=0x17efea6d08, dba=0x1ec03a16, nwr=3457, tim=1669958996, dfr=118,dlt=5, slt0x7ffa118dd650

now=1669959001, lct=1669959001

iop-3: nfr=4, buf=0x136fe66910, dba=0x1d040b4c, nwr=3457, tim=1669958996, dfr=118,dlt=5, slt0x7ffa11b71a40

now=1669959001, lct=1669959001

head -n 1000 rb_dbw5_2086868.trc

Trace file /oracle/diag/rdbms/rb/rb/trace/rb_dbw5_2086868.trc

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

Build label: RDBMS_19.11.0.0.0DBRU_LINUX.X64_210412

ORACLE_HOME: /oracle/product/19c

System name: Linux

Node name: dr-rbdb1

Release: 4.18.0-305.el8.x86_64

Version: #1 SMP Thu Apr 29 08:54:30 EDT 2021

Machine: x86_64

Instance name: rb

Redo thread mounted by this instance: 0

Oracle process number: 24

Unix process pid: 2086868, image: oracle@dr-rbdb1 (DBW5)

*** 2022-12-02T02:39:40.261107+01:00

*** CLIENT ID:() 2022-12-02T02:39:40.261125+01:00

*** SERVICE NAME:() 2022-12-02T02:39:40.261128+01:00

*** MODULE NAME:() 2022-12-02T02:39:40.261131+01:00

*** ACTION NAME:() 2022-12-02T02:39:40.261134+01:00

*** CLIENT DRIVER:() 2022-12-02T02:39:40.261136+01:00

WARNING: Failed to disable OOMK

iop-3: nfr=32, buf=0x1a7fb7fcd8, dba=0x1e000fde, nwr=3505, tim=1669957805, dfr=2,dlt=7, slt0x7f5b91804c20

*** 2022-12-02T06:10:12.933868+01:00

*** SESSION ID:(2281.21820) 2022-12-02T06:10:12.933914+01:00

*** SERVICE NAME:(SYS$BACKGROUND) 2022-12-02T06:10:12.933919+01:00

now=1669957812, lct=1669957812

iop-3: nfr=32, buf=0x9ffb055f0, dba=0x214608ba, nwr=3505, tim=1669957805, dfr=2,dlt=7, slt0x7f5b91858d90

now=1669957812, lct=1669957812

iop-3: nfr=32, buf=0x13efcf1ff8, dba=0x1f816692, nwr=3505, tim=1669957805, dfr=2,dlt=7, slt0x7f5b9139e320

now=1669957812, lct=1669957812

iop-3: nfr=32, buf=0x159fbf1018, dba=0x1d41826c, nwr=3505, tim=1669957805, dfr=2,dlt=7, slt0x7f5b912cf640

now=1669957812, lct=1669957812

iop-3: nfr=32, buf=0x198fe4b520, dba=0x20014146, nwr=3505, tim=1669957805, dfr=2,dlt=7, slt0x7f5b915f8e80

开了SR与oracle确认

问题确认：

经oracle确认，此场景属于19.11版本已知的bug--

Bug 33123985 - DBW0 Process Generate Huge Traces With Dumping DBWR Process State After DBRU 19.11 (Doc ID 33123985.8)

受到影响的版本有19.12.0、19.11.0和19.1.0

解决方法：

方法1：在线修改隐含参数（无需停数据库）

alter system set "_dbwr_stall_write_detection_interval" = 0 scope=both sid='*';

方法2：将PSU升级到19.13.0及以上（耗时长，需要停库）

方法3：下载小patch并应用（耗时短但需要停库）

下载路径：

https://updates.oracle.com/download/33123985.html