ORA-1422 and ORA-6512 from SYS.DBMS_STATS in Post Upgrade

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