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.sqlThis 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!
