Home / server / Questions / 1006598
Accepted
user1174838
Asked: 2020-03-12 17:37:44 +0800 CST

Only oracle user can run sqlplus

  • 772

I have a couple of RHEL 6 boxes with Oracle 12.2 server and 11.2 client. This is the vendor requirement.

On one server I can run sqlplus as any user, on the other server I can only run sqlplus as the oracle user. Running as any other user gives: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

When I say oracle user, I mean the OS user named oracle.

I've checked the listener.ora and tnsnames.ora (in both client and server ORACLE_HOME structures) and both are identical save the host names.

File/directory permissions (ownership and mode) look fine.

Both tnsping and lsnrctl status look fine.

Any ideas on what the problem could be?

Thanks

-- EDIT --

These environment variables are set and are functionally identical.

set | awk -F= '/ORA/ {print $1}' ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_TERM ORACLE_UNQNAME ORA_SDTZ

oracle rhel6

2 Answers

  1. As you read in oracle documentation you need also few more variables set. For the users other than oracle you should set example ORACLE_SID which should point to the SID of your database. Also you can consider to set TNSADMIN to point to the correct TNS file.

    • 0
  2. Best Answer

    The problem turned out to be that hostname returns a short name on the working server and an FQDN on the non-working server.

    I’ve updated /etc/sysconfig/network to set the hostname short and now things work.

    I don't know why this is needed but it fixes the problem and right now that all that matters.

    • 0