Oracle 12cR2 RAC节点查询GV$视图报错ORA-12805

环境介绍：

服务器	vShere 6 虚拟机
操作系统	OEL 7.0 with Kernal 3.8.13-35.3.1.el7uek.x86_64
数据库	Oracle RAC 12.2.0.1
节点数	3

 

现象：

首先是使用oratop命令的时候只有在节点1执行成功，其他节点出现ORA-12805的错误。

并且节点1上的Top Waits Events很不正常:

 

后来发现只有节点1可以查询GV$视图，其余两个节点查询时很慢并报错如下：

SQL> select count(*) from gv$instance;
select count(*) from gv$instance
*
ERROR at line 1:
ORA-12805: parallel query server died unexpectedly

 

解决方案：

MOS上搜了一下，发现有同样的现象描述Doc ID 2014298.1，同时下面的这个文章里有对应的Patch修复：

NOTE:1347455.1 - OERR: ORA 12805 parallel query server died unexpectedly

IIII

27223075

12.1.0.2.190115, 19.1

Wait for 'PX Deq: Join Ack' when no active QC but PPA* slaves show as busy

 跟上面的Wait Event相同。

 

摘抄MOS上的描述：

Description

This bug is only relevant when using Real Application Clusters (RAC) and Parallel Query (PQO)

Hang issue during slave join period and have to kill slave process to end the
hang. 
 
Rediscovery:
 A PPA* slave that is busy is thought to be available by a QC and
 is asked to join, but the slave cannot report back to the QC, so
 it hangs and has to be killed.
 
 You may run the following queries and see no rows returned from 
 the first one, and PPA* slaves returned from the second one:
 
1. No Query slaves running.
 select
   decode(px.qcinst_id,NULL,username,
         ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
   decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
   to_char( px.server_set) "Slave Set",
   to_char(s.sid) "SID",
   decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
   px.req_degree "Requested DOP",
   px.degree "Actual DOP"
 from
   v$px_session px,
   v$session s
 where
   px.sid=s.sid (+)
  and
   px.serial#=s.serial#
  order by 5 , 1 desc
 /
 
 no rows selected
 ...
 2. But PPA4/PPA5/PPA6/PPA7 are busy.
 SQL> select SLAVE_NAME from V$PQ_SLAVE where STATUS='BUSY';
 
 SLAV
 ----
 PPA4
 PPA5
 PPA6
 PPA7
 
Additional Info:
Any parallel slave process (p00*) can be affected by this bug and is not constrained to PPA* slaves.
 
Workaround
 None.