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)
  }

Answer 1

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?