No local connection via sqlplus (TNS:lost contact)
Thorsten Jens
I've recently had to restore our database from a (online) tar backup,
and I've had problems connecting via sqlplus since then. Solaris
8/64bit Sparc, Oracle 9.2.0.6.0 was restored but was patched to
9.2.0.7.0 since then.
Everything works fine as the 'oracle' user:
------------------------------------------------------------
oracle@tnsw16 ~ $ echo $ORACLE_BASE, $ORACLE_HOME, $ORACLE_SID
/oracle, /oracle/product/9.2.0, our_db
oracle@xxx16 ~ $ sqlplus system
SQL*Plus: Release 9.2.0.7.0 - Production on Tue Nov 22 12:34:58 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
------------------------------------------------------------
But as another user (who could connect like this before!), I get:
------------------------------------------------------------
oraload@xxx16 ~ $ echo $ORACLE_BASE, $ORACLE_HOME, $ORACLE_SID
/oracle, /oracle/product/9.2.0, our_db
oraload@xxx16 ~ $ sqlplus system
SQL*Plus: Release 9.2.0.7.0 - Production on Tue Nov 22 12:36:34 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
ld.so.1: oracle: fatal: libodm9.so: open failed: No such file or
directory
ERROR:
ORA-12547: TNS:lost contact
------------------------------------------------------------
I can, however, connect using the user@identifier syntax:
------------------------------------------------------------
oraload@xxx16 ~ $ sqlplus system@our_db
SQL*Plus: Release 9.2.0.7.0 - Production on Tue Nov 22 12:37:43 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
------------------------------------------------------------
I've searched a bit and found that this could be a permissions issue. I
think the permissions I checked look fine, though:
------------------------------------------------------------
oracle@xxx16 ~ $ ls -laF `which oracle sqlplus`
$ORACLE_HOME/lib/libodm*
-rwsr-s--x 1 oracle dba 69508136 Nov 20 16:45
/oracle/product/9.2.0/bin/oracle*
-rwxr-x--x 1 oracle dba 1058224 Nov 20 16:07
/oracle/product/9.2.0/bin/sqlplus*
-rw-r--r-- 1 oracle dba 9072 May 27 05:37
/oracle/product/9.2.0/lib/libodm9.so
-rw-r--r-- 1 oracle dba 9072 May 27 05:37
/oracle/product/9.2.0/lib/libodmd9.so
------------------------------------------------------------
Any ideas?
Thorsten
sle...@gmail.com
You may want to check the OS user's .profile between your oracle OS
user and the oraload OS user giving you the issue. The areas that you
should check are $PATH, all $ORA****** enovironment variables,
$LIBPATH, etc... These environment variables' values in both .profile
should be identical. You should also check to see whether the OS user,
"oraload", is in the OS "dba" group. It seems to me that the file
group ownership of the Oracle software installed are oracle:dba. If OS
user "oraload" is not in the "dba" group. It may generate the errors
that you encountered.
Per OFA, the file group ownership of Oracle SW should be oracle:dbop
where oracle belongs to both the dba and dbop groups. The dbop groups
is used for creating OS ids for database operators and database
administrators should be in the dba group instead.
Hope this help.
Regards,
Stephen Lee
Sybrand Bakker
>Any ideas?
LD_LIBRARY_PATH or LIBPATH issue?
--
Sybrand Bakker, Senior Oracle DBA
fitzj...@cox.net
This is usually not a 'permissions' issue, but a pathing problem,
notably an incomplete or unset LD_LIBRARY_PATH. I would verify that
the location of libodm9.so ($ORACLE_HOME/lib) is in your
LD_LIBRARY_PATH for the suspect user. From all indications it is not.
This has little, if anything, to do with an 'open failed: no such file
or directory' error.
> Any ideas?
>
> Thorsten
David Fitzjarrell
erwi...@gmail.com
oracle executables to the shared libs. Also as others have said make
sure that these other users are in the "dba " group or whichever group
is the owner of the libraries. Also look at the LD_LIBRARY_PATH and
make sure that $ORACLE_HOME/lib is in there. The executable calls a
routine on this shared library. But the calling user cannot access the
library
Thorsten Jens
> On 22 Nov 2005 03:44:03 -0800, "Thorsten Jens" <tho...@gmx.de> wrote:
>
> >Any ideas?
>
> LD_LIBRARY_PATH or LIBPATH issue?
(to you and all the people who pointed me to LD_LIBRARY_PATH)
PATH and LD_LIBRARY_PATH are the same for both users, LIBPATH is set
for neither of them! Even specifying LD_LIBRARY_PATH directly brings no
improvement. Check this out:
------------------------------------------------------------
oracle@xxx ~ $ LD_LIBRARY_PATH=/oracle/product/9.2.0/lib ldd `which
oracle`
libodm9.so => /oracle/product/9.2.0/lib/libodm9.so
libskgxp9.so => /oracle/product/9.2.0/lib/libskgxp9.so
libskgxn9.so => /oracle/product/9.2.0/lib/libskgxn9.so
libjox9.so => /oracle/product/9.2.0/lib/libjox9.so
libnsl.so.1 => /usr/lib/64/libnsl.so.1
libsocket.so.1 => /usr/lib/64/libsocket.so.1
libgen.so.1 => /usr/lib/64/libgen.so.1
libdl.so.1 => /usr/lib/64/libdl.so.1
libc.so.1 => /usr/lib/64/libc.so.1
libaio.so.1 => /usr/lib/64/libaio.so.1
librt.so.1 => /usr/lib/64/librt.so.1
libkstat.so.1 => /usr/lib/64/libkstat.so.1
libm.so.1 => /usr/lib/64/libm.so.1
libmp.so.2 => /usr/lib/64/libmp.so.2
/usr/platform/SUNW,Sun-Fire/lib/sparcv9/libc_psr.so.1
------------------------------------------------------------
Copy and pasting the exact same line to the other user:
------------------------------------------------------------
oraload@xxx ~ $ LD_LIBRARY_PATH=/oracle/product/9.2.0/lib ldd `which
oracle`
libodm9.so => (file not found)
libskgxp9.so => (file not found)
libskgxn9.so => (file not found)
libjox9.so => (file not found)
libnsl.so.1 => /usr/lib/64/libnsl.so.1
libsocket.so.1 => /usr/lib/64/libsocket.so.1
libgen.so.1 => /usr/lib/64/libgen.so.1
libdl.so.1 => /usr/lib/64/libdl.so.1
libc.so.1 => /usr/lib/64/libc.so.1
libaio.so.1 => /usr/lib/64/libaio.so.1
librt.so.1 => /usr/lib/64/librt.so.1
libkstat.so.1 => /usr/lib/64/libkstat.so.1
libm.so.1 => /usr/lib/64/libm.so.1
libmp.so.2 => /usr/lib/64/libmp.so.2
/usr/platform/SUNW,Sun-Fire/lib/sparcv9/libc_psr.so.1
------------------------------------------------------------
I trussed those two calls, and the error for 'oraload' seems to be this
one:
------------------------------------------------------------
23123: access("/oracle/product/9.2.0/bin/oracle", 1) = 0
23123: getuid() = 1016 [1016]
23123: fork() = 23124
23124: fork() (returning as child ...) = 23123
23124: execve("/oracle/product/9.2.0/bin/oracle", 0xFFBEF958,
0x00025480)
23124: *** cannot trace across exec() of
/oracle/product/9.2.0/bin/oracle ***
------------------------------------------------------------
The execve call succeeds for the 'oracle' user. Any idea on this one?
Thanks,
Thorsten
Thorsten Jens
> Did you restore the oracle home from the tar as well? If so relink the
> oracle executables to the shared libs. Also as others have said make
> sure that these other users are in the "dba " group or whichever group
> is the owner of the libraries. Also look at the LD_LIBRARY_PATH and
> make sure that $ORACLE_HOME/lib is in there.
I restored the entire /oracle ($ORACLE_BASE, thus including
$ORACLE_HOME) directory, including binaries, libs, datafiles, ...
Both users are in the dba group. LD_LIBRARY_PATH should be ok, check
out my answer to Sybrand's post.
Thorsten
Maxim Demenko
I'd suggest , you have the proper permissions issue,
the permissions on $ORACLE_HOME/bin/oracle should be set like -rwsr-s--x
( and you have after untar lost the s bit )
Best regards
Maxim
Papa Piquillo
chmod +x /oracle/product/9.2.0/lib/libodmd9.so
Thorsten Jens
The permissions of the oracle binary are correctly set.
Thorsten
Thorsten Jens
> I will try
> chmod +x /oracle/product/9.2.0/lib/libodmd9.so
Does not help. Why should a library be executable, anyway?
Thorsten
Papa Piquillo
execution permission to some libraries and the issue was resolved.
Maxim Demenko
Sorry, i've missed your original post.
Could you try not only set LD_LIBRARY_PATH, but as well export it ?
I'did try as nonprivileged user ( non dba group as well ) on one
executable in $ORACLE_HOME/bin with read for all permissions, here what
i got.
$ ldd tstshmO
libclntsh.so.9.0 => (file not found)
libwtc9.so => (file not found)
libnsl.so.1 => /usr/lib/libnsl.so.1
libsocket.so.1 => /usr/lib/libsocket.so.1
libdl.so.1 => /usr/lib/libdl.so.1
libsched.so.1 => /usr/lib/libsched.so.1
libc.so.1 => /usr/lib/libc.so.1
libaio.so.1 => /usr/lib/libaio.so.1
libposix4.so.1 => /usr/lib/libposix4.so.1
libkstat.so.1 => /usr/lib/libkstat.so.1
libm.so.1 => /usr/lib/libm.so.1
libmp.so.2 => /usr/lib/libmp.so.2
/usr/platform/SUNW,Ultra-80/lib/libc_psr.so.1
# now set the LD_LIBRARY_PATH
$ LD_LIRBRARY_PATH=$ORACLE_HOME/lib
$ ldd tstshmO
libclntsh.so.9.0 => (file not found)
libwtc9.so => (file not found)
libnsl.so.1 => /usr/lib/libnsl.so.1
libsocket.so.1 => /usr/lib/libsocket.so.1
libdl.so.1 => /usr/lib/libdl.so.1
libsched.so.1 => /usr/lib/libsched.so.1
libc.so.1 => /usr/lib/libc.so.1
libaio.so.1 => /usr/lib/libaio.so.1
libposix4.so.1 => /usr/lib/libposix4.so.1
libkstat.so.1 => /usr/lib/libkstat.so.1
libm.so.1 => /usr/lib/libm.so.1
libmp.so.2 => /usr/lib/libmp.so.2
/usr/platform/SUNW,Ultra-80/lib/libc_psr.so.1
# now export it
$ export LD_LIBRARY_PATH
$ ldd tstshmO
libclntsh.so.9.0 =>
/u01/app/oracle/products/9.2.0.6/lib/libclntsh.so.9.0
libwtc9.so => /u01/app/oracle/products/9.2.0.6/lib/libwtc9.so
libnsl.so.1 => /usr/lib/libnsl.so.1
libsocket.so.1 => /usr/lib/libsocket.so.1
libdl.so.1 => /usr/lib/libdl.so.1
libsched.so.1 => /usr/lib/libsched.so.1
libc.so.1 => /usr/lib/libc.so.1
libaio.so.1 => /usr/lib/libaio.so.1
libposix4.so.1 => /usr/lib/libposix4.so.1
libkstat.so.1 => /usr/lib/libkstat.so.1
libm.so.1 => /usr/lib/libm.so.1
libgen.so.1 => /usr/lib/libgen.so.1
libmp.so.2 => /usr/lib/libmp.so.2
/usr/platform/SUNW,Ultra-80/lib/libc_psr.so.1
Best regards
Maxim
Thorsten Jens
> Thorsten Jens schrieb:
> > erwi...@gmail.com schrieb:
> >
> >
> >>Did you restore the oracle home from the tar as well? If so relink the
> >>oracle executables to the shared libs. Also as others have said make
> >>sure that these other users are in the "dba " group or whichever group
> >>is the owner of the libraries. Also look at the LD_LIBRARY_PATH and
> >>make sure that $ORACLE_HOME/lib is in there.
> >
> >
> > I restored the entire /oracle ($ORACLE_BASE, thus including
> > $ORACLE_HOME) directory, including binaries, libs, datafiles, ...
> >
> > Both users are in the dba group. LD_LIBRARY_PATH should be ok, check
> > out my answer to Sybrand's post.
> >
> > Thorsten
> >
>
> Sorry, i've missed your original post.
> Could you try not only set LD_LIBRARY_PATH, but as well export it ?
LD_LIBRARY_PATH is and has always been exported :-/
Thorsten
Maxim Demenko
In that case it could be hardly explained (from your previous post) :
oraload@xxx ~ $ LD_LIBRARY_PATH=/oracle/product/9.2.0/lib ldd `which
oracle`
libodm9.so => (file not found)
libskgxp9.so => (file not found)
libskgxn9.so => (file not found)
libjox9.so => (file not found)
I could think only about 2 possible reasons
1) LD_LIBRARY_PATH is not *set* and *exported* to proper value ( this
suggestion don't stand as you pointed, that is not the case)
2) oraload user has no access to those libraries ( again permissions )
What is the output of
ls -l /oracle/product/9.2.0/lib/libodm9.so \
/oracle/product/9.2.0/lib/libskgxp9.so \
/oracle/product/9.2.0/lib/libskgxn9.so \
/oracle/product/9.2.0/lib/libjox9.so
if you issue it as *oraload* user ?
Best regards
Maxim
Thorsten Jens
> In that case it could be hardly explained (from your previous post) :
> oraload@xxx ~ $ LD_LIBRARY_PATH=/oracle/product/9.2.0/lib ldd `which
> oracle`
> libodm9.so => (file not found)
> libskgxp9.so => (file not found)
> libskgxn9.so => (file not found)
> libjox9.so => (file not found)
>
> I could think only about 2 possible reasons
> 1) LD_LIBRARY_PATH is not *set* and *exported* to proper value ( this
> suggestion don't stand as you pointed, that is not the case)
> 2) oraload user has no access to those libraries ( again permissions )
> What is the output of
> ls -l /oracle/product/9.2.0/lib/libodm9.so \
> /oracle/product/9.2.0/lib/libskgxp9.so \
> /oracle/product/9.2.0/lib/libskgxn9.so \
> /oracle/product/9.2.0/lib/libjox9.so
Just to make sure again:
------------------------------------------------------------
oraload@xxx ~ $ export | grep LD
declare -x LD_LIBRARY_PATH="/oracle/product/9.2.0/lib:/usr/lib"
------------------------------------------------------------
Permissions:
------------------------------------------------------------
oraload@xxx ~ $ ls -l /oracle/product/9.2.0/lib/libodm9.so \
> /oracle/product/9.2.0/lib/libskgxp9.so \
> /oracle/product/9.2.0/lib/libskgxn9.so \
> /oracle/product/9.2.0/lib/libjox9.so
-rw-r--r-- 1 oracle dba 7650528 May 27 2005
/oracle/product/9.2.0/lib/libjox9.so
-rwxr-xr-x 1 oracle dba 9072 May 27 2005
/oracle/product/9.2.0/lib/libodm9.so
-rw-r--r-- 1 oracle dba 38616 Apr 27 2002
/oracle/product/9.2.0/lib/libskgxn9.so
-rw-r--r-- 1 oracle dba 12264 Aug 26 01:24
/oracle/product/9.2.0/lib/libskgxp9.so
------------------------------------------------------------
Again, looks OK to me.
Thorsten
IANAL_VISTA
news:1132842105.8...@z14g2000cwz.googlegroups.com:
On some version(s) of Oracle the default installation precludes users
not part of the "dba" group access to needed file due to lack of
permission on higher level DIRECTORIES.
Maxim Demenko
Looks OK for me too.
( yesterday read in Tom Kyte blog "Debugging is fun, because it begins
with a mystery." - can't resist to repeat it).
Sorry, i've really no more ideas, only to be sure, last question
PATH are set and exported equally in both cases ?
I mean `which oracle` could not return different oracle executable for
oraload user as it is in case of oracle user ( silly suspiction , you
have more than one ORACLE_HOME) ?
Best regards
Maxim
IANAL_VISTA
AFAIK, the "only" way SQL*Net could be reporting a problem is
when TWO_TASK is set to some non-null value; which is wrong.
what happens when you
TWO_TASK=our_db
export TWO_TASK
Thorsten Jens
>
> On some version(s) of Oracle the default installation precludes users
> not part of the "dba" group access to needed file due to lack of
> permission on higher level DIRECTORIES.
oraload is member of the dba group, and all directories are owned by
oracle:dba, mode 755. User oraload can access and read the libodm9.so!
Thorsten
Thorsten Jens
> Thorsten Jens schrieb:
Both $PATHs are correct and almost the same (both contain
$ORACLE_HOME/bin as the first entry). There is only one $ORACLE_HOME,
and only one 'oracle' binary.
Thorsten
Thorsten Jens
IANAL_VISTA schrieb:
Ahhh, here we go: This makes everything work correctly again using the
'oraload' user. TWO_TASK was not set before, setting it to our_db makes
the connect go through. Has there been in change in 9.2.0.7 regarding
that?
Thorsten
Laurenz Albe
>> >Any ideas?
>>
>> LD_LIBRARY_PATH or LIBPATH issue?
>
> (to you and all the people who pointed me to LD_LIBRARY_PATH)
> PATH and LD_LIBRARY_PATH are the same for both users, LIBPATH is set
> for neither of them! Even specifying LD_LIBRARY_PATH directly brings no
> improvement. Check this out:
> ------------------------------------------------------------
> oracle@xxx ~ $ LD_LIBRARY_PATH=/oracle/product/9.2.0/lib ldd `which
> oracle`
> libodm9.so => /oracle/product/9.2.0/lib/libodm9.so
> ------------------------------------------------------------
>
> Copy and pasting the exact same line to the other user:
> ------------------------------------------------------------
> oraload@xxx ~ $ LD_LIBRARY_PATH=/oracle/product/9.2.0/lib ldd `which
> oracle`
> libodm9.so => (file not found)
I think I know what your problem is.
To sum up the situation:
The oracle executable has the SETUID and SETGID bits set as it should,
but only the oracle user can connect via sqlplus, other users in the dba
group cannot. Setting LD_LIBRARY_PATH does not help.
Was this correct so far?
The problem is that because the SETUID bit is set, the LD_LIBRARY_PATH is
ignored for the executable for other users.
This is to prevent a security leak: else you could set LD_LIBRARY_PATH to
a forged libc library and get it executed as root by invoking su, for
example.
The solution is to add /oracle/product/9.2.0/lib to the trusted library
path with crle.
Check with your system documentation as I have no Solaris in reach, but
crle -uvs/oracle/product/9.2.0/lib
should do the trick.
Is there an RPATH in Solaris? If yes, that would be another possibility:
relink the oracle executable so that it directly references the correct
library path.
Yours,
Laurenz Albe
Thorsten Jens
>
> I think I know what your problem is.
>
> To sum up the situation:
> The oracle executable has the SETUID and SETGID bits set as it should,
> but only the oracle user can connect via sqlplus, other users in the dba
> group cannot. Setting LD_LIBRARY_PATH does not help.
>
> Was this correct so far?
Correct.
> The problem is that because the SETUID bit is set, the LD_LIBRARY_PATH is
> ignored for the executable for other users.
> This is to prevent a security leak: else you could set LD_LIBRARY_PATH to
> a forged libc library and get it executed as root by invoking su, for
> example.
>
> The solution is to add /oracle/product/9.2.0/lib to the trusted library
> path with crle.
I tried that. Unsetting TWO_TASK (which was the solution provided
earlier today) and adding $ORACLE_HOME/lib to the trusted library path
produced the same failure as before, though.
Thanks,
Thorsten