某大型物流公司ADG Ora-30927报错处理

OCM之家 2021-08-19

630

江洋，中国OCM之家核心成员，某大型物流公司高级DBA，技术博客http://blog.csdn.net/dbljy2015

作者介绍

一、环境

数据库环境为DG一主三备（最大可用模式），操作系统均为CentOS release 6.5版本，数据库版本11.2.0.3.15

二、描述

2016年12月26日下午，业务人员突然说系统不能用，一些网页频繁报错，没办法将数据查出来，最后结合开发人将对应的sql提取出来进行分析，发现所有的sql均带有with语法，且都是对standby databae进行的查询。通过metalink查找发现，这与一个bug有关。在ADG环境中使用with语句且系统自动生成临时表时会出现。

ORA-30927: Unable to complete execution due to failure intemporary table transformation报错。（在standby database中会出现）

METALINK上的描述是：

On ADG,queries that use a cursor-duration temporary table may fail with ORA-30927errors.

Suchqueries use Star with Temp Transformation and subquery factoring (WITH clause).

（Bug 14143632-ora-30927 on active data guard（文档ID 14143632.8））

此时线上的数据库PSU已打到了11.2.0.3.15但是没有包含这个bug的补丁。补丁号（14143632）

例如：执行如下代码，with内的表被使用了两次，oracle会自动生成一个临时表来存放with内的表。

With a as (

Select object _id aa,object_name na fromdba_objects

)

Select * from a where aa in (select a.aa from a);

而对于with内的表只使用一次的是不会生成临时表：

with a as (

select object_id aa,object_name na from dba_objects

)

select * from a;

三、解决

在METALINK下载相应的补丁，上传至standby database服务器

通过opatch查看为一个online的补丁。

且需要19769496这个补丁，在查看以前安装过的补丁发现已经有这个补丁。现在就可以直接在线打补丁。

1、检查可以在线处理

[oracle@oracle-test 14143632]$ opatch query-all online

Oracle 中间补丁程序安装程序版本 11.2.0.3.6

Oracle Home : home/app/oracle/product/11.2.0/dbhome_1

Central Inventory :/home/app/oraInventory

from :/home/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version : 11.2.0.3.6

OUI version : 11.2.0.3.0

Log file location :/home/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2016-12-27_15-36-57下午_1.log

Patch created on 5 Jun 2015, 23:20:09 hrsPST8PDT

Need toshutdown Oracle instances: false

Patch is roll-backable: true

Patch is a "Patchset Update": false

Patch is a rolling patch: true

Patch has sql related actions: false

Patchis an online patch: true

Patch is a portal patch: false

Patch is an "auto-enabled" patch:false

Patch is translatable: false

List of platforms supported:

226: Linux x86-64

List of prereq patches:

19769496

List of overlay patches:

19769496

List of bugs to be fixed:

14143632: QUERIESMAY FAIL WITH ORA-30927 ERRORS ON AN ADG STANDBY DATABASE

This patch is a "singleton" patch.

This patch belongs to the "db"product family

This patch supports the patching model as"one-off"

This patch supports the language"en"

List of executables affected:

ORACLE_HOME/bin/oracle

ORACLE_HOME/bin/renamedg

ORACLE_HOME/lib/libclntsh.so.11.1

List of optional components:

oracle.rdbms: 11.2.0.3.0

List of optional actions:

Patch the Database instances with OnlinePatch hpatch/bug14143632.pch

Possible XML representation of the patch:

<REF_LIST>

</REF_LIST>

<BUG_LIST>

</BUG_LIST>

<FILE_LIST/>

</ONEOFF>

OPatch succeeded.

2、在线打补丁

检查现有补丁

[oracle@newfhldb1 OPatch]$ opatchlsinventory

Oracle 中间补丁程序安装程序版本11.2.0.3.6

Oracle Home : usr/app/oracle/110203/v01

Central Inventory : usr/app/oracle/oraInventory

from :/usr/app/oracle/110203/v01/oraInst.loc

OPatch version : 11.2.0.3.6

