Gentoo Websites Logo
Go to: Gentoo Home Documentation Forums Lists Bugs Planet Store Wiki Get Gentoo!
Bug 465252 - =dev-db/oracle-instantclient-basic-11.2.0.3 should install Network Client startup parameter file (sqlnet.ora)
Summary: =dev-db/oracle-instantclient-basic-11.2.0.3 should install Network Client sta...
Status: RESOLVED FIXED
Alias: None
Product: Gentoo Linux
Classification: Unclassified
Component: Current packages (show other bugs)
Hardware: All Linux
: Normal normal (vote)
Assignee: Michael Haubenwallner
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-04-09 12:32 UTC by Sergey S. Starikoff
Modified: 2013-11-11 09:36 UTC (History)
0 users

See Also:
Package list:
Runtime testing required: ---


Attachments
Network Client startup parameter file example (sqlnet.ora.sample,28.84 KB, text/plain)
2013-04-09 12:32 UTC, Sergey S. Starikoff
Details
sqlnet.log, written by sqldeveloper on network failure. (sqlnet.log,1.52 KB, text/plain)
2013-04-10 12:06 UTC, Sergey S. Starikoff
Details
sqlnet.ora for oracle 11.2 (instantclient) (sqlnet.ora,63.75 KB, text/plain)
2013-04-10 15:14 UTC, Michael Haubenwallner
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Sergey S. Starikoff 2013-04-09 12:32:07 UTC
Created attachment 344966 [details]
Network Client startup parameter file example

