ALTER PLUGGABLE DATABASE Fails with ORA-00604/ORA-00001: unique constraint (SYS.I_PDB_ALERT1) violated/ORA-1405: fetched column value is NULL

SYMPTOMS

Opening a PDB fails with the following errors:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PDB_ALERT1) violated

The alert log showed an ORA-1405

CHANGES

APEX was upgraded.  Although the product wouldn’t necessarily need to be APEX, the issues reported for this error so far have been associated with having installed/upgraded APEX in a PDB.   In this case, the failing SQL is ‘select version from registry$ where status not in (9, 99) and namespace=’SERVER’ and cid=’APEX’, so APEX is the option causing the issue in the PDB.

CAUSE

Bug 29128693 – AFTER RUNNING OPATCHAUTO, PDB FAILS TO OPEN WITH ORA-00001: UNIQUE CONSTRAINT (SYS.I_PDB_ALERT1) VIOLATED.  If an entry in registry$ has a NULL version, the open of the pdb will fail with ORA-1405.

SOLUTION

1. Bug 29128693 is fixed in 20.1.  Do one of the following:

a.) Upgrade to 20.1, when available.

b.) Apply Patch 29128693 for your RDBMS version/OS.

Workaround:
The work around is to open the PDB in upgrade mode, and then fix/delete the software so that all versions have a proper value and are all not null.
After this, close and open the PDB in normal mode.

2. Although the PDB should now open without error after applying the patch, the APEX install failed.  You will still need to fix/delete the APEX install in the PDB.  Open an SR for APEX if you need assistance.