ORA-3170|Oracle 19c ADG Deadlocked 死锁问题
前 言
《周处除三害》出自《晋书·周处传》和《世说新语》，明朝人黄伯羽改编为《蛟虎记》传奇，广为流传，京剧中仍保留有《除三害》剧目。
公元三世纪中叶，义兴阳羡（今宜兴市）传颂着周处除三害的故事。周处（242-297），字子隐，义兴阳羡人。其祖父周宾为三国东吴咨议参军，后转广平太守。父周鲂为东吴名将，任鄱阳太守，赐爵关内侯。
————————————————————————————
微信公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
周处父亲死亡，年少时身材魁梧，体力过人，武艺高强。好驰骋田猎，不修细行，纵情肆欲，横行乡里。民谣说：“小周处，体力强，日弄刀弓夜弄枪。拳打李，脚踢张，好像猛虎扑群羊。吓得乡民齐叫苦，无人敢与论短长。”这位“少孤，不修细行，州里患之”的七尺少年，被乡民与南山猛虎、西氿蛟龙合称为阳羡城“三害”。后来，这个说法传到了周处那里，他自知为人所厌，突然悔悟，只身入山射虎，下水搏蛟，经三日三夜，在水中追逐数十里，终于斩杀猛虎、孽蛟。他自己也改邪归正，认认真真拜师学文练武，这一来城内“三害”皆除。
周处除“三害”后，发愤图强，拜文学家陆机、陆云为师，终于才兼文武，得到朝廷的重用，历任东吴东观左丞、晋新平太守、广汉太守，迁御史中丞。他为官清正，不畏权贵，因而受到权臣的排挤。西晋元康六年（296），授建威将军，奉命率兵西征羌人，次年春于六陌（今陕西乾县）战死沙场。死后追赠平西将军，赐封孝侯。
最近几天比较火爆的电影《周处除三害》，上周五也去电影院看了看，这是一部中国台湾省在大陆播放的电影，影片大概讲述了主人公陈桂林（阮经天 饰）因追债杀掉帮派老大在逃亡藏匿过程中失去了陪伴他长大的奶奶，这也是他最后一个亲人，同时因买药的医师善意的谎言得知自己只有不到三个月的生命。万念俱灰的他原打算投案自首，碰巧赶上当时运钞车因交通事故翻车，散落满地的纸钞被旁边村子里的村民们捡到，警方呼吁捡到钱的村民来公安局自首交还捡到的纸钞，主人公陈桂林刚好在这个时候来自首，但是他发现警员居然不认识他这个通缉犯，相反还让他排好队交还捡到的纸币，可是当发现他在三大通缉犯中仅仅排名第三时，内心突然躁动起来。在此之后，他决定仿效古时候周处除三害的故事，临终之际要在江湖上留下他的传奇名号，让世人都知道他叫陈桂林。通过买药的医师张贵卿（谢琼煖 饰），陈桂林找到了分别排名第二的香港仔（袁富华 饰）和排名第一的牛头/林禄和的线索，随后踏上征途,拿着从警方手里夺过来的手枪干掉了香港仔和他的三位手下，并顺利救出了被霸凌的程小美。电影高潮在陈桂林除掉通缉榜排名第一的林禄和，此害以心灵修习导师身份隐藏甚深，表面教人净化心灵治愈疾病，实则是犯下诸多罪行的邪教头子。贪嗔痴，三毒熬煎，红尘混沌之人，被业障缠绕，永远无法跳脱因果轮回……
《周处除三害》的英文翻译是 The Pig, the Snake, and the Pigeon，正好对应了佛教轮回中的“三毒”，贪嗔痴，猪代表痴念，意为不明事理，不分善恶是非，起诸邪行，这也正是陈桂林恶的映射，也以奶奶的小猪手表作为象征；蛇代表嗔念，对违逆的处境生出嗔恨，暴怒意气，正好对应了香港仔的残暴欺凌，在他的手上也留有蛇状的刺青；鸽子代表贪念，非得到否则兴起不甘，代表就是林禄和，表象温驯，实则贪婪，背上亦有鸽翅的纹身。在藏传佛教壁画中，蛇与鸽都是由猪的嘴中吐出生成，意为万生万物皆有痴的执念而导致分化，陈桂林以周处作比，射鸽杀蛇，既是以痴为恶始，也以放下执念为终，三毒是产生诸恶业的根本，当他死去的那一刻，众生轮回之苦，也随之消散。
说完《周处除三害》才进入到今天的正题,当作为 DBA 的我们遇到数据库的害虫(Bug)该怎么破？当我们使用的是开源数据库时遇到了 Bug，可以提交 Bug 及规避方法给开源社区，等待下一个版本迭代，然后拿到迭代版本来升级，例如 MySQL 8.0.29 出现的严重 Bug，官方紧急下掉了此版本的下载链接，在 8.0.30 版本中修复了。如果我们使用的是像Oracle 一样的商用数据库，当我们遇到 Bug 时，通常需要先判断是否可以自己规避掉这个 Bug，如果不能规避，查找官方 MOS 是否有相同的 Bug 及解决方案，如果有解决方案，那么通常是一个 oneoff 的小补丁，或者通过修改参数解决，或者 oneoff 补丁加修改参数解决。如果你遇到的可能是 Bug 首次发现官方还没有发现，你同样也可以通过 SR 的方式提交 Bug，目前为止我还没有遇到过首次发现 Bug 的情况，这里就先不讨论了。接下来看看我们 Oracle ADG 备库上出现的 Bug 31632548 及解决办法。
Oracle 19.15
事情是这样的，在一套 Oracle 19c RAC + ADG 架构的数据库中，数据分析师经常使用 ADG 去做一些报表汇总等相关的查询，只要能通过 SQL 实现业务逻辑，则完全不会理会是否有性能问题。于是在使用 ADG 备库的情况下，通过 Tableau 工具做报表时出现了如下图的错误：ORA-03170:可读物理备用上已出现死锁（还原段 584）
然后询问数据分析师说是在原来的 SQL 代码段中新增了一段查询 SQL，运行时出现了上面的错误，等待了一段时间后继续运行还是报错一样，他在原 SQL 的基础上添加了如下的一段 SQL 才导致的这个问题。
jieke as (select
DOCDATUMID,
CREATETIME
from(
select
DOCDATUMID,
CREATETIME
,row_number()over(partition by DOCDATUMID order by CREATETIME desc ) 排序
from mlog
where OPTTYPE in ('URGENT') and 标签内容 like '%MARKET_URGENT%' and 标签内容 like '%ViewLabelsAdd%'
)where 排序=1)
插播解释何为 Tableau，致力于帮助人们查看并理解数据。Tableau 帮助任何人快速分析、可视化并分享信息。超过42000家客户通过使用 Tableau 在办公室或随时随地快速获得结果。数以万计的用户使用 Tableau Public 在博客与网站中分享数据。
经过查阅 MOS 官方文档才发现在 12.1.0.2 到 19.19（19.3和 19.4 除外） 版本中均存在此Bug 31632548，Bug 31632548 - ADG: ORA-3170: deadlocked on Readable Physical Standby (Doc ID 31632548.8)
Description
Release Notes:
ORA-03170 error which is deadlock on readable standby in ADG setup. But works fine on Primary.
Backport feasible?
Yes, Only to 19c versions
Forward merge required? ('Yes' or 'No', and reason why not):
No.
Rediscovery Information:
If ORA-3170 returned in Standby but in primary it works fine.
Patch 31632548 可以单独下载这个 oneoff 小补丁，也可以下载 DBRU19.20 以上的季度补丁，如下所示的 RU21 补丁包里已经集成了 31632548 补丁，通过应用补丁便可以彻底解决这个问题，那么我们就只能申请停机窗口去应用 RU 补丁了。
Patch 31632548: EXADATA: PARALLEL QUERY FAILS ON ADG W/ORA-03170: DEADLOCKED ON READABLE PHYSICAL STANDBY (UNDO SEGMENT 5500) BUT WORKS ON PRIMARY
[oracle@jieke-19r1 ~]$ opatch lspatches
35648110;OJVM RELEASE UPDATE: 19.21.0.0.231017 (35648110)
35655527;OCW RELEASE UPDATE 19.21.0.0.0 (35655527)
35643107;Database Release Update : 19.21.0.0.231017 (35643107)
OPatch succeeded.
[oracle@jieke-19r1 ~]$ opatch lsinv | grep 31632548
31630551, 31630794, 31630946, 31631985, 31632273, 31632534, 31632548
31632548 补丁 readme 要求停机操作
Ensure that you shut down all the services running from the Oracle home.
Note:
- For a Non-RAC environment, shut down all the services running from the Oracle home.
- For a RAC environment, shut down all the services (database, ASM, listeners, nodeapps, and CRS daemons) running from the Oracle home of the node you want to patch. After you patch this node, start the services on this node.Repeat this process for each of the other nodes of the Oracle RAC system. OPatch is used on only one node at a time.
- Please use -local option to apply the patch to the particular node. e.g., opatch apply -local
临时解决办法
当然也可以根据我们分析师总结到的经验去调整 SQL 写法，把表自关联写成两个表试试，去做 SQL 等价改写。他们的介绍是这个死锁问题只出现在 Tableau 程序中，使用 DBeaver 等其他客户端运行是没有问题的，问题也比较怪异，这里就先记录下来。下面是我们数据分析师遇到的自述及解决办法，仅供参考。
在 DBEAVER 上可以跑，但是 TABLEAU 上出现物理死锁，是因为有表自关联触发的.
之前遇到过物理死锁的情况，供参考：
1、使用表自关联；
2、union all 字段没有明确定义列；
3、表定义重复（定义了两个表都是A）
不要做表自关联，写成两个表
比如：
with a (select * from bal)
,a1(select * from bal)
select * from a
left join a1 on a.id=a1.id
那么这个事就算告一段落了，下面是之前一位朋友遇到的在 11.2.0.4 的 ADG 上也遇到了同样的问题，只不过他这个错误在后台 alert 日志中出现，也不确定是否前端页面或者应用程序中有报错，我这个相当于前端客户端的报错，数据库 alert 日志中却没有报错。
11.2.0.4
11.2.0.4.0 ADG，报错如下：
《Bug 18058112 - ORA-3170 deadlocked on readable physical standby (undo segment 65535) and:or ORA-600 [ktcdso-1] on ADG (Doc ID 18058112.8)》这个 bug 在 11.2.0.3 和 11.2.0.4 及 12.1.0.1 版本中出现，在 12.1.0.2 和 12.2.0.1 中得到修复。
Description
ORA-3170 on readable Physical Standby Database with Undo Segment 65535;
Undo Segment Number (usn) is maxvalue of 0xffff.
This can be also manifested with ORA-600 [ktcdso-1] showing the xid with the maxvalue usn.
Example from the trace content:
env [...]: (scn: ... xid: 0xffff.000.c162a118 ....
Bug 18058112 : ORA-3170: DEADLOCKED ON READABLE PHYSICAL STANDBY ON ADG
当然解决办法要么去升级要么打 oneoff 小补丁，这里他选择了忽略这个错误，首先补丁无法下载，其次没有业务驱动，也没有业务影响，那么是可以忽略的，这里也是简单记录一下，为后来人踩坑。
参考文章
Bug 31632548 - ADG: ORA-3170: deadlocked on Readable Physical Standby (Doc ID 31632548.8)
Bug 18058112 - ORA-3170 deadlocked on readable physical standby (undo segment 65535) and:or ORA-600 [ktcdso-1] on ADG (Doc ID 18058112.8)
全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
欢迎关注我公众号【JiekeXu DBA之路】，第一时间一起学习新知识！以下四个地址可以找到我，其他地址均属于盗版侵权爬取我的文章，而且代码格式、图片等均有错乱，不方便阅读，欢迎来我公众号或者墨天轮地址关注我，第一时间收获最新消息。
欢迎关注我的公众号【JiekeXu DBA之路】，第一时间一起学习新知识！————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
