Tips and Tricks for Oracle in a UNIX World
July 25, 2006
The title says it all, except these are some of my personal favorites.
Use a standard setup in shell scripts
Why re-invent the wheel? Any script that relies on access to SQL*Plus or SQL*LDR (on the same machine) can be easily initialized. Take the following code snippet and put it into its own file:
Let's say you named the file as shell.txt. Save shell.txt in the user's $HOME directory. If I am logged in as oracle and my home directory is /opt/oracle, then $HOME refers to /opt/oracle. When starting a new script, you have environment checking already added in, you also have the new script file created in the current working directory. The example below checks for ORACLE_HOME and the sqlplus executable. Other executables can be included/checked, but chances are, that if sqlplus exists under ORACLE_HOME, so does sqlldr.
While you are at it, especially if the new script is going to interview the user, add in a "yes or no" block. The script can then test SOME_VARIABLE's value and branch appropriately.
Get output from a query returned into a shell script variable
The utility of this is endless as most anything from a query in SQL*Plus can be returned into a shell variable. Although there are other ways of adding a timestamp as part of a file's name, this example uses the date returned from a query and places it into a file's name (e.g., a spool file).
As a test, put this into my_new_script.ksh and run it.
linux.box.net> my_new_script.ksh The timestamp is 20060720_18200
If you are familiar or comfortable using date formatting/masking in UNIX, this approach does everything UNIX does and more because of the numerous ways in which dates can be formatted. You can also include a random number (concatenated with the date query) if the 86,400 seconds in a day in combination with the date is not enough to make a file name unique.
Two things to take note of include the back tick single quotation mark and the use of the tr command. The "`" character is hard to see, especially if your editor allows color mode, so don't forget to include it (or check for it during troubleshooting). The other item is the "tr" (translate) command. This helps to stop carriage/line return when referencing the variable. If your output looks like what is shown below, the tr command's conditioning of the variable's value will make the variable more "normal."
The timestamp is 20060720_18200
Use mail as a file transfer utility
Let's say you just spooled the output of a query into a file. If you work in a PC desktop/UNIX server environment, as is very common, how do you get the spool file to your desktop (or other users, for that matter)? Are you one of those who copies the file to your home directory, starts up PuTTY/WinSCP3/Tectia/etc., copies the file onto your PC, then attached it to an email? If the file is small enough (relatively speaking), send it via mail using:
mail s "Your subject line" your_name@your_domain.com < file_to_send
The exact name of "mail" can vary across platforms/vendors, but either mail or mailx typically does the trick. Of course, your environment has to be configured to allow mail to be sent from UNIX-land to PC-world.
Armed with this functionality, you can create an alert log checker and have it send mail when it discovers problems or alertable conditions in the end of the alert log. Add a cron job to check every five minutes (as an example). Use the tail command to output the bottom however many lines you want from the alert log into a temp file, grep for ORA-xxxxx or whatever else is of interest, and if you get a hit, send the results or temp file via email.
This method isn't bulletproof in that a lot can happen in five minutes and the scan (using
tail -#_of_lines) may miss the event you were hoping to be alerted on. Not to knock the improvements in OEM/Database Control/whatever, but a cron on UNIX is quite a bit more trustworthy than relying on an Oracle agent. Keep in mind that this isn't an either-or choice. The cron can backup the agent, and vice versa.
Run a script right now
Related to cron jobs is the "at" command. On Linux, running a script is as simple as:
at f alert_log_checker.ksh now
This is much easier (and safer) to perform when compared to waiting for the scheduled cron job to start, editing the crontab and introducing errors/typos, and preventing output from scrolling in your window. The "now" part can include noon, midnight, 0600, and a variety of other named time formats.
Pick one shell and become proficient in it (for beginners)
Bash, Korn, Bourne, C, and the list goes on and on. Each shell has it own nuances, so instead of having your scripts spread over several shells, pick one and stick with it. The payoff is consistency. You can almost always switch between shells, so if your personal preference is the C-shell (hard to beat the tab key for file name completion), but all "real" work is done the Korn shell, learn how to switch between them.
There are scores of books (and bibles) on tips, tricks, techniques, utilities, insider secrets, internals, power tools, complete (but not really, depending on the publisher) reference, essentials, nutshells, and well, you must get the idea by now. They all complement one another because if there were one definitive guide (add that one to the list as well), the market wouldn't have that many books to choose from. You have the freedom to pick and choose the ones you like, so happy hunting. Perhaps some of the ones in this article will make your life and job easier.