Recovery Catalog Schema Upgrade Fails With ORA-02298 On Constraint ROUT_F3

Oracle Database - Enterprise Edition - Version 19.16.0.0.0 and later

Recovery Catalog schema upgrade to version 19.16  fails With ORA-02298 on constraint ROUT_F3

RMAN> upgrade catalog
recovery catalog is partially upgraded to 19.16.00.00
error creating upgcat_add_rout_f3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: Oracle error from recovery catalog database: ORA-02298: cannot validate (<schema_owner>.ROUT_F3) - parent keys not found

As per debug trace, we can see the constraint that is being created

DBGSQL: RCVCAT> alter table rout add CONSTRAINT rout_f3 FOREIGN KEY(site_key)
DBGSQL: REFERENCES node ON DELETE CASCADE
DBGSQL: sqlcode = 2298
DBGSQL: error: ORA-02298: cannot validate (<schema_owner>.ROUT_F3) - parent keys not found (krmkosqlerr)

ORA-02298 here means that we found SITE_KEY in child ROUT table which is not present in parent table NODE. Below query can be run to identify such rows

select SITE_KEY,count(*) from <schema_owner>.ROUT
where SITE_KEY not in (select distinct SITE_KEY from <schema_owner>.NODE)
group by SITE_KEY;

Solution

Cleanup the ROUT table to remove above records

SQL> delete from <schema_owner>.ROUT where SITE_KEY not in (select distinct SITE_KEY from <schema_owner>.NODE);
SQL> commit;

Re-run the "upgrade catalog" operation.