OUI version : 11.2.0.3.0

Log file location :/usr/app/oracle/110203/v01/cfgtoollogs/opatch/opatch2016-12-27_13-42-01下午_1.log

Lsinventory Output file location :/usr/app/oracle/110203/v01/cfgtoollogs/opatch/lsinv/lsinventory2016-12-27_13-42-01下午.txt

已安装的顶级产品 (1):

Oracle Database 11g 11.2.0.3.0

此 Oracle 主目录中已安装 1 个产品。

中间补丁程序 (2) :

Patch 21280463 : applied on Sat Mar26 01:09:34 CST 2016

Unique Patch ID: 19052992

Created on16 Jul 2015, 05:28:04 hrs PST8PDT

Bugsfixed:

13911083, 14313519, 19791273, 13632140

This patchoverlays patches:

20760997

This patchneeds patches:

20760997

asprerequisites

Patch 20760997 : applied on Sat Mar26 01:07:34 CST 2016

Unique Patch ID: 18966522

Patch description: "Database Patch Set Update : 11.2.0.3.15 (20760997)"

Created on8 Jun 2015, 23:57:50 hrs PST8PDT

Sub-patch 20299017; "Database Patch Set Update : 11.2.0.3.14 (20299017)"

Sub-patch 19769496; "Database Patch Set Update : 11.2.0.3.13 (19769496)"

Sub-patch 19121548; "Database Patch Set Update : 11.2.0.3.12 (19121548)"

Sub-patch 18522512; "Database Patch Set Update : 11.2.0.3.11 (18522512)"

Sub-patch 18031683; "Database Patch Set Update : 11.2.0.3.10 (18031683)"

Sub-patch 17540582; "Database Patch Set Update : 11.2.0.3.9 (17540582)"

Sub-patch 16902043; "Database Patch Set Update : 11.2.0.3.8 (16902043)"

Sub-patch 16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)"

Sub-patch 16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"

Sub-patch 14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"

Sub-patch 14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"

Sub-patch 13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"

Sub-patch 13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"

Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"

Bugsfixed:

13593999, 10350832, 19433746, 14138130, 12919564, 14198511, 13561951

13588248, 13080778, 20134036, 13804294, 16710324, 18031683, 12873183

21031410, 16992075, 14193240, 14472647, 12880299, 13369579, 14799269

13840704, 14409183, 13492735, 14263036, 12857027, 13496884, 14263073

16038929,13834436, 13015379, 17748833, 13732226, 16563678, 13866822

20134034, 13742434, 13944971, 12950644, 17748831, 12899768, 16929165

16272008, 13063120, 14613900, 13958038, 21031412, 13503204, 20334344

13972394, 11877623, 17088068, 13072654, 12395918, 16710753, 13429702

13814739, 17343514, 13649031, 13981051, 10256843, 15981698, 13901201

12797765, 17333200, 19211724, 12923168, 16761566, 13384182, 16279401

13466801, 15996344, 14207163, 21146680, 13596581, 18673304, 13724193

11063191, 13642044, 12940637, 19915271,12595606, 18641419, 14052871

15931756, 9163477, 18262334, 13945708, 16872333, 12797420, 14123213

13041324, 12865902, 15869211, 14003090, 16314468, 16019955, 11708510

17865671, 13026410, 14637368, 13737746, 13742438, 15841373, 16347904

16088176, 15910002, 19517437, 19827973, 16362358, 16505333, 14398795

14182835, 13579992, 11883252, 16344871, 10182005, 10400244, 13742436

14275605, 19197175, 9858539, 20477071, 14841812, 16338983, 9703627

20777150, 13483354, 14393728, 14207317, 17165204, 12764337, 20477069

16902043, 14459552, 14191508, 14588746, 12964067, 19358317, 20477440

12780983, 12583611, 14383007, 14546575, 13476583, 15862016, 13489024

12985237, 17748830, 19554106, 14088346, 13448206, 19458377, 16314466

13419660, 18139695, 12591399, 14110275, 13430938, 13467683, 17767676

