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.

Oracle LOCAL=NO Script

LOCAL=NO processes are remote dedicated server processes. There are a couple of times when it is important for me get a little more detail about these processes. One of those times is during an instance shutdown. When trying to shutdown an instance, it is possible for a LOCAL=NO process to cause the shutdown to hang. At this point you can ask the user get out of the offending application or kill their process.

One thing I occasionally do is monitor the top processes on a server. If over time I see that a process is consistently the top process, I investigate it to see whether the source query can be tuned. This has been the case most of the time.

MySQL Birthday Example

Even though I am an Oracle DBA, I enjoy tinkering with MySQL on occasion. I recently needed to write a query that would select birthdays for the next 8 days starting with today’s date. This might seem very straightforward, but what happens if I run the query on December 28th? Only records for Dec 28, 29, 30 and 31 will be returned. Hmmm… I scoured Google and lurked several sites looking for the solution. Believe it or not I couldn’t find any copy-and-paste examples. After tinkering for a while I came up with something. You will first need to create a test table and load it with test data.

Sendmail – Set From Address

For years I have had to build the email header in a file in order to set the From address with sendmail. After some tinkering I devised a method of sending an email using a one liner. I have to admit, it is slick:

echo “Hello World” | formail -A ‘Subject: Test Subject!’ -A ‘From: sender@domain.com’ -A ‘To: recipient@domain.com’ | sendmail -oi -t

You may have to prefix echo, formail, and sendmail with paths if they aren’t in your own path.

How to run Oracle E-Business Client on Linux

I have been a Linux Desktop user for a while now. One of the biggest inconveniences associated with using a Linux desktop is not being able to run certain applications. For this reason I have kept a Windows XP VM around. One of the applications that prevents me from ditching Windows completely is Oracle Applications. I currently use Oracle Applications 11.5.10.2 and have experience with 11.5.9. Recently I decided to do a little investigating on how I could remedy this inconvenience. I began the investigation on Google. I found many people suggesting that I edit the appsweb.cfg file. This solution actually worked. I was able to load the E-Business Suite Applet on my Fedora Core 7 machine using Sun JRE 1.5.0_11. Even though IE on Windows still used Jinitiator with this solution, Firefox on Windows would crash because Jinitiator and the Sun JRE were being loaded simultaneously. Very strange.