Home » SQL & PL/SQL » Client Tools » sql*plus only works for user oracle (10g, SLES9)
sql*plus only works for user oracle [message #361587] Thu, 27 November 2008 01:51 Go to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
I have a new server. When I execute sqlplus as Oracle, it works fine. When I do it as one of my users, I get:

sqlplus

Enter user-name: unloads
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

A permission problem maybe ? (Linux Error: 2: No such file or directory)

I echo'd the variables from the users profile, and ORACLE_HOME, ORACLE_BASE, and ORACLE_SID is set correctly.
I also set LD_LIBRARY_PATH (not sure if I need it for the user).


when I use "sqlplus unloads/passwd@unich" it works fine, just when I type only sqlplus, and follow the prompts, I get this error.

What could be the problem ?

Dirk
Re: sql*plus only works for user oracle [message #361597 is a reply to message #361587] Thu, 27 November 2008 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The local database/instance your ORACLE_SID is pointing to is not started.

Quote:
when I use "sqlplus unloads/passwd@unich" it works fine

You don't (try to) reach the same database/instance.

Regards
Michel
Re: sql*plus only works for user oracle [message #361600 is a reply to message #361587] Thu, 27 November 2008 02:50 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
I did some troubleshooting:

At the end of the user's .profile I echo $ORACLE_SID to make sure which sid the user is using when he is logged in. This gives me:

unich

This is the same sid when I run "sqlplus unloads/passwd@unich" as the user (which works).

Is this not a permission problem in Linux ?


Dirk
Re: sql*plus only works for user oracle [message #361606 is a reply to message #361587] Thu, 27 November 2008 03:14 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
I have also added this user (unloads) to the oinstall group, but this didn't help.
Re: sql*plus only works for user oracle [message #361610 is a reply to message #361600] Thu, 27 November 2008 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no relation bewteen ORACLE_SID and service name (@...) in connection string.
Service name refers to an entry in tnsnames.ora file.

Regards
Michel
Re: sql*plus only works for user oracle [message #361618 is a reply to message #361587] Thu, 27 November 2008 03:26 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
It have read a lot about the SID & Service Name, but it seems that I still don't understand it properly.

The following is what I have in my tnsnames.ora

UNICH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = unic-db-test)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = unich)
)
)


I now see from your answer that "sqlplus unloads/passwd@unich" works because I am connecting to the above service name in my tnsnames.ora (no matter what the sid is set to).

What must I do so that the user can connect by only typing sqlplus from the linux prompt, and then following the sql prompts (username & password) ?

Dirk
Re: sql*plus only works for user oracle [message #361624 is a reply to message #361618] Thu, 27 November 2008 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What must I do so that the user can connect by only typing sqlplus from the linux prompt, and then following the sql prompts (username & password) ?

You must be log in database server (unic-db-test) and set ORACLE_HOME and ORACLE_SID, and export these variables.

Regards
Michel
Re: sql*plus only works for user oracle [message #361632 is a reply to message #361587] Thu, 27 November 2008 03:51 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
Yes, I have already done so, in this user's .profile

ORACLE_SID=unich
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10GR2

export ORACLE_SID ORACLE_BASE ORACLE_HOME


But still I cannot use only "sqlplus", I must use "sqlplus unloads/passwd@unich"

Dirk
Re: sql*plus only works for user oracle [message #361650 is a reply to message #361632] Thu, 27 November 2008 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post
uname -n
env | grep ORACLE | sort

Connect to the database (the way you can) and post:
select instance_name from v$instance;
select * from global_name;

Regards
Michel
Re: sql*plus only works for user oracle [message #361653 is a reply to message #361587] Thu, 27 November 2008 04:41 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
uname -n
unic-db-test

User Oracle:
env | grep ORA | sort

ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10GR2/
ORACLE_SID=unich
ORA_NLS10=/opt/oracle/product/10GR2//nls/data


User unloads:
env | grep ORA | sort

ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10GR2
ORACLE_SID=unich


SQL>select instance_name from v$instance;

INSTANCE_NAME
----------------
unich

SQL>select * from global_name;

GLOBAL_NAME
-----------------------------------------------------
UNICH
Re: sql*plus only works for user oracle [message #361951 is a reply to message #361653] Fri, 28 November 2008 12:46 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Setting ORACLE_SID is only appropriate when the database is on the same machine that you are running sqlplus on. When you specify ORACLE_SID, (and no connect string @unich), then sqlplus doesn't use Oracle NET, so tnsnames.ora isn't used.

To specify connect string interactively, try:
sqlplus

Enter user-name: unloads@unich
Enter password:
Re: sql*plus only works for user oracle [message #362235 is a reply to message #361951] Mon, 01 December 2008 06:57 Go to previous messageGo to next message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
"Setting ORACLE_SID is only appropriate when the database is on the same machine that you are running sqlplus on."

Thank you Andrew.

Yes, this is what we are trying to do. The user opens a secure shell (ssh) connection to the server, and then runs sqlplus from the server.

We have servers where she does this currently, but on this (new) server, she isn't able to login using only "sqlplus".

I have compared the environments, and cannot figure out why yet.

Dirk
Re: sql*plus only works for user oracle [message #362650 is a reply to message #361587] Wed, 03 December 2008 09:26 Go to previous message
dirkm
Messages: 86
Registered: November 2008
Location: Centurion - South Africa
Member
For now I have "hacked" this by replacing this user's sqlplus with a script of my own (shell script), which allows her to only type sqlplus, instead of sqlplus@unich ...
Previous Topic: Can I create an XLS file from SQL*Plus?
Next Topic: SQL Developer
Goto Forum:
  


Current Time: Thu Dec 08 00:10:21 CST 2016

Total time taken to generate the page: 0.06566 seconds