ORA-39113 Unable to determine database version
Tags： NETWORK_LINKORA-39113Oracle故障处理
ORA-39113: Unable to determine database version
Oracle数据泵之数据泵之NETWORK_LINK请参考：https://www.xmmup.com/dbbao59-kongjianbuzuzenmebanbiepashujubengzhinetwork_linklaibang.html
报错现象
使用impdp+network_link把10.2.0.1的数据库数据执行不落地的方案直接导入到12.2.0.1的库中，报错：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | create public database link dbl_source connect to system identified by lhr using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.50 )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )'; impdp \'/ AS SYSDBA\' DIRECTORY=DATA_PUMP_DIR FULL=Y \ LOGFILE=impdp_netlink_lhr_20210416.log network_link=dbl_source EXCLUDE=STATISTICS PARALLEL=8 [oracle@lhr admin]$ impdp system/oracle DIRECTORY=DATA_PUMP_DIR \ > LOGFILE=impdp_netlink_lhr_20210416.log network_link=dbl_source EXCLUDE=STATISTICS PARALLEL=8 \ > SCHEMAS=LHR,LHR1 table_exists_action=REPLACE Import: Release 12.2.0.1.0 - Production on Thu Nov 25 17:00:01 2021 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production ORA-39006: internal error ORA-39113: Unable to determine database version ORA-06550: line 1, column 7: PLS-00352: Unable to access another database 'DBL_SOURCE' ORA-06550: line 1, column 7: PLS-00201: identifier 'SYS@DBL_SOURCE' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ORA-39097: Data Pump job encountered unexpected error -6550 |
原因和解决方案
可能的原因有：
1、必须是public的dblink，请自行检查。请参考：ORA-39113 When Running A DataPump Job Through NETWORK_LINK (Doc ID 2100177.1)
2、dblink必须创建的没有错误，例如源库的system的密码是否正确，源库的system用户是否已锁住，检查方法，在目标库执行：
1 | select * from dual@dbl_source; |
若能正常返回结果，则证明没有问题。
3、GLOBAL_NAMES需要设置为false。请参考：ORA-39113 When Running A DataPump Job Through NETWORK_LINK (Doc ID 2100177.1)
4、system权限不足，可以执行如下命令赋权：
1 | grant execute on sys.DBMS_UTILITY to system; |
也可以使用sys用户来直接导入。
参考：ORA-39006 ORA-39113 ORA-06550 PLS-00352 When Performing Datapump Operations Using Network Link (Doc ID 2607463.1)
5、版本跨度太大，例如从11.2.0.1导入到12.2.0.1就会报该错。
解决办法：使用10g导出到本地，然后sftp到目标端，然后使用12客户端导入到目标数据库。
ORA-39006 ORA-39113 ORA-06550 PLS-00352 When Performing Datapump Operations Using Network Link (Doc ID 2607463.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
A DataPump job executed through database link fails with:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | > impdp <LOGIN>/<PASSWORD> network_link=<DB_LINK_NAME> tables=<TABLE_NAME> Import: Release 12.1.0.2.0 - Production on Fri Jan 8 12:33:35 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options ORA-39006: internal error ORA-39113: Unable to determine database version ORA-06550: line 1, column 7: PLS-00352: Unable to access another database '<DB_LINK_NAME>' ORA-06550: line 1, column 7: PLS-201: identifier 'SYS@<DB_LINK_NAME>' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ORA-39097: Data Pump job encountered unexpected error -6550 |
There is no log file generated for the DataPump import failure.
The database link used to unload the data with DataPump is a private database link created as follow:
1 2 3 4 5 6 7 8 9 | create database link <DB_LINK_NAME> connect to system IDENTIFIED BY <password> using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = <service_name>) ) )'; |
CHANGES
CAUSE
DataPump Utility calls:
1 | BEGIN SYS.DBMS_UTILITY.DB_VERSION@splink(:ver,:compat); |
to determine the compatibility level and version of the remote(source) database.
- ORA-39113 is raised when DataPump job can not access SYS.DBMS_UTILITY package through a private database link.
In the unpublished BUG 22505732 - TRANSPORTABLE TABLESPACE NETWORK_LINK IMPORT FAILS WITH ORA-04054, Development has confirmed ORA-39113 is an expected error and closed the internal bug as not a bug with the comments: "private db links would not work in expdp/impdp".
- In case the value of the GLOBAL_NAMES initialization parameter is TRUE, the error is raised when the database link doesn't have the same name as the database to which it connects. The value
SOLUTION
To avoid the ORA-39113 error during the network_link impdp job you need to:
Use a public database link.
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！Note: If GLOBAL_NAMES is set to TRUE:
1.a. Set parameter GLOBAL_NAMES to FALSE.
- OR -
1.b. The value
selected from the view GLOBAL_NAME in the source database has match with DB LINK name. Create the database link like:
create public database link
and
- To rule out an incompatibility issue: Be sure the guidelines from Note 553337.1 regarding Data Pump compatibility are followed.
Note:
--------
Difference between private and public database link:
PRIVATE
Creates link in a specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link to access database objects in the corresponding remote database.
PUBLIC
Creates a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.
REFERENCES
NOTE:553337.1 - Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video]
