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.
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;
764 views last month, 3 views today