14548763, 19638161, 13424216, 12834027, 13632809, 13853126, 13377816

13036331, 14727310, 9812682, 12320556, 16747736, 13584130, 16175381

17468141, 12829021, 14138823, 15862019, 12794305, 14546673, 12791981

13503598, 13787482, 10133521, 12744759, 13399435, 19433747, 18641461

14023636, 13553883, 14762511, 9095696, 14343501, 12977562, 13860201

13257247, 14176879, 13783957, 16014985, 14480675, 12312133, 13559697

13146182, 16306019, 12974860, 9706792, 12940620, 20004087, 13098318

13773133, 15883525, 16794244, 13340388, 13528551, 13366202, 12894807

20004021, 13259364, 12747437, 13454210, 12748240, 13385346, 15987992

13923995, 16101465, 14571027, 13582702, 12784406, 13907462, 19769496

13493847, 13035804, 13857111, 13544396, 16710363, 10110625, 20134033

14128555, 12813641, 8547978, 14226599, 17478415, 17050888, 16923127

17333197, 9397635, 14007968, 21031413, 13912931, 12693626, 12925089

14189694, 17761775, 12815057, 16721594, 13332439, 20477068, 19972198

14038787, 11071989, 14207902, 12596444, 14062796, 21151526, 12913474

20299010, 14390252, 13840711, 13370330, 16314470, 14062794, 13358781

12960925, 17333202, 9659614, 13699124, 14546638, 13936424, 9797851

19433745, 16794240, 14301592, 13338048, 12938841, 12620823, 12656535

21031411, 12678920, 13719292, 14488943, 14062792, 16850197, 14791477

13807411, 16794238, 13250244, 12594032, 15862022, 14098509, 15826962

12612118, 9761357, 18096714, 19854461, 14053457, 18436647, 13918644

13527323, 18173595, 12797620, 10625145, 19289642, 15862020, 13910420

12780098, 13696216, 14774091, 10263668, 14841558, 13849733, 16794242

16944698, 15862023, 16056266, 13834065, 20134035, 13853654, 14351566

13723052, 18173593, 14063280, 13011409, 13566938, 13737888, 13624984

16024441, 17333199, 13914613, 17540582, 14258925, 14222403, 14755945

13645875, 12571991, 13839641, 14664355, 12998795, 14469008, 13719081

13361350, 20657441, 14188650, 17019974, 13742433, 14508968, 16314469

16368108, 12905058, 6690853, 13647945, 16212405, 12849688, 18641451

13742435, 13464002, 18681866, 12879027, 13534412, 18522512, 12585543

12747740, 12535346, 13878246, 13790109, 16382448, 12588744, 13916549

13786142, 12847466, 13855490, 13551402, 12582664, 19972199, 13871316

14262913, 14657740, 17332800, 14558880, 14695377, 13612575, 12912137

19699191, 13484963, 12387467, 14163397, 17437634, 13772618, 19006849

16694777, 13070939, 15994107, 12391034, 14369664, 13605839, 12588237

16279211, 16314467, 12945879, 15901852, 12976376, 17762296, 14692762

7276499,12755231, 13680405, 13742437, 14589750, 14318397, 11868640

14644185, 13326736, 19309466, 13596521, 20558005, 13001379, 12898558

13099577, 17752121, 13911711, 9873405, 18673325, 16372203, 16344758

11715084, 9547706, 16231699, 14040433, 12662040, 12617123, 14406648

17748832, 16530565, 12845115, 16844086, 13354082, 17748834, 13794550

13397104, 19537916, 13913630, 16524926, 16462834, 12983611, 13550185

13810393, 14121009, 13065099, 11840910, 13903046, 15862017, 13572659

16294378, 13718279, 13657605, 17716305, 14480676, 13632717, 14668670

14063281, 14158012, 13736413, 13420224, 13812031, 12646784, 16299830

18440047, 14512189, 10359307, 12755116, 14035825, 17230530, 13616375

13366199, 13427062, 18673342, 12861463, 15862021, 13092220, 17721717

