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.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *