| sql*plus only works for user oracle [message #361587] |
Thu, 27 November 2008 01:51  |
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 #361600 is a reply to message #361587] |
Thu, 27 November 2008 02:50  |
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 #361618 is a reply to message #361587] |
Thu, 27 November 2008 03:26 |
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 #361632 is a reply to message #361587] |
Thu, 27 November 2008 03:51 |
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 #361653 is a reply to message #361587] |
Thu, 27 November 2008 04:41 |
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 |
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 |
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 |
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 ...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
