ORA-24203: operation failed, queue table SYS.KUPC$DATAPUMP_QUETAB has errors

The problem

Today I have a simple task to export schema from one database to another. Export was really simple and correct one. On import side at the beginig of log there was:

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by ZUCRI, not by you

import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
IMP-00008: unrecognized statement in the export file: 
  
. importing ZUCRI's objects into TAB
...

Oracle does not allow exp from higher version (10.2.0.4) and then imp into lower (10.1.0.4) version. If 10.1.04 imp is to be used, then 10.1.0.4 exp must be first used.

Unfortunately as mentioned, this was impossible because mine source was on 10.2.0.4 and have to be ported to 10.1.0.4 .
So only solution was to use Oracle Data Pump utility with "version" parameter which allow import from higher version.

C:\>expdp '/ as sysdba' DUMPFILE="zucri.dmp" LOGFILE="exp_zucri.log" DIRECTORY=DATA_PUMP_DIR VERSION=10.1.0.4 COMPRESSION=METADATA_ONLY CONTENT=ALL SCHEMAS=('ZUCRI')

Export: Release 10.2.0.4.0 - 64bit Production on Utorak, 10 Kolovoz, 2010 13:45:02

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20100810134502" and "" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1606
ORA-24203: operation failed, queue table SYS.KUPC$DATAPUMP_QUETAB has errors

Notice "VERSION=10.1.0.4" in expdp command. In this case this was crucial part.

But it seems that some sys parts are invalid so I have to manage different approach to be able to run expdp utility.

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Uto Kol 10 13:47:11 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Tes
ting options

SQL> select object_name from all_objects where owner = 'SYS' AND status != 'VALID' order by 1;

OBJECT_NAME
------------------------------
AQ$KUPC$DATAPUMP_QUETAB
AQ$_KUPC$DATAPUMP_QUETAB_E
AQ$_KUPC$DATAPUMP_QUETAB_F
AQ$_KUPC$DATAPUMP_QUETAB_V
KUPC$DATAPUMP_QUETAB
SYSNTK+Hp6PazTJaPOReVq6YXqQ==
SYSNTsDyaSCdvTmySdAs5Ztvhbw==
SYSNTWBJ9iDeeSuyp/p6B7f1iEg==

8 rows selected.

Quick help with utlrp (quick output):

SQL> @utlrp

TIMESTAMP
--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN  2010-08-10 13:49:07

...

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END  2010-08-10 13:50:00


PL/SQL procedure successfully completed.
...
ERRORS DURING RECOMPILATION
---------------------------
                          4


PL/SQL procedure successfully completed.

And object validity checking again:

SQL> select object_name from all_objects where owner = 'SYS' AND status != 'VALID' order by 1;

OBJECT_NAME
------------------------------
AQ$KUPC$DATAPUMP_QUETAB
AQ$_KUPC$DATAPUMP_QUETAB_E
AQ$_KUPC$DATAPUMP_QUETAB_F
AQ$_KUPC$DATAPUMP_QUETAB_V
KUPC$DATAPUMP_QUETAB
SYSNTK+Hp6PazTJaPOReVq6YXqQ==
SYSNTsDyaSCdvTmySdAs5Ztvhbw==
SYSNTWBJ9iDeeSuyp/p6B7f1iEg==

8 rows selected.

wasn't successful in any way-so now I was really stuck!

The solution

Mine database version was 10.2.0.4, and this database was hugely patched with all kinds of patches from 10.1 version. So this error raised somewhere in that time.

After searching Metalink, solution seems to be very easy. Recreate queue tables, that were invalid:

SQL> exec dbms_aqadm.drop_queue_table(queue_table =>'SYS.KUPC$DATAPUMP_QUETAB', force=> TRUE);

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  dbms_aqadm.create_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
  3    multiple_consumers => TRUE,
  4    queue_payload_type =>'SYS.KUPC$_MESSAGE',
  5    comment => 'DataPump Queue Table',
  6    compatible=>'10.1.0');
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      IF SQLCODE = -24001 THEN NULL;
 10      ELSE RAISE;
 11      END IF;
 12  END;
 13  /

PL/SQL procedure successfully completed.

Let's check invalids again:

SQL> select object_name from all_objects where owner = 'SYS' AND status != 'VALID' order by 1;

no rows selected

SQL>

All seems fine!

Try expdp:

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by ZUCRI, not by you

import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
IMP-00008: unrecognized statement in the export file: 
  
. importing ZUCRI's objects into TAB
. . importing table                 "ADR_CVOROVI2"          0 rows imported
...

Works!

The End

According metalink notes, there is another solution:

@?\rdbms\admin\catproc.sql  
@?\rdbms\admin\utlrp.sql 

This scripts will re-create all the pl/sql in database, and may cause some user sp/function/package invalid during executing, and it costs long time - more then dozen of minutes with total database inactivity to other users-unacceptable.

Cheers!