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

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

This post has been published in 2009 and a lot has happened since that day. It appears as if it was still quite popular, which is why I decided to update it to Oracle 19c. What hasn’t changed though is the lack of adoption of this feature. I can’t quite understand why there are still so many scripts out there somehow trying to hide a clear text password from plain sight, with varying degree of success. Not storing a password in the backup script at all is probably the most secure way of doing things.

This article was based on Oracle 19.5.0, running on Oracle Linux 7. I used a single instance, non-CDB database. Instructions for other configurations may differ, refer to the official documentation (see below) for details.

Creating the Secure External Password Store

The procedure to create the password store hasn’t changed much since the original post was written. My intention in this example is to call RMAN to create a database backup without having to provide the password for the database. The password store is persisted on the database server for convenience.

First, I need a directory where to store the wallet. I went with $ORACLE_BASE/admin/$ORACLE_SID/wallet. I should probably add that my database is a single instance, non-CDB database. The wallet is created within said directory:

[oracle@server1 wallet]$ mkstore -wrl $(pwd) -create
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter password:   
Enter password again:   
[oracle@server1 wallet]$ 

Make sure to use a Strong Password when prompted. The next step is to add the credential to the wallet, as shown in this example:

[oracle@server1 wallet]$ mkstore -wrl $(pwd) -createCredential SEPTEST sys
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line 
Enter your secret/Password:   
Re-enter your secret/Password:   
Enter wallet password:   
[oracle@server1 wallet]$  

The -createCredential flag takes the TNSname and the username as arguments. Theoretically it allows you to enter the corresponding password at the command line, but that’s a Very Bad Idea as it’s too easy to get hold of the password. Please don’t do that. In the above example I added the SYS account (for backups) to the password store. Note the first prompt is the password for the SYS account, the next prompt is for the wallet as per the first step.

Using the Secure External Password Store

Now that the credential is stored in the wallet, the next steps are to make them available to clients. This is done in 2 files: sqlnet.ora and tnsnames.ora. Since I don’t like messing around with these in $ORACLE_HOME/network/admin I put them in the directory containing my backup script. The first file to create is sqlnet.ora:

$ cat sqlnet.ora 
WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/ORA19NCDB/wallet)
    )
  )

SQLNET.WALLET_OVERRIDE = TRUE 

This is the bare minimum, refer to the official documentation (Chapter 3 of the 19c Security Guide) for any potential additional parameters you must add.

The next file to create is tnsnames.ora. Just remember that your entry has to match the first argument in the call to -createCredential. In this example it’s SEPTEST.

$ cat tnsnames.ora 
SEPTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19NCDB)
    )
  ) 

The only remaining task is to set TNS_ADMIN to the directory containing the 2 files, and I’m good to go:

$ export TNS_ADMIN=$(pwd)
$ rman target /@septest

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jan 16 18:06:02 2020
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA19NCD (DBID=49197792)

RMAN>  

Note the connection syntax is different: when connecting via the Secure External Password Store you prefix the @tnsname with a forward slash as shown in the example.

More Information

If you are a fan of sqlcl, you might find an earlier post about using the Secure External Password Store with sqlcl useful. Even more information can be found in the Oracle Database 19c Security Guide, chapter 3 “Configuring Authentication”.