From time to time experienceing issues with network operability I've came to necessity of configuring Oracle client's (at least in part of error handling and logging).
So, in addition to tnsnames.ora (bug #354059) I find useful to include in package general network client config file (sqlnet.ora).
And again I find the one from FreeBSD oracle8-client-0.2.0_1 to be good enough.

According to my experience, at least log_file_client directory must be set, and the directory must be writable by process, which uses Oracle client.
Comment 1 Sergey S. Starikoff 2013-04-10 12:06:01 UTC
Created attachment 345116 [details]
sqlnet.log, written by sqldeveloper on network failure.

Maybe not only example, but real config needed.
Not long ago I've caught a network failure with runned sqldeveloper.

And as result find the following structure in my user's homedir:

$ find oradiag_myuser/
oradiag_myuser/
oradiag_myuser/diag
oradiag_myuser/diag/clients
oradiag_myuser/diag/clients/user_myuser
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/lck
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/lck/AM_1096102193_3488045378.lck
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/lck/AM_1096102262_3454819329.lck
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/lck/AM_3216668543_3129272988.lck
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/lck/AM_1744845641_3861997533.lck
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/metadata_dgif
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/metadata
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/metadata/ADR_CONTROL.ams
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/metadata/INC_METER_PK_IMPTS.ams
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/metadata/ADR_INVALIDATION.ams
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/metadata/INC_METER_IMPT_DEF.ams
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/alert
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/alert/log.xml
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/cdump
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/stage
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/sweep
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/trace
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/trace/sqlnet.log
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/metadata_pv
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/incpkg
oradiag_myuser/diag/clients/user_myuser/host_3852720741_80/incident

sqlnet.log (file attached) contained references to non-existent directory /usr/lib64/oracle/11.2.0.3/client/log

Accordingly with my understanding of FHS it means that non only example, but real sqlnet.ora will be useful.
Comment 2 Michael Haubenwallner gentoo-dev 2013-04-10 15:14:08 UTC
Created attachment 345128 [details]
sqlnet.ora for oracle 11.2 (instantclient)

This is how I think it should be done in the ebuild - could you please try if it fits your needs before I actually make the ebuild?

* mkdir -p /var/log/oracle/{log,network/{log,trace}}

* Put this attached sqlnet.ora into /etc/oracle/

The actual content basically is a dump of:
http://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm
created by using the browser's "Save Page as Text File" capability, and manual cleanup and reformatting in some text editor, preceding each line with '#'.

Each parameter showing "ORACLE_HOME" in the default value is configured to use "/var/log/oracle" instead, plus the default subdirectory, that is:

$ grep -v '#' sqlnet.ora 
TNSPING.TRACE_DIRECTORY=/var/log/oracle/network/trace
ADR_BASE=/var/log/oracle/log
LOG_DIRECTORY_CLIENT=/var/log/oracle/network/log
LOG_DIRECTORY_SERVER=/var/log/oracle/network/trace
LOG_FILE_CLIENT=/var/log/oracle/network/log/sqlnet.log
TRACE_DIRECTORY_SERVER=/var/log/oracle/network/trace
TRACE_FILE_CLIENT=/var/log/oracle/network/trace/cli.trc
TRACE_FILE_SERVER=/var/log/oracle/network/trace/svr.trc
Comment 3 Sergey S. Starikoff 2013-04-11 09:06:58 UTC
(In reply to comment #2)
> This is how I think it should be done in the ebuild - could you please try
> if it fits your needs before I actually make the ebuild?
> 
Of course I'll make a check.
But before I think necessary to adjust answers on some additional questions:
owner/group/mode for /var/log/oracle/ with subdirectories (default should be not writable for usually non-privilleged processes, running oracle's client)?

According to my experience, if missed sqlnet.ora and running web-server experience database connect issues, sqlnet.log is written in DocumentRoot (when it's writable by wwwowner), to moving it into /var/log/oracle/ requires not only proper config sqlnet.ora, but setting permissions (making /var/log/oracle/ directory writable by wwwowner user).
So, I think, group write permission with adding group oracle and warning message about that oracle client users should be listed in oracle group (with automatically performed update for system users while, for example, emergeing php with apache2 use enabled and oci8 support.

I thing reasonable and rather well the following settings:
/etc/group:
oracle:*:201:
(AFAIK 201 is a standard oracle's GID)
$ ls -l /var/log/ | grep oracle
drwxrwx---   2 root  oracle       512  8 апр 08:57 oracle/

The last question is about emulation of network failure:
Is my guess about simple /etc/init.d/net.lo0 stop enough?

Installing sqlnet.ora do you think that example file is not needed?
Comment 4 Michael Haubenwallner gentoo-dev 2013-04-12 13:07:02 UTC
(In reply to comment #3)
> But before I think necessary to adjust answers on some additional questions:
> owner/group/mode for /var/log/oracle/ with subdirectories (default should be
> not writable for usually non-privilleged processes, running oracle's client)?

That's an important thought indeed!

But requiring a separate OS group feels like too much overhead for simply allowing users to use another client library.

> According to my experience, if missed sqlnet.ora and running web-server
> experience database connect issues, sqlnet.log is written in DocumentRoot
> (when it's writable by wwwowner),

Most likely this is because DocumentRoot is the web-server's current directory.

Question now is how to configure sqlnet.ora to get those logfiles into the client process' current directory: By simply omitting any path component from the sqlnet.ora values? Like:

-LOG_FILE_CLIENT=/var/log/oracle/network/log/sqlnet.log
+LOG_FILE_CLIENT=sqlnet.log

> The last question is about emulation of network failure:
> Is my guess about simple /etc/init.d/net.lo0 stop enough?

Uhm, I don't think this would be enough, except for when the database server is running on the local machine eventually.

> Installing sqlnet.ora do you think that example file is not needed?

A sample config file feels necessary only when it is impossible to provide reasonable default values to be installed as a real config file.
Comment 5 Sergey S. Starikoff 2013-04-14 17:41:31 UTC
(In reply to comment #4)
> But requiring a separate OS group feels like too much overhead for simply
> allowing users to use another client library.

To my mind Oracle, even client, is a such type application, where this overhead is reasonable solution.
Or you can suggest some alternatives?

> > According to my experience, if missed sqlnet.ora and running web-server
> > experience database connect issues, sqlnet.log is written in DocumentRoot
> > (when it's writable by wwwowner),
> 
> Most likely this is because DocumentRoot is the web-server's current
> directory.
> 
> Question now is how to configure sqlnet.ora to get those logfiles into the
> client process' current directory: By simply omitting any path component
> from the sqlnet.ora values? Like:
> 
> -LOG_FILE_CLIENT=/var/log/oracle/network/log/sqlnet.log
> +LOG_FILE_CLIENT=sqlnet.log

No!
I've seen such behaviour, but it doesn't mean that I find it correct.
First of all because messages are written to sqlnet.log not when issue occures, but when server with non-empty sqlnet error buffer caught SIGHUP.
Also, to my mind DocumentRoot mustn't be writable by wwwowner.

/var/log/oracle/ is much better place for Oracle client's error messages (leaded by sqlnet.ora).

> > The last question is about emulation of network failure:
> > Is my guess about simple /etc/init.d/net.lo0 stop enough?
> Uhm, I don't think this would be enough, except for when the database server
> is running on the local machine eventually.

Ops. I've forgot to completely edit pasted path.
Of course /etc/init.d/net.lan0
Comment 6 Michael Haubenwallner gentoo-dev 2013-04-29 16:02:27 UTC
(In reply to comment #5)
> > But requiring a separate OS group feels like too much overhead for simply
> > allowing users to use another client library.
> 
> To my mind Oracle, even client, is a such type application, where this
> overhead is reasonable solution.

Maybe yes - but are there other things besides logfiles the client writes down?
If not, having another OS group still feels lots of overhead just for logfiles...

> Or you can suggest some alternatives?

(In reply to comment #1)
> sqlnet.log (file attached) contained references to non-existent directory
> /usr/lib64/oracle/11.2.0.3/client/log

I'm thinking about moving the entire instantclient (that is: ORACLE_HOME) from
/usr/lib*/oracle/PV/client/ to /opt/oracle/instantclient/, IMO this is where it really belongs to.

This move is independent of a separate oracle group, but once in /opt, I won't hesitate that much to create a log/ directory being group-writeable for 'users', so the default values of sqlnet.ora become useful - while still providing a sqlnet.ora.sample file eventually.
Comment 7 Sergey S. Starikoff 2013-05-07 13:08:33 UTC
(In reply to comment #6)
> Maybe yes - but are there other things besides logfiles the client writes
> down?
Created files were listed in comment #1.
For me it's mostly sqlnet.log.
It is very strange log file. For using php5 with oci8 support on web-server (oryginally I've got messages from sqldeveloper; web-server on Gentoo wasn't tested on behaviour with oracle connect issue), it's updated not at the time of an issue, but on server's HUP. So, custom error handling procedure is wanted. When it's done, I'll be satisfied with simple forwarding messages to /dev/null.

> If not, having another OS group still feels lots of overhead just for
> logfiles...
Asking upstream about adding syslog support (AFAIR, current versions of Oracle client doesn't support syslog logging) looks to be better solution.
But I'm not familiar with closed-source applications bug-tracking system.

> (In reply to comment #1)
> > sqlnet.log (file attached) contained references to non-existent directory
> > /usr/lib64/oracle/11.2.0.3/client/log
> 
> I'm thinking about moving the entire instantclient (that is: ORACLE_HOME)
> from
> /usr/lib*/oracle/PV/client/ to /opt/oracle/instantclient/, IMO this is where
> it really belongs to.
Looks to be reasonable.


> This move is independent of a separate oracle group, but once in /opt, I
> won't hesitate that much to create a log/ directory being group-writeable
> for 'users', so the default values of sqlnet.ora become useful - while still
> providing a sqlnet.ora.sample file eventually.
To my mind, 'users' is a common group for regular non-privilleged system users.
But oracle client often used by nologin (daemon) users.
Adding them to 'users' to my mind is not a good idea.
Using 'adm' group also looks bad.
So, I don't see alternatives for adding a system group for write (primarily log) permission (keeping in mind umask value).
Comment 8 Sergey S. Starikoff 2013-07-16 11:53:57 UTC
I've missed one item.
When user together with Apache web server, default configuration (no sqlnet.ora) of oracle client writes log messages into /var/tmp/oradiag_apache/ (/var/tmp/oradiag_apache/diag/clients/user_apache/...) directory.
Comment 9 Michael Haubenwallner gentoo-dev 2013-08-27 11:40:13 UTC
(In reply to Sergey S. Starikoff from comment #8)
> /var/tmp/oradiag_apache/

Which owner/group/permission is used there (compared to the apache daemon)?

Question: Where is the definition of this location, and how is the instantclient informed about it (ORACLE_HOME env, current directory, ...)?
Answer 1: Re-reading comment#1 gives the impression that apache daemon's HOME is /var/tmp/, and instantclient uses ~/oradiag_$USER/.

With that information in mind, I'll prefer to simply ignore the "nonexistent /usr/lib64/oracle/11.2.0.3/client/log/" message, and let instantclient fall back to ~/oradiag_$USER/ instead, no?
Comment 10 Sergey S. Starikoff 2013-09-02 12:13:08 UTC
(In reply to Michael Haubenwallner from comment #9)
> With that information in mind, I'll prefer to simply ignore the "nonexistent
> /usr/lib64/oracle/11.2.0.3/client/log/" message, and let instantclient fall
> back to ~/oradiag_$USER/ instead, no?

To my mind this solution requires at least a warning message in elog.
I'll prefer to use error handling in my code and redirect this output anywhere in /tmp or even into /dev/null.
Comment 11 Michael Haubenwallner gentoo-dev 2013-09-04 06:57:48 UTC
(In reply to Sergey S. Starikoff from comment #10)
> > With that information in mind, I'll prefer to simply ignore the "nonexistent
> > /usr/lib64/oracle/11.2.0.3/client/log/" message, and let instantclient fall
> > back to ~/oradiag_$USER/ instead, no?
> 
> To my mind this solution requires at least a warning message in elog.

Something like this?
  oracle-instantclient-${PV} does not provide an sqlnet.ora configuration file,
  redirecting oracle diagnostics into ~USER/oradiag_USER/ instead.
  It should be safe to ignore this message in sqlnet.log there:
    Directory does not exist for read/write [ORACLE_HOME/client/log] []
  See https://bugs.gentoo.org/show_bug.cgi?id=465252 for reference.
  In case you really need an sqlnet.ora file, you may want to consult
  http://search.oracle.com/search/search?q=sqlnet.ora
Comment 12 Sergey S. Starikoff 2013-10-15 09:02:14 UTC
(In reply to Michael Haubenwallner from comment #11)
> Something like this?
Well enough, but I find useful to make a little additional notes:

>   oracle-instantclient-${PV} does not provide an sqlnet.ora configuration
> file,
>   redirecting oracle diagnostics into ~USER/oradiag_USER/ instead.
+++ (written only on database/network connect issues).
>   It should be safe to ignore this message in sqlnet.log there:
>     Directory does not exist for read/write [ORACLE_HOME/client/log] []
>   See https://bugs.gentoo.org/show_bug.cgi?id=465252 for reference.
>   In case you really need an sqlnet.ora file
+++ (i.e. if you want directly analyse low-level debug info 
+++ or if you don't want to see it at all),
>    you may want to consult
>   http://search.oracle.com/search/search?q=sqlnet.ora
Comment 13 Michael Haubenwallner gentoo-dev 2013-11-11 09:36:28 UTC
(In reply to Sergey S. Starikoff from comment #12)
> (In reply to Michael Haubenwallner from comment #11)
> > Something like this?
> Well enough, but I find useful to make a little additional notes:

Included with additional notes in oracle-instantclient-basic-11.2.0.3, thank you for them.