Ameer Khan

Oracle datapump error ORA-39077: unable to subscribe agent KUPC$A_1_083325450472000

During import we faced following error.

Error Details:-

D:\oracle\product\19.3.0\dbhome_1\bin>impdp HOSPITAL/HOSPITAL@ORCL dumpfile=EXP_HOSPITAL_20231113140110_A.DMP logfile=IMP_HOSPITAL_20231113140110_A.LOG directory=dumps   Import: Release 19.0.0.0.0 – Production on Mon Nov 13 16:59:16 2023 Version 19.13.0.0.0   Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.   Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production ORA-31626: job does not exist ORA-31638: cannot attach to job SYS_IMPORT_FULL_01 for user HOSPITAL ORA-06512: at “SYS.KUPV$FT”, line 1142 ORA-06512: at “SYS.KUPV$FT”, line 1744 ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95 ORA-06512: at “SYS.KUPV$FT_INT”, line 498 ORA-39077: unable to subscribe agent KUPC$A_1_165917326000000 to queue “KUPC$C_1_20231113165917_0” ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95 ORA-06512: at “SYS.KUPC$QUE_INT”, line 294 ORA-00972: identifier is too long ORA-06512: at “SYS.DBMS_AQADM_SYS”, line 9306 ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 1873 ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 3802 ORA-06512: at “SYS.DBMS_RULE_ADM”, line 296 ORA-06512: at “SYS.DBMS_RULEADM_INTERNAL”, line 106 ORA-24000: invalid value “SYS”.”KUPC$C_1_20231113165917_0$10471″, RULE SET should be of the form [SCHEMA.]NAME ORA-00972: identifier is too long ORA-06512: at “SYS.DBMS_RULEADM_INTERNAL”, line 109 ORA-06512: at “SYS.DBMS_RULEADM_INTERNAL”, line 97 ORA-06512: at “SYS.DBMS_RULE_ADM”, line 290 ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 3757 ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 3709 ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 1756 ORA-06512: at “SYS.DBMS_PRVTAQIS”, line 1516 ORA-06512: at “SYS.DBMS_AQADM_SYS”, line 9900 ORA-06512: at “SYS.DBMS_AQADM_SYS”, line 9269 ORA-06512: at “SYS.DBMS_AQADM”, line 881 ORA-06512: at “SYS.KUPC$QUE_INT”, line 267 ORA-06512: at “SYS.KUPC$QUE_INT”, line 1360 ORA-06512: at line 1 ORA-06512: at “SYS.KUPC$QUEUE_INT”, line 65 ORA-06512: at “SYS.KUPV$FT_INT”, line 465 ORA-06512: at “SYS.KUPV$FT”, line 1664

Cause:-

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 11.2.0.4.0, in which case the name of a rule set then cannot exceed 30 characters.

Solution:-

Since our database was of version 19c which was upgraded from 11.2.0.4 but the compatible was still set to “11.2.0.4.0”. Because of this the issue was present.

After changing the compatible parameter to “19.0.0” it was resolved

SQL> alter system set compatible=’19.0.0’ scope=spfile;