Re: impdp Issue

Sometimes it is useful to know which files are being used to resolve a name, as there may be some used that you aren't aware of for some reason.

$ strace /u01/app/oracle/product/21c01/bin/tnsping does-not-exist 2>&1 | grep '[.]ora.*= 0$'
lstat("/u01/app/oracle/product/21c01/env.ora", {st_mode=S_IFREG|0644, st_size=852, ...}) = 0
access("/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora", F_OK) = 0
access("/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora", F_OK) = 0
access("/u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora", F_OK) = 0
stat("/u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora", {st_mode=S_IFREG|0640, st_size=401, ...}) = 0

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Principal Consultant at Pythian
Oracle ACE Alumni
Pythian Blog http://www.pythian.com/blog/author/still/ Github: https://www.pythian.com/blog/technical-track/author/jared-still <https://github.com/jkstill>
Personality: http://www.personalitypage.com/INTJ.html

On Tue, Sep 17, 2024 at 6:54 AM Scott Canaan <srcdco_at_rit.edu> wrote:

> Correct, but it’s a first step.
>
>
>
> I got it working with help from Oracle support. I had to create the
> database link with the tnsnames.ora description embedded in it, instead of
> using the tnsnames.ora alias. I don’t know why that would have changed.
>
>
>
> *Scott Canaan ‘88*
>
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>
> *From:* Jared Still <jkstill_at_gmail.com>
> *Sent:* Tuesday, September 17, 2024 9:51 AM
> *To:* Scott Canaan <srcdco_at_rit.edu>
> *Cc:* Andy Sayer <andysayer_at_gmail.com>; dimensional.dba_at_comcast.net;
> Oracle-L Freelists <oracle-l_at_freelists.org>
> *Subject:* Re: impdp Issue
>
>
>
>
> tnsping will not tell you if a connection string is correct or not. it
> just tells you if the listener is up.
>
>
>
> example:
>
> $ tnsping lestrade/does-not-exist
>
> TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-SEP-2024
> 06:38:36
>
> Copyright (c) 1997, 2021, Oracle. All rights reserved.
>
> Used parameter files:
> /opt/oracle/instantclient_19_16/network/admin/sqlnet.ora
>
> Used EZCONNECT adapter to resolve the alias
> Attempting to contact
> (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=does-not-exist))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.81)(PORT=1521)))
> OK (0 msec)
>
>
>
>
>
>
>
> On Tue, Sep 10, 2024 at 06:08 Scott Canaan <dmarc-noreply_at_freelists.org>
> wrote:
>
> I have done a tnsping and it works. I can also query the remote database
> from SQL*Plus with no issue:
>
>
>
> oracle_at_advcdevl4:ADVCDEVL>tnsping advcprod
>
>
>
> TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-SEP-2024
> 09:04:06
>
>
>
> Copyright (c) 1997, 2024, Oracle. All rights reserved.
>
>
>
> Used parameter files:
>
> /var/opt/oracle/sqlnet.ora
>
>
>
>
>
> Used TNSNAMES adapter to resolve the alias
>
> Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL
> = TCP)(HOST = advdb)(PORT =1521))) (CONNECT_DATA = (SID = ADVCPROD)))
>
> OK (0 msec)
>
> oracle_at_advcdevl4:ADVCDEVL>
>
>
>
> SQL> select name, created from v$database_at_advcprod;
>
>
>
> NAME CREATED
>
> --------- ---------
>
> ADVCPROD 23-DEC-16
>
>
>
> SQL>
>
>
>
> So I don’t see why impdp is having trouble with it, unless it’s a new bug
> introduced with the latest patch. This did work several months ago when I
> last did it.
>
>
>
> *Scott Canaan ‘88*
>
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>
> *From:* Andy Sayer <andysayer_at_gmail.com>
> *Sent:* Tuesday, September 10, 2024 9:01 AM
> *To:* Scott Canaan <srcdco_at_rit.edu>
> *Cc:* dimensional.dba_at_comcast.net; Oracle-L Freelists <
> oracle-l_at_freelists.org>
> *Subject:* Re: impdp Issue
>
>
>
>
>
> ORA-12154: TNS:could not resolve the connect identifier specified
>
> Means the connection string you’ve used in the DB link does not resolve
> from the DB server. It will have nothing to do with pdbs/services/sids. Are
> you sure you made the entry in the right tnsnames file? Have you tnsping'ed
> from the DB server to confirm it works?
>
>
>
> Unfortunately, you’re on 12.1 so you don’t have dbms_tns which helps test
> things here.
>
>
>
> Thanks,
>
> Andy
>
>
>
> On Tue, 10 Sep 2024 at 5:53 AM, Scott Canaan <dmarc-noreply_at_freelists.org>
> wrote:
>
> No.
>
>
>
>
> *Scott Canaan ‘88 *
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>
> *From:* dimensional.dba_at_comcast.net <dimensional.dba_at_comcast.net>
> *Sent:* Tuesday, September 10, 2024 8:48 AM
> *To:* Scott Canaan <srcdco_at_rit.edu>; 'Oracle-L Freelists' <
> oracle-l_at_freelists.org>
> *Subject:* RE: impdp Issue
>
>
>
> Are you using PDBs on 19c?
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Scott Canaan ("srcdco")
> *Sent:* Tuesday, September 10, 2024 5:25 AM
> *To:* Oracle-L Freelists <oracle-l_at_freelists.org>
> *Subject:* impdp Issue
>
>
>
> I’m trying to import from one database to another via the network. I’ve
> done this many, many times with no issues, until now. I create the
> database link, put the entry in the tnsnames.ora file, and verified that
> all is good. The tnsping returns correctly. I can query the remote
> database from SQL*Plus with no issue, but when I try to run the impdp, I
> get:
>
>
>
> ORA-39001: invalid argument value
>
> ORA-39200: Link name "advcprod" is invalid.
>
> ORA-12154: TNS:could not resolve the connect identifier specified
>
>
>
> The source database is Oracle 12.1 on Red Hat 7, the destination database
> is Oracle 19 on Red Hat 8. I’ve tried setting the SID and SERVICE_NAME in
> the tnsnames.ora file and it doesn’t matter, I get the same error either
> way.
>
>
>
>
> *Scott Canaan ‘88 *
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l