Timothy E. Archer

The need often arises to run Oracle SQL scripts or PL/SQL procedures from a shell script. For instance, in my environment we run a lot of jobs through cron. Part of the job may be to connect to the database and run a PL/SQL function or procedure. Below I will describe how to do this, and some of the various “extras” that I include in my shell scripts. All of the examples below have been tested on Oracle 10gR2 and RedHat AS 4.

The Basic Syntax

In its simplest format, you can call SQLPlus from a shell script. The basic format of a shell script doing this is:

#!/bin/sh
sqlplus system/manager@prod_db <<ENDOFSQL
select sysdate from dual;
exit;
ENDOFSQL

continue reading…

A basic, but very useful command I use in the day to day management of my servers is the Unix find command. The find command will search through directories looking for files that match your search criteria.

The most command tasks I use the find command for are to:

• Find files greater than X kBytes, especially log files that are getting big.
• Find files modified in the last X days. This is useful for finding old log files and deleting them.
• Finding files that have a certain keyword in their file name. For example, finding everything named *.txt.
• Using find to execute grep to look through the content of the files for certain keywords.

continue reading…

One minor thing I do on all of my unix based Oracle database servers is setup the shell prompt to give me some key information. Namely, I always want to see my current directory, the user I am logged in as, the server I am logged into, and my current ORACLE_SID. Since I typically have many servers, oracle instances, and shell sessions open to manage them all, its easy to get confused as to what server and database your executing commands against.

My solution to this is to setup my shell prompt to show all of this information. The instructions I provide below will show you how to do this on a RedHat Linux AS3 system. It should work on most other Linux systems, and needs minor modifications for use on Sun Solaris systems. continue reading…

Are you looking to backup your MySQL database? My site runs with MySQL as its backend database, and as such I wanted a backup script I can just drop in.

I have found a great open source utility called AutoMySQLBackup which can be downloaded from SourceForge.net. There is no need to reinvent the wheel writing your own, this one works extremely well.

Here are the steps I followed to configure this script: continue reading…