Ernst Leber sent me an email today. He hit an error at a customer upgrading to Oracle 19.9.0 on Exadata with AutoUpgrade. ORA-1422 and ORA-6512 from SYS.DBMS_STATS in Post Upgrade were signaled – and he better flashed back to the Guaranteed Restore Point. He found even a MOS note but still had questions. So time to blog about it in case you hit this error sequence as well.

Photo by Kelly Sikkema on Unsplash
What happens, and when does it happen?
At first, this is not an AutoUpgrade issue. You may see the same error stack in the post upgrade phase regardless of method (AutoUpgrade, dbupgrade or DBUA) you use to upgrade your database.
This is the error sequence Ernst spotted in the AutoUpgrade console:
Errors in database [ABCD1] Stage [GRP] Operation [STOPPED] Status [ERROR] Info [ Error: UPG-2000 [Unexpected exception error] Cause: Creation of GRP failed For further details, see the log file located at /u01/app/oracle/cfgtoollogs/autoupgrade/ABCD/ABCD1/103/autoupgrade_20210504_user.log] ------------------------------------------------- Logs: [/u01/app/oracle/cfgtoollogs/autoupgrade/ABCD/ABCD1/103/autoupgrade_20210504_user.log] ------------------------------------------------- DATABASE NAME: E661 CAUSE: ERROR at Line 781619 in [/u01/app/oracle/cfgtoollogs/autoupgrade/ABCD/ABCD1/103/dbupgrade/catupgrd20210504111655e660.log] REASON: ORA-01422: exact fetch returns more than requested number of rows ACTION: [MANUAL] DETAILS: 01422, 00000, "exact fetch returns more than requested number of rows" // *Cause: The number specified in exact fetch is less than the rows returned.
Not nice – but luckily there is a GRP. And he could flash back to it.
In addition, the upgrade summary showed this:
Oracle XML Database UPGRADED 19.9.0.0.0 00:01:08 Datapatch 00:01:47 Final Actions 00:01:52 Post Upgrade ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.DBMS_STATS", line 40753 ORA-06512: at "SYS.DBMS_STATS", line 40037 ORA-06512: at "SYS.DBMS_STATS", line 38914 ORA-06512: at "SYS.DBMS_STATS", line 38023 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 24646 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 24633 ORA-06512: at "SYS.DBMS_STATS", line 37679 ORA-06512: at "SYS.DBMS_STATS", line 38596 ORA-06512: at "SYS.DBMS_STATS", line 39740 ORA-06512: at "SYS.DBMS_STATS", line 40185 ORA-06512: at "SYS.DBMS_STATS", line 40734 ORA-06512: at line 149 Post Upgrade 00:00:06
So in the post upgrade phase, the error happens.
There is a MOS Note, and a patch
Ernst found already MOS Note: 2525596.1 – Database Upgrade to 12.2, 18c, 19c fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS. In this note he could read the exact error pattern he saw as well – and the recommendation to apply patch 29213893 to cure this issue.
In addition, the note explains that he hit unpublished BUG 29213893 – DBMS_STATS FAILING WITH ERROR ORA-01422 WHEN GATHERING STATS FOR USER$ TABLE. But it doesn’t give more detail. Actually, the bug’s subject is a bit misleading as it describes the result but doesn’t say anything about the root cause.
The ORA-1422 happens for gather_table_stats on a table sharing its name with remote table.
So you see this effect when:
select OBJ#, DATAOBJ#, NAME, LINKNAME, NAMESPACE, TYPE# from obj$ where name='USER$'; OBJ# DATAOBJ# NAME LINKNAME NAMESPACE TYPE# ---------- ---------- ------------------------------ -------------------- ---------- ---------- 92815 92815 USER$ DB2K2 1 2 22 10 USER$ 1 2
You see that USER$ has two entries – one with the additional attribute “LINKNAME”.
These two rows cause the trouble – and of course, you MUST NOT delete anything here.
What is the solution?
You need to apply a patch. Patch 29213893 will cure this misbehavior. When you apply it, it will exchange two plb files in ?/rdbms/admin:
- prvtstai.plb – Package Body dbms_stats_internal
- prvtstas.plb – Package dbms_stats_internal
The README tells you:
Oracle Database 19 Release 19.11.0.0.210420DBRU OPTIMIZER Patch for Bug# 29213893 for Generic Platforms This patch is non-RAC Rolling Installable. This patch is Data Guard Standby-First Installable.
So unfortunately you can’t apply it rolling in a RAC environment. And as all patch bundles (RUs, RURs, BPs) are always “RAC rolling”, this patch will never make it into an RU or BP.
But for me, this means that you should include this patch by default into your new home provisioning procedure to avoid such issues.
Further Links and Information
- MOS Note: 2525596.1 – Database Upgrade to 12.2, 18c, 19c fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS
- Patch 29213893
- BUG 29213893 – DBMS_STATS FAILING WITH ERROR ORA-01422 WHEN GATHERING STATS FOR USER$ TABLE
- Oracle 19c Installation with 19.11.0, OJVM and one-off patches
–Mike
Hi Mike,
Thanks for this information.
Funny enough, the Patch 29213893 readme does not mention you need to stop all the software running on the Oracle Home, before the calll to ‘opatch apply’.
Also, the patch mentions:
false
I did not have currently a RAC for testing, but on a standalone DB, the patch is completely online, so I wonder if for RAC would not be the same. Question behind: are online patches always described as “non-rolling”?
In anycase, as this patch is only need for upgrades, when preparing the new OH, one can always installed there before running the upgrade and avoid the problem.
Cheers
Miguel Anjo
Hi Miguel,
you are right. As the patch exchanges only 2 plb files in ?/rdbms/admin it won’t affect the production run when you APPLY the patch.
BUT … and this is the crucial point which decides whether a patch is RAC rolling, or not … when you run datapatch, there must be only one instance open – and there must be no access to the DB by the application as this is an internal stats package.
And I agree with you – this is very misleading for such a patch.
Mike
Hy Mike
Thank’s for this information. But like I’am an old Oracle DBA, I allways fall down into this issue in 9.2.0.5. The bug 3262241 describe into note 3262241.8 said it was fixed with 9.2.0.6 or 10.1.0.2.
So how it could be possible it’s appear again !
Best regards
Pierre