Blog Archives

Configure unixODBC for use with PHP and MSSQL on Oracle Linux

This short article describes how to configure unixODBC 2.2.11-10.el5 in conjunction with PHP 5.3.3-26. Many forums out there contain articles that describe the absence of php_mssql drivers in the oracle yum repo. There various reason for that; non in which Oracle has a part. No matter what reason you like best, there is a decent alternative by using unixODBC.

To help all the people out that are just looking for a solution I wrote this article. I wont go into depths, ill just describe the major steps with some hints and tips. Happy reading ūüôā

The OS version of my virtual box image:

Linux sandboxpinguin 2.6.18-194.0.0.0.3.el5xen #1 SMP Mon Mar 29 18:27:00 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
  1. Make sure you have the latest version of unixODBC installed. If Yum is configured correctly the following command should do the trick.
     yum update unixODBC
  2. Download the freeDTS driver, this is the driver unixODBC will use to connect to mssql. If wget is available on your production environment (remove it) after running the command:
     wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-stable.tgz

    . Make sure you are in an desired location like your home folder.

  3. Unpack the tar-Gzip ball by running the following command
    tar -xf  ./freetds-stable.tgz
  4. Browse into the unpacked folder and run the configure command
    ./configure --with-tdsver=7.2 --enable-msdblib
  5. If the configure ran without any issues you can link/compile the driver by running the command:
    make

    and then

    make install

    and then

    make clean

    Congratulations, you are now the pride owner of the freeDTS driver ūüôā

The next part tend to get a bit fuzzy, feel free to ask questions in the comment and ill try to answer them to the best of my ability.

There are allot of articles available on how to configure the unixODBC DSN correctly. Be adviced: the config is specific for your setup and usually needs to be tweaked. In order to enable you to ill explain the concepts of unixODBC. Ill point out some documentation, commands and stuff. Afterward ill have a short tutorial of the steps i used to configure the odbc connection.

  1. unixODBC is configured properly by using the
    odbcinst

    command.

  2. unixODBC will write into:
    odbc.ini

    files and uses input files to do so;

  3. The connection can be tested with osql commands from bash, but! it will use the hidden .odbc.ini file in your profile instead of the /etc/odbc.ini. PHP and odbcinst use the one in /etc/odbc.ini. If the one in your profile works than make sure it is identical to the one located in /etc/ directory. Below how the osql output will look if configured correctly. (charset isnt relevant in this stage)
    screenshot
  4. Documentation on how to use FreeTDS in conjunction with unixODBC can be found here.
    http://www.freetds.org/userguide/odbcconnattr.htm
  5. Documentation on how to use ODBC can be found here
    http://www.unixodbc.org/odbcinst.html
    (ignore the freetds configuration here and use ad4 to figure the settings out for your setup)

Next ill describe my steps in order to make it work.

Configuring the FreeTDS driver

  1. Create the file /etc/odbcDriver.ini
  2. Insert the following in the file (check the paths)
    [FreeTDS]
    Description     = FreeTDS Driver with protocol v5.0
    Driver          = /usr/local/freetds/lib/libtdsodbc.so
    
  3. Create the file /etc/odbc.ini
  4. Insert the following and tweak this to match your environment
    [ExampleSource]
    Description     = FreeTDS Driver with protocol v5.0
    Driver          = /usr/local/freetds/lib/libtdsodbc.so
    Server          = [SERVERIP]
    Port            = [REMOTE_TSQL_PORT]
    ClientCharset   = UTF-8
    TDS_Version     = 7.1
    Database        = [DATABASENAME]
    Trusted_Connection = Yes      # Required with most MSSQL environments.
    
  5. Register the ODBC driver
     odbcinst -i -d -f /etc/odbcDriver.ini 
  6. Register the data source
     odbcinst -i -s -f /etc/odbc.ini

Finally test your config by using the php odbc functions.

<?php
$sql = "select 1 + 5 as outcome";

$conn = odbc_connect("ExampleSource" , "Username", "Password");
$result = odbc_exec($conn, $sql);
$row = odbc_fetch_array($result);
echo $row['outcome'];

Good luck querying ūüôā

Oracle Enterprise Linux 6.x networking

Lately I got many questions regarding the network configuration of Oracle Enterprise Linux 6 (Red Hat Enterprise Linux 6).
Enough to write a little article about it.

It seems that some of the network configuration was altered in OEL6. The reason as far as I know is the implementation of the NetworkManager daemon. I don’t know why they are using CamelCase for the daemon name, but mind that. Even though the NetworkManager should make the configuration as painless as possible (at least thats what the manual page said), it seems to actually make the configuration more of a pain for some.

Below I will cover some topics in an effort to get you going and remove the pain ūüôā

Configuring eth0 for manual operation

  • Step 1: disable the NetworkManager daemon
    service NetworkManager stop
  • Step 2: remove the NetworkManager from Init (start-up)
    chkconfig --level 2345 NetworkManager off
  • Step 3: open the ifcfg-eth0 config file (alter the suffix ‘eth0’ to match the adapter of your choice)
    vi /etc/sysconfig/network-scripts/ifcfg-eth0
  • Step 4: Alter the following to match your environment…
    DEVICE=eth0
    TYPE=Ethernet
    HWADDR={Your MAC address here}
    ONBOOT=yes
    NM_CONTROLLED=no
    BOOTPROTO=static
    IPADDR=192.168.1.10
    #PREFIX=24    [can be used alternativly to NETMASK=]
    NETMASK=255.255.255.0
    NETWORK=192.168.1.0
    BROADCAST=192.168.1.255
    GATEWAY=192.168.1.1
    
  • Step 5: Write/close the configuration file¬†(:wq in vi)
  • Step 6: Restart the network service
    service network restart
  • TIP 0: Obviously match the configuration above to match your home network.
  • TIP 1: NetworkManager is not always present in which case you can obviously skip step 1 – 2.
  • TIP 2: There are reports that NETMASK=xxx.xxx.xxx.xxx is actually more stable then PREFIX=xx notation.
    My advice, use NETMASK= which is also better understood by non networking guys.
  • TIP 3: Not sure about the correct NETWORK, NETMASK, BROADCAST or PREFIX¬†settings, give ipcalc a try:
    ipcalc --netmask {IPADDR}
    ipcalc --prefix {IPADDR} {NETMASK}
    ipcalc --broadcast {IPADDR} {NETMASK}
    ipcalc --network {IPADDR} {NETMASK}
    

Configuring DNS

DNS always seems to be a bugger and a hard one to understand. Do note that DNS is JUST A IP PHONEBOOK. Nothing fancy there. Also there are various ways of configuring DNS. One way is by adding the DNS configuration in the ifcfg-suffix configuration file with the DNS1=ip.ip.ip.ip DNS2=ip.ip.ip.ip keywords.¬†As an¬†effect, the networking service will update the appropriate¬†configuration files.¬†To¬†be frank,¬†I find this to be confusing and do not like duplicate configurations everywhere in my -has to be clean- environment. My advice is to configure the DNS is the appropriate files directly like this…

  • Step 1: Edit¬†the¬†resolve.conf where DNS is configured.
    vi /etc/resolv.conf
  • Step 2: Add or Alter the following to match your environment
    search mydomain.home
    nameserver 192.168.1.1
    nameserver 8.8.8.8
    
  • Step 3: Test to see if name resolution works
    nslookup
    set debug
    www.google.com
    
  • TIP 1: Linux actually tries to find the ip in the /etc/hosts file first. If you know the hostnamename and FQDN to an certain IP and it can be classified as static. Consider using the hostsfile instead of a centralized DNS. This will boost performance if the name is resolved often. If multiple systems use and depend on a machine reference, use centralized DNS in order to lighten the administrative tasks.
    vi /etc/hosts
  • TIP 2: Experiencing slow log on times or slow application performance? A faulty DNS configuration might just be the¬†cause. A quick way to test this is by¬†temp. disabling DNS all together. This can be done by editing the /etc/nsswitch.conf file.
    vi /etc/nsswitch.conf
    • alter the line
      hosts:     files dns
    • to the line
      hosts: files
    • write the file and test if the performance has improved.
  • The reason for this is that DNS is often used to register user logon or session information based on the visitors IP address. Examples are the ssh daemon, ftp servers, webservers, linux logon, etc.

