Credits to Chris Smids from Proximus in Belgium 🙂 Thanks, Chris!!!
Upgrade to Oracle 12.1.0.2 is slow in phase: #65 ?
You are wondering why phase: #65 of the database upgrade to Oracle Database 12.1.0.2 takes quite a while. You dig down into the catupgrd0.log and recognized this statement taking a while:
dbms_output.put_line('catuposb, update 4 - rows updated ' || rows_updated); END; -- end of update for system internally generated objs /
The cause for this issue is buried in the script catuposb.sql hitting stale histograms which did not get refreshed even if you gathered dictionary stats before the upgrade as recommended.
Offered Workarounds
- Gather table stats (DBMS_STATS.GATHER_TABLE_STATS) on SYS.USER$ first
- Exchange catuposb.sql with the version distributed via  MOS Note:2147232.1 – Upgrade to 12c : CATUPOSB.SQL SLOW DURING UPGRADE
Important things to know
- Bug 21744290 – catuposb.sql can be slow during upgrade depending on no. of objects in the DB
is supposed to be fixed in the April 2016 BPs and PSU – but in Chris’ case Oracle Support confirmed that even though Chris had the BP the issue still happens. And this observation is correct. The fix from December 2015 is missing in the catuposb.sql distributed with the April 2016 PSU and BP.When you compare it now with the version Oracle Support is distributing you’ll find the fix being included – but the two more recent fixes in the current catuposb.sql (see above) are missing.
We’ll sort this out and I will update you via this blog post. - These are the lines missing in the version of catuposb.sql distributed with the April 2016 PSU/BP:
-- bug 22178855: gather table stats on user$ after update to avoid slow -- select in update 4 dbms_stats.gather_table_stats('SYS', 'USER$');
What should you do?
Actually it is not supported to edit “our” scripts in ?/rdbms/admin so please wait for a clean version of catuposb.sql being distributed. I will update the blog asap. In between I think it should be ok to gather stats on USER$ beforehand in order to prevent this from happening:
SQL> exec dbms_stats.gather_table_stats('SYS', 'USER$');
Or you’ll wait for the July 2016 Proactive BP and PSU which should be available on July 19, 2016. It will contain the correct version of the script including all fixes.
–Mike
Cheers mike – all good stuff as usual….
I gathered stats for SYS.USERS$ before the upgrade, still ran into this issue.
I had to start over but replaced catuposb.sql according to 2147232.1 and it ran fine.
The difference must be that during the upgrade the script gathers statistics with the 12c software.
Cheers
Rodolfo,
this is expected as it gets changed during the upgrade and therefore the stats need to be gathered later on – that is the fix we implemented.
Cheers and sorry for the inconvenience
Mike
Hi Mike,
We have the latest catuposb.sql file but still we are seeing the issue. update #4 step took more than 6 hours to finish.
Rem MODIFIED (MM/DD/YY)
Rem apfwkr 06/24/16 – Backport apfwkr_blr_backport_22178855_12.1.0.2.0
Rem from st_rdbms_12.1
Rem apfwkr 01/14/16 – Backport cmlim_bug-21744290 from main
Rem cmlim 10/08/15 – bug 21744290: rewrite sql optimally
Rem apfwkr 05/11/16 – Backport cmlim_bug-22178855 from main
Rem cmlim 12/03/15 – bug 22178855: gather table stats on user$ to
Rem speed up select in update #4
Hi there,
thanks for the update and sorry to hear this. You are actually the 3rd case who reported such an issue. At the moment my colleagues are investigating this (it seems to have to do with stale histograms).
Would you please mind to open an SR to have this tracked? Then will give you the chance to get an immediate update once the issue is diagnosed and solved.
Thanks in advance!
Mike
Thanks, I raised and SR.
Mike,
We recently upgraded one of our Dev instances (Exadata) from 11.2.0.4 to 18c (18.3) using DBUA and came across the error “ORA-01422: exact fetch returns more than requested number of rows” while it was executing catuposb.sql. All our pre-upgrade checks completed without any errors and we are not sure where it is complaining the mismatch in the no. of rows.
catuposb.sql : 122 rows in userxt
catuposb.sql : before update – 38 oracle-supplied and common-user user$ rows
catuposb.sql : 58 user$ rows updated with oracle-supplied and common-user bits
catuposb.sql : after update – 96 oracle-supplied and common-user user$ rows
The manual execution of dbms_stats.gather_table_stats(‘SYS’, ‘USER$’) works fine without any error. However, the upgrade is not able to complete successfully and the DB components are not marked with ‘VALID’ state in the dba registry.
Have you ever come across with this issue? It seems like an issue related to entries in SYS.USER$ table, but unable to figure out which specific entry.
Appreciate your inputs on this.
Thanks,
Balu.
Hi Balu,
thanks for your message. I have another customer reporting more or less the same thing.
First of all, INVALID means only that at least one error was found during upgrade. Check registry$error for the error code. Then recompile with utlrp.sql.
If the status changes to VALID, all is fine. If it stays invalid, then please set:
event=”1422 trace name errorstack level 12″
in your spfile and run the upgrade again. But preferably not with the DBUA as I think to remember that DBUA blindly removes events and underscores from the spfile.
In case the error stays, you will need to open an SR please – but feel free to send me the SR number, so my team mate and I can have a look.
Thanks and sorry for the inconvenience,
Mike
Mike,
We have an existing SR (3-18933324351 ) open with Oracle support and is under further investigation with back-end development. We are able to pin-point exactly where the error occurs in the upgrade in catuposb.sql, but could not find the reasoning. DBUA does not return back the control for manually executing utlrp to further validate the components of the registry. We did further analysis in google and came across this article https://dba010.com/2017/05/20/upgrade-12-1-0-2-to-12-2-0-1-fails-at-109-phase-with-unexpected-error-encountered-in-catctlmain-error-stack-below-exiting/ , which hit the same error on another object name ‘SYS.USERMIG$’. We used the same approach in our experimental database by deleting the additional entries for ‘USER$’ object name in obj$ table (select to_char(OBJ#), name, ctime, STATUS from obj$ where name=’USER$’; ) and did another upgrade trial run and it completed successfully without any errors. We need help in figuring out why the additional entries cause this error and also, if deleting these entries would cause any issues down the road, though the upgrade completed successfully.
Appreciate your help on this.
– Balu
Balu,
thanks – and we are discussing this internally. The support folks asked us for assistance with the SR as well. We’ll get back to you via the SR most likely.
Cheers,
Mike