使用impdp導數時報ORA-39126與ORA-01775: looping chain of synonyms錯誤

wxjzqym發表於2014-03-31
OOPAI
今天在使用impdp做資料遷移時遇到ORA-39126和ORA-01775的錯誤，以下為整個操作過程及解決方案。
環境：源庫：10.2.0.5 64bit    目標庫：10.2.0.5 64bit
以下是本人的操作步驟
1.源庫匯出資料
expdp cnbmbak/oracle directory=dump_dir dumpfile=test.dmp tables=CNBMCRM1822.test

2.複製檔案到目標庫
。。。。。。

3.目標庫匯入資料
impdp cnbmbak/oracle directory=dump_dir dumpfile=test.dmp remap_schema=CNBMCRM1822:cnbmbak
報錯資訊如下：
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [SELECT connect_type, need_execute, parallel_load FROM   DATAPUMP_OBJECT_CONNECT WHERE  object_type = :1]
ORA-01775: looping chain of synonyms
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6409

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xa47dda78     15370  package body SYS.KUPW$WORKER
0xa47dda78      6436  package body SYS.KUPW$WORKER
0xa47dda78     12590  package body SYS.KUPW$WORKER
0xa47dda78      3397  package body SYS.KUPW$WORKER
0xa47dda78      7064  package body SYS.KUPW$WORKER
0xa47dda78      1340  package body SYS.KUPW$WORKER
0x943802f8         2  anonymous block

Job "CNBMBAK"."SYS_IMPORT_FULL_01" stopped due to fatal error at 10:52:21

4.目標庫開啟1775事件
sqlplus / as sysdba
SQL> alter system set events '1775 trace name ERRORSTACK level 3';


5.目標庫再次匯入資料
重現錯誤
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [SELECT connect_type, need_execute, parallel_load FROM   DATAPUMP_OBJECT_CONNECT WHERE  object_type = :1]
ORA-01775: looping chain of synonyms

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6409

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xa47dda78     15370  package body SYS.KUPW$WORKER
0xa47dda78      6436  package body SYS.KUPW$WORKER
0xa47dda78     12590  package body SYS.KUPW$WORKER
0xa47dda78      3397  package body SYS.KUPW$WORKER
0xa47dda78      7064  package body SYS.KUPW$WORKER
0xa47dda78      1340  package body SYS.KUPW$WORKER
0xb763bea0         2  anonymous block

Job "CNBMBAK"."SYS_IMPORT_FULL_02" stopped due to fatal error at 11:08:58

6.目標庫關閉1775事件
SQL> alter system set events '1775 trace name errorstack off';

7.查詢trace檔案
這裡又遇到個疑問，1775事件設定後無法產生相應的trace

補充：關於test表無論在目標庫還是源庫都沒有與之相關的同義詞存在

解決方法：（以下方法為好心網友提供，經測試可以解決上述錯誤）
有可能是datapump的資料字典出問題，可以嘗試reload datapump的資料字典，
For Oracle version 10.2:

1. Catdph.sql will Re-Install DataPump types and views
SQL> @$ORACLE_HOME/rdbms/admin/catdph.sql

Note: If XDB is installed, then it is required to run "catmetx.sql" script also.
Use this code to verify if XDB is installed:
SQL> select substr(comp_name,1,30) comp_name,
     substr(comp_id,1,10) comp_id,
     substr(version,1,12) version,
     status
     from dba_registry;

Sample output if XDB installed,
Oracle XML Database    XDB    -version-    VALID

2. prvtdtde.plb will Re-Install tde_library packages
SQL> @$ORACLE_HOME/rdbms/admin/prvtdtde.plb

3. Catdpb.sql will Re-Install DataPump packages
SQL> @$ORACLE_HOME/rdbms/admin/catdpb.sql

4. Dbmspump.sql will Re-Install DBMS DataPump objects
SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.sql

5. To recompile  invalid objects, if any
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

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

相關文章