Friday, November 19, 2021

Service Unavailable after upgraded to APEX 21.2

I was freak out when I saw this right after APEX upgrade. I've been doing APEX upgrade since APEX 4 and this is the first time I encounter this error.

Reviewing the upgrade log and I found this foreign Key error

Error starting at line : 53 File @ C:\Users\chiup\Downloads\apex_21.2\apex\coreins5.sql
In command -
begin
    --
    -- We need to directly delete dependencies (to XDB), because the
    -- namespace switch from SERVER to DBTOOLS would result in a FK error.
    -- There is no suitable API in dbms_registry.
    --
    delete from sys.registry$dependencies d
        where cid = 'APEX';
    --
    -- Update registry
    --
    wwv_install_api.upgrade_registry;
end;
Error report -
ORA-02292: integrity constraint (SYS.REGISTRY_PROGRESS_FK) violated - child record found
ORA-06512: at "SYS.DBMS_REGISTRY", line 299
ORA-06512: at "APEX_210200.WWV_INSTALL_API", line 603
ORA-06512: at line 12
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.

Checking the SYS.REGISTRY$PROGRESS table, I found an orphan record way back from APEX 5 days.

Delete the orphan record, drop APEX_210200 schema, re-run the upgrade process and I am back in business.