sqlplus through SSH failed to resolve tns

Question

I setup an EC2 instance and RDS instance. Then installed oracle instance client on EC2 instance. After that I managed to do sqlplus and connect with database from EC2 instance. To do that I created a tnsnames.ora file and enter the service details of the database.

I can do,

sqlplus user/password@db_alias

But I cannot do, (This gives ERROR: ORA-12154: TNS:could not resolve the connect identifier specified)

ssh username@ip sqlplus user/password@db_alias

Password less ssh also configured. And I'm doing the ssh to the current machine itself. Any thought would be helpful.

Addition to the details. Since I installed oracle instance client, tnsping command is not available. I achieve this by adding following function to the .profile file.

whence tnsping >/dev/null 2>&1 ||
  tnsping() {
    sqlplus -L -s x/x@$1 </dev/null |
      grep ORA- |
        (grep -v ORA-01017 || echo OK)
  }

Where did you create tnsnames.ora? Your environment may be missing a $TNS_ADMIN variable pointing to its location when you ssh in like that, though it seems to have the $PATH and other Oracle variables already. — Alex Poole
@AlexPoole I put the tnsnames.ora in $ORACLE_HOME/network/admin. Also set the $TNS_ADMIN to above mentioned path. Some times it may be a permission issue of the tnsnames.ora. I'll check. — Amith Chinthaka
But how are you setting TNS_ADMIN - manually in your shell before you call ssh? Or in an environment file (.bashrc, .prifle etc.) so it is picked up by the new session that sqlplus is being run in? — Alex Poole
I added it to the Environment file. So I hope it is available for each session. Addition to the previous comment. tnsnames.ora file has the required permission level. — Amith Chinthaka
@AlexPooleYup! The variable is not available at the time of doing sqlplus via ssh. My OS is ubuntu. So the .profile has an entry to source .bashrc and .bashrc has those records. I cannot think a reason for not setting the environment variables. — Amith Chinthaka

Amith Chinthaka Amith Chinthaka · Accepted Answer · 2018-08-17T05:37:45

This problem could be able to narrow down to the problem in loading environment variables (specifically $TNS_ADMIN). Since the .bashrc has an validation to check the login shell is an interactive one or non-interactive one, the variables which defined at the bottom was not loaded.

# ~/.bashrc: executed by bash(1) for non-login shells.
# see /usr/share/doc/bash/examples/startup-files (in the package bash-doc)
# for examples

# If not running interactively, don't do anything
case $- in
    *i*) ;;
      *) return;;
esac

The reason for tns not to be resolved via ssh is unavailability of $TNS_ADMIN variable. By defining that variables at the beginning of .bashrc, I could able to fix this.

See Also Why does an SSH remote command get fewer environment variables then when run manually?

sqlplus through SSH failed to resolve tns

1 Answers