impdp oracle集群,关于ORACLE 11.2.0.3RAC impdp导入的问题--ORA-39126: Workerc

最新推荐文章于 2024-11-25 14:15:59 发布

Kingkongmilo

最新推荐文章于 2024-11-25 14:15:59 发布

阅读量405

点赞数

文章标签： impdp 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

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