1

I am more familiar with Windows so Linux is a rather strange place for me at present, so please exuse me if this is a dumb question.

Basically, i would like to know if anyone has any suggestions and/or recommendtations for writing scripts to shutdown/startup, backup and a number of other regular DBA tasks.

I would also appriciate any guidence on how to set up scripts to change envionments/homes in a multi-homed environment.

One thing i did forget to mention its default shell for RHEL 5.

thanks.

7 Answers 7

5

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.

3
  • Brilliant, thats what exactly what i was looking for.
    – OilyRag
    Jun 28, 2009 at 14:57
  • 1
    I think the DBA group on *nix is just DBA and that ORA_DBA is the windows equivalent.
    – DCookie
    Jun 29, 2009 at 18:15
  • Note that Oracle already installs dbshut and dbstart shell scripts.
    – Gaius
    Oct 26, 2010 at 10:54
0

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
2
  • I was looking for more information and any possible pitfalls in writing the actual scripts to perform the tasks. getting them to run automatically will be required but not in the first instance.
    – OilyRag
    Jun 28, 2009 at 11:53
  • Ah - okay :) I would strongly suggest using the full path to any programs you call, for example '/usr/bin/php arguments' rather than just 'php arguments' - they may both work in testing however the PATH environment variable might turn out to be different when run in cron!
    – nixgeek
    Jun 28, 2009 at 11:54
0

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.

0

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

0

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.

0

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.

2
  • After a lot of time, frustration and pain we did get Grid control installed and managing our environment. However, the simplicity of a handful of commandline scripts that both DBA's and sys ops can run in time of trouble is priceless especally as we have had issues with availabilty of our DB's and management console when our systems have gone down. Thankfully only on our Test environments.
    – OilyRag
    Jun 29, 2009 at 18:54
  • I agree, GC is a bit touchy, and is rather poorly documented. And until you get to 10.2.0.4 and above it's a bit buggy, too.
    – DCookie
    Jun 29, 2009 at 20:10
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .