Re: [External] : DBD::Oracle use_drcp and connection class

[Christopher Jones]

On 5/3/2022 3:44 am, Andrei A. Voropaev via dbi-users wrote:

Hello,

after looking at the sources of DBD::Oracle I am somewhat confused. OCI
offers Session pooling and Connection pooling. "DRCP" is abbreviation
for "connection pooling", but DBD::Oracle offers "session pooling",
which is not exactly the same.

Connected to this is the question about "connection class"
(ora_drcp_class). This parameter is necessary for "connection pooling",
but I couldn't find anything about it in the documentation for "session 
pooling".
Can someone please clarify this? So far it appears, that this parameter
is simply ignored in "session pooling".

Thank you.


I'll give some background, some of which you may know.

For Oracle applications, Oracle Call Interface (OCI) Connection pooling is mostly superseded by OCI Session pooling.  For various reasons to do with 
searchability & user name expectations, we tend to generically refer to 'connection pools' in applications even if at the OCI level the underlying 
Session Pool API is used. As well as giving great app scalability, the Session Pool supports all the goodies of Oracle's "Continuous Availability" 
features.


DRCP is a different pool of 'server' processes that resides on the database host.  These processes are the database end of application connections.  
DRCP is great if the database host is short of memory but it's not a solution for every connection problem.  Check the doc for when it should be used: 
https://www.oracle.com/pls/topic/lookup?ctx=dblatest=GUID-015CA8C1-2386-4626-855D-CC546DDC1086


Both DRCP and OCI Session pools support the concept of a connection class name.  This allows some isolation of session memory if pools are shared 
between distinct use cases.  Commonly session pools are not shared, so connection class isn't set for them.  Setting the class name is more (very) 
important for DRCP since it directly affects performance (as well as giving the security separation).


Although DRCP can be used for any application connection, it is more efficient if it is used in conjunction with OCI Session Pooling (or JDBC 
equivalent). I was going to write more, but I recently put out a blog which mentions DRCP and has a diagram: "Always Use Connection Pools - and How" 
https://blogs.oracle.com/opal/post/always-use-connection-pools There is also further background in miscellaneous doc like 
https://oracle.github.io/node-oracledb/doc/api.html#drcp and https://www.oracle.com/technetwork/topics/php/whatsnew/php-scalability-ha-twp-128842.pdf 
My colleague is currently working on an updated DRCP technical brief; follow the above blog to be notified when it is published.


I can't comment on the DBD::Oracle specifics too much, however there is a reference in the doc to session pooling here: 
https://metacpan.org/pod/DBD::Oracle#ora_envhp  From tracing OCI, when you set ora_drcp then an OCI Session pool is created.  This is nice.  I 
couldn't quickly see if it is efficiently used like a full application pool, but the exposed settings would imply it is.   For example settings like 
ora_drcp_min seem related to sizing of the session pool (which is in the Perl process), not to the DRCP pool (which is in the Database tier).  Sizing 
of the DRCP pool is done via PL/SQL when the pool is created.


Overall, it looks like DBD::Oracle has DRCP+Session Pooling, or no pooling at 
all.

Chris

--
https://twitter.com/ghrd

Re: DBD::Oracle insert speed

[Christopher Jones]

On 10/1/21 11:22 am, Peter Meszaros wrote:

Hi,
I have already asked this question at perlmonks.org 
 but I 
did not get proper answer.


Module DBD::Oracle 
 
has serious performance degradation at inserting rows. Except execute_array (or bind_param_array), all other insert solutions are pretty slow (eg. 
bind by name). DBD::Oracle use its own wrapper (dbimp.c, oci8.c) and seem to be implemented in an ineffective way. Comparing to otlv4 
(http://otl.sourceforge.net/otl3_intro.htm 
) 
which is a wrapper over Oracle's OCI interface written in C++ the basic http://otl.sourceforge.net/otl3_ex10.htm 
 
is much (~10 times) faster than DBD::Oracle row based insertion. DBD::Oracle implements DATA_AT_EXEC mode with OCIBindDynamic for row based 
insertion (not array) as can be seen below and this block is repeated for each value.


... dbd_bind_ph(1): bind :id <== '1774684594' (type 0 (DEFAULT (varchar))) dbd_rebind_ph() (1): rebinding :id as '17746...' (not-utf8, ftype 1 
(VARCHAR), csid 0, csform 0(0), inout 0) dbd_rebind_ph_char() (1): bind :id <== '17746...' (size 10/12/0, ptype 3(VARCHAR), otype 1 ) Changing 
maxlen to 12 dbd_rebind_ph_char() (2): bind :id <== '17746...' (size 10/12, otype 1(VARCHAR), indp 0, at_exec 1) bind :id as ftype 1 (VARCHAR) 
OCIBindByName(163ad08,1675e58,1614d20,":id",placeh_len=3,value_p=1675a00,value_sz=12,dty=1,indp=1675e78,alenp=0,rcodep=1675e70,maxarr_len=0,curelep=0 
(*=0),mode=DATA_AT_EXEC,2)=SUCCESS OCIBindDynamic(163a970,1614d20,1675e20,7fdf910dacc0,1675e20,7fdf910daf30)=SUCCESS 
OCIAttrGet(163a970,OCI_HTYPE_BIND,1675e3c,0,31,1614d20)=SUCCESS dbd_rebind_ph(): bind :id <== '17746...' (in, not-utf8, csid 873->0->873, ftype 1 
(VARCHAR), csform 0(0)->0(0), maxlen 12, maxdata_size 0) OCIAttrSet(163a970,OCI_HTYPE_BIND, 7ffda0f7f960,0,Attr=31,1614d20)=SUCCESS ...
[download] 

