12c Upgrade and Concurrent Stats Gathering
source link: https://jarneil.wordpress.com/2014/10/07/12c-upgrade-and-concurrent-stats-gathering/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
12c Upgrade and Concurrent Stats Gathering
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 processes5084 die;5085 }But what of more use was the bottom of a catupgrd.log file:
11:12:35 269 /catrequtlmg: b_StatEvt = TRUEcatrequtlmg: b_SelProps = FALSEcatrequtlmg: b_UpgradeMode = TRUEcatrequtlmg: b_InUtlMig = TRUEcatrequtlmg: Deleting table statscatrequtlmg: Gathering Table Stats OBJ$MIGdeclare*ERROR at line 1:ORA-20000: Unable to gather statistics concurrently: Resource Manager is notenabled.ORA-06512: at "SYS.DBMS_STATS", line 34567ORA-06512: at line 152This 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.sqlPL/SQL procedure successfully completed.catrequtlmg: b_StatEvt = TRUEcatrequtlmg: b_SelProps = FALSEcatrequtlmg: b_UpgradeMode = TRUEcatrequtlmg: b_InUtlMig = TRUEcatrequtlmg: Deleting table statscatrequtlmg: Gathering Table Stats OBJ$MIGdeclare*ERROR at line 1:ORA-20000: Unable to gather statistics concurrently: Resource Manager is notenabled.ORA-06512: at "SYS.DBMS_STATS", line 34567ORA-06512: at line 152PL/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')--------------------------------------------------------------------------------TRUEI 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')--------------------------------------------------------------------------------FALSESQL> @catrequtlmg.sqlPL/SQL procedure successfully completed.catrequtlmg: b_StatEvt = TRUEcatrequtlmg: b_SelProps = FALSEcatrequtlmg: b_UpgradeMode = TRUEcatrequtlmg: b_InUtlMig = TRUEcatrequtlmg: Deleting table statscatrequtlmg: Gathering Table Stats OBJ$MIGcatrequtlmg: Gathering Table Stats USER$MIGcatrequtlmg: Gathering Table Stats COL$MIGcatrequtlmg: Gathering Table Stats CLU$MIGcatrequtlmg: Gathering Table Stats CON$MIGcatrequtlmg: Gathering Table Stats TAB$MIGcatrequtlmg: Gathering Table Stats IND$MIGcatrequtlmg: Gathering Table Stats ICOL$MIGcatrequtlmg: Gathering Table Stats LOB$MIGcatrequtlmg: Gathering Table Stats COLTYPE$MIGcatrequtlmg: Gathering Table Stats SUBCOLTYPE$MIGcatrequtlmg: Gathering Table Stats NTAB$MIGcatrequtlmg: Gathering Table Stats REFCON$MIGcatrequtlmg: Gathering Table Stats OPQTYPE$MIGcatrequtlmg: Gathering Table Stats ICOLDEP$MIGcatrequtlmg: Gathering Table Stats TSQ$MIGcatrequtlmg: Gathering Table Stats VIEWTRCOL$MIGcatrequtlmg: Gathering Table Stats ATTRCOL$MIGcatrequtlmg: Gathering Table Stats TYPE_MISC$MIGcatrequtlmg: Gathering Table Stats LIBRARY$MIGcatrequtlmg: Gathering Table Stats ASSEMBLY$MIGcatrequtlmg: delete_props_data: No Props DataPL/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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK