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

Last updated on OCTOBER 10, 2016

Applies to:

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

Symptoms

Hi

We used DBUA t oupgrade to 12.1.0.2 but _diag_adr_enabled was set to FALSE instead of TRUE

So after upgrade we did reset _diag_adr_enabled to TRUE and applied steps described in the two Notes

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


but it did not help :

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

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


How can it be fixed ?

Regards

Shawn

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