HOW TO RECREATE X$DIAG_xxxx OBJECTS ? ( ORA-00942, ORA-04063)

(Doc ID 2095513.1)

Last updated on MAY 03, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Using DBUA to upgrade to 12.1.0.2 but _diag_adr_enabled was set to FALSE instead of TRUE has introduced the error ORA-00942 when querying the ADR views.

After upgrade the parameter _diag_adr_enabled was reset to TRUE and the steps from documents:

<Note 1243837.1> - Upgrading From 11GR1 To 11GR2, DBUA Fails With ORA-00942 on X$DIAG_ADR_CONTROL
<Note 1674422.1> - 'ORA-04063: View "SYS.V_$DIAG_VIPS_PACKAGE_MAIN_INT" Has Errors' During Upgrade

were executed, but without fixing the issue:

CATADRVW.SQL or CATPROC.SQL or CATUPGRD.SQL always report the same errors :
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
09:29:43 SQL> create or replace view
09:29:43 2 v_$diag_ADR_CONTROL as select * from x$diag_ADR_CONTROL;
v_$diag_ADR_CONTROL as select * from x$diag_ADR_CONTROL
*
ERROR at line 2:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.07
09:29:43 SQL> create or replace public synonym
09:29:43 2 v$diag_ADR_CONTROL for v_$diag_ADR_CONTROL;

Synonym created.

Elapsed: 00:00:00.08
09:29:43 SQL> grant select on v_$diag_ADR_CONTROL to dba;
grant select on v_$diag_ADR_CONTROL to dba
  *
ERROR at line 1:
ORA-04063: view "SYS.V_$DIAG_ADR_CONTROL" has errors
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

and same type of errors for all the v_$diag_xxxxx, x$diag_xxxxx objects : ORA-00942 followed with ORA-04063

Even after running utlrp all the views V_$DIAG_xxxxx are invalid because x$diag_xxxxx objects do not exist .

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms