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.
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.
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
(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?
(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.
(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
(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.
(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).
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.
(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?
(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.
(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
(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
(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.
