ORA-39001, ORA-39200, ORA-02019 OR ORA-12154 While Using DataPump Import (IMPDP) With NETWORK_LINK (Doc ID 2045806.1) To BottomTo Bottom 

In this Document
Symptoms
Cause
Solution
References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.
SYMPTOMS

Running a DataPump Import through NETWORK_LINK the following errors are reported:

    ORA-39001: invalid argument value
    ORA-39200: Link name "MYTEST" is invalid.
    ORA-02019: connection description for remote database not found
CAUSE

It seems like the issue is related to service name interpretation, when NETWORK_LINK parameter is involved.  The underlying error was ORA-02019/ ORA-12154.

Below are some key points notes during our investigation:

•   Customer was able to create the database link with service name and query through that database link.
•   Service name entry exists in TNSNAMES.ORA
•   Initially the issue points at Doc ID 1620131.1 (DataPump Import Fails With Errors ORA-39001, ORA-2019) and added JOB_NAME parameter there.
    But after adding JOB_NAME parameter, the underlying network related error changed from ORA-02019 to ORA-12154

SOLUTION

We need to check the last error which is the underlying error, causing the database link error during the DataPump import execution.
And use the following options to fix the issue:


1. Check whether the TNSNAMES.ORA is accessible and contains the service as addressed by the database link

2. Check whether the database link can be accessed via SQLPLUS

3. Check whether the issue is fixed after replacing whole service name description instead of service name, while creating dblink and import

4. If you are using local naming (TNSNAMES.ORA file), then:

   • Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile(SQLNET.ORA)
   • Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible
   • Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file
   • Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable

5. Is the dblink referenced in TNS_ADMIN environment variable? In some cases LISTENER, TNSNAMES might be running from different home and TNS_ADMIN to locate TNSNAMES.ORA. In such cases, impdp utility can be invoked by creating a symbolic link in DB_HOME/network/admin to TNSNAMES.ORA under the LISTENER home.

6. Add the parameter NAMES.DEFAULT_DOMAIN = world anywhere in the SQLNET.ORA file and test the import
 

REFERENCES

NOTE:1620131.1 - DataPump Import Fails With Errors ORA-39001, ORA-2019
NOTE:812039.1 - ORA-39001, ORA-39071, ORA-06553 When Running IMPDP Using NETWORK_LINK
NOTE:1160207.1 - Error ORA-39001 When Using DBMS_DATAPUMP API Over A Network Link