ORA-12850: Could not allocate slaves on all specified instances

最新推荐文章于 2022-01-18 21:10:26 发布

好记忆不如烂笔头abc

最新推荐文章于 2022-01-18 21:10:26 发布

阅读量2k

点赞数

分类专栏： oracle19c

本文链接：https://blog.csdn.net/jycjyc/article/details/117550783

版权

oracle19c 专栏收录该内容

172 篇文章

订阅专栏

当Oracle 19.10 RAC数据库遇到查询错误ORA-12850时，可能由于执行计划差异或资源分配问题导致。重启集群数据库可以暂时解决问题。诊断方法包括检查与gv$视图相关的跟踪文件，启用特定的会话跟踪，并通过DBMS_SHARED_POOL清除库缓存。已知问题可在文档1345758.1中查找。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

oracle 19.10 rac本来好好的，突然查询报错。网上类似报错各种修改参数，别这样乱动。

重启下集群数据库就好了。

srvctl stop database -d xxx

srvctl start database -d xxx

更多参考：

How To Diagnose ORA-12850 Could Not Allocate Slaves On All Specified Instances (Doc ID 1350377.1)

Concepts/Definitions

The error ORA-12850 is raised by the Query Coordinator for a parallel query against a gv$ view. For a query against a gv$ view results in slaves spawned on every node.

See Note:734139.1 What are gv$ views?

In case that the Query Coordinator can not allocate slaves on every node a ORA-12850 is thrown. In some cases the root cause is a execution plan difference between the slaves and the Query Coordinator.

How to diagnose

You see the ORA-12850 only on RAC systems. In most cases a ORA-12850 there are traces files written to the trace folders. Queries against gv$ views are using slaves allocated from the high end, that means they start with slaves PZ99 and go down ( PZ98,PZ97,.. ) . So look for traces with the pattern ..pz99..trc created/updated at the time the ORA-12850 occur.

In case the ORA-12850 is reproducible in SQL Plus it can help for Oracle Support if you reproduce the problem with the following tracing on, and collect the generated trace files:

alter session set tracefile_identifier='PX_traces_12850';
alter session set "_px_compilation_debug" = 1;
alter session set events '12850 trace name errorstack level 1';
alter session set events '10053 trace name context forever, level 1';
alter session set "_px_trace" = all;
<run the failing query>
alter session set "_px_compilation_debug" = 0;
alter session set events '10053 trace name context off';
alter session set events '12850 trace name errorstack off';
alter session set "_px_trace" = none

In case you know the SQL that caused the ORA-12850, experienced DBAs/developers can purge the SQL from the library cache on all nodes and try to execute the SQL again.
For more information about purge please readNote:457309.1 How To Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOL Package