Is this design intentional? OCI documentation said that OCIBindDynamic can be useful at working with big data items 
https://web.stanford.edu/dept/itss/docs/oracle/10gR2/appdev.102/b14250/oci05bnd.htm#i427753 
. 
C++ code using otlv4 can process blob/clob data and it does not use OCIBindDynamic at all. So, why DBD::Oracle implemented this way?


Thanks in advance

In all language drivers for Oracle DB, the "Array DML" interface is significantly faster compared with a loop that executes individual statements.  
Reducing network round-trips is a big win. See 
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/binding-and-defining-in-oci.html#GUID-442E47AD-83A8-4959-9A73-DBFB7D3482A6


--
https://twitter.com/ghrd

Re: DBD error

[Christopher Jones]

On 17/7/20 10:02 pm, Bala GANESH wrote:

Hi Support,


"Support" ? :)


I am receiving following error while installing DBD Perl module on IBM AIX 7.1 
.Please help me to solve this.. bash-3.2# perl Makefile.PL
* ld: 
0711-736 ERROR: Input file /oracle/product/11.2.0.4/lib/libclntsh.so 
: 
        XCOFF64 object files are not allowed in 32-bit mode. make: 1254-004 The error code from the last command is 8. 
* 
pibdev:root[/oracle/PIBDEV2/post/softwares/DBD-Oracle-1.80]# export ORACLE_HOME=/oracle/product/19.7/ 
pibdev:root[/oracle/PIBDEV2/post/softwares/DBD-Oracle-1.80]# export LD_LIBRARY_PATH=/oracle/product/19.7/lib 
pibdev:root[/oracle/PIBDEV2/post/softwares/DBD-Oracle-1.80]# perl Makefile.PL Using DBI 1.643 (for perl 5.028001 on aix-thread-multi) installed in 
/usr/opt/perl5/lib/site_perl/5.28.1/aix-thread-multi/auto/DBI/ Configuring DBD::Oracle for perl 5.028001 on aix (aix-thread-multi) If you encounter 
any problem, a collection of troubleshooting guides are available under lib/DBD/Oracle/Troubleshooting. 'DBD::Oracle::Troubleshooting' is the 
general troubleshooting guide, while platform-specific troubleshooting hints live in their labelled sub-document (e.g., Win32 hints are gathered in 
'lib/DBD/Oracle/Troubleshooting/Win32.pod'). Installing on a aix, Ver#7.1 Using Oracle in /oracle/product/19.7 DEFINE _SQLPLUS_RELEASE = 
"190700" (CHAR) Oracle Version 19.7.0.0 (19.7) Found direct-link candidates: libclntsh.a libclntsh.so Oracle sysliblist: /lib/crt0_64.o -ldl -lc 
-lm -lpthreads -lodm -lbsd_r -lld -lperfstat Found header files in /oracle/product/19.7/rdbms/public. Your LIBPATH env var is set to '' WARNING: 
Your LIBPATH env var doesn't include '/oracle/product/19.7/lib' but probably needs to.


Did you try setting LIBPATH instead of LD_LIBRARY_PATH? 
https://www.ibm.com/support/pages/libpath-environment-variables-aix-platforms

Chris


client_version=19.7 DEFINE= -DUTF8_SUPPORT -DORA_OCI_VERSION=\"19.7.0.0\" -DORA_OCI_102 -DORA_OCI_112 Checking for functioning wait.ph 
 System: 
perl5.028001 aix fvt-p7a2-lp2 1 7 668a7a00 Compiler:   xlc_r -O -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1 -qnoansialias 
-qlanglvl=extc99 -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 -D_LARGE_FILES Linker:     /usr/bin/ld Sysliblist: /lib/crt0_64.o -ldl -lc -lm 
-lpthreads -lodm -lbsd_r -lld -lperfstat Linking with -lclntsh. Unrecognized argument in LIBS ignored: '/lib/crt0_64.o' 
LD_RUN_PATH=/oracle/product/19.7/lib:/oracle/product/19.7/rdbms/lib Using DBD::Oracle 1.80. Using DBD::Oracle 1.80. Using DBI 1.643 (for perl 
5.028001 on aix-thread-multi) installed in /usr/opt/perl5/lib/site_perl/5.28.1/aix-thread-multi/auto/DBI/ Generating a Unix-style Makefile Writing 
Makefile for DBD::Oracle Writing MYMETA.yml and MYMETA.json pibdev:root[/oracle/PIBDEV2/post/softwares/DBD-Oracle-1.80]# make Skip 
blib/arch/auto/DBD/Oracle/dbdimp.h (unchanged) Skip blib/arch/auto/DBD/Oracle/Oracle.h (unchanged) Skip 
blib/lib/DBD/Oracle/Troubleshooting/Linux.pod (unchanged) cp mk.pm 
 
blib/arch/auto/DBD/Oracle/mk.pm 
 Skip 
blib/lib/DBD/Oracle/Troubleshooting/Sun.pod (unchanged) Skip blib/lib/DBD/Oracle/Troubleshooting/Aix.pod (unchanged) Skip 
blib/lib/DBD/Oracle/Troubleshooting/Vms.pod (unchanged) Skip blib/lib/DBD/Oracle/GetInfo.pm (unchanged) Skip 
blib/lib/DBD/Oracle/Troubleshooting/Hpux.pod (unchanged) Skip blib/lib/DBD/Oracle/Troubleshooting/Cygwin.pod (unchanged) Skip 
blib/lib/DBD/Oracle/Troubleshooting/Win32.pod (unchanged) Skip blib/lib/DBD/Oracle/Troubleshooting/Macos.pod (unchanged) Skip 
blib/lib/DBD/Oracle/Troubleshooting.pod (unchanged) Skip blib/lib/DBD/Oracle.pm (unchanged) Skip blib/lib/DBD/Oracle/Object.pm (unchanged) Skip 
blib/lib/DBD/Oracle/Troubleshooting/Win64.pod (unchanged) Skip blib/arch/auto/DBD/Oracle/ocitrace.h (unchanged) Running Mkbootstrap for Oracle ()   
      chmod 644 "Oracle.bs"          "/usr/bin/perl" -MExtUtils::Command::MM -e 'cp_nonempty' -- Oracle.bs blib/arch/auto/DBD/Oracle/Oracle.bs 644   
      xlc_r -c  -I/oracle/product/19.7/rdbms/public -I/usr/opt/perl5/lib/site_perl/5.28.1/aix-thread-multi/auto/DBI  -D_ALL_SOURCE -D_ANSI_C_SOURCE 
-D_POSIX_SOURCE 

Re: Perl script excessively executing statement

[Christopher Jones]

On 18/2/20 9:51 am, JohnD Blackburn wrote:

The problem is not about how well the SQL runs.  It runs in less than a second, 
and it should only be getting executed 12 times an hour.

What I'm trying to do is to figure out how the SQL statement got executed 1000 
times more than it should have done (which brought the db to its knees for 50 
minutes with nothing being able to connect).  If there is something in DBI that 
is automatically re-executing for some unknown reason, I need to be able to 
mange how often it retries, and how many times, and even how long between 
re-executions.   The logic in the script does not account for 12000 executions 
in a 1 hour period, so I need to figure out how it happened so I can prevent it 
happening in the future.

There is locking around the perl script execution so that the perl script can't 
be started if the previous execution has not completed, and the logs show that 
in the 50 minute period in question, the script only ran once .  It didn't 
report any errors, and appeared to eventually exit successfully.  The only 
thing abnormal is that Oracle Stats Pack shows that SQL statement in the script 
executed 12000 times and was consuming all available DB threads.  I've reviewed 
the DBI debugging links provided by Brian Fennell and have now added some DBI 
debugging to the script in the hopes that I can get more info on how DBI is 
behaving.

I came to this mailing list because it seems to me that the problem is with 
DBI, either I've hit some bug, or my usage is not correct (or there is some 
other problem deeper down in the stack)

Cheers,
John


The scenario is unexpected, so I think you're at the 'take an aspirin and if pain persists...' stage.  Add the extra error checking, locking and 
logging, and then see if it happens again.


As well as the extra locking around the script invocation, you could ensure the script itself doesn't execute the statement unnecessarily, e.g. in a 
loop after a failure.


Make sure you're using bind variables appropriately, since these can reduce overhead on the DB.  See https://metacpan.org/pod/DBD::Oracle#Placeholders 
They are also important for reducing SQL Injection attacks.


Chris








-Original Message-
From: Steven Lembark 
Sent: Tuesday, 18 February 2020 3:47 AM
To: dbi-users@perl.org
Cc: lemb...@wrkhors.com
Subject: Re: Perl script excessively executing statement

CAUTION: This email originated from outside of the organization. Do not click 
links or open attachments unless you recognize the sender and know the content 
is safe.


On Thu, 13 Feb 2020 06:58:37 +
JohnD Blackburn  wrote:


The DBA pulled info from some cache that showed the SQL statement from
the script was executed 12610 times.

So if I were to add an “or die $!” statement after the
fetchrow_array(), that might fix things?  (or prevent it from trying
to continue if there are errors?) Or catch errors after the execute
statement?

Q: What are you really trying to accomplish?

Basic rules:

If you need to run SQL more than once parepare it.

If you are not sure how well it runs then you can evaluate it beorehand -- 
which also spots errors, can help track down un-used indexes.

As noted, generating a single query, preparing and running once, then 
extracting the rows (preferably as arrays rather than hashes) will be the most 
effecient way.

The database is going to do a much better job filtering anything it can locally 
in native format beore you convert it to ASCII char strings and pull the same 
content across a LAN.



--
Steven Lembark3646 Flora Place
Workhorse ComputingSt. Louis, MO 63110
lemb...@wrkhors.com+1 888 359 3508


--
https://twitter.com/ghrd

Re: Perl script excessively executing statement

[Christopher Jones]

On 13/2/20 5:58 pm, JohnD Blackburn wrote:


The DBA pulled info from some cache that showed the SQL statement from the 
script was executed 12610 times.

So if I were to add an “or die $!” statement after the fetchrow_array(), that might fix things? (or prevent it from trying to continue if there are 
errors?)


Or catch errors after the execute statement?


Error checking after each call is a good idea.

The prepare() (if my implementation refreshing skills are accurate) maps to an Oracle call that does a lightweight parse to check things like bind 
variables and quotes.  Most errors will come from the execute() - bogus syntax, wrong columns, DB space errors etc.


Chris



*From:* Christopher Jones 
*Sent:* Thursday, 13 February 2020 12:56 PM
*To:* dbi-users@perl.org; JohnD Blackburn 
*Subject:* Re: Perl script excessively executing statement

*CAUTION:*This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know 
the content is safe.


On 13/2/20 11:13 am, JohnD Blackburn wrote:

Is that a behavior of DBI or DBD::Oracle?

