PROBLEM:

We have received below error in the alert log.

2017-12-07 08:01:24.890000 +03:00
Errors in file /oradbtrace/diag/rdbms/prodid2/trace/prodid2_m001_3322.trc:
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated

ANALYSIS AND SOLUTION:

Lets check the trace file /oradbtrace/diag/rdbms/prodid2/trace/prodid2_m001_3322.trc.

Trace file /oradbtrace/diag/rdbms/prodid2/trace/prodid2_m001_101138.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options ORACLE_HOME = /oracle/app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      prodid2
Release:        2.6.32-573.el6.x86_64
Version:        #1 SMP Wed Jul 1 18:23:37 EDT 2015
Machine:        x86_64
Instance name: eaidb2
Redo thread mounted by this instance: 2
Oracle process number: 1225
Unix process pid: 101138, image: oracle@prodid2(M001)


*** 2017-12-07 06:43:03.358
*** SESSION ID:(4026.606) 2017-12-07 06:43:03.358
*** CLIENT ID:() 2017-12-07 06:43:03.358
*** SERVICE NAME:(SYS$BACKGROUND) 2017-12-07 06:43:03.358
*** MODULE NAME:(MMON_SLAVE) 2017-12-07 06:43:03.358
*** CLIENT DRIVER:() 2017-12-07 06:43:03.358
*** ACTION NAME:(Automatic Report Flush) 2017-12-07 06:43:03.358 ----------->>>>>>>>>>>>   THIS ONE 

ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated Dump of memory from 0x000000177FA765E8 to 0x000000177FA8C32C
177FA765E0                   48544957 4E4F4D20          [WITH MON]
177FA765F0 524F5449 5441445F 53412041 45532820  [ITOR_DATA AS (SE]
177FA76600 5443454C 534E4920 44495F54 454B202C  [LECT INST_ID, KE]
177FA76610 4E202C59 28324C56 515F5850 44495343  [Y, NVL2(PX_QCSID]
177FA76620 554E202C 202C4C4C 54415453 20295355  [, NULL, STATUS) ]
177FA76630 54415453 202C5355 53524946 45525F54  [STATUS, FIRST_RE]
177FA76640 53455246 49545F48 202C454D 5453414C  [FRESH_TIME, LAST]
177FA76650 4645525F 48534552 4D49545F 52202C45  [_REFRESH_TIME, R]
177FA76660 45524645 435F4853 544E554F 5250202C  [EFRESH_COUNT, PR]
177FA76670 5345434F 414E5F53 202C454D 2C444953  [OCESS_NAME, SID,]
177FA76680 4C515320 2C44495F 4C515320 4558455F  [ SQL_ID, SQL_EXE]
177FA76690 54535F43 2C545241 4C515320 4558455F  [C_START, SQL_EXE]

In the trace the ACTION NAME:(Automatic Report Flush)

Automatic report capturing is a new feature in oracle 12c, As part of this feature, some monitoring SQLs are executed by MMON_SLAVE to identify the resource-intensive SQLs queries. And this monitoring SQLs while executing sometimes fail and this type of error will be logged in the alert log.

Oracle already acknowledged this bug.

However, to fix this the workaround is to set parameter And this doesn’t need database bounce.

SQL> alter system set "_report_capture_cycle_time"=0 scope=both sid='*'   /* Default is 60 

After setting this parameter, this type of error will be suppressed.