solution for orA-39080

Get following error when trying to export full database with expdp,

———————————————–
Export: Release 10.2.0.4.0 – 64bit Production on Wednesday, 03 March, 2010 4:34:
13

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 OLAP, Data Mining and Real Application Testing options
orA-31626: job does not exist
orA-31637: cannot create job SYS_EXPORT_FULL_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_20100303043415” and “” for Data Pum
p 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
———————————————–

and I applied oracle 2010 Jan CPU patches to the database recently. so that might be the reason.

Here is my solution,

Step 1: Check sys invalid objects

Select object_name
FROM all_objects
Where owner = UPPER(‘SYS’)
AND status = ‘INVALID’
orDER BY object_name;

Result like,

OBJECT_NAME
——————————
AQ$KUPC$DATAPUMP_QUETAB
AQ$_KUPC$DATAPUMP_QUETAB_E
AQ$_KUPC$DATAPUMP_QUETAB_F
AQ$_KUPC$DATAPUMP_QUETAB_P
AQ$_KUPC$DATAPUMP_QUETAB_V
KUPC$DATAPUMP_QUETAB
SYSNTnwmdbaUvRTOZM49YT4VlcQ==
SYSNTpwrTQocOSXuhZC0z89/tHQ==
SYSNTsm7diAjpS56VFEIFhB+xsw==
SYSNTuBZCGRetRV+Neigk+VnCog==
SYSNTvuyYj3bbSYCAfx1D9YL+UQ==
SYSNTwxwIJT51T3CSQixoUxZCbg==

The tables/views AQ$ and KUPC$ are used for datapump, but now they are invalid,

Step 2: drop the old datapump advanced queue,
exec dbms_aqadm.drop_queue_table(queue_table =>’SYS.KUPC$DATAPUMP_QUETAB’, force=> TRUE);

Step 3: re-create the queue ,

BEGIN
dbms_aqadm.create_queue_table(queue_table => ‘SYS.KUPC$DATAPUMP_QUETAB’,
multiple_consumers => TRUE,
queue_payload_type =>’SYS.KUPC$_MESSAGE’,
comment => ‘DataPump Queue Table’,
compatible=>’8.1.3′);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -24001 THEN NULL;
ELSE RAISE;
END IF;
END;
/

you can check invalid objects again, no invalid objects any more. and test the expdp again, it works perfect.
this solution is pretty good, since it takes only 1 minute.

there is another solution,
———————————————-
Step 2.  run this under sys user
@?\rdbms\admin\catproc.sql  

this script 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 30 mins .

3. compile invalid objects
@?\rdbms\admin\utlrp.sql
———————————————-

发表评论?

0 条评论。

发表评论


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>