ORA-04061: Existing state of string has been invalidated

I got ” ORA-04061: Existing state of string has been invalidated ”  error in Oracle database.

ORA-04061: Existing state of string has been invalidated

Details of error are as follows.

ORA-04061: Existing state of string has been invalidated

Cause: An attempt to resume the execution of a stored procedure using the existing state
 which has become invalid or inconsistent with the stored procedure because the procedure
 has been altered or dropped.

Action: Try again; this error should have caused the existing state of all packages to be re-initialized.

Validating logfiles...done
Patch 31281355 apply (pdb pdbName): WITH ERRORS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31281355/23688465/31281355_apply_xxxNov11_11_23_45.log (errors)
-> Error at line 72459: script rdbms/admin/catsnmp.sql
- ORA-04068: existing state of packages has been discarded
- ORA-04061: existing state of package body "SYS.DBMS_AQADM_SYS" has been invalidated
- ORA-04065: not executed, altered or dropped package body "SYS.DBMS_AQADM_SYS"
- ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"
- ORA-06512: at "SYS.DBMS_AQADM", line 1090
- ORA-06512: at line 2

Existing state of string has been invalidated

This ORA-04061 error is related with the attempt to resume the execution of a stored procedure using the existing state which has become invalid or inconsistent with the stored procedure because the procedure has been altered or dropped.

ORA-04061 is originated from rdbms/admin/catsnmp.sql . The script “rdbms/admin/catsnmp.sql” is invalidating the SYS.DBMS_AQADM and/or SYS.DBMS_AQADM_SYS, thus resulting in datapatch failure.

One or the other pl/sql blocks must have left some state behind.

This error is typical for:
Use a package (that sets some state)
Replace a package (discard the state)
Use it again. => encountered ORA-4061 error as the state was lost.

The problem here is the package invalidation after the associated AQ sql/plb has been applied (prvtaqds.plb in this case). Making use of the package which is in invalid state often results into ORA-4061, and that’s what is observed in the sqlpatch_catcon_0.log for package DBMS_AQADM_SYS.

Unpublished BUG 32174571 – 12C TO 19.10.201104 UPGRADE FAILING WITH ORA-04061 FOR SYS.DBMS_AQADM_SYS

As a workaround =>

1. Before applying datapatch, restart your database with the underscore parameter “_srvntfn_job_deq_timeout” set to zero in init.ora of all the instances.

_srvntfn_job_deq_timeout=0

2. Also once db is started, run utlrp.sql to validate all the invalid objects before running datapatch.

3. Once datapatch installation is done, reset the underscore parameter _srvntfn_job_deq_timeout :

SQL> alter system reset "_srvntfn_job_deq_timeout" scope=both;

Solution 1 :

Please restart the database which will help to overcome the error as one or the other pl/sql blocks must have left some state behind which was causing failure.
Once the Database is restarted, no state will be discarded.

Solution 2 :

1. Apply the patch 32174571

2. Execute datapatch

Note : The bug 32174571 is fixed in 19.10 DB RU

If you got this error on Package or procedures, you should compile it as follows.

SQL> alter procedure PROCEDURE_NAME compile ;

To recompile a package body, use the following command:

ALTER PACKAGE <object_name> COMPILE BODY;

To recompile a type body, use the following command:

ALTER TYPE <object_name> COMPILE BODY;

Do you want to learn Oracle Database for Beginners, then read the following articles.