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, Aug 16 '18 at 18:27
@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, Aug 16 '18 at 18:30
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, Aug 16 '18 at 18:42
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, Aug 17 '18 at 03:14
@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, Aug 17 '18 at 04:13
I found the reason for `bashrc` is not been loaded. In `bashrc` there is a validation to check the login shell is interactive or non-interactive. If not running interactively, `bashrc` exit. So I defined the variables at the beginning of the file. Thanks @AlexPoole for your thought. — Amith Chinthaka, Aug 17 '18 at 05:30

score 1 · Answer 1 · answered Aug 17 '18 at 05:37

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 Answers1