STATIC ROUTES

In some case you want linux to use alternative routes to access certain Linux resources. The way to go in these cases are creating routes. In most cases you want these to be presistant in which case ‘route add –‘ wont suffice. In our example we will create two new routes. On describing a route to a specific host, the other describing the route to a specific network. Alter the example to match your needs.

  • STEP 1: Create a new file called static-routes in the¬†/etc/sysconfig/ directory
    vi /etc/sysconfig/static-routes
  • STEP 2: Add the following, obviously matching your specific needs
    any net 192.168.2.0/24 gw 192.168.1.254 metric 1
    any host 192.168.2.254 gw 192.168.1.254 metric 1
  • STEP 3: Restart the network service
    service network restart
  • TIP 1: SIOCADDRT: No such process means the designated gateway doesnt exsist on any known interface. (typo?)
  • TIP 2: view the route information usint the route command
  • TIP 3: use the ipcalc –prefix {IPADDR} {NETMASK} command to determin the right /prefix for your environment.
  • TIP 4: In older environments the ifup-routes is used, this shscript still exsists in the /etc/sysconfig/network-scripts/ifup-routes

Locate my mac address

The ifcfg-eth# config allows you to configure the specific mac address to guarantee the IP is bound to the right adapter. In virtualized environments this might save you a lot of trouble in the situation where the virtualized domain is altered. On the other hand it might cause trouble when the staticly configured MAC is migrated in virtual environments. Either case, you might want to know the MAC linux sees belonging to an certain adapter. You can find the MAC address in the following location:

 cat /sys/class/net/eth0/address

Obviously you need to alter eth0 in the path to match the adapter you are looking for. Not sure? The change directory to /sys/class/net and perform a list to see all discovered and registered adapters.

IPTables (Linux firewall)

By default IPtables (which is the linux firewall) is enabled. You can view the running configuration by checking the service status like this.

 service iptables status

You can simply turn the firewall off by modifying and applying steps 1-2 of the first configuring eth0 instruction. This will reduce the security of your linux platform significantly. My advice, add the ports you need for your services and let IPtables protect you. The easiest way is by simply editing the iptables configuration file.

 vi /etc/sysconfig/iptables 

Adding a port is as easy as copy/pasting the always present firewall rule that allowes port 22 (ssh). Copy past it and alter the -p (protocol) -dport (destination port) to match your needs. For example, allowing HTTP/HTTPS.

-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 443 -j ACCEPT

afterward restart iptables

service iptables restart

TIP: If you are experimenting with IPv6 (then your Instant COOL!), mind that the ipv6 firewall is called ip6tables and the configuration is called the same. The basic iptables doesnt handle ipv6 at all.

TIP: If you are using ipv6 code your IPv4 ip to ease administration. Example:

ipv4: 192.168.10.1/32
ipv6: 2001::0192:0168:0010:0001/64
Then route on the nibble of choice.

Additional questions?

Just post it below and maybe ill respond in due time ūüôā

Check_VM for Oracle VM and Nagios.

Personal backup…

Refinements might be added if bugs or improvements are found. So keep an eye out for newer versions ūüėČ

This script might also be compatible with other  Xen clones.


#!/usr/bin/perl
#
# Author : Chris Gralike
# Company: AMIS Services BV
#
# Simple but effective Oracle VM check command for use with nagios
# This command checks the state of any given VM machine using the XM command.
# It will try to match the friendly name as well as the system name.
# It will return OK - and usefull metadata on succes, NOK on failure.
# usage : check_xm vmname
# ########################

use strict;                     # Good practice
use warnings;                   # Good practice

my (@data, @values, @name, $vmname, $vmcheck, $i, $result);

# Get the command parameters
if( ($#ARGV + 1) == 1 ) {
$vmname = $ARGV['0'];
}else{
print "usage: ./check_xm vmname \n";
exit 1;
}

# Perform the actual test
open(XM, "xm list|");
$i = 0;
while(<XM>){
if($i > 0){
# Split the output in portions
@data = split(" ", $_);
# Get the human readable name
@name = split('_', $data['0']);
if(!$name['1']){
$name['1'] = 'dezeisnietingebruik!';
}
if(($vmname eq $name['1']) || ($vmname eq $data['0'])){
print "OK - $data['0'] is active with Id:$data['1'] $data['3']CPUs $data['2']M \n";
exit 0;
}
}
$i++;
}
close XM;

# If the loop was finished without result, then there is a problem!
print "NOK - $vmname is not running on this server\n";
exit 2;

Oracle Business Process Management (BPM) Suite.

Did you know that AMIS Services BV now offers Oracle BPM consultancy services on the dutch market?

As one of the few Oracle Specialists in the Netherlands, AMIS Services will now also offer services based on Oracle BPM suite, and help organizations implement and utilize the full potential of Oracle BPM suite within any branch.

http://www.oracle.com/us/technologies/bpm/index.html

With a team of experts in Business Process Management and a company of know-how in the Oracle field of expertise, AMIS Services BV is “the” party to help you reach your business goals.

Want to know more about our company?
Then please have a look at our corporate website http://www.amis.nl
or see what our experts are sharing in the fields of Oracle business, application Development and related products.
http://technology.amis.nl/blog/

Choose for success, choose AMIS Services BV to be you partner in business.

Compile PHP5.3.6 on OEL5.5 x64

All you need is apache installed, then download php 5.3.6. src and run the following configuration command.

./configure --build=x86_64-redhat-linux-gnu --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/usr/lib64 --libexecdir=/usr/libexec --localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man --infodir=/usr/share/info --cache-file=../config.cache --with-libdir=lib64 --with-config-file-path=/etc --with-config-file-scan-dir=/etc/php.d --disable-debug --with-pic --disable-rpath --without-pear --with-bz2 --with-curl --with-exec-dir=/usr/bin --with-freetype-dir=/usr --with-png-dir=/usr --enable-gd-native-ttf --without-gdbm --with-gettext --with-gmp --with-iconv --with-jpeg-dir=/usr --with-openssl --with-pcre-regex --with-libexpat-dir=/usr --with-zlib --with-layout=GNU --enable-exif --enable-ftp --enable-magic-quotes --enable-sockets --enable-sysvsem --enable-sysvshm --enable-sysvmsg --enable-wddx --with-unixODBC=shared,/usr --enable-shmop --enable-calendar --with-libxml-dir=/usr --with-apxs2=/usr/sbin/apxs --with-mysql --with-gd --enable-soap --enable-mbstring --with-xsl --disable-dba --without-unixODBC --disable-xmlreader --disable-xmlwriter

Next note the errors during configuration and install the required packages needed from the install media then rerun the command up till it finishes succesfully.

When the configuration is done, run the “make all” command after which you are requested to run the “make test” command. Then finaly run the “make install” command.

Installing the correct packages might consume some time…

Good luck! ūüôā

–If you encounter an “Cannot find libmysqlclient” error you either didnt install all the mysql packages or you need to add –with-libdir=lib64 behind the –with-mysql=/usr/bin entry.

(The mysql module isnt shipped anymore with the distribution!)

RHEL5 init script for tomcat catalina

I have written an init script for Tomcat Catalina running in RHEL version 5. I have tested this script using Oracle Enterprise Linux 5.5 Carthage. The script should comply to the init standards defined for RedHat Enterprise Linux using the INIT Functions lib.

The script also alows the use of chkconfig eventhough you might want to alter the used priorities (56 10)

#!/bin/sh
#
# "$Id: catalina ,v 1.0 2010/08/10 Chris_g Exp $"
#
#   Startup/shutdown script for tomcat(Catalina) Application server.
#
#   Linux chkconfig stuff:
#
#   chkconfig: 2345 56 10
#   description: Startup/shutdown script for the tomcat application server.
######

# Source function library.
######
. /etc/init.d/functions

# Define where the catalina.sh script is located.
######
CATALINA_BIN='/u01/tomcat/bin/catalina.sh 1> /dev/null';

# Find the catalina process using ps / awk.
# The match function will return 0 when no match is found with the string "java".
# Position $9 should contain the path to the Java executable used by catalina.
######
PROC=`ps -efc | grep apache.catalina | awk 'BEGIN { FS=" "}; { if( match($9, "java") != 0 ) print $9;}'`

# Replace a potential empty string with a fake process so the RH daemon functions are able to parse
# it properly
######
if [[ "$PROC" == '' ]]; then
    PROC='Tomcat_JVM';
fi

# Define the application name that is listed in the daemonize step.
PROG='Tomcat JVM';

# LOCKFILE
LOCK='/var/lock/subsys/tomcat';

start () {
        echo -n $"Starting $PROG: "

        # start daemon
        daemon $CATALINA_BIN start
        RETVAL=$?
        echo
        [ $RETVAL = 0 ] && touch $LOCK
        return $RETVAL
}

stop () {
        # stop daemon
        echo -n $"Stopping $PROG: "
        killproc $PROC
        RETVAL=$?
        echo
        [ $RETVAL = 0 ] && rm -f $LOCK
}

restart() {
        stop
        start
}

case $1 in
        start)
                start
        ;;
        stop)
                stop
        ;;
        restart)
                restart
        ;;
        status)
                status $PROC
                RETVAL=$?
        ;;
        *)

        echo $"Usage: $prog {start|stop|restart|status}"
        exit 3
esac

exit $RETVAL

# INSTALL
1. Touch a new tomcat file in your init directory.
>touch /etc/init.d/tomcat
2. Copy paste the code above into this file using vi
vi /etc/init.d/tomcat
(putty users)
press the insert button (this should put vi in insert mode)
Alter the tomcat catalina.sh path and copy the altered code to your clipboard and paste it into putty using a richt mouse click.
press esc (this should get you out of insert mode)
next press ” shift + : “, “w”, “enter” (this should save the file)
3. If catalina.sh was able to start tomcat (all vars/java configured) then now the tomcat script should be able to handle the startup.
4. If catalina was allready running, try;

     service tomcat status
     This should allready give a result equal to;
¬†¬†¬†¬† java (pid 14389) is running…

5. Add tomcat to the chkconfig for automatic startup
¬†¬†¬†¬† chkconfig –level 2345 tomcat on

Hope this helps ūüėČ

OracleVM & Oracle licensing….

Be aware of licensing, ‘soft’ partitioned virtualized guests with Oracle products installed are a financial RISK!

OracleVM supports ‘Hard’ partitioning.

In essence the OracleVM product is equal to any ‘soft’ partitioning solution. This is because in essence the Oracle (Xen) Hypervisor will also use¬†all of the resources in the¬†pool to spread the workload as efficiently as possible (para virtualized). In this scenario the licensing model will also require you to license all the available CPUs on the fysical box.

There¬†is a supported method to circumvent this. If you configure the domain¬†to map its¬†virtual¬†CPUs to Fysical CPUs, this setup is accepted by Oracle as ‘Hard’ partitioning. A special configuration entry needs to be set to the vm.cfg of each virtual machine¬†running¬†an Oracle Database as described in this Oracle Document.
http://www.oracle.com/technology/tech/virtualization/pdf/ovm-hardpart.pdf

Remember to set this configuration rule!
Else Oracle Licensing will identify your cool OracleVM infrastructure as just another Soft partitioned VM solution that requires¬†you to license each fysical CPU available to the virtual platform¬†ūüėČ

Additional reading : http://www.oracle.com/corporate/pricing/partitioning.pdf

-Rgrds,

NDO2DB startup script for RH (EL) / OEL

Below a script designed to use the RH . /etc/init.d/functions library. It will use 2 “find” commands to test if it can find the ndo2db binairy and config file. Next it will daemonize the ndo2db process following the Redhat standard init procedure.

This script was written for Redhat enterprise linux using Nagios, Centreon (auto generation of the config)

Edit the “searchdir” for a broader searchscope. In the current code the nagios tree is searched for both the config and the ndo2db binairy.

touch a file in the init.d like so.

touch /etc/init.d/ndodaemon

NExt edit the file and add the code below.

vi /etc/init.d/ndodaemon

Please note the fact that this script uses the /etc/init.d/functions file. Not all linux distro`s deliver this file. Do check its availability in your own distro!

#!/bin/sh
#
# "$Id: ndodaemon.sh,v 0.9 2010/01/19 Chris Exp $"
#
#   Startup/shutdown script for the NDO2DB daemon under centreon/nagios.
#
#   Linux chkconfig stuff:
#
#   chkconfig: 2345 56 10
#   description: Startup/shutdown script for NDO2DB Daemon \
#                using Centreon / Nagios.

# Source function library.
. /etc/init.d/functions

# Set various vars
#DAEMON=ndo2db;
prog=ndo2db;
SEARCHDIR="/usr/local/nagios"
NDODAEMON=`find $SEARCHDIR -name "ndo2db"`;
NDOCONFIG=`find $SEARCHDIR -name "ndo2db.cfg"`;

#Check if both the ndo daemon and config are found.
#echo -n $"Searching ndo config & binairies : "
if test -f $NDODAEMON
then
        if test -f $NDOCONFIG
        then
                RETVAL=0;
                #echo "Files found!";
        else
                echo "ERROR: Config file not found!";
                exit 1;
        fi
else
        echo "ERROR: ndo2db not found!";
        exit 1;
fi
        start () {
                if test -f "/var/lock/subsys/ndo2db"
                then
                        echo "ndo2db is allready running...";
                        return 1;
                fi

                echo -n $"Starting $prog: "
                # start daemon
                daemon $NDODAEMON -c $NDOCONFIG
                RETVAL=$?
                echo
                [ $RETVAL = 0 ] && touch /var/lock/subsys/ndo2db
                return $RETVAL
        }
        stop () {
                # stop daemon
                echo -n $"Stopping $prog: "
                killproc $NDODAEMON
                RETVAL=$?
                echo
                [ $RETVAL = 0 ] && rm -f /var/lock/subsys/ndo2db
        }

        restart() {
                stop
                start
        }

        case $1 in
                start)
                        start
                ;;
                stop)
                        stop
                ;;
                restart)
                        restart
                ;;
                condrestart)
                        [ -f /var/lock/subsys/ndo2db ] && restart || :
                ;;
                reload)
                        echo -n $"Reloading $prog: "
                        killproc $NDODAEMON -HUP
                        RETVAL=$?
                        echo
                ;;
                status)
                        status $NDODAEMON
                        RETVAL=$?
                ;;
                *)

                echo $"Usage: $prog {start|stop|restart|condrestart|reload|status}"
                exit 3
        esac
exit $RETVAL

Next if you have chkconfig available as package, you should be able to add the script to the linux init using the commands;

chkconfig --add ndodaemon
chkconfig --level 2345 ndodaemon on

start the ndodaemon manually using

/etc/init.d/ndodaemon start
#or
service ndodaemon start

For those not using Centreon (a shame ūüėõ ) here is an example (default) ndo2db configuration file.

###################################################################
# #
# GENERATED BY CENTREON #
# #
# Developped by : #
# – Julien Mathis #
# – Romain Le Merlus #
# #
# http://www.centreon.com #
# For information : contact@centreon.com #
###################################################################
# #
# Last modification January 19, 2010, 11:11 am #
# By AMIS Services #
# #
###################################################################

ndo2db_user=nagios
ndo2db_group=nagios
socket_type=tcp
socket_name=/var/run/ndo.sock
tcp_port=5668
db_servertype=mysql
db_host=localhost
db_name=centstatus
db_port=3306
db_prefix=nagios_
db_user=centreon
max_timedevents_age=1440
max_systemcommands_age=1440
max_servicechecks_age=1440
max_hostchecks_age=1440
max_eventhandlers_age=1440

Hope this helps!

-Regards,

OEL 5.4 Released by Oracle

Oracle released a new version of Oracle Enterprise Linux (OEL) version 5.4. For all of you that dont know this distro. Its a fork from the Redhat Enterprise Linux server. Its full featured and free for download / usage. Curious? Just check it out.

You can check the release notes here;
Release notes

And get your download here;
Download Link

Got curious? Great!
Check it out, and let me know what you think!

Compiling Tomcat APR-Native on OEL5.2

This is just a short tutorial on how to compile APR on Oracle Enterprise Linux 5.2. If you ever need to install Apache Portable Runtime on a Oracle Enterprise linux machine, Then this is what you need, and how to configure it.

Read the rest of this entry