13043012, 16619892, 13685544, 18325460, 13499128, 15862018, 13839336

19727057, 13866372, 13561750, 12718090, 13848402, 13725395, 12401111

5144934,12796518, 13362079, 12917230, 12614359, 14408859, 13042639

13923374, 11732473, 14220725, 12621588, 13524899, 14480674, 14751895

13916709, 14781609, 14076523, 15905421, 12731940, 13343438, 14205448

17748835, 15853081, 17082364, 14127231, 14273397, 16844448, 14467061

20331945, 12971775, 16864562, 20074391, 14489591, 14497307, 13872868

12748538, 10242202, 20803576, 14230270, 13931044, 13686047, 16382353

14095982, 17333203, 19121548, 13591624, 14523004, 13440516, 16794241

13499412, 13035360, 14062795, 12411746, 13040943, 12905053, 13843646

18173592, 20296213, 16794243, 13477790, 14841409, 14609690, 14062797

13059165, 12959852, 12345082, 16703112, 13890080, 17333198, 16048375

16450169, 12658411, 13780035, 14062793, 19271438, 19259446, 13038684

18740215, 16742095, 13742464, 14052474, 13066936, 13060271, 13911821

13457582, 7509451, 19710542, 13791364, 12821418, 13502183, 13705338

15856660, 14237793, 16794239, 21031414, 13554409, 15862024, 13103913

13645917, 12772404

OPatch succeeded.

打14143632补丁：

[oracle@newfhldb1 ~]$ cd 14143632/

[oracle@newfhldb1 14143632]$ ls

etc files online README.txt

**此处sid username password 均对应当前数据库的sid,username以及password最好使有dba权限的用户**

[oracle@newfhldb1 14143632]$ opatchapply online -connectString <sid>:<username>:<password>

Oracle 中间补丁程序安装程序版本11.2.0.3.6

Oracle Home : usr/app/oracle/110203/v01

Central Inventory : usr/app/oracle/oraInventory

from :/usr/app/oracle/110203/v01/oraInst.loc

OPatch version : 11.2.0.3.6

OUI version : 11.2.0.3.0

Log file location :/usr/app/oracle/110203/v01/cfgtoollogs/opatch/

14143632_Dec_27_2016_12_57_10/apply2016-12-27_12-57-10下午_1.log

补丁程序只应在 '-all_nodes' 模式下应用/回退。

将 RAC 模式转换为 '-all_nodes' 模式。

Applying interim patch '14143632' to OH'/usr/app/oracle/110203/v01'

Verifying environment and performing prerequisitechecks...

All checks passed.

提供电子邮件地址以用于接收有关安全问题的通知, 安装Oracle Configuration Manager 并启动它。如果您使用 My Oracle

Support 电子邮件地址/用户名, 操作将更简单。

有关详细信息, 请访问http://www.oracle.com/support/policies.html。

电子邮件地址/用户名:

尚未提供电子邮件地址以接收有关安全问题的通知。

是否不希望收到有关安全问题 (是 [Y], 否 [N]) [N] 的通知: y

Backing up files...

正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...

正在数据库 'fhlsys' 上安装和启用联机补丁程序'bug14143632.pch'。

Verifying the update...

Patch 14143632 successfully applied

Log file location:/usr/app/oracle/110203/v01/cfgtoollogs/opatch/

14143632_Dec_27_2016_12_57_10/apply2016-12-27_12-57-10下午_1.log

OPatch succeeded.

一共三台standby数据库，依次安装。

补丁安装成功，在执行相应带有with且生成临时表的sql语句，执行成功。

近期精彩推荐

收集热点表的统计信息

一次ORA-04030故障处理过程

记一次因存储缓存意外丢失引发的故障处理过程

Oracle快速云服务（Oracle DatabaseExadata Express Cloud Service）初体验

中国OCM之家

专注数据共现梦想

QQ群：554334183

oracle

文章转载自OCM之家，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

某大型物流公司ADG Ora-30927报错处理

评论