Found frequest Oracle Errors in the alert log with “ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated”, Below is the error details
Mon Oct 18 23:16:34 2021
Errors in file /u01/app/oracle/diag/rdbms/odsprd1/odsprd12/trace/odsprd12_m000_28840.trc:
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated
Mon Oct 18 23:16:47 2021
Thread 2 advanced to log sequence 423 (LGWR switch)
Let’s read the error description
[oracle@ORA-X1node1 ~]$ oerr ora 12850
12850, 00000, "Could not allocate slaves on all specified instances: %s needed, %s allocated"
// *Cause: When executing a query on a gv$ fixed view, one or more
// instances failed to allocate a slave to process query.
// *Action:Check trace output for instances on which slaves failed to start.
// GV$ query can only proceed if slaves can be allocated on all
// instances.
[oracle@ORA-X1node1 ~]$This is something to deal with system tables? So quickly reviewed Oracle Support and found the relevant error fix and workaround.
MOS Note: The failing query as per the mmon trace file is different compared to the one that is discussed in the Doc ID 2102131.1.
Solution was to apply patch for Bug 24554937 which specifically deals with the query with SQL_ID dfffkcnqfystw run by mmon background process.
So new internal bug was filed: Bug 26536320 PROBLEM IN GROUP-BY SORT CONVERTED FROM GROUP-BY HASH
Since our Oracle Database version is 12.1 we have workaround to use the undocumented parameter ,whereas in Oracle Database 19c the bug was fixed.
SQL> alter system set "_report_capture_cycle_time"=0 sid='*';
System altered.
SQL>The above setting can be modifiable with Immediate, It helps in disabling the auto report capturing which is introduced in Oracle Database 12c.
& Highly recommend to apply the latest PSU on Oracle RDBMS 12.1.0.2
