ORA-12850 when you run AutoUpgrade?

Nicola made a comment the other day informing me that they hit an issue on some of their databases when upgrading. But is there a general issue with ORA-12850 when you run AutoUpgrade? Or is this error just the reflection of another issue? Let’s see, I will explain this and include a workaround as well.

Photo by Jamie Street on Unsplash

What is the problem?

Nicola traced it down mostly already. During the upgrade of their CDBs, this error happened in some runs:

with segments as (select /*+ MATERIALIZE PARALLEL(2) */ tablespace_name, inuse from( select ds.tablespace_name, row_number() over (partition by tablespace_name order by 1) rn, round(nvl(sum(ds.bytes) over (partition by ds.tablespace_name),0)/1024,2) as inuse from sys.dba_segments ds) where rn=1),tmp_ts_query as ( select null group_name, tablespace_name, bytes, maxbytes from sys.dba_temp_files where tablespace_name = 'TEMP' ),ts_qresult as ( SELECT /*+ MATERIALIZE */ nvl(dtf.group_name, dt.tablespace_name) as name, dt.contents as contents, decode(dt.contents,'TEMPORARY',1,0) as temporary, decode(dt.extent_management,'LOCAL',1,0) as localmanaged, nvl(ds.inuse,0) as inuse, nvl(round(sum(case dt.contents when 'TEMPORARY' then dtf.bytes else ddf.bytes end)/1024, 2),0) as alloc, nvl(round(sum(case dt.contents when 'TEMPORARY' then decode(dtf.maxbytes, 0, 0, dtf.maxbytes-dtf.bytes) else decode(ddf.maxbytes, 0, 0, ddf.maxbytes-ddf.bytes) end)/1024,2),0) as auto, max(nvl(dt.max_extents, 0)) as max_extents FROM sys.dba_tablespaces dt left join segments ds on (dt.tablespace_name = ds.tablespace_name) left join sys.dba_data_files ddf on (ddf.tablespace_name = dt.tablespace_name) left join tmp_ts_query dtf on (dtf.tablespace_name = dt.tablespace_name) WHERE (dt.tablespace_name in ('UNDOTBS1', 'SYSTEM','SYSAUX')) or (dt.tablespace_name in ( SELECT distinct T.tablespace_name FROM sys.dba_queues Q, sys.dba_tables T WHERE Q.queue_table=T.table_name AND Q.owner = T.owner)) or dtf.tablespace_name is not null group by nvl(dtf.group_name, dt.tablespace_name), dt.contents, dt.extent_management, ds.inuse )select tq.name || '#' || tq.contents || '#' || tq.temporary || '#' || tq.localmanaged || '#' || tq.inuse || '#' || tq.alloc || '#' || tq.auto || '#' || (tq.alloc+tq.auto) || '#' || tq.max_extents from ts_qresult tq order by tq.name  ;
 
*
ERROR at line 2:
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 1 allocated

From the SR it is unclear to me whether the upgrade really finished but it looked like as it does – but it takes quite a while.

Since the engineer did not request the customer to upload the logs, we couldn’t have a look at them. But let me all remind you that you can easily fetch all necessary logs together (and you should!) with:

java -jar autoupgrade.jar -config YourConfig.cfg -zip

This is one of the most convenient features in AutoUpgrade.

Anyhow, I forwarded this to the upgrade team, and Carol, the VP, got back to me quickly with a hint.

What is the root cause?

This isn’t an upgrade issue. The ORA-12850 is just the symptom of an issue with parallel query slaves. In this case, to be more specific, does it has to do with NLS settings.

Carol pointed me to:

• Bug# 37137441 – DB UPGRADE FROM 19C TO 23AI USING AUTO-UPGRADE ANALYZE FAILING WITH ORA-12850

which turned out to be a duplicate of:

• Bug 35016572 – NLS LENGTH SEMANTICS MISMATCH (BYTE VS CHAR) IN QC AND ITS SLAVES

The bug explains what happens and has a workaround to unset the NLS settings causing the issue with an ALTER SESSION command. But this won’t be applicable here since AutoUpgrade runs unattended.

How do you fix this?

The fix is available as one-off patch, and it will be included into the 19.27.0 April 2025 Release Update. But as I write this, the April RU is more than one month away. Hence, only the one-off patch would help here.

But luckily, AutoUpgrade is a super-cool tool, and the team has seen this error before. Joe shared the workaround with me. You need to add this parameter to your config file, and it will skip the PARALLEL hint causing the slaves to be allocated and getting in trouble.

prefix.tune_setting=QUERY_HINT_PARALLEL=NO_HINT

That’s it – and then you are good to go.

Other ideas I’ve had were changing NLS_LANG to: export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 or altering the LOCALE in the session you call AutoUpgrade from. Maybe this would work as well – but the above additional parameter is the much saver and easier approach.

Further Links and Information

• AutoUpgrade: tune_setting
• MOS Note: 1345758.1 – OERR: ORA-12850 Could not allocate slaves on all specified instances: %s needed
• Bug 31729262 – AUTOUPGRADE fails in database initialization with ORA-12850: could not allocate slaves on all specified instances

–Mike