Oracle 版本: 19.19
OS 版本: Linux 8.6
問題描述:
透過 DB Link 執行 impdp 作業時發生 ORA-02019 錯誤 :
$ impdp \'/ as sysdba\' directory=impdir schemas=scott network_link=uat19c logfile=imp_scott.log
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=impdir schemas=scott network_link=uat19c logfile=imp_scott.log Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "SCOTT"."DEPT" 5.937 KB 198 rows . . imported "SCOTT"."EMP" 8.570 KB 2616 rows ORA-31693: Table data object "SCOTT"."LOC" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-02019: connection description for remote database not found ORA-02019: connection description for remote database not found . . imported "SCOTT"."JOS" 3.127 KB 53 rows Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at MON AUG 15 03:20:29 2022 elapsed 0 00:00:08 |
測試 DB Link 是可以正常運行的 :
問題分析:
這個現象可能與 Bug 27164609 : NETWORK_LINK IMPDP FAILS TO LOAD DATA DUE TO ORA-2019 WHEN DB_DOMAIN IS NULL 有關。
主要是因為 impdp 透過 DB Link 的行為從 12.2 版本開始改變，在 12.2 之前使用的 access_method 是 INSERT_AS_SELECT ，但 12.2 開始將 access_method 的預設模式改為 direct_path ，而 direct_path 這個模式沒辦法正確的解析 DB Link 是否含有 domain name 而造成這個錯誤。
檢查目前所使用的 uat19c 這個 DB Link ，的確內容是沒有包含任何 domain name :
即便如此還是發生了 ORA-2019 問題。
徹底解決方式還是使用 access_method=insert_as_select ，使用 12.2 之前的行為模式，不受 domain name 的影響。
解決方法:
將 access_method=insert_as_select 加入 impdp 即可 :
$ impdp \'/ as sysdba\' directory=impdir schemas=scott network_link=uat19c access_method=insert_as_select logfile=imp_scott.log
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=impdir schemas=scott network_link=uat19c access_method=insert_as_select logfile=imp_scott.log Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "SCOTT"."DEPT" 5.937 KB 198 rows . . imported "SCOTT"."EMP" 8.570 KB 2616 rows . . imported "SCOTT"."LOC" 1.435 KB 25 rows . . imported "SCOTT"."JOS" 3.127 KB 53 rows Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at MON AUG 15 03:29:43 2022 elapsed 0 00:00:48 |
