ORA-04068: existing state of packages has been discarded

[satish]

Dear Erman,

we are on R12.2.5
database version 12.1.0.2(2 node rac)

Our concurrent requests got stuck in  output post processor for long time with error "ORA-04068: existing state of packages has been discarded".

FNDOPP24989.txt:[1/1/19 2:37:33 PM] [UNEXPECTED] [OPPAQMON:24989] java.sql.SQLException: ORA-04068: existing state of packages has been discarded
FNDOPP24989.txt:[1/1/19 2:58:54 PM] [UNEXPECTED] [24989:RT6955214] java.sql.SQLException: ORA-04068: existing state of packages has been discarded
FNDOPP24989.txt:[1/1/19 3:01:11 PM] [UNEXPECTED] [24989:RT6955223] java.sql.SQLException: ORA-04068: existing state of packages has been discarded
FNDOPP24990.txt:[1/1/19 2:37:33 PM] [UNEXPECTED] [OPPAQMON:24990] java.sql.SQLException: ORA-04068: existing state of packages has been discarded
FNDOPP24990.txt:[1/1/19 2:55:07 PM] [UNEXPECTED] [24990:RT6955199] java.sql.SQLException: ORA-04068: existing state of packages has been discarded
FNDOPP24990.txt:[1/1/19 3:12:38 PM] [UNEXPECTED] [24990:RT6955289] java.sql.SQLException: ORA-04068: existing state of packages has been discarded
FNDOPP24991.txt:[1/1/19 2:37:33 PM] [UNEXPECTED] [OPPAQMON:24991] java.sql.SQLException: ORA-04068: existing state of packages has been discarded
FNDOPP24991.txt:[1/1/19 2:59:08 PM] [UNEXPECTED] [24991:RT6955215] java.sql.SQLException: ORA-04068: existing state of packages has been discarded
FNDOPP24991.txt:[1/1/19 3:06:32 PM] [UNEXPECTED] [24991:RT6955267] java.sql.SQLException: ORA-04068: existing state of packages has been discarded

recent changes:

granted privileges on synonyms to readonly schema
compiled invalids using utlrp.sql->while compiling,we found invalids increased to 15,000 then cancelled the script
compiled invalids using adadmin(compile apps schema)->invalids count decreased and back to normal 12

Please need your expertise which of the above operation would have caused the error "ORA-04068: existing state of packages has been discarded"

Also,please let us know "can we compile invalids using adadmin when instance is in use"?
Also,please let us know "can we compile invalids using utlrp.sql when instance is in use"?

Thanks for all the support

[ErmanArslansOracleBlog]

your db version is up-to-date..
ORA-04068 can be a result of compilation. It may be an expected behaviour in specific cases.
I mean, in most cases incidences of ORA-4068 and its associated errors are correct,

Read the following for further details ->

Troubleshooting PL/SQL Program Unit Invalidation Related Errors (e.g. ORA-4068, ORA-4065, ORA-6508, PLS-907) (Doc ID 2298084.1)

However; the weird thing is that  the utlrp.sql increased your invalid count to 15000...
Probably, utlrp.sql compiled an object that was referenced by several other objects.

1)Did you check the concurrent program that was generating ORA-04068? What was the PLSQL object that the concurrent program was trying to use?

2)What was the invalid count before running utlrp.sql?

3)Did you only grant some synonyms? or did you recreate any synonyms ?

4)What is the value of the hidded parameter named -> "_DISABLE_FAST_VALIDATE" ?

5)any errors in the alert log?

6)Are specific objects always involved or does the problem seem to randomly affect any package?

7)What, if any, DDL is executed just before the state goes bad? This may be found in audit data, via DDL triggers or by checking DBA_OBJECTS.LAST_DDL_TIME to identify whether DDL might have been executed that in some way relates to the affected packages.

8)How is the problem cleared, albeit temporarily, e.g. by flushing the shared pool, manually recompiling certain objects, recreating synonyms etc?

Note that, adadmin compiles invalid apps schema objects + their dependencies.. utlrp.sql on the other hand; compiles all the invalids in the database.

