HOW TO TROUBLESHOOT INVALID WRI$_REPT_* OBJECTS AFTER EXECUTING DATAPATCH SCRIPT DURING 19.16 RU APPLY

HOW TO TROUBLESHOOT INVALID WRI$_REPT_* OBJECTS AFTER EXECUTING DATAPATCH SCRIPT DURING 19.16 RU APPLY

During the data patch script execution after RU patching from 19.x to 19.16, some of the server manageability report objects become invalid due to dependent objects in a PDB database for one of my customers.

Issue:

  -> Error at line 92219: script rdbms/admin/prvtemxi.plb
      - ORA-04045: errors during recompilation/revalidation of SYS.WRI$_REPT_AUTO_INDEX
      - ORA-22345: recompile type SYS.WRI$_REPT_AUTO_INDEX before attempting this
      - operation
  -> Error at line 92230: script rdbms/admin/prvtemxi.plb
      - ORA-04045: errors during recompilation/revalidation of SYS.WRI$_REPT_AUTO_INDEX
      - ORA-22345: recompile type SYS.WRI$_REPT_AUTO_INDEX before attempting this
      - operation
  -> Error at line 118697: script rdbms/admin/prvtash.plb
      - Warning: Type Body created with compilation errors.
  -> Error at line 126033: script rdbms/admin/prvtaii.plb
      - Warning: Type Body created with compilation errors.
  -> Error at line 153284: script rdbms/admin/execemx.sql
      - ORA-04063: package body "SYS.PRVT_EMX" has errors
      - ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_EMX"
      - ORA-06512: at line 1

Below are the rept objects which got invalid after the data patch execution

select owner,
object_type,
object_name,
status
from dba_objects
where status = 'INVALID'
order by object_id;

OWNER                             OBJECT_TYPE             OBJECT_NAME                    STATUS
--------------------------------- ----------------------- ------------------------------ ----------
PUBLIC                            SYNONYM                 WRI$_REPT_ASH_OMX              INVALID
PUBLIC                            SYNONYM                 WRI$_REPT_AUTO_INDEX           INVALID
SYS                               PACKAGE BODY            PRVT_EMX                       INVALID
SYS                               TYPE                    WRI$_REPT_ASH_OMX              INVALID
SYS                               TYPE                    WRI$_REPT_AUTO_INDEX           INVALID
SYS                               TYPE BODY               WRI$_REPT_ASH_OMX              INVALID
SYS                               TYPE BODY               WRI$_REPT_AUTO_INDEX           INVALID

Approach:

1)As a basic step, executed utlrp script multiple times and there is no outcome

$sqlplus "/as sysdba"
SQL > @?/rdbms/admin/utlrp.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL > @?/rdbms/admin/utlrp.sql

2) Tried manual compile of the objects but still no outcome

DECLARE
 err_code EXCEPTION;
 PRAGMA EXCEPTION_INIT(err_code, -4043);
BEGIN
 dbms_pdb.exec_as_oracle_script('alter type WRI$_REPT_ASH_OMX compile');
 dbms_pdb.exec_as_oracle_script('alter type WRI$_REPT_AUTO_INDEX compile');
 dbms_pdb.exec_as_oracle_script('alter type WRI$_REPT_ASH_OMX compile');
 dbms_pdb.exec_as_oracle_script('alter type WRI$_REPT_AUTO_INDEX compile');
 dbms_pdb.exec_as_oracle_script('alter package PRVT_EMX compile body');
 EXCEPTION
   WHEN err_code THEN NULL;
END;
/

3) Drop and recreate the objects worked like charm!

[root@staxhost admin]# grep 'prvt_emx' *
grep: backport_files: Is a directory
catrept.sql:Rem    yxie        04/19/11 - move file management to prvt_emx package
dbmsrep.sql:Rem    kyagoub     12/20/11 - move get_swf from dbms_report to prvt_emx
dbmsrep.sql:Rem    yxie        04/21/11 - remove file management to prvt_emx package
e1102000.sql:DROP PUBLIC SYNONYM prvt_emx;
e1102000.sql:DROP package body prvt_emx;
e1102000.sql:DROP package prvt_emx;
execemx.sql:exec prvt_emx.register_files(TRUE, NULL, '&ui_mode');
prvsemxi.plb:CREATE OR REPLACE PACKAGE prvt_emx wrapped
prvtemxi.plb:CREATE OR REPLACE PACKAGE BODY prvt_emx wrapped

Drop all the invalid objects which are relevant to REPT, execute catrept.sql and compile PRVT_EMX package at the CDB level .

$sqlplus / as sysdba
DROP SYNONYM SYS.WRI$_REPT_ASH_OMX;
DROP SYNONYM SYS.WRI$_REPT_AUTO_INDEX;
DROP TYPE  SYS.WRI$_REPT_ASH_OMX;
DROP TYPE SYS.WRI$_REPT_AUTO_INDEX;
DROP TYPE BODY SYS.WRI$_REPT_ASH_OMX;
DROP TYPE BODY SYS.WRI$_REPT_AUTO_INDEX;

@?rdbms/admin/catrept.sql

alter package PRVT_EMX compile body;

Issue is cleared and data patch script completed successfully!

select owner,
object_type,
object_name,
status
from dba_objects
where status = 'INVALID'
order by object_id;

no rows selected