Scripting Oracle tasks in Linux

Oracle is managed using the sqlplus & rman tools, whatever the operating system. Your scripts should not require too many changes to work on Linux.

As said, use crontab (have a look at the cron manpage) and don't forget to add the account you use to perform the maintenance to the dba group.

I usually work with 2 scripts: The first one set the environment and the call the second that will perform the action.

It could be like this:

shutdown_DevDB1.sh

#!/bin/sh
set ORACLE_SID=DevDB
set ORACLE_HOME=/opt/oracle/Home1
set ORA_SCRIPTS=/opt/oracle/scripts

if [ -f $ORA_SCRIPTS/$ORACLE_HOME/$ORACLE_SID-shutdown.sql ]; then
    $ORA_SHUT = $ORA_SCRIPTS/$ORACLE_HOME/$ORACLE_SID-shutdown.sql
else
    $ORA_SHUT = $ORA_SCRIPTS/generic/shutdown.sql
fi
$ORA_SCRIPTS/shutdown.sh

shutdown.sh

#!/bin/sh
echo `date`: shutting down $ORACLE_SID.
$ORACLE_HOME/bin/sqlplus /nolog @$ORA_SHUT
if [ $! != 0 ]; then
    echo Impossible to shut down $ORACLE_SID
    exit -1
fi
echo $ORACLE_SID has been shut down.

shutdown.sql

connect / as sysdba
shutdown immediate;
quit;

Most of the time, you can just copy the first script and change the first three lines. And if you have a database with some singularities, put the sql file in the right place to use it instead of the generic one.

I would suggest investigating 'cron' as soon as possible, you will probably find /etc/crontab tells you a few "useful" things and there are shortcut directories for regular intervals:

- /etc/cron.hourly
- /etc/cron.daily

Additionally 'crontab -u -l' will show the crontab for a user and this is normally stored under /var/spool/cron with Linux. None of this is specific to Oracle however it is essential for getting jobs to run automatically and reliably, very helpful for conducting database snapshots and backups, etc etc etc.

Scripts for startup and shutdown are normally hiding in '/etc/init.d' and are often provided/installed by the software package upon initial installation.

If you could edit your question to include more details about your environment, then that will enable someone to give a more complete answer to your problem :-)

• Linux distribution
• Software versions

I would recommend using a scripting language rather than shell scripts for more complex tasks. Having a persistent connection to the database through multiple disparate tasks is much easier when you can establish a database handle. In shell scripts, you tend to call multiple command line tools, each of which must establish a connection to the database which is subsequently torn down when the tool finishes running.

O'Reilly has a book that covers Perl and Oracle specifically from a DBA perspective as opposed to application developers:

http://oreilly.com/catalog/9780596002107/

That you may find quite helpful.

Changing env. is pretty simple, you could use a few aliases to switch your ORACLE_HOME and PATH to point to different database installations:

alias dbprob='export ORACLE_HOME=/ora/app/10.2/dbprod'
alias dbtest='export ORACLE_HOME=/ora/app/10.2/dbtest'

As for a simple start/stop script, look at the following URL, at the 'oracle.sh' script which is a native RHEL startup script

Just to add, get a book on shell scripting and make sure you have a good working knowledge of this. Sh and its derivatives are quite a bit better for scripting than windows CMD files - the underlying paradigm is not that different but it works somewhat better.

One of the other posters mentioned perl, for which a wide variety of libraries are available to work with Oracle. Look up www.cpan.org and hunt around. Python also sports quite a lot of Oracle libraries, of which perhaps the most interesting is Anthony Tuninga's CX_Oracle and its associated tooling.

If you want to come up to speed on Linux/Unix, this Stackoverflow posting has links to many resources and a list of good books on various aspects of the system. There are many, many books on Oracle, of which I think Tom Kyte's Expert one-on-one Oracle and Effective Oracle by Design are a good start.

Finally, if you are working with Oracle, get a working knowledge of the data dictionary. This is exceedingly useful, and (IMHO) quite a bit better than the one on SQL Server.

You should also look at Oracle Enterprise Manager/Grid Control for managing your database environment. It has a rich set of management capabilities predefined for you (including such tasks as startup/shutdown of an instance). Just another thought.

Have a look also at oraToolKit - The Swiss Army Knife for Oracle.

Its quite good, it helps with Oracle installation tasks on various platforms, startup/shutdown using their "appctl" and various backup tasks or options.