======================
General
======================
When connecting with sqlplus a/a@connectstr the connection is OK
When connecting with db_link, which has using same 'connectstr' an error is thrown:
ORA-12154: TNS:could not resolve the connect identifier specified from db_link
Why?
======================
Issue
======================
The issue is that there are several tnsnames.ora files on the Linux server.
sqlplus client is using the tnsnames.ora under ${ORACLE_HOME}/network/admin${ORACLE_HOME} is set by environment variables, for example to: /software/oracle/121
But oracle server might be using another tnsnames.ora file.
For example, under path: /oracle_db/db1/19000/db_home/network/admin/
======================
Solution
======================
Option A - Define the full connect string when creating dblink
This will avoid using tnsnames.ora
Example:
CREATE DATABASE LINK LINK_EMRGT_31 CONNECT TO EMAPMADA identified by EMAPMADA using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11.22.33.44)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=EMRGT)))';
Option B - Locate the correct tnsnames.ora using lsnrctrl utility
oracle@my_host:/software/oracle/121/network/admin>% lsnrctl
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-APR-2020 16:10:32
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> set current_listener EMRGT
Current Listener is EMRGT
LSNRCTL> set displaymode verbose
Service display mode is VERBOSE
LSNRCTL> services
Service "EMRGT" has 2 instance(s).
Instance "EMRGT", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
(ADDRESS=(PROTOCOL=beq)(PROGRAM=/software/oracle/12.2.0.1/db_home/bin/oracle)(ENVS='ORACLE_HOME=/software/oracle/12.2.0.1/db_home,ORACLE_SID=EMRGT')(ARGV0=oracleEMRGT)(ARGS='(LOCAL=NO)'))
Instance "EMRGT", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:3253029 refused:0 state:ready
LOCAL SERVER
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=/oracle_db/db1/19000/db_home/bin/oracle)(ARGV0='oracleEMRGT')(ARGS='(LOCAL=NO)')(ENVS='XDG_SESSION_ID=93,SITEBACKUP_ROOT=/etc/sh/sitebackup,HOSTNAME=qdcrt-1-aps-01,ORACLE_BASE_HOME=/oracle_db/db1/19000/db_home,BACKUP_ROOT=/etc/sh/backup,SHELL=/bin/bash,TERM=xterm,SH_ETC=,HISTSIZE=1000,SSH_CLIENT=10.30.217.7 51511 22,NUM_OF_EXPORTS=1,ORASH=/etc/sh/orash,SSH_TTY=/dev/pts/0,BAS_ORACLE_LIST=igt,PROJECT_SID=igt,CALCULATE_STATS=NONE,USER=oracle,LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lha=01;31:*.lz4=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lrz=01;31:*.lz=01;31:*.lzo=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.cab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36:,LD_LIBRARY_PATH=/oracle_db/db1/19000/db_home/lib:/oracle_db/db1/19000/db_home/lib,ORACLE_SID=EMRGT,ORACLE_BASE=/oracle_db/db1/19000,PATH=,MAIL=/var/spool/mail/oracle,PWD=/oracle_db/db1/19000/db_home/bin,LANG=en_US.UTF-8,IS_DIRECT=y,ORACLE_BASE_CONFIG=/oracle_db/db1/19000/db_home,HISTCONTROL=ignoredups,SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass,HOME=/software/oracle/oracle,SHLVL=2,LOGNAME=oracle,SSH_CONNECTION=10.30.217.7 51511 10.10.30.90 22,INFRA_ROOT=/usr/local/etc/starhome,LESSOPEN=||/usr/bin/lesspipe.sh %s,DISPLAY=localhost:10.0,XDG_RUNTIME_DIR=/run/user/501,ORA_EXP=/backup/ora_exp,ORACLE_HOME=/oracle_db/db1/19000/db_home,_=/oracle_db/db1/19000/db_home/bin/sqlplus,ORA_NET2_DESC=9,12,SKGP_SPAWN_DIAG_POST_FORK_TS=,SKGP_HIDDEN_ARGS=,SKGP_SPAWN_DIAG_PRE_FORK_TS=,SKGP_SPAWN_DIAG_PRE_EXEC_TS=,ORACLE_SPAWNED_PROCESS=1,RDMAV_FORK_SAFE=1,RDMAV_HUGEPAGES_SAFE=1')(ENV_POLICY=NONE))