[satish]

Thanks for the update erman

1)Did you check the concurrent program that was generating ORA-04068? What was the PLSQL object that the concurrent program was trying to use?

Inventory summary report..issue happened only for 5 request ids

2)What was the invalid count before running utlrp.sql?

20 invalids

3)Did you only grant some synonyms? or did you recreate any synonyms ?

Granted synonyms

4)What is the value of the hidded parameter named -> "_DISABLE_FAST_VALIDATE" ?

_DISABLE_FAST_VALIDATE is not set

5)any errors in the alert log?

Working on it

6)Are specific objects always involved or does the problem seem to randomly affect any package?

Issue happended only once

8)How is the problem cleared, albeit temporarily, e.g. by flushing the shared pool, manually recompiling certain objects, recreating synonyms etc?

We cancelled the request id's from backend

Also,please let us know is it safe to run admin or utlrp while instance is in use?

Thank you,
satish

[ErmanArslansOracleBlog]

utlrp.sql and adadmin Apps schema compile is safe in certain cases.
As you see in this issue of yours, it wasn't safe actuualy..
Basically don't compile code into a running production system... I mean don't compile a running code..

please see the following note -> Troubleshooting Guide - Invalid Objects in the E-Business Suite Environment 11i and 12 (Doc ID 1325394.1)

Basically ->  If you have only some invalid objects, it is better to compile  them manually.
Also note that, you need to compile the objects, which are belonging to Database Schemas like SYS, SYSTEM, CTXSYS, first..
the objects in APPS and APPLSYS may be dependent to them..

I think, this was what happened in your case, probably, there was a compilation done for and object in SYS or SYSTEM and APPS objects were highly dependent to it..

1)What were those 20 invalid objects?
2)Are there any errors in Database Alert log?

[satish]

Thanks for the update erman

All 20 are custom invalids.
below error is seen in alert log

ORA-00060: deadlock resolved; details in file .../diag/rdbms/proddb/PRODDB1/trace/PROD_ora_14324.trc
Errors in file .../diag/rdbms/proddb/PRODDB1/trace/PROD_ora_13290.trc  (incident=359524):
ORA-03137: TTC protocol internal error : [3120] [94] [] [] [] [] [] []
Incident details in: /u02/PRODDB/12.1.0/admin/PRODDB_erpproddb1/diag/rdbms/proddb/PRODDB1/incident/incdir_359524/PRODDB1_ora_13290_i359524.trc

ORA-04045: errors during recompilation/revalidation of APPS.CSP_RECEIVING_LINES_V
but current status is as below

SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from apps.ad_objects where OBJECT_NAME='CSP_RECEIVING_LINES_V';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE        STATUS
------------------ -------
CSP_RECEIVING_LINES_V
VIEW               VALID

[satish]

Dear Erman,

Thanks for the updates.We guess some ddl would have happened during the compile.Could you please let us know Is this parameter recommended _DISABLE_FAST_VALIDATE? in EBS

Thank you for the support

[ErmanArslansOracleBlog]

1)Were these errors recorded while you were  doing that utlrp compilation?
2) Your database is 12C. So you need to leave this parameter as is. It should be FALSE by default..
Please check the value of it using the below query:

--Remember it is a hidden parameter..
--Following query is for displaying all the hidden parameters (parameter name starting with "_")

SELECT
  ksppinm,
  ksppstvl
FROM
  x$ksppi a,
  x$ksppsv b
WHERE
  a.indx=b.indx
AND
  substr(ksppinm,1,1) = '_'
ORDER BY ksppinm

[satish]

Thanks for the update

1)Were these errors recorded while you were  doing that utlrp compilation?
Yes

2)Parameter value is false

Thanks for all the support

[ErmanArslansOracleBlog]

Okay, we need to check that trace files .. (I mean the trace files related with the ORA- errors)
But it was probably a caused by a  concurrently running DDL..
It is a deadlock and it probably breaks the utlrp run..

There should not be any DDL related statements in execution while running the utlrp script which may lead to deadlock situation.