關於ORACLE 11.2.0.3RAC impdp匯入的問題--ORA-39126: Workerc

流浪的野狼發表於2015-05-15
Oracle
今天在給某專案做備份資料還原是出現如下錯誤，P6（V8版本）系統對11.2.0.4 RAC的支援上存在一定的bug。故專案只能用11.2.0.3，在安裝系統時時間比較倉促也並未打上最新的PSU，故而有了如下報錯：資料庫整庫匯入，使用table_exists_action=replace來出來重複的表資料，因一直統計資訊存在一定問題，事先採用exclude=statistics 剔除了統計資訊的匯入。
impdp \'/ as sysdba\' dumpfile=CRlandpm_fullbak20150512.dmp logfile=impdp_CRlandpm_fullbak20150512.log  full=y table_exists_action=replace exclude=statistics directory=dump parallel=8
ORA-31085: schema "" already registered
Failing sql is:
BEGIN dbms_xmlschema.registerSchema(:1, :2, (:3 = 1), FALSE,FALSE,FALSE,FALSE, :4, options=> :5, schemaoid => :6, import_options => :7); END; 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION [TABLE:"IX"."AQ$_STREAMS_QUEUE_TABLE_T"] 
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24 characters
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9001
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x38220d900     20462  package body SYS.KUPW$WORKER
0x38220d900      9028  package body SYS.KUPW$WORKER
0x38220d900     16105  package body SYS.KUPW$WORKER
0x38220d900     16832  package body SYS.KUPW$WORKER
0x38220d900     16484  package body SYS.KUPW$WORKER
0x38220d900      3956  package body SYS.KUPW$WORKER
0x38220d900      9725  package body SYS.KUPW$WORKER
0x38220d900      1775  package body SYS.KUPW$WORKER
0x1a91a8e58         2  anonymous block
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION [TABLE:"IX"."AQ$_STREAMS_QUEUE_TABLE_T"] 
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24 characters
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9001
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x382206e30     20462  package body SYS.KUPW$WORKER
0x382206e30      9028  package body SYS.KUPW$WORKER
0x382206e30     16105  package body SYS.KUPW$WORKER
0x382206e30     16832  package body SYS.KUPW$WORKER
0x382206e30     16484  package body SYS.KUPW$WORKER
0x382206e30      3956  package body SYS.KUPW$WORKER
0x382206e30      9725  package body SYS.KUPW$WORKER
0x382206e30      1775  package body SYS.KUPW$WORKER
0x337770c98         2  anonymous block
Job "SYS"."SYS_IMPORT_FULL_03" stopped due to fatal error at 18:24:00
[oracle@ERP2DB01 dump]$   
針對上面的問題，很大程度上懷疑是bug問題，在MOS上查了下果然發現下面這麼一段
Impdp Fails With ORA-39126: Worker Unexpected Fatal Error In KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION (文件 ID 943357.1)
Oracle Database - Enterprise Edition - Version 9.2.0.8 to 11.1.0.7 [Release 9.2 to 11.1]
Information in this document applies to any platform.

SYMPTOMS

-- Problem Statement:
Datapump Import fails with the following errors:
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PROCESS_TABLE_EXISTS_ACTION
[TABLE:"APPLSYS"."AQ$_FND_CP_TM_RET_AQTBL_T"]
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24 characters
ORA-00955: name is already used by an existing object

CAUSE

Error ORA-24019 is self-explanatory: the queue table name is 25 characters long, whereas only 24 are allowed.

There are restrictions in regards to the names of the queue_tables: 
Oracle Streams Advanced Queuing User's Guide and Reference 10g Release 2 (10.2) 
Chapter 8 Oracle Streams AQ Administrative Interface 

DBMS_AQADM.CREATE_QUEUE_TABLE --&gt Queue table names must not be longer than 24 characters. 
If you attempt to create a queue table with a longer name, error ORA-24019 results 

SOLUTION

 In normal case the way to resolve this is to either exclude this queue from being imported and then manually create it afterwards, or recreate the queue in the source database with a valid name (<= 24 characters) and export and import into the destination database.

But since it is followed by ORA-00955: name is already used by an existing object,then in this case, the first thing to check would be whether the interested queue_tables do have messages or are empty. 
If the queue_tables are empty then prior to import they would need to be dropped as follow:

 At the TARGET Database: 

1) Drop queue_table with force parameter set to TRUE,the queue table name is  FND_CP_TM_RET_AQTBL as shown:
connect / as sysdba 
Begin 
dbms_aqadm.drop_queue_table ('APPLSYS.FND_CP_TM_RET_AQTBL',TRUE); 
End; 
/

 

2) Re-run the IMPDP 

OR 

Use the TABLE_EXISTS_ACTION=TRUNCATE option - this deletes existing rows and then loads rows from the source
索然這個文件描述的問題有所不同，單頁大同小異，解決我的這個報錯也夠了.......
採用table_exists_action=truncate後解決問題............
此外在執行
connect / as sysdba 
Begin 
dbms_aqadm.drop_queue_table ('APPLSYS.FND_CP_TM_RET_AQTBL',TRUE); 
End; 
/
則個的時候並未向預測的一樣有任何資訊，所指定的表不存在...........

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28612416/viewspace-1656720/，如需轉載，請註明出處，否則將追究法律責任。

相關文章