Category Archives: Perl

An introduction to collectl

Some of you may have seen on twitter that I was working on understanding collectl. So why did I start with this? First of all, I was after a tool that records a lot of information on a Linux box. It can also play information back, but this is out of scope of this introduction.

In the past I have used nmon to do similar things, and still love it for what it does. Especially in conjunction with the nmon-analyzer, an Excel plug in it can create very impressive reports. How does collectl compare?

Getting collectl

Getting collectl is quite easy-get it from sourceforge: http://sourceforge.net/projects/collectl/

The project website including very good documentation is available from sourceforge as well, but uses a slightly different URL: http://collectl.sourceforge.net/

I suggest you get the archive-independent RPM and install it on your system. This is all you need to get started! The impatient could type “collectl” at the command prompt now to get some information. Let’s have a look at the output:

$ collectl
waiting for 1 second sample...
#<--------CPU--------><----------Disks-----------><----------Network---------->
#cpu sys inter  ctxsw KBRead  Reads KBWrit Writes   KBIn  PktIn  KBOut  PktOut
1   0  1163  10496    113     14     18      4      8     55      5      19
0   0  1046  10544      0      0      2      3    164    195     30      60
0   0  1279  10603    144      9    746    148     20     67     11      19
3   0  1168  10615    144      9    414     69     14     69      5      20
1   0  1121  10416    362     28    225     19     11     71      8      35
Ouch!

The “ouch” has been caused by my CTRL-c to stop the execution.

Collectl is organised to work by subsystems, the standard option is to print CPU, disk and network subsystem, aggregated.

Continue reading

Advertisements

Compile DBD::Oracle 1.23 with Oracle 11.2 client

This article discusses how to compile DBD::Oracle for Oracle client 11.2 x86-64 on Linux (Fedora 12 to be precise). I recommend not to mess around with your distribution’s perl, that’s why I will base it on ActivePerl 5.10 from Activestate.com. If you (and you system administrator don’t mind mangling your distribution’s perl you might skip the first bit and continue straight with the section DBD::Oracle.

This is particularly useful for installations on nagios servers to enable monitoring of Oracle databases directly through perl’s DBD::Oracle without those ugly “sqlplus -S….<<EOF” constructs I see far too often.

Active Perl

I downloaded active perl straight from their website, it’s version 5.10.1.1006 for glibc 2.3.3-291086. Download and unzip the file:

gunzip -cd ActivePerl-5.10.1.1006-x86_64-linux-glibc-2.3.3-291086.tar.gz | tar xvf -
cd ActivePerl*
sudo ./install.sh

I installed the distribution into /opt/ActivePerl-5.10, the default, and also generated the HTML documentation. Don’t forget to change your environment variables, such as PATH before using the new perl

DBD::Oracle

Download DBD::Oracle, version 1.23 was current at the time of this writing:

wget http://search.cpan.org/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.23.tar.gz
cd DBD-Oracle-1.23
. oraenv # will set your LD_LIBRARY_PATH, $ORACLE_HOME and other variables needed.
export PATH=/opt/ActivePerl-5.10/bin:$PATH

From then on everything should be pretty smooth sailing:

perl Makefile.pl
Using DBI 1.607 (for perl 5.010001 on x86_64-linux-thread-multi) installed in /opt/ActivePerl-5.10/lib/auto/DBI/
Argument "6.55_02" isn't numeric in numeric ge (>=) at Makefile.PL line 61.

Configuring DBD::Oracle for perl 5.010001 on linux (x86_64-linux-thread-multi)

Remember to actually *READ* the README file! Especially if you have any problems.

Installing on a linux, Ver#2.6
Using Oracle in /u01/app/martin/product/11.2.0/client_1
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
Oracle version 11.2.0.1 (11.2)
Found /u01/app/martin/product/11.2.0/client_1/rdbms/lib/ins_rdbms.mk
Using /u01/app/martin/product/11.2.0/client_1/rdbms/lib/ins_rdbms.mk
...

I was a bit surprised to see the following warning during the execution of the perl Makefile.pl command. I decided

WARNING: Oracle /u01/app/martin/product/11.2.0/client_1/rdbms/lib/ins_rdbms.mk doesn’t define a ‘build’ rule.

WARNING: I will now try to guess how to build and link DBD::Oracle for you.
This kind of guess work is very error prone and Oracle-version sensitive.
It is possible that it won’t be supported in future versions of DBD::Oracle.
*PLEASE* notify dbi-users about exactly _why_ you had to build it this way.

I guess that’s owed at the relatively new Oracle client version. Execute make to build the module.

You should also test the build before proceeding. This proved to be a bit more tricky, you need to set some environment variables first. These are:

  • export PATH=/opt/ActivePerl-5.10/bin:$PATH
  • export ORACLE_DSN=”dbi:Oracle:<valid tnsnames.ora entry>”
  • export ORACLE_USERID=username/password

Don’t forget ORACLE_HOME and LD_LIBRARY_PATH etc, easiest done through a quick “source oraenv” in your session. With that set you can issue a “make test”. Edit Makefile line 1005 and set TEST_VERBOSE to 1 if you want more output from the tests which is useful for troubleshooting.

Finally, issue the “sudo make install” to install the DBD::Oracle package into your new perl distribution.

You’re done, congratulations!

Oracle’s secure external password store

I have written a large number of nagios checks with various degrees of sophistication over the past, most of them perl scripts. The general problem one faces with these is the database login. Regardless of which way you chose, a password will be stored somewhere and nothing is worse than storing the sys password somewhere in cleartext for checking standby datatabases. I only found two ways acceptable:

  • Using the cryptographic API
  • Using Oracle’s secure password store

I freely admit that I never really attended cryptographic courses at the University and neither was I too keen on this as a solution was needed quickly. So sorry, I won’t go into the perl cryptography here (but I found this link useful: http://www.perl.com/pub/a/2001/07/10/crypto.html).

When I saw that RUEI (Real User Experience Insight) uses exactly this to connect to the database, I thought it’s really worth spending time with. The secure password store is also something very few people seem to know about, but bear in mind it’s very basic. If I remember correctly I tested this with DBD::Oracle 1.21 on Linux and Oracle 10.2.0.3 32bit.

An example is more verbose than 1000 lines of text so without further ado here’s a stub that worked:

#!/u01/app/oracle/product/10.2.0/db_1/perl/bin/perl

use strict;
use warnings;
use DBI;

my $dbh=DBI->connect("dbi:Oracle:secureTNSName");

my $sth = $dbh->prepare("select name from v\$database");
$sth->execute;

my $r;

while ($r = $sth->fetchrow_hashref('NAME_lc')) {
 print "$r->{name}\n";
}

$dbh->disconnect if defined $dbh;

The script uses Oracle’s perl which comes with every database, connects to the secure TNS name (note the lack of passwords!), queries and prints the database name. Before this works without a problem a few prerequisites are necessary.

First I created a new directory for my tnsnames.ora and sqlnet.ora files which didn’t interfere with the settings in $ORACLE_HOME/network/admin.

[oracle@devbox001 securePasswordStore]$ export TNS_ADMIN=`pwd`
[oracle@devbox001 securePasswordStore]$ echo $TNS_ADMIN
/home/oracle/martin/securePasswordStore

[oracle@devbox001 securePasswordStore]$ mkstore -wrl `pwd` -create
Enter password:
Enter password again:
[oracle@devbox001 securePasswordStore]$ ls -lrt
total 20
-rw-r--r--    1 oracle   oinstall      163 Jun  5 10:31 tnsnames.ora
-rw-------    1 oracle   oinstall     7912 Jun  5 10:32 ewallet.p12
-rw-------    1 oracle   oinstall     7940 Jun  5 10:32 cwallet.sso

This creates the wallet and secures it with a password. So far the wallet is empty. Let’s add credentials to it:

[oracle@devbox001 securePasswordStore]$ mkstore -wrl `pwd` \
 >  -createCredential secureTNSName martin somePassword
Enter password:
Create credential oracle.security.client.connect_string1

This created a credential for user “martin” with password “somePassword” and an identifier “secureTNSName”. Let’s create the corresponding tns entry, we connect against single instance database “DEV”:

[oracle@devbox001 securePasswordStore]$ vi tnsnames.ora
secureTNSName =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = devbox001)(PORT = 1521))
    )
    (CONNECT_DATA =
       (SERVICE_NAME = DEV)
    )
 )

[oracle@devbox001 securePasswordStore]$ tnsping secureTNSName

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 05-JUN-2009 10:35:07

Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
  (HOST = devbox001)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DEV)))
OK (0 msec)

The sqlnet.ora file needs some changes as well, for me it was only the wallet location:

[oracle@devbox001 securePasswordStore]$ cat sqlnet.ora

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
 (SOURCE=
   (METHOD = FILE)
    (METHOD_DATA = (DIRECTORY=/home/oracle/martin/securePasswordStore)
   )
 )

With that we are now ready to connect! Note that the syntax requires you to specify the TNS name directly after the “/”, no spaces allowed.

[oracle@devbox001 securePasswordStore]$ sqlplus /@secureTNSName

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jun 5 10:43:33 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "MARTIN"
SQL> select name from v$database;

NAME
---------
DEV

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Metalink references:

  • 340559.1 – Using The Secure External Password Store
  • 403744.1 – How to Use an External Password Store With The JDBC Driver

Sometimes it requires a training course to get to know these features, I picked this one up when I did the security training which really is more a course for developers but never mind.