Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

weird problem when login in using sqlplus from non oracle unix account

SOLVED
Go to solution
Henrique Silva_3
Regular Advisor

weird problem when login in using sqlplus from non oracle unix account


user could not login to DB using

sqlplus user/password eventhough environment for ORACLE_HOME and ORACLE_SID were correct.

got error :

ORA - 01034 OracleNot Available and
ORA - 27101 Shared Memory Realm Does Not Exist.

From Unix user oracle, I could login fine.

sudo to such user,and indeed, could not login, but, if I used

sqlplus user/pass@SID, it worked fine !!! ???

so, problem must be with tnsnames.ora, I thought. I have a symb link from
/etc/tnsnames.ora and all other .ora files to
$ORACLE_HOME/network/admin/*ora files. Changed premissions on the tnsnames file to be wide open.

SAME PROBLEM :-((( !!!!

looked at oracle account, and ORACLE_HOME was

/u00/app/oracle/product/8.1.7 while user's ORACLE_HOME was /u00/app/oracle/product/8.1.7_64

THere is a symbolic link from 8.1.7_64 to 8.1.7, meaning that they point to the same directory.

When I changed his ORACLE_HOME to point to the 8.1.7 directory, it WORKED FINE !!! ??????
Validate it by changing the ORACLE_HOME for the oracle unix account, and IT FAILED to work there.

Question ? WHY ???

links point to the same directory, and tnsnames is a link from /etc pointing to the proper place ? What am I missing here ?

thanks,

Henrique
"to be or not to be, what was the question ???? "
12 REPLIES
Ian Dennison_1
Honored Contributor

Re: weird problem when login in using sqlplus from non oracle unix account

Do you have following variable set?

TNS_ADMIN=/app/bin/oracle817/network/admin

Also, can you perform an 'env' as the user concerned and post it here?

Share and Enjoy! Ian
Building a dumber user
Yogeeraj_1
Honored Contributor

Re: weird problem when login in using sqlplus from non oracle unix account

hi,

Have you set your TNS_ADMIN environment variable?

Normally, with 8.1.7, the ora-27101 is reporting that the shared memory key generated by the client doesn't match any currently existing keys. This is to be expected if the ORACLE_HOME used by the client isn't the same as the one used to startup the database with OR the ORACLE_SID is not correctly referencing the right instance.

Please revert

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Henrique Silva_3
Regular Advisor

Re: weird problem when login in using sqlplus from non oracle unix account

TNS_ADMIN is not set on either unix account, but I set it now just to see, and problem is still there.

I CAN login using sqlplus, as long as I qualify the string with the @SID_NAME, even with the 8.1.7_64 directoryon ORACLE_HOME.

without the @SIDNAME string, will only work fine, if I have 8.1.7 directory instead.

????????????????????????????

env for such user is :

> env
_=/usr/bin/env
MANPATH=/usr/share/man/%L:/usr/share/man:/usr/contrib/man/%L:/usr/contrib/man:/usr/local/man/%L:/usr/local/man:/opt/upgrade/share/man/%L:/opt/upgrade/share/man:/usr/dt
/share/man:/opt/pd/share/man/%L:/opt/pd/share/man:/opt/ignite/share/man/%L:/opt/ignite/share/man:/opt/samba/man:/opt/resmon/share/man:/opt/pred/share/man/%L:/opt/pred/
share/man:/opt/hparray/share/man/%L:/opt/hparray/share/man:/opt/sudo/man:/opt/perf/man/%L:/opt/perf/man:/opt/OV/man/itose/%L:/opt/OV/man/itose:/opt/graphics/common/man
:/opt/audio/share/man:/opt/ansic/share/man/%L:/opt/ansic/share/man:/opt/langtools/share/man/%L:/opt/langtools/share/man:/opt/image/share/man:/opt/imake/man:/opt/pwplus
/share/man://opt/perl/man
PATH=/usr/bin:/opt/ansic/bin:/usr/ccs/bin:/opt/perl/bin:/usr/contrib/bin:/opt/nettladm/bin:/opt/fc/bin:/opt/fcms/bin:/opt/upgrade/bin:/opt/pd/bin:/usr/bin/X11:/usr/con
trib/bin/X11:/opt/resmon/bin:/usr/sbin/stm/uut/bin/progs:/opt/pred/bin:/opt/hparray/bin:/usr/sbin/diag/contrib:/opt/sudo/bin:/opt/perf/bin:/opt/OV/bin/OpC:/opt/graphic
s/common/bin:/opt/langtools/bin:/opt/imake/bin:/usr/local/bin:/opt/pwplus/bin:.:/develop/radia/bin:/develop/radia/link:/u00/app/oracle/product/8.1.7/bin:/u00/app/oracle/product/8.1.7/lib:.
COLUMNS=167
EDITOR=vi
LOGNAME=SOMEUSER
HOSTNAME=SOMESERVER
ORACLE_SID=SIDNAME
ERASE=^H
PS1=$HOSTNAME:$PWD/
Radia >
SHELL=/usr/bin/ksh
HISTSIZE=1000
HOME=/home/SOMEUSER
TERM=hpterm
ORACLE_HOME=/u00/app/oracle/product/8.1.7
PWD=/home/SOMEUSER
TZ=EST5EDT
ENV=/home/SOMEUSER/.kshrc
LINES=55
uxsd01:/home/SOMEUSER/


Again,

sqlplus user/pass@SID works either way

sqlplus user/pass ONLY WORKS if ORACLE_HOME points to 8.1.7 instead of 8.1.7_64 enventhough they are the samedirectories from following the symb link.

cheers,

Henrique
"to be or not to be, what was the question ???? "
Henrique Silva_3
Regular Advisor

Re: weird problem when login in using sqlplus from non oracle unix account



BTW, the SIDNAME here on the env for this account, is THE SAME as the one which I am trying to login. Just made it generic.

AND, as you can see,when I change ORACLE_HOME, then, I CAN login fine without the
@SIDNAME string.

very weird indeed !!!

Henrique

PS.: This instance is patched up with 8.1.74 patch, but does not have the latest security patch.
"to be or not to be, what was the question ???? "
Tom Danzig
Honored Contributor

Re: weird problem when login in using sqlplus from non oracle unix account

The oracle account may have special group priviledges for access to shrared memory that the regular unix user does not have. Try adding the unix user to the same group as the oracle account and see if that helps.

See man pages for setprivgrp(1M), getprivgrp(2), privgrp(4)
Henrique Silva_3
Regular Advisor

Re: weird problem when login in using sqlplus from non oracle unix account


THe problem is that setting up 8.1.7_64 for the oracle account, SHOWS the same problem.
It only worked there before, because the oracle account had the ORACLE_HOME pointing to the proper directory.

This is one of those things that will BOTHER ME until I find out why this is not working. It is a SYMBOLIC LINK to the same directory !!

Why is there a problem with THIS symbolic link ? :-(((

Thanks,

Henrique
"to be or not to be, what was the question ???? "
Brian Crabtree
Honored Contributor

Re: weird problem when login in using sqlplus from non oracle unix account

Most of what you are describing happens when the "oracle" exeuctable ($OH/bin/oracle) is not suid. The reason that Oracle can do it is because it owns the shared memory, and a sqlnet connection will work because the processes are normally owned by oracle directly as well. I would verify that the binary is correct by executing:

chmod 4755 $ORACLE_HOME/bin/oracle

Thanks,

Brian
Henrique Silva_3
Regular Advisor

Re: weird problem when login in using sqlplus from non oracle unix account


thanks guys, BUT, BOTH accounts fail to work properly, when their ORACLE_HOME is set to
8.1.7_64 directory, which is symbolic link to 8.1.7 !!! SAME DIRECTORY, same files, same permissions ( btw, oracle executable is 4751 ).

I think I reinstalled oracle on this box and set the ORACLE_HOME to 8.1.7 the second time around. Not sure if any compiled code has 8.1.7 now instead of 8.1.7_64. However, it still does not explain why it fails if the name of the directory is something else or is a link !!!

ARE THERE ANY PROBLEMS WITH LINKS on hpux 11 and oracle 8.1.7 ? this simple makes no sense at all, but I can recreate the problem any time I want to !!

Henrique
"to be or not to be, what was the question ???? "
Steven E. Protter
Exalted Contributor

Re: weird problem when login in using sqlplus from non oracle unix account

There are not any know problems with symbolic links on HP-UX 11.11

Thats pretty stable, old technology.

Who made the symbolic link?

If root did it, this could be an issue.

Proper Oracle setup is to NOT allow root to fire up sqlplus. There are a lot of good reasons.

Do an ll on the symbolic link. If root owns it, delete it and recreate it using the oracle admin account.

If what I've said isn't the cause of the problem then its probably purely an environment problem. See prior posts. Envirnonment for oracle users should be consistent across user accounts.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
James A. Donovan
Honored Contributor
Solution

Re: weird problem when login in using sqlplus from non oracle unix account

From a Metalink Forums thread
search keys: ORA-27101 symbolic links

==============================

RDBMS Version: 8.1.7
Operating System and Version: Windows 2000
Error Number (if applicable): ORA-1034, ORA-27101
Product (i.e. SQL*Loader, Import, etc.): sqlplus
Product Version:

shared memory realm error occuring only when connect to network.

Interesting problem when trying to connect to a Local database NOT connected to the network, connection occurs fine.
HOWEVER, connected to the network the errors
ORA-1034 - Oracle not available and
ORA-27101 - shared memory realm does not exist; appear.

The user profile does not change as the local profile is used.
Listener.ora, sqlnet.ora and tnsnames.ora files do not change.

Any ideas?

Thank you in advance.
Craig.
crb@amsa.gov.au





--------------------------------------------------------------------------------

From: Oracle, Harm Joris ten Napel 22-Nov-01 09:31
Subject: Re : shared memory realm error occuring only when connect to network.


Hi,

yes, every once in a while this little lesson needs to be thaught,
once it's understood enlightment follows quickly:

When you start an oracle database a key for the shared memory segment
is calculated based on the contents of ORACLE_SID and ORACLE_HOME,
call it a hash value. When you try to connert later, your server
process needs to find the shared memory segment so it does that by
taking your ORACLE_SID and ORACLE_HOME and calculates the hash value,
when at the designated key no SGA is present you get the ora-27101.

Note that when the contents of ORACLE_HOME differ, although it may
point to the same physical location on disk, the hash value will be
different, this is the main reason why Oracle discourages the use of
symbolic links: you can but you have to be consequent.

When attemting to connect over sqlnet, the ORACLE_HOME and ORACLE_SID
from the listener.ora are used, in your case this must differ from
what ORACLE_HOME and ORACLE_SID where when you first started your
database (especially trailing slashes '/' in your listener.ora).

P.S. there is a slight change (rare) that the server process spawned
by the listener cannot access the SGA because of permission problems,
this usually happens when having more users than just oracle that own
software on your system.

greetings,

Harm







--------------------------------------------------------------------------------

From: Oracle, Gina Thunder 26-Nov-01 15:53
Subject: Re : shared memory realm error occuring only when connect to network.


Thank you, Harm. Also note that there is a bug with the startup: #1522966. The workaround is to comment out the SQLNET.AUTHENTICATION_SERVICES= (NTS) in your $ORACLE_HOME/network/admin/sqlnet.ora file. There is a fix for this problem within the 8.1.7.0.1. patchset. To find this patch, you will need to use the following method:

=> Patches
=> Click top link to take you to the "(old) patch download screen"
=> Select the following from the drop down menus:
-All Products
-MS Windows NT

=>Then do a 'find' on the page for patch: 1522966

Gina
Oracle Support Services

Remember, wherever you go, there you are...
James A. Donovan
Honored Contributor

Re: weird problem when login in using sqlplus from non oracle unix account

also see Metalink Note:122183.1

...
Remember, wherever you go, there you are...
Henrique Silva_3
Regular Advisor

Re: weird problem when login in using sqlplus from non oracle unix account


Jim, thanks, this makes SENSE now !!!! wow !!

so, it is not so transparent to play with binaries and oracle homes !!!

Looking at my listener.ora file, I had the ORACLE_HOME set there as 8.1.7_64 !!! Corrected that and restarted the listener with the proper ORACLE_HOME !!!

I LEARNED SOMETHING NEW TODAY !!!

Thanks a lot, a really appreciate it. I feel that forums like this, really helps everyone understand this beast !! :-)

Henrique
"to be or not to be, what was the question ???? "