ORA-24067: exceeded maximum number of subscribers for queue | ORA-39257 ORA-39077 ORA-31693 ORA-29913

Hi,

When you start an import operation, you can get ” ORA-24067: exceeded maximum number of subscribers for queue | ORA-39257 ORA-39077 ORA-31693 ORA-29913 ” errors.

 

ORA-24067: exceeded maximum number of subscribers for queue

Error details are as follows.

ORA-39257: internal error: [kupuatjq1] [SYS_IMPORT_TABLE_03] [19] [MSDBA] [11] [0x7f48851f18d4] [39077]
ORA-39077: unable to subscribe agent KUPC$A_1_093756298515000 to queue "KUPC$C_4_20201024000819_0"
ORA-24067: exceeded maximum number of subscribers for queue SYS.KUPC$C_4_20201024000819_0
ORA-31693: Table data object "MSDBA"."TEST_TABLE":"PART_20200918"."SPART_20200918_006" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout

 

 

ORA-39257: internal error

ORA-39077: unable to subscribe agent

ORA-24067: exceeded maximum number of subscribers for queue

ORA-31693: Table data object

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

 

This error ORA-39257 ORA-39077 ORA-24067 ORA-31693 ORA-29913 is related with the Parallel parameter, to solve this problem, perform export import paralel 1 ( serial )

Once the sequence SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N exceeds 10000, the name of a rule set then exceeds 30 characters, which should not be a problem any more with 19c version.

 

The max identifier length with DB compatibility set to < 12.2  is  30
The max identifier length with DB compatibility set to >= 12.2  is  128

The issue in this case was caused due to compatible setting 12.1.0.2.0, in which case the name of a rule set then cannot exceed 30 characters.

 

Set compatible parameter to be>= 12. 2 to accommodate identifier length upto 128.

 

Bug:22862597 : ORA-39077 AND ORA-24067 DURING DATA PUMP IMPORT
Max subscriber queue error when running Data Pump with parallel > 1

 

Bug:22862597 is fixed in 12.2.

Possible Workaround:  Run Data Pump without parallel.

NOTE:

With the fix for Bug 22862597 if the error ORA-24067 occurs, it could be related to number of subscribers for queue has exceeded 1024 limit.
Possible workaround is to run Data Pump with parameter parallel value in 1/2 until it works or until we get to 1.

 

 

 

 

 

 

You can read the following article, if you want to learn more details about Export – import.

Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-4

 

 

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 *