I was upgrading an Exadata test database from 11.2.0.4 to 12.1.0.2 and I came across a failure scenario I had not encountered before. I’ve upgraded a few databases to both 12.1.0.1 and 12.1.0.2 for test purposes, but this was the first one I’d done on Exadata. And the first time I’d encountered such a failure.
I started the upgrade after checking with the pre upgrade script that everything was ready to upgrade. And I ran with the maximum amount of parellelism:
$ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql . . . Serial Phase #:81 Files: 1 A process terminated prior to completion. Died at catcon.pm line 5084.
That was both annoying and surprising. The line in catcon.pm is of no assistance:
5080 sub catcon_HandleSigchld () { 5081 print CATCONOUT "A process terminated prior to completion.\n"; 5082 print CATCONOUT "Review the ${catcon_LogFilePathBase}*.log files to identify the failure.\n"; 5083 $SIG{CHLD} = 'IGNORE'; # now ignore any child processes 5084 die; 5085 }
But what of more use was the bottom of a catupgrd.log file:
11:12:35 269 / catrequtlmg: b_StatEvt = TRUE catrequtlmg: b_SelProps = FALSE catrequtlmg: b_UpgradeMode = TRUE catrequtlmg: b_InUtlMig = TRUE catrequtlmg: Deleting table stats catrequtlmg: Gathering Table Stats OBJ$MIG declare * ERROR at line 1: ORA-20000: Unable to gather statistics concurrently: Resource Manager is not enabled. ORA-06512: at "SYS.DBMS_STATS", line 34567 ORA-06512: at line 152
This error is coming from the catrequtlmg.sql, but my first thought was checking if the parameter resource_manager_plan was set, and it turned out it wasn’t. However setting the default_plan and running this piece of sql by itself produced the same error:
SQL> @catrequtlmg.sql PL/SQL procedure successfully completed. catrequtlmg: b_StatEvt = TRUE catrequtlmg: b_SelProps = FALSE catrequtlmg: b_UpgradeMode = TRUE catrequtlmg: b_InUtlMig = TRUE catrequtlmg: Deleting table stats catrequtlmg: Gathering Table Stats OBJ$MIG declare * ERROR at line 1: ORA-20000: Unable to gather statistics concurrently: Resource Manager is not enabled. ORA-06512: at "SYS.DBMS_STATS", line 34567 ORA-06512: at line 152 PL/SQL procedure successfully completed.
I then started thinking about what it meant by gather statistics concurrently and I noticed that I had indeed set this database to gather stats concurrently (it’s off by default):
SQL> select dbms_stats.get_prefs('concurrent') from dual; DBMS_STATS.GET_PREFS('CONCURRENT') -------------------------------------------------------------------------------- TRUE
I then proceeded to turn of this concurrent gathering and rerun the failing SQL:
SQL> exec dbms_stats.set_global_prefs('CONCURRENT','FALSE'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('concurrent') from dual; DBMS_STATS.GET_PREFS('CONCURRENT') -------------------------------------------------------------------------------- FALSE SQL> @catrequtlmg.sql PL/SQL procedure successfully completed. catrequtlmg: b_StatEvt = TRUE catrequtlmg: b_SelProps = FALSE catrequtlmg: b_UpgradeMode = TRUE catrequtlmg: b_InUtlMig = TRUE catrequtlmg: Deleting table stats catrequtlmg: Gathering Table Stats OBJ$MIG catrequtlmg: Gathering Table Stats USER$MIG catrequtlmg: Gathering Table Stats COL$MIG catrequtlmg: Gathering Table Stats CLU$MIG catrequtlmg: Gathering Table Stats CON$MIG catrequtlmg: Gathering Table Stats TAB$MIG catrequtlmg: Gathering Table Stats IND$MIG catrequtlmg: Gathering Table Stats ICOL$MIG catrequtlmg: Gathering Table Stats LOB$MIG catrequtlmg: Gathering Table Stats COLTYPE$MIG catrequtlmg: Gathering Table Stats SUBCOLTYPE$MIG catrequtlmg: Gathering Table Stats NTAB$MIG catrequtlmg: Gathering Table Stats REFCON$MIG catrequtlmg: Gathering Table Stats OPQTYPE$MIG catrequtlmg: Gathering Table Stats ICOLDEP$MIG catrequtlmg: Gathering Table Stats TSQ$MIG catrequtlmg: Gathering Table Stats VIEWTRCOL$MIG catrequtlmg: Gathering Table Stats ATTRCOL$MIG catrequtlmg: Gathering Table Stats TYPE_MISC$MIG catrequtlmg: Gathering Table Stats LIBRARY$MIG catrequtlmg: Gathering Table Stats ASSEMBLY$MIG catrequtlmg: delete_props_data: No Props Data PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
It worked! I was able to upgrade my database in the end.
I wish the preupgrade.sql script would check for this. Or indeed when upgrading, the catrequtlmg.sql would disable the concurrent gathering.
I would advise checking for this before any upgrade to 12c and turning it off if you find it in one of your about to be upgraded databases.
I believe this issue is specific to a combination of concurrent stats gathering and the resource manager. In fact you should be able to gather stats concurrenty with no issue unless the resource manager is disable. So the other option to fix the problem could be to enable resource manager by the following SQL:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘DEFAULT_PLAN’;
— Kirill Loifman