Maybe your script, if you are blindly looping when it gets an error?  Overall, I don't think there is enough information to point directly at a 
cause.  Presumably the DBA meant that a SQL statement (of some kind) was e


xecuted 12610 times; not that your script was invoked that number of times.

My script says prepare or die, so any retries would have had to come 
directly from the DBD::Oracle module

Script basically says:

use DBD::Oracle;

my $dbh = DBI->connect("dbi:Oracle:$dbSID", $user, $passwd, {AutoCommit => 
0 });

my $statement = <Somewhere you might want to tune ora_prefetch_rows or RowCacheSize, depending how many rows you expect the query to return.  This is unrelated to 
your question.


my $arraySelect = $dbh->prepare($statement) or die $!;

$arraySelect->execute();

while ( my ($var1, $var2, $var3, $var4) = $arraySelect->fetchrow_array() ) {

    

}

$arraySelect->finish();

$dbh->disconnect();

I don’t work much with the DBD::Oracle module or perl and what I have is 
just reworked from scripts others have written.

Is there parameters for the DBD::Oracle functions that can affect their 
behavior? If this is behavior of the execute function, I really need to
be able to reign it in to limit its impact if it ever does it again.  Not 
knowing why the issue triggered in the 1^st place, I don’t know how to
reproduce it to test if any mitigations are sufficient.

You can set sqlnet.ora parameters to bound the time taken for connection and statement execution.  Refer to 
https://cx-oracle.readthedocs.io/en/latest/user_guide/ha.html#network-configuration 
<https://urldefense.com/v3/__https://eur03.safelinks.protection.outlook.com/?url=https*3A*2F*2Fcx-oracle.readthedocs.io*2Fen*2Flatest*2Fuser_guide*2Fha.html*23network-configuration=02*7C01*7Cjohnd.blackburn*40au.abb.com*7C75ee9e961c994c802fe708d7b030497c*7C372ee9e09ce04033a64ac07073a91ecd*7C0*7C0*7C637171593755922211=wVBUPnUXOnie2z9yYwGPwC4J*2FtiDtMUXl1DXAjKZtyo*3D=0__;JSUlJSUlJSUlJSUlJSUlJSUl!!GqivPVa7Brio!P9p6l_vepYYnGyu45_vqxFEUnEd_tpXLUZ8DxElcAZGs05cgs-IJxDx4g8H-qje3S1gj1g$> 
and https://oracle.github.io/node-oracledb/doc/api.html#connectionha 
<https://urldefense.com/v3/__https://eur03.safelinks.protection.outlook.com/?url=https*3A*2F*2Foracle.github.io*2Fnode-oracledb*2Fdoc*2Fapi.html*23connectionha=02*7C01*7Cjohnd.blackburn*40au.abb.com*7C75ee9e961c994c802fe708d7b030497c*7C372ee9e09ce04033a64ac07073a91ecd*7C0*7C0*7C637171593755932204=iZPYDmpjTj*2FPw*2FShQqdiSQIccucpjLqO1chp*2BQq9I4Q*3D=0__;JSUlJSUlJSUlJSUlJSUlJSUlJQ!!GqivPVa7Brio!P9p6l_vepYYnGyu45_vqxFEUnEd_tpXLUZ8DxElcAZGs05cgs-IJxDx4g8H-qjdP4_-wrg$> 
since the sqlnet.ora settings will be the same for DBD::Oracle -  the network layer is common across all the C-based drivers.  Depending on your 
requirements, you may want to sleep between retries.


Chris

Cheers,

John

*From:* Geoffrey Rommel  <mailto:wgrom...@gmail.com>
*Sent:* Thursday, 13 February 2020 2:56 AM
*To:* JohnD Blackburn  
<mailto:johnd.blackb...@au.abb.com>
*Cc:* dbi-users@perl.org <mailto:dbi-users@perl.org>
*Subject:* Re: Perl script excessively executing statement

*CAUTION:*This email originated from outside of the organization. Do not 
click links or open attachments unless you recognize the sender and
know the content is safe.

I don't work with Oracle, but here's a guess. Maybe the database was 
unresponsive before your script started running, not as a result of it. If
so, maybe your script tried to prepare the statement, failed, and retried 
12000 times. Eventually the DBA noticed the problem and restarted the
database, at which time your script was terminated along with everything 
else.

On Tue, Feb 11, 2020 at 11:56 PM JohnD Blac

Re: Perl script excessively executing statement

[Christopher Jones]

On 13/2/20 11:13 am, JohnD Blackburn wrote:


Is that a behavior of DBI or DBD::Oracle?

Maybe your script, if you are blindly looping when it gets an error?  Overall, I don't think there is enough information to point directly at a 
cause.  Presumably the DBA meant that a SQL statement (of some kind) was executed 12610 times; not that your script was invoked that number of times.



My script says prepare or die, so any retries would have had to come directly 
from the DBD::Oracle module

Script basically says:

use DBD::Oracle;

my $dbh = DBI->connect("dbi:Oracle:$dbSID", $user, $passwd, {AutoCommit => 0 });

my $statement = prepare($statement) or die $!;

$arraySelect->execute();

while ( my ($var1, $var2, $var3, $var4) = $arraySelect->fetchrow_array() ) {

    

}

$arraySelect->finish();

$dbh->disconnect();

I don’t work much with the DBD::Oracle module or perl and what I have is just 
reworked from scripts others have written.

Is there parameters for the DBD::Oracle functions that can affect their behavior? If this is behavior of the execute function, I really need to be 
able to reign it in to limit its impact if it ever does it again.  Not knowing why the issue triggered in the 1^st place, I don’t know how to 
reproduce it to test if any mitigations are sufficient.


You can set sqlnet.ora parameters to bound the time taken for connection and statement execution.  Refer to 
https://cx-oracle.readthedocs.io/en/latest/user_guide/ha.html#network-configuration and 
https://oracle.github.io/node-oracledb/doc/api.html#connectionha since the sqlnet.ora settings will be the same for DBD::Oracle - the network layer is 
common across all the C-based drivers. Depending on your requirements, you may want to sleep between retries.


Chris


Cheers,

John

*From:* Geoffrey Rommel 
*Sent:* Thursday, 13 February 2020 2:56 AM
*To:* JohnD Blackburn 
*Cc:* dbi-users@perl.org
*Subject:* Re: Perl script excessively executing statement

*CAUTION:*This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know 
the content is safe.


I don't work with Oracle, but here's a guess. Maybe the database was unresponsive before your script started running, not as a result of it. If so, 
maybe your script tried to prepare the statement, failed, and retried 12000 times. Eventually the DBA noticed the problem and restarted the 
database, at which time your script was terminated along with everything else.


On Tue, Feb 11, 2020 at 11:56 PM JohnD Blackburn mailto:johnd.blackb...@au.abb.com>> wrote:

Hi all,

I have a perl script in my monitoring system that has been working for 
months without an issue.

Basically, it connects to an Oracle 12c database, prepares a statement, 
then it executes the statement, then it has a while loop to process the
returned rows.

So under normal conditions the statement is executed once every 5 minutes.

Now on Friday last week, it did something really strange which I cannot 
account for the behaviour.

According to the DBA, the statement in the script was executed 12610 times 
over a 50 minute period causing the database to become
non-responsive.  The DBAs also stated that the script only connected to the 
database once at the beginning of the 50 minute period.  Average
execution time of the statement was 0.26 seconds.

According to the log for my script, the script only executed once at the 
beginning of the 50 minute period, and then after that, returned to
executing every 5 minutes.

Since that incident, the statememt in question has only executed the 
expected 12 times per hour.

I have yet to find a satisfactory reason the SQL statement from this perl 
script executed so many times in the 50 minute period.

Script is running on an Oracle Linux 7.7 server with;

  * oracle 12c client installed
  * perl 5.16.3
  * perl-DBI 1.627-4 ( from Oracle Linix Latest yum repository)
  * perl-DBD-ODBC 1.50.-3 (from EPEL)
  * DBD::Oracle 1.80 (from CPAN)

Oracle 12 database is on a remote server.

Anyone have any ideas why the SQL statement would have been executed 12000+ 
times in a 50minute period, when the script and its schedule should
not have executed the SQL any more frequiently than 12 times an hour?

Regards,

John


--
https://twitter.com/ghrd

Re: Hunting down (possible) memory leak in DBD::Oracle

[Christopher Jones]

On 16/1/18 9:17 am, Fennell, Brian wrote:


