Cross Platform Database Monitoring Program

I have said this before, using Bash on Linux/Unix to interface with an Oracle Database is a kludge at best.  I am always looking for ways to simplify processes.  You know, like writing code to fulfill one need, but is versatile enough to be used to fulfill other needs.

My latest attempt to fulfill a need to monitor Oracle Database’s is my SQLTrigger Java application.  The application is really very simple, but is extremely flexible.  The application is XML driven.  The following is an example XML file.  This will give you an idea of the power of this application.

<?xml version="1.0"?>
<root>
  <jdbcdriver><![CDATA[oracle.jdbc.OracleDriver]]></jdbcdriver>
  <jdbcurl><![CDATA[jdbc:oracle:thin:system/manager//dbsvr1:1521/std1.us.domain.com]]></jdbcurl>
  <sql><![CDATA[SELECT a.tablespace_name, ROUND(a.used_percent), sys_context('userenv', 'db_name') FROM dba_tablespace_usage_metrics a, dba_tablespaces b WHERE a.tablespace_name=b.tablespace_name AND b.contents = 'PERMANENT' order BY 1]]></sql>
  <trigger><![CDATA[
      if (COL2 >= 95) {
        return true;
      } else {
        return false;
      }
    ]]></trigger>
  <command>
    <exe><![CDATA[/opt/scripts/pager]]></exe>
    <arg><![CDATA[DBA24X7]]></arg>
    <arg><![CDATA[COL3 tablespace COL1 is COL2% full.]]></arg>
  </command>
  <command>
    <exe><![CDATA[/opt/scripts/mail.sh]]></exe>
    <arg><![CDATA[COL3 COL1 tablespace alert!]]></arg>
    <arg><![CDATA[tablespace COL1 is COL2% full.]]></arg>
    <arg><![CDATA[dba@domain.com]]></arg>
  </command>
</root>

Let’s start with an explanation of each tag.

• jdbcdriver
  • This tag specifies the name of the JDBC driver you will be using.  Even though I use the application extensively with Oracle, you should be able to use it with any JDBC driver.
• jdbcurl
  • This tag contains the JDBC URL that will be used to connect to the database
• sql
  • This tag contains the SQL statement that will be executed.  The SQL statement in this example monitors tablespace % usage.  An explanation of the number of columns will follow.
• trigger
  • This tag contains what I have titled a JavaScript Trigger.  If you don’t know JavaScript, don’t be afraid, it is easy to learn, and there probably will not be a need for much variation in the script I provided for most people.  This is a good time for me to point out the COLX string in the trigger tag.  COLX (X denotes the column position starting with 1) in the trigger or command tags, correspond to the column position in the SQL statement.  In this example, COL2 is % used that is returned from the SQL statement.  In this example if % used is greater than or equal to 95, the command will be triggered.  Please note that each row that is returned will be evaluated by the JavaScript trigger.  This is important, because in this example we want to check tablespace usage for all permanent tablespaces in the database.
• command
  • This tag contains other tags.  An exe tag (there will only be one exe tag in each command tag) and an arg tag (there can be one or more arg tags in each command tag).  You may have one or more command tags.  This example shows two commands.  A command that will send a page, and a command that will send an email.  I must rant about this for a minute.  You might wonder why I am calling a mail script instead of calling sendmail directly.  Well this is because Java’s ability to execute OS calls is, how should I say this…. a joke (PHP has the best method of executing system commands that I have ever seen).  You cannot pass a string to the OS and expect it to be executed as if you were at the command line, so funky things like piping to commands will not work.  You have to place these commands in a shell script and pass only arguments to the shell script.  Please note the COLX designation in each arg.  COLX will be replaced with the column value from the SQL statement prior to passing the arg to the exe.

To execute SQLTrigger.class you need to set your CLASSPATH to include the JDBC driver you are using.  The following is an example shell script that sets the CLASSPATH.

#!/bin/sh

# cd
cd /opt/scripts/SQLTrigger;

# set env
export CLASSPATH=.:/opt/scripts/SQLTrigger/ojdbc6.jar;
export JAVA_HOME=/opt/jdk1.6.0_30;

# execute
$JAVA_HOME/bin/java SQLTrigger ${1};

Note that SQLTrigger.class takes one argument. The argument is the path/name of the XML file you want to use. To get the most out of SQLTrigger, you would have multiple XML files. For example one for monitoring tablespace usage and one for monitoring blocking locks.

To use SQLTrigger you need the following things:

• SQLTrigger (rename the file to SQLTrigger.class)
• Java 1.6
• JDBC Driver
• An XML file to parse

Sync Windows CIFS Share File Listing with Database Table

I recently had the need to store a list of file names on a Windows share within an Oracle database table.  This may have been easy if the database ran on Windows, but fortunately it runs on Linux.  To do the sync I wrote a Java application that connects to a Windows share, does a file listing, and then stores the listing in a database table.  To run this, you will need to do the following:

Create Database Table:

CREATE TABLE
    WINDOWS_SHARE_DATA
    (
        SHARE_NAME VARCHAR2(30) NOT NULL,
        DIRECTORY VARCHAR2(30) NOT NULL,
        FILE_NAME VARCHAR2(65) NOT NULL
    )

You will also need:
Java
Jcifs Library
Oracle JDBC Driver

To run the application first set your classpath so that it includes the Jcifs Library and the Oracle JDBC Driver:

export CLASSPATH=.:/classes/jcifs-1.3.17.jar:/classes/ojdbc6.jar

To run the application, do the following:

$JAVA_HOME/bin/java CifsToDB "smb://DOMAIN;USERNAME:PASSWORD@SERVER/SHARE/DIRECTORY/" "jdbc:oracle:thin:USERNAME/PASSWORD//SERVER:PORT/SERIVCENAME"

Download the following class. Rename the file to CifsToDB.class.

CifsToDB Java Class

How to see who is connected to your iTunes share on a Mac

The following command will show you who is connected to your iTunes share on a Mac:

lsof -i | grep -i itunes | grep ">" | grep -v "http" | awk '{print $9}' | cut -d ">" -f2 | cut -d ":" -f1

Building a portable executable on Linux

As a DBA who prefers Linux/Unix, you might guess that I script most everything I do.  Bash was my scripting language of choice, but honestly it has its limitations.  Interacting with a database via a Bash script can be flimsy to say the least.

When I am not DBA’ing I am developing.  Over the last couple of years I have come to know and love PHP.  Most people think of PHP as a language used for building web applications/sites, and it is, but PHP also has a command line interface known as php-cli.  Several weeks ago I thought to myself, “Why am I writing all this extra bash code when I could do this simply (and cleanly) with PHP?”  So from this point forward I am abandoning Bash for PHP for command line scripting.

Oracle RDBMS unexpire account

Let me preface this by saying you are doing this at your own risk!

So I have an Oracle SSO DB user whose account is locked.  SSO passwords are random and created when SSO is installed.  I can retrieve the password using the ldapsearch tool, then do an “alter user USERNAME identified by PASSWORD;” but that isn’t all that fun.

Instead, here is a slick way to unexpire an expired account with the original password (when you don’t know the original password):

select password, spare4 from sys.user$ where name='USERNAME';
PASSWORD    SPARE4
1FDF39535EDB8EDD    S:5BEFDD3B650E6910F42EAB51EBDFF612205E92620B56166AF480551F41B6

alter user USERNAME identified by values 'S:5BEFDD3B650E6910F42EAB51EBDFF612205E92620B56166AF480551F41B6;1FDF39535EDB8EDD';
notice the semicolon between SPARE4 and PASSWORD in the above statement.

This should unexpire the account using the current password.  You should probably check that the account is not also locked.

Oracle Autoincrement Column On Insert

MySQL has a nice little autoincrement attribute that will allow you to increment a column value on insert. Oracle does not have this, but there is a workaround:

Here is an example:

1.
create table mytable (id number, xyz varchar2(255));

2.
create sequence mytable_seq start with 1 increment by 1 nomaxvalue;

3.
create trigger mytable_trigger
before insert on mytable
for each row
begin
select mytable_seq.nextval into :new.id from dual;
end;
/

Oracle E-Business Suite aka Apps on RHEL 5 – PD KSH

In the Oracle documentation for installing the apps on Linux there is a requirement that KSH be installed.  However the real requirement is PD KSH.  There are scripts like, adchkutl.sh that are looking specifically for PD KSH.  PD KSH is not avaliable on RHEL 5.  The following note is on RedHat’s site:

http://kbase.redhat.com/faq/FAQ_103_12857.shtm

This is not a good solution as it seems to cause issues with a kde.sh script located in /etc/profile.d.

This led me to create a TAR since there is currently no documentation regarding this on MetaLink.  I got to speak with a Senior Engineer who suggested setting the following:

KSH_VERSION=’@(#)PD KSH v5.2.14 99/07/13.2′; export KSH_VERSION

Guess what!  It works.