We are running a great training this week in Munich, called TECH CHALLENGE for Oracle people only. And one group hit the below issue so I thought I should share this as it is very likely it will happen with the upgrade to Oracle Database 12c in your environment as well.
During the run of catctl.pl or DBUA you may get an error saying:
ORA-20000: Unable to gather statistics concurrently:
Resource Manager is not enabled.
ORA-06512: at “SYS.DBMS_STATS”
Even worse, the upgrade will fail and exit in phase 68 of the parallel upgrade.
This issue is new with Oracle Database 12.1.0.2 and is logged as bug19664340
Reason why this happens:
It seems to be “somebody” has switched statistics gathering with Oracle Database 12.1.0.2 to CONCURRENT.
Workaround:
- SQL> exec dbms_stats.set_global_prefs(‘CONCURRENT’, ‘FALSE’);
- Reset it after upgrade with
SQL> exec dbms_stats.set_global_prefs(‘CONCURRENT’, ‘TRUE’); - Enter SQL*Plus in your 12c environment [even if you had used the DBUA]
- Restart your database in upgrade mode
SQL> startup upgrade
SQL> exit - cd to $ORACLE_HOME/rdbms/admin
- $ORACLE_HOME/perl/bin/perl catctl.pl -l /home/oracle/upgrade -p 68 catupgrd.sql
The “-p” option will restart the parallel upgrade now at phase 68.
I also would like to thank Jason Arneil, who’s Blog Post helped me a lot!
Since Feb 23, 2015, there’s a generic one off patch available on top of Oracle 12.1.0.2.
And please see also the COMMENT section with the comments by Arun Gupta – thanks a lot, Arun!
-Mike
Mike,
The way you have described the workaround will not work. If I start the upgrade script from scratch (step 6), it re-creates the DBMS_STATS package and sets CONCURRENT=TRUE which causes upgrade to fail. I have been struggling with problem for last two days and have researched the issue quite a bit.
The option CONCURRENT is new in 12c. The documentation does not list TRUE or FALSE as valid values for this option but apparently these are. Documentation does not even list a default value for this option. Apparently, TRUE is default value. I did not expect this kind of problem in Oracle documentation.
The upgrade script checks for the value of CONCURRENT and if the value is not FALSE or OFF, the script tries to gather stats with CONCURRENT option. CONCURRENT requires resource manager to be enabled. However, resource manager is disabled by default. So, CONCURRENT=TRUE and resource manager disabled causes upgrade to fail.
I also tried setting RESOURCE_MANAGER_PLAN=MIXED_WORKLOAD to enable resource manager, but re-running the upgrade script from scratch caused resource manager related packages to be re-created and reset RESOURCE_MANAGER_PLAN to an empty string causing upgrade to fail.
This is clearly a bug. Oracle creates DBMS_STATS with CONCURRENT=TRUE and creates resource manager disabled. Since resource manager is a prerequisite for CONCURRENT, upgrade fails.
In Jason Arneil’s blog, he ran just the failing script after modifying the value of CONCURRENT to FALSE. This is not a smooth way to upgrade. Would you be able to file bug?
I believe Oracle already has bug filed but it is for engineered systems only, Doc ID 1937782.1. Please see bug description for 19664340. It is not a public bug so I am not sure if it is fixed in any patch for 12.1.0.2.
Thanks,
Arun
Arun,
first of all thanks for your comment, your research work – and sorry for the inconvenience.
Actually there’s a one off (interim) patch available via download from MOS for generic platforms:
Please login to MOS and do a patch search for patch "19664340".
Second I have verified with the doc that you are right – the options "TRUE" and "FALSE" are not mentioned here:
http://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68664
The behavior during the upgrade always is (since 10g):
Resource Manager plans will be turned off. This happens implicitly during the STARTUP UPGRADE or ALTER DATABASE OPEN UPGRADE action. Therefore this little piece will always get you in trouble.
And it’s clearly a bug to create the DBMS_STATS with CONCURRENT=TRUE as this would require the resource manager’s plan to be present – but it isn’t (as you have learned the hard way unfortunately). This is what has been filed under bug19664340.
If you think that there needs to be another bug filed (I will update the above blog post with the link to the patch) I’ll have to ask you to open an SR and let Support file it. That is the procedure I’ll have to stick with.
Actually the procedure I described above has solved the issue for us at the training in October where one of my colleagues has hit the issue. I was sitting side by side with him and we did exactly what I described – and for us it worked.
Sorry again for this huge inconvenience!!!
And thanks a lot for your comment – this is really helpful!
Mike
Mike,
I applied the patch and upgrade did complete. However, utlu121s reported ton of errors and catuppst did not run because of these errors. I had to run it manually. I checked with dbupgdiag.sql and no errors were reported. My understanding is that output of utlu121s never gets cleared even if the errors no longer exist. utlrp ran fine and reported zero invalid objects. DBA_REGISTRY is also reporting all objects valid, so I can call the upgrade successful.
In a plain vanilla 12.1.0.2 install this issue does not happen and CONCURRENT is set to OFF. Somewhere between 12.1.0.2 and Oct 2014 CPU, this bug was introduced.
Oracle should include some kind of advisory in upgrade procedure about this issue.
If the upgrade fails part of the way, it will lead to lot of SRs and inconvenience for the customers.
Thanks,
Arun
Arun,
your finding in 12.1.0.2 is correct. The utlu121s.sql script reads out static information which exists right after the upgrade – but before recompilation.
Actually the upg_summary.log is the better source. I think we haven’t said that the utlu121s.sql has become obsolete but personally I consider it this way – and I think we don’t even mention it in our workshops anymore.
The reason why you saw such a "mess" after upgrade and before recompilation:
– the ORACLE SERVER in 12.1 will go into INVALID state once there’s any error. So even in your case when you cleaned that up the information stays static.
Hope this helps – and just fyi, my manager Carol filed a bug 19664340 to get the fix included into the April PSU.
We did ask as well to included it into the KNOWN ISSUES AND ALERTS notes in MOS for 12.1.0.2 so others will know about it as well.
Thanks again for raising this – and sorry for any inconvenience!
Thanks Mike. Your blog is an excellent source of information. It provides an excellent opportunity to discuss issues with someone of your knowledge and experience.
Arun,
thanks a lot for your feedback – and thanks for digging so deep into that topic.
Kind regards
Mike
🙂
I applied the patch and the upgrade now shows to have succeed and past the phase 68 where is was failing before. I am working with Oracle support because DBUA said everything was successful but there are invalid components and objects. Also no stats will run get the below message.
ORA-20000: Unable to gather statistics concurrently: Resource manager plan is not active or is not managing CPU usage
I don’t understand why this bug fix, created february 2015, is not included in subsequent PSU’s from Oracle, especially as the bug is gerneric, well know and documented.
Well … I see your points.
And believe me, we discuss this topic internally a lot at the moment for many other fixes.
Not in our hands.
But I can understand your frustration (and I’m in agreement with you which does not help at all – but just mentioning it) …
Thanks
Mike
More – if you are on non-CDB with defined multi sub plans resource manager, running gather_table_stats with CONCURRENT set to TRUE drivers to ORA-01422 error.
Hi Mike,
Thanks for the blog. This solution is not only useful on upgrades but if you install OJVM on an existing database (12.1.0.2 with PSU5 and PSU160119) as well. The component is listed as "INVALID" on dba_registry and utlrp.sql did not work.