$ egrep -B1 -A20 -i 'invalid write' 
/copy/sandbox/feeds/data/search4_1/valgrind-log.txt | head -22
==19402==
==19402== Invalid write of size 4
==19402==at 0xBD747E6: __intel_ssse3_rep_memcpy (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xBD6CE95: _intel_fast_memcpy.P (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)


Valgrind doesn't do the greatest on binaries built with the Intel compiler:

https://bugs.kde.org/show_bug.cgi?id=286769
https://bugs.kde.org/show_bug.cgi?id=139776

Chris

--
http://twitter.com/ghrd

Re: I probably mentioned this the last time I installed DBD:Oracle but I couldn't find it...

[Christopher Jones]

On 18/10/17 8:06 am, Bruce Johnson wrote:

Oracle’s latest instant client for 64-bit linux puts the demo.mk file in 
/usr/share/oracle/12.2/client64/demo

The DBD::Oracle installer only looks in /usr/share/oracle/12.2/client64/ to 
find it and thus installing from cpan fails.

Dunno if this is really a reportable bug or not.  I just put a symlink to it in 
the expected directory and everything worked just fine.


If it's annoying, why not log it at 
https://github.com/pythian/DBD-Oracle/issues so it can at least be tracked?

Chris

--
http://twitter.com/ghrd

Re: Perl DBI libraries for connecting 12c

[Christopher Jones]

On 10/10/17 3:42 pm, John R Pierce wrote:

On 10/9/2017 6:32 PM, Lincoln A Baxter wrote:

You can solve this problem by building your own perl with gcc. It's not that 
hard.
After build, install it in a location on path (actually you pick the install 
location has part of configuring the build).
Then you can build the DBI, and DBD::oracle. You do need to have the oracle 
client libraries and headers (which you have to get from Oracle).



or you could install the perl from sunfreeware/unix packages or opencsw.

--
john r pierce, recycling bits in santa cruz



For the record, Instant Client is available in Solaris packages: 
https://blogs.oracle.com/jmcp/oracle-instant-client:-now-available-in-ips
(This may not help Michael, who is using an older version of Solaris)

Chris

--
http://twitter.com/ghrd

Re: (Fwd) Oracle.pm

[Christopher Jones]

On 8/3/17 3:45 am, Bruce Johnson wrote:



On Mar 6, 2017, at 5:59 PM, Gowtham > wrote:

Hi Ron,

I did that.
I have two scenarios here. Both the scenarios have same 
($dbh,$dbname,$user,$auth,$attr) values.
Scenario 1: executed code with ORACLE_HOME set to correct value.

Scenario 2: executed code with incorrect/wrong ORACLE_HOME env variable.

- So my code returns undef in the second scenario. I'm trying to understand the execution part inside the subroutine _login to establish the 
dependency of ORACLE_HOME on DBI connection.


$ORACLE_HOME is critical to any API involving oracle; this is not a function of _login or exclusive to DBI. If it’s set incorrectly or undef, you 
will always get a failure like this.


Without more details on your particular application, it’s difficult to determine why your script is failing, but most common is that the perl module 
is executed via some sort of process spawning a new shell and the environment in that shell is not properly set.


What you need to ensure that the environment variable $ORACLE_HOME is set in the process that is failing; either explicitly in the perl script  via 
$ENV{‘ORACLE_HOME’}= 'path to oracle_home’; or by ensuring that the defaults for the process include that variable.


In Apache you can set it via a SetEnv statement:

SetEnv ORACLE_HOME /path/to/oracle/home

Be aware that more than just ORACLE_HOME is needed, you also need to set LD_LIBRARY_PATH at a minimum, and perhaps TNS_ADMIN , ORACLE_BASE and 
ORACLE_SID.


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs


As a general reminder for future people stumbling upon this thread, ORACLE_HOME is only needed if the code/driver/app is linking with the Oracle 
libraries from a full Oracle DB install, or with the full Oracle client install.  The variable is not needed (and almost never should be set) if using 
Oracle Instant Client.


Chris

--
http://twitter.com/ghrd

Re: DBD::SQLAnywhere SEGV when Oracle handle exists

[Christopher Jones]

On 7/3/17 4:38 am, Douglas Wilson wrote:

After some searching, I tried using the ora_connect_with_default_signals with 
INT and CHLD, and tried setting BEQUEATH_DETACH=yes in a local
sqlnet.ora, but still same result.



Stack trace?  Versions?

Have you worked back from the point of the crash, i.e. looked at SQLAnywhere to 
see what it is doing?

Chris



On Mar 4, 2017 5:17 AM, "Martin J. Evans" > wrote:

On 02-Mar-17 10:54 PM, Douglas Wilson wrote:

DBD::SQLAnywhere seems to work ok for Sybase IQ, but if I first create a
DBD:Oracle handle, the SQLAnywhere connect hangs for a while, and
eventually segfaults. FYI on redhat Linux.


I don't have the info to hand right now but I've heard similar reports 
before. I think it had something to do with the method used to connect to
Oracle and if that method is chosen it captures SIGCHLD and maybe another 
signal as well.

Martin
--
Martin J. Evans
Wetherby, UK



--
http://twitter.com/ghrd

Re: (Fwd) Issues with Oracle DBD in Cygwin

[Christopher Jones]

On 18/09/2016 12:41 AM, Tim Bunce wrote:

- Forwarded message from "Neargarder, Keith" 
 -

Date: Fri, 16 Sep 2016 22:16:11 +
From: "Neargarder, Keith" 
To: "'t...@cpan.org'" , "'byter...@cpan.org'" , "'yan...@cpan.org'" 
, "'mjev...@cpan.org'" 
Subject: Issues with Oracle DBD in Cygwin





   1.   after unpacking the instant client files into 
c:\oracle\instantclient_12_1 (set as ORACLE_HOME)
   I do NOT have a "lib" or "rdbms/lib" subdirectory - am I missing something? 
I have been using
   LD_LIBRARY_PATH=$ORACLE_HOME/lib and I see it and the rdbms/lib referenced 
in LD_RUN_PATH but those
   directories do not exist. If it were a problem I sure would expect some sort 
of error message but
   nothing jumps out at me???


If you are using Instant Client, set LD_LIBRARY_PATH to the directory
where you unzipped it.  In your case c:\oracle\instantclient_12_1

[I'm not a cygwin user]

Chris

--
http://twitter.com/ghrd

Re: ORA-12154 - (DBD: login failed)

[Christopher Jones]

You possibly need to bring your 10g client up to date with patches so authentication succeeds; I've seen similar things in the past.  But, in general, 
why use 10g client if you are connecting to 12c?  You may as well use the 12c client.


I forget whether 8i used service names or only the old "SID" names, but check you are using the correct tnsnames.ora syntax if you have upgraded the 
oracle client.


Chris

On 9/08/2016 1:36 AM, Mani, Arunkumar (BMS - India GDC) wrote:


I'll probably explain it with a piece of code and output, so everybody can 
explain the problem I face clearly.

If you look at 2 codes, the only change is I modified 8i client with 10g 
client. After that it couldn’t resolve the TNS connection string.

_Perl code with 8i client - works:_

#!/usr/local/bin/perl

#use DBI

#use strict;

use strict;

use lib '/u01/home/oracle/admin/site/lib/perl';

use DBI;

$ENV{ORACLE_HOME} = '/u01/home/oracle/product/8.1.7.4';

$ENV{TNS_ADMIN} = '/home/httpd/cgi-bin/oradba';

my $sid = "ECLD.WORLD";

my $usr = "mania2";

my $pas = "blah";

my $dbh = DBI->connect("dbi:Oracle:tns:$sid", $usr, $pas) or die $DBI::errstr;

Output:

bash-2.03$ ./perl_testing1.pl

DBI->connect(tns:ECLD.WORLD) failed: ORA-01017: invalid username/password; 
logon denied (DBD: login failed) at ./perl_testing1.pl line 14

ORA-01017: invalid username/password; logon denied (DBD: login failed) at 
./perl_testing1.pl line 14.

bash-2.03$

_Perl code with 10g client - not working:_

#!/usr/local/bin/perl

#use DBI

#use strict;

use strict;

use lib '/u01/home/oracle/admin/site/lib/perl';

use DBI;

$ENV{ORACLE_HOME} = '/u01/home/oracle/product/10.2.0';

$ENV{TNS_ADMIN} = '/home/httpd/cgi-bin/oradba';

my $sid = "ECLD.WORLD";

my $usr = "mania2";

my $pas = "blah";

my $dbh = DBI->connect("dbi:Oracle:tns:$sid", $usr, $pas) or die $DBI::errstr;

Output:

bash-2.03$ ./perl_testing1.pl

DBI->connect(tns:ECLD.WORLD) failed: ORA-12154: TNS:could not resolve the 
connect identifier specified (DBD: login failed) at ./perl_testing1.pl line 14

ORA-12154: TNS:could not resolve the connect identifier specified (DBD: login 
failed) at ./perl_testing1.pl line 14.

bash-2.03$

Arunkumar Mani

ITO Service Delivery Consultant

Database Engineering, BMS Account

arunkumar.m...@hpe.com

+91 80 338 59305  Office

+91 74062 71026  Mobile

Bangalore, Karnataka/India

hpe.com

-Original Message-
From: Howard, Chris [mailto:howa...@prpa.org]
Sent: Monday, August 8, 2016 7:55 PM
To: Mani, Arunkumar (BMS - India GDC) ; Nelson, Erick ; mohammed.must...@wipro.com; 
dbi-users@perl.org

Subject: RE: ORA-12154 - (DBD: login failed)

ORACLE_HOME not defined correctly?

(I would test by printing out all ENV from within script)

-Original Message-

From: Mani, Arunkumar (BMS - India GDC) [mailto:arunkumar.m...@hpe.com]

Sent: Monday, August 08, 2016 5:38 AM

To: Nelson, Erick; mohammed.must...@wipro.com ; 
dbi-users@perl.org 

Subject: RE: ORA-12154 - (DBD: login failed)

Instead of @, we define the connection string in TWO_TASK, and it 
still looks for tnsnames.ora or sqlnet.ora for resolution and it fails.

Arunkumar Mani

ITO Service Delivery Consultant

Database Engineering, BMS Account

arunkumar.m...@hpe.com 

+91 80 338 59305  Office

+91 74062 71026  Mobile

Bangalore, Karnataka/India

hpe.com

-Original Message-

From: Nelson, Erick [mailto:erick.nel...@hdsupply.com]

Sent: Monday, August 8, 2016 5:01 PM

To: Mani, Arunkumar (BMS - India GDC) >; mohammed.must...@wipro.com 
; dbi-users@perl.org 


Subject: Re: ORA-12154 - (DBD: login failed)

Did you try TWO_TASK env var ?





Erick Nelson

Cell 858-740-6523

Home 760-930-0461

From: "Mani, Arunkumar (BMS - India GDC)" >>


Date: Monday, August 8, 2016 at 1:14 AM

To: "mohammed.must...@wipro.com " 
>>, 
"dbi-users@perl.org " 
>>


Subject: RE: ORA-12154 - (DBD: login failed)

All were done earlier. The problem is perl doesn't recognize my sqlnet.ora or 
tnsnames.ora, though I define TNS_ADMIN correctly in the script.

Note: This was working when we used Oracle 8 client.

Arunkumar Mani

ITO Service Delivery Consultant

Re: ORA-12154 - (DBD: login failed)

[Christopher Jones]

DBD::Oracle uses Oracle's OCI not JDBC.
The official OCI Support matrix is Document #207303.1 (for those with MOS 
access).

Chris

On 9/08/2016 1:07 AM, Mike Towery wrote:

*Which version of JDBC drivers support which version of Oracle database?*

Please refer to the table below for the Oracle database versions supported by JDBC drivers. Best Practice that we recommend is, JDBC driver version 
should always be either same as or higher than the Oracle database version being used in order to leverage the latest capabilities of the JDBC driver.


*Interoperability Matrix*   *Database 12.1.0.x* *Database 11.2.0.x* 
*Database 11.1.0.x*
*JDBC 12.1.0.x* 
/Yes/

/Yes/

/Yes/
*JDBC 11.2.0.x* 
/Yes/

/Yes/

/Yes/
*JDBC 11.1.0.x* 
/Yes/

/Yes/

/Yes/


--
*What are the various supported Oracle database version vs JDBC compliant 
versions vs JDK version supported?

*The table below mentions the supported Oracle database versions, equivalent  supported JDK versions, JDBC version compliant to in each of the 
releases and also mentions the JDBC jar file names which need to be used for specific releases.

*Oracle Database version
*   *JDK Version supported
*   *JDBC specification compliance
*   *JDBC Jar files specific to the release*
12.1 or 12cR1   JDK8, JDK 7 & JDK 6 JDBC 4.1 in the JDK 8 & JDK 7 
drivers
JDBC 4.0 in the JDK 6 drivers   ojdbc7.jar for JDK 8 and JDK 7
ojdbc6.jar for JDK 6
11.2 or 11gR2   JDK 6 & JDK 5
JDK 7 & JDK 8 supported in 11.2.0.3 and 11.2.0.4JDBC 4.0 in the JDK 6 
drivers
JDBC 3.0 in the JDK 5 drivers   ojdbc6.jar for JDK 8, JDK 7 and JDK 6.
ojdbc5.jar for JDK 5
11.1 or 11gR1   JDK 6 & JDK 5   JDBC 4.0 in the JDK 6 drivers   JDBC 
3.0 in the JDK 5 drivers   ojdbc6.jar for JDK 6
ojdbc5.jar for JDK 5



Reference: http://www.oracle.com/technetwork/topics/jdbc-faq-090281.html



--

On Mon, Aug 8, 2016 at 9:40 AM, Mike Towery > wrote:

I don't think the 10G Oracle client works with Oracle 12.  Have you tried 
the Oracle 12 client?

On Mon, Aug 8, 2016 at 9:25 AM, Howard, Chris > wrote:



ORACLE_HOME not defined correctly?

(I would test by printing out all ENV from within script)



-Original Message-
From: Mani, Arunkumar (BMS - India GDC) [mailto:arunkumar.m...@hpe.com 
]
Sent: Monday, August 08, 2016 5:38 AM
To: Nelson, Erick; mohammed.must...@wipro.com 
; dbi-users@perl.org 

Subject: RE: ORA-12154 - (DBD: login failed)

Instead of @, we define the connection string in TWO_TASK, 
and it still looks for tnsnames.ora or sqlnet.ora for resolution and
it fails.

Arunkumar Mani
ITO Service Delivery Consultant
Database Engineering, BMS Account

arunkumar.m...@hpe.com 
+91 80 338 59305   Office
+91 74062 71026   Mobile

Bangalore, Karnataka/India
hpe.com 



-Original Message-
From: Nelson, Erick [mailto:erick.nel...@hdsupply.com 
]
Sent: Monday, August 8, 2016 5:01 PM
To: Mani, Arunkumar (BMS - India GDC) >; mohammed.must...@wipro.com
; dbi-users@perl.org 

Subject: Re: ORA-12154 - (DBD: login failed)

Did you try TWO_TASK env var ?
>
>


Erick Nelson
Cell 858-740-6523 
Home 760-930-0461 


From: "Mani, Arunkumar (BMS - India GDC)" >>
Date: Monday, August 8, 2016 at 1:14 AM
To: "mohammed.must...@wipro.com 
>"
>>,
"dbi-users@perl.org >" 

Re: ORA-12154 - (DBD: login failed)

[Christopher Jones]

You can connect to 12c using 10gR2 client.

Chris

On 9/08/2016 12:40 AM, Mike Towery wrote:

I don't think the 10G Oracle client works with Oracle 12.  Have you tried the 
Oracle 12 client?

On Mon, Aug 8, 2016 at 9:25 AM, Howard, Chris > wrote:



ORACLE_HOME not defined correctly?

(I would test by printing out all ENV from within script)



-Original Message-
From: Mani, Arunkumar (BMS - India GDC) [mailto:arunkumar.m...@hpe.com 
]
Sent: Monday, August 08, 2016 5:38 AM
To: Nelson, Erick; mohammed.must...@wipro.com 
; dbi-users@perl.org 

Subject: RE: ORA-12154 - (DBD: login failed)

Instead of @, we define the connection string in TWO_TASK, and 
it still looks for tnsnames.ora or sqlnet.ora for resolution and it fails.

Arunkumar Mani
ITO Service Delivery Consultant
Database Engineering, BMS Account

arunkumar.m...@hpe.com 
+91 80 338 59305   Office
+91 74062 71026   Mobile

Bangalore, Karnataka/India
hpe.com 



-Original Message-
From: Nelson, Erick [mailto:erick.nel...@hdsupply.com 
]
Sent: Monday, August 8, 2016 5:01 PM
To: Mani, Arunkumar (BMS - India GDC) >; mohammed.must...@wipro.com
; dbi-users@perl.org 

Subject: Re: ORA-12154 - (DBD: login failed)

Did you try TWO_TASK env var ?
>
>


Erick Nelson
Cell 858-740-6523 
Home 760-930-0461 


From: "Mani, Arunkumar (BMS - India GDC)" >>
Date: Monday, August 8, 2016 at 1:14 AM
To: "mohammed.must...@wipro.com 
>"
>>,
"dbi-users@perl.org >" >>
Subject: RE: ORA-12154 - (DBD: login failed)

All were done earlier. The problem is perl doesn't recognize my sqlnet.ora 
or tnsnames.ora, though I define TNS_ADMIN correctly in the script.
Note: This was working when we used Oracle 8 client.

Arunkumar Mani
ITO Service Delivery Consultant
Database Engineering, BMS Account

arunkumar.m...@hpe.com >
+91 80 338 59305   Office
+91 74062 71026   Mobile

Bangalore, Karnataka/India
hpe.com 

[hpesm_pri_grn_pos_email_06]

From: mohammed.must...@wipro.com 
>
[mailto:mohammed.must...@wipro.com ]
Sent: Thursday, August 4, 2016 8:53 PM
To: Mani, Arunkumar (BMS - India GDC) >>; dbi-users@perl.org 
>
Subject: Re: ORA-12154 - (DBD: login failed)




Testing: Manually, try connecting to DB using current .ora file content.





Solution:  update .ora file with right
Ip:
Port:
Sid/service name:




Regards,
Mustafa
+44-7440 56 12 32 


From: Mani, Arunkumar (BMS - India GDC) >>
Sent: Thursday, August 4, 2016 8:41 PM
To: dbi-users@perl.org >
Subject: ORA-12154 - (DBD: login failed)


** This mail has been sent from an external source **

Hi,



We have a perl script which is used to connect to all our target databases 
( close to 1000) and fetch the user related information and inturn
compare it with our enterprise directory which is used to clear the 
inactive users periodically. This was a very old script and it was using
Oracle 8i client. Since most of