ORACLE 12c 12.1.0.2.0 ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated

目录 ORACLE12C

DB version:oracle 12c 12.1.0.2.0

Errors in file /u01/app/oracle/diag/rdbms/tos/LOCDB1/trace/LOCDB1_m001_28575.trc:
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated
Tue Jan 16 06:12:07 2018

High CPU Usage and/or Frequent Occurrences of ORA-12850 For Monitor Queries by MMON From 12.1 (Doc ID 2102131.1)
In this Document
Symptoms Changes Cause Solution References
APPLIES TO:
Oracle Database – Enterprise Edition – Version 12.1.0.1 and later Information in this document applies to any platform.
SYMPTOMS
MMON consumes more CPU due to the monitoring activity in 12.1
High CPU consumption and time spent while executing monitor-related queries from both MMON_SLAVE like following:

The frequent occurrences of ORA-12850 may be present in the Alert.log in both a RAC and NON-RAC environment. The failing queries are consistently executed against GV$SQL_MONITOR:
CHANGES
Upgrading the database to 12.1.
CAUSE

WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS)STATUS, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT, PROCESS_NAME, SID, SQL_ID,
SQL_EXEC_START, SQL_EXEC_ID, DBOP_NAME, DBOP_EXEC_ID, SQL_PLAN_HASH_VALUE, SESSION_SERIAL#, SQL_TEXT, IS_FULL_SQLTEXT, PX_SERVER#, PX_SERVER_GROUP, PX_SERVER_SET,
PX_QCINST_ID, PX_QCSID, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CL ...; 

Thu Sep 08 04:00:41 2016 Errors in file /app/oracle/diag/rdbms/dbname/dbinstance/trace/dbinstance_m002_14490.trc: ORA-12850: Could not allocate slaves on all specified instances: 3 needed, 2 allocated There is a new feature in 12C called “Automatic Report Capturing Feature”. As part of this feature, some monitoring SQLs are executed by MMON_SLAVE to identify the resource-intensive SQLs and generate the SQL Monitoring report automatically for those SQLs. Those SQLs consume little more CPU and it is expected behavior being a new feature. Such monitoring queries can be identified from (G)V$SQLSTATS. However, If the CPU consumption is significantly high then it is not an expected behavior and could be due to optimizer choosing suboptimal plan for the SQL statements. This can happen due to Adaptive Optimization, a new feature in 12c.

SOLUTION
1. The new feature can be disabled to reduce the CPU consumption: Or 2. Kill MMON SLAVE from os. The sid and serial number can be obtained from ASH report. Document 2031605.1 Adaptive Query Optimization

SQL> alter system set "_report_capture_cycle_time"=0; /* Default is 60 seconds */

NOTE: _report_capture_cycle_time=0 /* This is system modifiable with immediate */

TIP: There are some known bugs for the problem which are fixed in latest PSU of 12.1.0.2. Hence, it is strongly recommended to apply the latest PSU for 12.1.0.2.
Document 1924126.1 12.1.0.2 Patch Set Updates – List of Fixes in each PSU
REFERENCES

 

下载pdf:Document 2102131.1